Accounting Chapter 4 Homework Then Type The Cell Addresses Numbers That

1

EXCEL TEMPLATES

OPERATIONS GUIDE

EXCEL TEMPLATES were developed by McGraw-Hill/Irwin to accompany FINANCIAL

ACCOUNTING, Fourth Edition, by David Spiceland, Wayne Thomas, and Don Herrmann. It is

designed to provide you, the student, with experience in using microcomputers in accounting by

working selected problems at the end of chapters, using spreadsheet templates developed for

Excel. The purpose of this guide is to provide general software operating instructions as well as

to provide specific help in completing Excel Template problems. The guide is divided into five

sections:

Using Excel: This section is an abbreviated guide to using Excel spreadsheet

software. It is not meant to be a comprehensive user’s manual; rather, it is an

introduction to the terminology, features, and keystrokes necessary to work with

Excel Templates.

Installation and Configuration: This section will lead you through the steps

necessary to copy Excel Template problems on your computer’s hard drive.

USING EXCEL

WHAT IS AN EXCEL SPREADSHEET

An Excel spreadsheet is a grid of 16,384 rows and 256 columns that allow you to enter and

manipulate numbers and text and to create graphs.

SCREEN TERMINOLOGY (Refer to Figure 1.)

Toolbar – Key #1

The row of buttons below the menu bar of the application window gives quick access to

commands and tools such as bold, italic, and formatting.

Formula Bar – Key #2

The location below the toolbar where text, values and formulas are displayed as they are

entered and edited.

Document Window – Key #3

Excel allows more than one document to be open simultaneously. Therefore, each

Rows – Key #4

Rows are designated by a number along the left side of the document window. The rows

are numbered from 1 through 16,384.

3

Ranges

A range is one or more contiguous cells that have been selected. It is identified by the

upper left and lower right cell addresses separated by a colon. For example, A1:F15 is

the rectangular block of cells covered by this address range. Many features in Excel

require that a range of cells be selected before performing an action.

Active Cell – Key #7

Data can only be entered into one cell at a time. This cell is referred to as the active cell,

recognizable by a heavy border surrounding it. The mouse, arrow keys, and various key

Figure 1

4

Cell Pointer – Key #7

The cell pointer highlights the active cell in the spreadsheet. You may move the cell

Status Line – Key #8

The status line is a bar at the bottom of the screen that indicates what Excel is prepared to

do next. It will show prompts, explanations, and guidance for current actions.

Indicators – Key #9

MOUSING AROUND THE SPREADSHEET

Excel is designed to be used with a mouse. Most actions are performed with the left mouse

button, including selecting all menu options. The right mouse button is used only to bring up

shortcut menus in various places in the Excel window. This manual, unless otherwise indicated,

refers to clicking the left mouse button.

There are three main actions performed with the mouse:

2. Drag. Point at something on the screen, click and hold down the left mouse button and

3. Double click. Press the mouse button twice in rapid succession. This action will produce

The following mouse actions may be used to move through a document window:

Use the vertical and horizontal scroll bars at the right and bottom of the document window to

move through the spreadsheet. NOTE: Using one of the scroll bars does not activate a cell or

range of cells; it is simply used to view sections of the document.

Click on a particular cell to activate it.

5

Click and drag the mouse to select a range of cells.

Click on the letter at the top of each column to select the entire column. Click and drag across the

letters to select more than one column.

CELL POINTER MOVEMENT USING THE KEYBOARD

In addition to using a mouse, you may also use the keyboard to move around the spreadsheet

(refer to Table 1).

Table 1 – Common Keyboard Movement

Excel

ACTION

 or  Arrow

Moves the cellpointer up or down one cell.

 or  Arrow

Moves the cellpointer left or right one cell.

Ctrl-,,,

Moves the cellpointer to the intersection of a blank and nonblank cell.

LAUNCHING THE PROGRAM

Click the Start button, point to Programs, and then click on the Microsoft Excel program icon.

6

MENUS

Main Menu

To access a Menu, click on the menu

