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