Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
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
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
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.
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
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