item with the mouse. Next click on the

desired option in the menu to activate it

(refer to Figure 2.)

Dialog Boxes

Items on a menu followed by ellipses (…) will display a dialog box where more action

must be taken to complete the option. Items without ellipses will execute immediately.

Within a dialog box there may be many

7

Shortcut Menus

Clicking the right mouse button accesses

shortcut menus. These menus provide

convenient and efficient means of making

Excel user manual.

FORMATTING CELLS

Select the cell or range of cells to be formatted. Choose Format, Cells… to bring up the Format

Cells dialog box (refer to Figure 3.) You may also click the right mouse button while you have

selected a cell or range of cells to bring up a shortcut menu (refer to Figure 4.) Click on Format

Cells… on this menu and the Format Cells dialog box will appear. Click on the Number Tab.

The left side of the box lists the various numeric format categories while the right side of the box

lists any formatting options. Scroll through the options and double click on the desired format to

apply it to the selected cells. Table 2 includes examples of numeric formatting:

Table 2 – Numeric Cell Formats

DISPLAY

FORMAT

2.47 or 5673

General (default)

18,564.00

Number, 2 decimal places, comma selected

$14.96

Currency, 2 decimal places, dollar sign selected

Percent, 1 decimal place

3/3/95

Date

6.45E+00

Scientific, 2 decimal places

NOTE: Phone numbers, zip codes, and social security numbers are not generally considered

8

numbers. Excel assumes entries such as 555-1212 or 97405-1234 are text unless an operator,

such as an equal sign (=), is placed in front of the entry.

CONTENTS OF A CELL

Cells may contain values, formulas, functions or text.

Values

Values are usually entered as numbers. When entered into a cell, values are right justified

by default. This data can be used in mathematical calculations.

To enter a numeric value, select a cell or range of cells, type the number, then press

<ENTER>. Numeric formatting in Excel is applied to a cell or range of cells rather than

to the data itself. When a value is placed in a cell, it will be shown with the current cell

formatting. If you want the number 1234 in a cell to represent dollars, type 1234 and

press <ENTER>, then apply formatting to the cell (refer to Table 2 in the “Formatting

Cells” section above.)

NOTE: You need not enter zeros after the decimal point. Excel will handle this for

you based on the number of decimal places specified when setting the

numeric format for the cell.

Formulas

Formulas perform mathematical calculations on the numerical contents of a cell or range

of cells. All formulas must start with an equal sign (=). Below, Table 3 lists the

mathematical operators that can be used in a formula:

Table 3 – Mathematical Operators

OPERATOR

OPERATION

^

Exponentiation

Addition

Subtraction

*

Multiplication

Division

Division, integer result

9

within parentheses are performed first. If there is more than one occurrence of any

arithmetic operator, the calculations occur from left to right.

To create a formula, begin with an equal sign (=). Then type the cell addresses or

numbers that you wish to include in the calculations with the appropriate operators and

parentheses. Spaces are not valid in a formula. Table 4 displays some common types of

formulas.

NOTE: You may use actual numbers in a formula, but if there is already a cell

with that value in it, use the cell address.

Table 4 – Typical Formulas

FORMULA

ACTION

=A7+B7+C7

Adds contents of the listed cells.

=D10/C10

Divides contents of the listed cells.

Functions

Functions are special formulas built into Excel that perform complicated

calculations. They begin with an equal sign (=) followed by the function name,

=F15*G2

Multiplies the contents of the listed cells.

10

Common Functions

=SUM(list)

Use: Adds the values in the given list of cells.

Example: =SUM(A1:A45)

=COUNT(list)

Use: Counts the number of cells in a range. Cells containing text or

blank cells are not counted.

Example: =COUNT(G3:K3)

Counts how many numbers are in cells G3 through K3.

=PMT(rate,nper,pv,fv,type)

Use: Gives the periodic payment for an annuity. The arguments fv and

type are not mandatory.

C3 and D3.

=IF(condition,true_expression,false_expression)

Use: Performs decision-making tests in situations with at least two

