Accounting Excel Guide Then Type The Cell Addresses Numbers That

1

EXCEL TEMPLATES

OPERATIONS GUIDE

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

ACCOUNTING, Fifth 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

2

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

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.

Columns – Key #5

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,

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.

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:

1. Point and click. Point at something on the screen and click. This action will normally

select something such as a menu item, cell, or file name.

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

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.

To move to a particular cell in the spreadsheet, choose Edit Goto or press the F5 function key to

bring up the Goto dialog box, and enter the desired cell address in the Reference box.

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.)

With the keyboard, you may access the

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

options from which to choose (refer to

7

Shortcut Menus

Clicking the right mouse button accesses

shortcut menus. These menus provide

convenient and efficient means of making

changes in the spreadsheet. Shortcut

Excel user manual.

FORMATTING CELLS

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

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.

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

The standard precedence for arithmetic calculation is multiplication and division before

addition and subtraction unless there are parentheses, in which case, the calculations

9

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.

=F15*G2

Multiplies the 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,

then arguments surrounded by parentheses. Function syntax consists of:

=fcnname(argument1,argument2,…,argumentn). The arguments are information

Common Functions

=SUM(list)

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

=AVERAGE(list)

Use: Calculates the mean average of a group of numbers. This function

interprets any cell containing text as a zero value and ignores blank

cells.

=COUNT(list)

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

blank cells are not counted.

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

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

type are not mandatory.

11

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

shortcut menu. For more information, see the Excel user manual or online help

screens.

Table 5 – Typical Date Formats

DATE

3/4/99

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>.

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.

After making desired changes to the cell contents, press <ENTER>. If you have made a change

and decide you do not want it, press ESC to return to the ready mode.

COPYING CELL CONTENTS

Copying from One Cell to Another Cell

Select the source cell (the cell you want to copy) and choose Edit, Copy or you may click

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

MOVING CELL CONTENTS

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

the toolbar. A marquee will appear around the cell. Select the upper left cell in the destination

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

NOTE: Because the destination will assume the size of the source, select only the upper

left cell of the destination range if more than one cell is being moved or copied.

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

15

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

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)

is to be inserted. Click on Insert, Cells. The Insert Dialog box will appear. Click on Shift

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