possible outcomes.

Dates in Excel

To enter a date, type it in using one of the suggested formats listed in Table 5. To

change the way the date is displayed, use Format Cells from the menu bar or

12

Point Method to Create Formulas and Functions

To build formulas and functions, use the point method. Type the equal sign (=) to

begin the formula, and then use your movement keys (mouse or keyboard) to

select the cell you want to reference. Notice that the cell address now appears in

the formula bar. Type the next operator (+, -, /, *, etc.). You may then move to the

next cell you want to reference. Repeat this procedure until you have built the

formula you want, then press <ENTER>.

For functions, you may also use the point method. Type in the function name and

opening parenthesis [e.g., =SUM(]. Select the first cell of the range of cells that

you want to sum. That address will appear in the function [e.g., =SUM(A1]. Click

and drag on the desired range of cells and the range will appear in the formula bar

[e.g., =SUM(A1:A10]. Close the parentheses and press <ENTER> [e.g.,

=SUM(A1:A10)].

NOTE: Each time you select a cell or range of cells, a marquee will appear

around the selection, illustrating what range will be used in a

formula.

AutoSum Button ()

Use the AutoSum button on the toolbar to enter the Sum function. Select the cell

in which the Sum function will appear. Click once on the AutoSum button ().

Text

Text is all other data entered in a spreadsheet and is usually used to describe values.

Excel determines that data is text if the first character of the data is a letter of the alphabet

(except dates; refer to the “Dates in Excel” section above.)

13

EDITING CELL CONTENTS

Re-entering the data in a cell is one way to edit a cell. You may also select the desired cell and

click inside the Formula bar to place your cursor in the cell contents. In addition, you may press

the edit function key (F2), or double-click on the cell to edit data directly in the cell.

Table 6 shows keyboard movement that assists with editing:

Table 6 – Editing Keys

KEY

ACTION

 or 

Moves the cursor 1 character left or right.

CTRL- 

Moves to the next word or reference.

COPYING CELL CONTENTS

Copying from One Cell to Another Cell

CTRL- 

Moves to the previous word or reference.

Moves the cursor to the end of the edit line.

Ends the edit mode.

Moves to the beginning of the edit line.

Toggles between insert and typeover mode.

Deletes 1 character to the left.

14

Copying from One Cell to Many Cells

Select the source cell and choose Edit, Copy, or you may click on the Copy button on the

toolbar. A marquee will then appear around the cell. Select the destination range and

choose Edit, Paste, or click on the Paste button on the toolbar.

Copying from Many Cells to Many Cells

MOVING CELL CONTENTS

Select the source cell or range of cells and choose Edit, Cut, or click on the Cut button on

CHANGING COLUMN WIDTH

If the column width is too narrow for the text, it will exceed the boundaries of the column. If

there is data in the cell next to it, the text will appear truncated. When the column width is too

narrow for a value and the cell format is general, the value will appear in scientific notation (e.g.,

1.2E+09). All other formats will appear as pound signs (#####) when the value is too large.

INSERTING COLUMNS AND ROWS

Use Insert to add blank cells, ranges of cells or entire blank rows or columns. Excel shifts the

other cells of the worksheet to make room and adjusts references in formulas to reflect the new

locations. Excel applies appropriate formats to the inserted cells if the surrounding cells have

formatting. Rows are inserted above the active cell(s) and columns are inserted to the left of the

active cell(s).

Insert an Entire Row or Column

Select a cell or range of cells below (or to the right) where the row(s) or column(s) is to

be inserted. Click Insert, then Column or Row. Excel will insert an entire blank row or

column in the worksheet. If more than one cell was selected across a row or down a

column then a corresponding number of rows or columns will be inserted.

Insert a Partial Row or Column

Select a cell or range of cells below (or to the right) where the partial row(s) or column(s)

SORTING

Sorting is used to rearrange data in the worksheet

using sort keys. The first sort key sorts all of the data,

the second and third sort keys sort within the previous

key. To sort rows using up to three columns as your