Efficiency Techniques

in Excel

EXCEL REVIEW
2001-2002
 This handout is meant to serve as a
quick review of some of the principal features of Excel.
It’s not meant to cover all Excel’s features
or to treat them in any depth.

This guide may jog your memory

about a feature or two you might have used and then forgotten
or introduce you to something new
in Excel that you’d like to explore further.

If you’re not already familiar with a feature described here

and you think it might be useful to you, I hope you’ll
consult Excel’s online help or a good reference guide
for a complete description.

Examples and illustrations are drawn from Excel 2000.

Paula Ecklund
Spring 2001
Contents

Page

I. Workbook Organization
The workspace................................................................................................................. 1
The document window .................................................................................................. 2
Toolbars............................................................................................................................ 3
Worksheet tabs: adding, deleting, naming, reordering............................................. 5
Copying and moving worksheets ................................................................................ 6
Configuring the Excel environment to suit you ......................................................... 7

II. Data Entry & Edit

Determining the Enter key order of movement ......................................................... 8
The formula bar............................................................................................................... 9
Formats and when to apply them ................................................................................ 10
Fill and Extend ................................................................................................................ 11
Why name cells & ranges?............................................................................................. 13
Validation......................................................................................................................... 16
Special copying ............................................................................................................... 17
Transposition................................................................................................................... 18
Text to Columns.............................................................................................................. 19

III. Getting Basic Information

The status bar .................................................................................................................. 21
The title bar...................................................................................................................... 22
Online help ...................................................................................................................... 23
Go to particular objects or types ................................................................................... 26

IV. Arranging & Viewing the Worksheet

Adding & deleting rows & columns ............................................................................ 27
One- and two-way splits................................................................................................ 28
Viewing multiple windows (& moving between them) ........................................... 29
Moving & selecting: power keyboard techniques...................................................... 30
Zoom................................................................................................................................. 31
Custom views.................................................................................................................. 32
Combining views & scenarios: the Report Manager ................................................. 33
V. Calculations
Recommendations .......................................................................................................... 34
Copying formulas: Relative & absolute addressing .................................................. 35
Using built-in functions ................................................................................................. 38
Analysis Toolbox ............................................................................................................ 40
Referencing across sheets or books .............................................................................. 42
IF and the Logical Functions ......................................................................................... 44

VI. Debugging
Testing your model (with small numbers).................................................................. 46
Audit features.................................................................................................................. 47
Handling error values .................................................................................................... 48

VII. Data Management Essentials

Sort .................................................................................................................................... 49
Subtotal............................................................................................................................. 50
Outline.............................................................................................................................. 51
AutoFilter......................................................................................................................... 52
Data Table ........................................................................................................................ 53
Pivot Table ....................................................................................................................... 55

VIII. Documenting
Adding comments to cells ............................................................................................. 58

IX. Format Tips

Row & Column Autofit.................................................................................................. 60
Autoformat ...................................................................................................................... 61
The Formatting Toolbar: Highlights ............................................................................ 63
Conditional Formatting ................................................................................................. 65
The Drawing Toolbar ..................................................................................................... 66
Styles: Changing & Creating (within a workbook).................................................... 67

X. Charting Basics ...................................................................................................................... 68

XI. Printing Basics
Fit to page......................................................................................................................... 72
Headers & footers ........................................................................................................... 73
Printing part of a worksheet.......................................................................................... 74

XII. Other Resources ................................................................................................................ 75

I. Workbook Organization

The Workspace
Excel’s workspace is the window that holds the Excel menus, toolbars, status
area, and workbook documents.

You can have more than one workbook open at the same time. You can have
more than one view of the same worksheet document visible at the same time.
Use Window, New Window to get a second worksheet view.

Use Window, Arrange, Tile to see multiple sheets or books at the same time.

1 Workbook Organization
 The Document Window
A workbook may be displayed in a document window within the Excel
workspace. A document window is distinct from the workspace window, as in
the illustration below. Here, both the workspace window and the document
window have their own title bar.

Clicking the expand button at the upper-right-hand corner of the document

window merges the document window with the workspace window. With this
arrangement, they share title bars. You get a little more screen space as a result.

The workspace window

and document window
share a title bar. The
document file name is
still visible.

2 Workbook Organization
 Toolbars
Excel has twenty-four preconfigured toolbars. Most toolbar buttons are shortcuts
to commands also on Excel’s menus. Excel usually opens with at least the
Standard toolbar visible right below the menus.

A list of the fourteen most commonly used toolbars displays if you

right-click any gray (unoccupied) area at the top of the Excel
workspace (or choose View, Toolbars from Excel’s menus). The
checkmark to the left of a toolbar name indicates that the toolbar is
currently toggled “on”, meaning that it’s visible. Click a toolbar
name in this list to toggle it on or off.

Choose the commands View, Toolbars, Customize to open the dialog box that
shows the complete list of Excel toolbars. Again, checked toolbars are the toolbars
currently visible in your Excel workspace.

You can customize any toolbar or construct your own using the View, Toolbars,
Customize command.

3 Workbook Organization
 Toolbars normally “dock” at the top or bottom of the display. But if you prefer
you can drag and shape any toolbar to make it float in the workspace. In the
illustration below the Formatting toolbar floats over the displayed worksheet.

The Formatting toolbar arranged as a rectangle floating over the worksheet.

4 Workbook Organization
 Worksheet tabs: adding, deleting, naming, reordering
When you open a new workbook Excel automatically provides you with one or
more worksheets. Each worksheet is on its own tab. The number of tabs depends
on how you have your Excel configured (Tools, Options, General, Sheets in new
workbook).

Add a new worksheet tab with Insert, Worksheet.

Delete a worksheet tab by making that worksheet current and clicking Edit,
Delete Worksheet.

Name a worksheet tab with Format, Sheet, Rename. (Or, right-click the tab name.)
The tab name appears in reverse video; enter a new name to replace the old one.

Reorder the sheets in your workbook using drag and drop. For example, to move
“Sheet 3” to the left of the “Expenses Sheet” in the illustration above, point to the
“Sheet 3” tab name, hold down the left mouse button, and drag to the left of the
“Expenses Sheet” tab. Release the mouse button.

Use the left- and right-facing triangles to the left of the tabs to move through a
view of all the worksheet tab names.

5 Workbook Organization
 Copying and moving worksheets
Make the worksheet you want to move or copy the current worksheet. If you’ll
be dealing with more than one workbook (e.g., moving or copying from one
workbook to another), make sure all workbooks are open.

From the menus select Edit, Move or Copy Sheet to open the “Move or Copy”
dialog.

The default is to move the current worksheet. Click the “Create a copy” box at the
bottom left of the dialog if you want to copy instead of move.

The default is to remain in the current workbook. Use the drop-down arrow to
the right of the “To book” option to select another workbook as the target for the
operation.

Use the “Before sheet” option to specify the location of the moved or copied
worksheet relative to the other worksheets in the target workbook.

6 Workbook Organization
 Configuring the Excel environment to suit you
Most of Excel’s user preference information is stored in a big, multi-tabbed
“Options” dialog box. Select Tools, Options to open this dialog.

Examples of some of the options you might want to set:

On this tab Set

General The number of sheets in a new workbook

The standard font and font size
User name (used on comments, change tracking, etc.)

Edit Whether or not to allow cell drag & drop

Whether or not to allow editing directly in cells

View How you want Excel to display comments

Whether to display gridlines, row/col headers, zero values, etc.

Calculation Whether to use manual or automatic workbook calculation

The number of iterations in a goal seek operation

Color Standard chart marker fill colors

7 Workbook Organization
II. Data Entry & Edit

Determining the Enter key order of movement

One user-configurable option in Excel is where the next current cell will be
located after you hit the enter key. The default after you hit the enter key is for
Excel to move down one cell, but you can instead choose to have Excel move one
cell up, one cell right, or one cell left instead. You might change the default to
better accommodate data entry requirements for a particular task.

Set this option by selecting Tools, Options, Edit and choosing from the drop-down
list in “Move selection after Enter, Direction”.

8 Data Entry and Edit

 The Formula bar
Excel’s Formula bar is located directly above the worksheet document window.
The Formula bar does three things:
1. At right: Displays the contents of the current cell. For a cell holding a formula,
it shows the formula, not the evaluated value the worksheet itself
displays. In the example below, Cell D3.
2. At left: Displays the cell reference or name of the current cell or range. This
location is called the “name box”. In the example below the current cell
(Cell D3) is named TotalSales so that name displays in the Formula bar
name box.
3. As you enter a formula: Displays a red X mark and a green check mark to
help you conveniently confirm or cancel the operation.

Even if you have several worksheets visible in the Excel workspace at one time,
there’s only one Formula bar. It applies to whichever worksheet has the focus.1

The formula bar in this

illustration refers to the
current cell in the
sample.xls file because
that file has the focus.

1 You can tell which worksheet has the focus because its title bar is emphasized. Move the focus
from one sheet to another by clicking anywhere within the sheet you want to move focus to.

9 Data Entry and Edit

 Formats and when to apply them
The “Format Cells” tabbed dialog includes all Excel’s formatting options. Open
this dialog with the Format, Cells commands.

Many of the options in this dialog are also available more conveniently on the
Formatting toolbar, a portion of which is shown here.

Excel permits you to format data as you enter it, or afterwards. You can also
“pre-format” empty cells in anticipation of adding data later.

The “Format Painter” is a formatting tool so useful that it’s located on the
Standard toolbar instead of the Formatting toolbar. Use it to quickly copy an
existing format to other data.

For example, if you’ve elaborately formatted a range with a special typeface, font
size, border, alignment, and background color, you could transfer that format to
data elsewhere in your spreadsheet using the Format Painter button. Click once
anywhere in the range that includes the format-to-copy. Click the Format Painter
button. Then drag over the new range you want to apply that format to.

10 Data Entry and Edit

 Fill and Extend
Excel makes it easy to copy data quickly. In the example below, the value 5000 is
entered into Cell A1. Dragging the “fill box” (the small black box that appears in
the lower-right-hand corner of the current cell) down to Cells A2:A5
automatically fills these cells with the same value.

Or, to accomplish the

same result, highlight
the value to copy and
the range to copy to
and select Edit, Fill,
Down.

A useful variation is available. The Edit, Fill, Series command opens the “Series” dialog
which allows for more than a simple copy.

Two Edit, Fill, Series examples:

Date, incremented by year. Growth with a step value of 5.5.

11 Data Entry and Edit

 “Extend” is similar to “fill”, but isn’t confined to numbers. Excel has its own set
of built-in text lists you can extend. And, you can “teach” Excel how to use the
extend feature for any list you may devise. For example, entering the value
January in Cell A1 and dragging the Cell A1 fill box down through Cell A6
results in a list of months of the year. Excel has the months of the year stored as a
built-in extensible list.

Lists are stored in the Options dialog (Tools, Options, Custom Lists). Days of the
week and months of the year are standard. Any list of your own that you add to
this dialog is available to any worksheet you open.

12 Data Entry and Edit

 Why name cells & ranges?
Naming key cells can make your worksheet easier to read. Excel will use names
in place of cell references in formulas. In the example below, the formula bar
displays the formula in Cell E3 as =TotalSales*TaxRate. Without cell names, the
formula would display as the less comprehensible =D3*B1.

If you assign names to cells after having used those cells in calculations, Excel
doesn’t automatically replace cell references in the calculations with the new cell
names. However, you can over-ride this default and have new names used post-
formula-creation with the command Insert, Name, Apply.

Quickly name a cell or range by selecting it and entering the name in the “Name
Box” at the left of the formula bar. The Name Box displays the current cell or
range reference or the current cell or range name, if one is assigned.

Name a cell or range to be able to go to it quickly using the F5 “Go To” key.
Hitting F5 opens the Go To dialog box.

13 Data Entry and Edit

 Range names for an Excel list (like the simple one below) can be used to
simplify references and in calculations.

In this example, select the list (the range B3:C7 in the illustration below)
and use Insert, Name, Create to open Excel’s “Create Names” dialog. Excel
guesses where the likely names are in relation to the data. In this case, the
data is in C4:C7 and the names come from the top row and left column.

With names established, you can use a “shorthand” reference to refer to the
numeric cells. Cell C4 can also be called January A since it falls at the
intersection of those row and column names. In like manner, Cell C5 can also be
referred to as January B.

Cell E5 contains the formula =January A and displays the value 10.
Cell E6 contains the formula = January A + January B and displays the value 25.

There’s another way to name a range whose values you intend to use
in a calculation: By using the “Label Ranges” dialog. In this example, Year99
imagine you have a list of values like the one at right, with the label 10
“Year99” at the top of the column. Select the range (including the 30
label) and choose Insert, Name, Label to open the “Label Ranges” 50
dialog. 50

Excel fills in the label range.

Specify that you’re using
“Column labels” and click OK.

14 Data Entry and Edit

 You can now use the “label range name” in calculations.

For example, this formula:

=SUM(Year99)

returns the value 140.

The formula
=SUM(Year99, 100)

returns the value 240.

As a quicker alternative to using the Insert, Name, Label command, you can select
a range (not including its header) and use the Name Box to assign a range name.
Then use that range name in calculations, as above.

If you assign a cell or range a name and then want to change it, you can. But
Excel offers no simple method. The only way to change a range name is to use
Insert, Name, Define, add the new name and then delete the old name.

15 Data Entry and Edit

 Data Validation
You may want control over the data values that are entered in a worksheet. To
apply data validation, highlight the range in question and choose the Data,
Validation command. Complete the validation options on the Settings tab of this
dialog.

In the example above, only whole numbers between 1 and 10 are permitted in
the selected range. The user who enters an invalid value sees this message:

Customize the error message using the Data Validation dialog’s Input Message and
Error Alert tabs. For example, this customized message provides information but
doesn’t inhibit data entry:

16 Data Entry and Edit

 Special Copying
The standard Windows Edit, Copy and Edit, Paste commands are available in
Excel. Excel’s alternative Paste Special command extends your options.

An often-used option under the Paste category is paste Values. For example, the
values that display in Column A below are all formulas. In Column B, they’re
actual numbers not formulas, because they’ve been copied with Paste Special,
Values.

Another very useful ability of Paste Special is calculations, available in the

Operation segment of the dialog. In the example above, each cell in the range
D1:D5 contained 1 before copying from B1:B5 and using the Paste Special, Add
option.

17 Data Entry and Edit

 Transposition
You may have data entered in a spreadsheet that you want to transpose. In the
example below, three values are entered in Row 1.

To copy these values and transpose them to column format put the values on the
Windows Clipboard with the Copy command. Select the columnar range you
want the copy to fill. Then select Edit, Paste Special and in the Paste Special dialog
click the Transpose box.

18 Data Entry and Edit

 Text to Columns
It’s easy in Excel to enter long rows-full of data but to
have the data actually reside in a single column. In the 1998 Taxrate 3.2%
example at right, the dates, the text label, and the tax 1999 Taxrate 3.3%
rates should each have been entered in their own cells 2000 Taxrate 5.1%
in order to be most useful. 2001 Projected rate 5.12%

Excel provides a way to change this data arrangement.

Select the range in question (data in a single column) and choose Data, Text to
Columns. Excel opens the “Convert Text to Columns Wizard”.

The Wizard steps you

through three sets of
questions.

The first asks whether

your data is already
arranged as if in columns
or whether the data items
are delimited in some
way.

If delimited, describe the

delimiter(s).

In this example, the data

is irregular so Excel won’t
be able to automatically
arrange it neatly. It will
require some manual
adjustment later.

19 Data Entry and Edit

 The final step lets you
set data formats and
skip particular
columns, if you don’t
need them.

The result is the data parsed into separate cells.

1998Taxrate 3.20%
1999Taxrate 3.30%
2000Taxrate 5.10%
2001Projected rate 5.12%

Note that in this case, because “Projected rate” is two words and we designated a
space as a delimiter, Excel couldn’t arrange the data properly in the last row. A
little manual editing would be required to clean up the converted data.

20 Data Entry and Edit

III. Getting Basic Information

The Status Bar

Located across the bottom of the Excel workspace, the Status Bar provides
information about the current work environment. In the illustration below, the
status bar shows CAPS, NUM, and SCRL to indicate that the Caps Lock, Num
Lock, and Scroll Lock keys are on.

In addition, the Status Bar can provide “on-the-fly” information about data in
your spreadsheet.

For example, if you

highlight a range of
numbers the Status
Bar reports the sum of
those numbers.

Right-clicking the sum

report on the Status
Bar displays a drop-
down list of other
calculation options:
Average, Count,
Count Nums, Max,
and Min.

21 Getting Basic Information

 The Title Bar
The Excel workspace has a title bar and each workbook document you open also
has a title bar. You can display them separately or together.

In the illustration above, the workbook is displayed in its own document

window within the Excel workspace. The workbook has a title bar with the title
sample.xls. The workspace has its own title bar with the title Microsoft Excel.

Clicking the middle “expand” button at the upper-right-hand corner of the

spreadsheet expands the sample.xls document so it shares a title bar with the
Excel workspace. If you’re working with a single worksheet visible at a time, this
option gives you more viewing space. The combined title bars look like this:

22 Getting Basic Information

 Online Help
Excel has extensive online help with a variety of access methods. You can get
online help from within dialog boxes, via the menus, or courtesy of the Office
Assistant.

For example, the “Paste

Function” dialog box displays
a help button. Choose
whatever function you’re
interested in and then click
the help button to open
information about that
function in the online help.

If your Office Assistant is turned on, click the Help Button on the Standard
Toolbar (or hit F1) to “open” it. Then click the Office Assistant cartoon
character to open a space in which to type a question or
term you want help with. Depending on the state of your
spreadsheet when you invoke the Office Assistant, the
Assistant may suggest some relevant help topics.

If you don’t want to see the Office Assistant in the workspace,

right-click and choose Hide.

If you don’t want to use the Office Assistant at all, right-click

and choose Options to open the Office Assistant options
dialog. Then toggle off the “Use the Office Assistant” option.

Tip: Right click the Office Assistant and choose Animate to see the
Assistant’s repertoire of tricks.

23 Getting Basic Information

 While the Office Assistant can be amusing and sometimes helpful, Excel 2000 has
a much improved online help system that’s worth getting to know. You can
invoke it by turning off the Office Assistant and choosing the commands Help,
Microsoft Excel Help.

24 Getting Basic Information

 Excel’s complete online help system displays in two windows: A navigation
(subject) pane left and a topic (content) pane at right. The topic pane or both the
topic and navigation panes can be open on top of the current worksheet, making
it easy to follow online help instructions as you work.

Use the arrow icons

at the upper left to
control the display
of the navigation
pane.

There are three

entrances into online
help: “Contents”,
the “Answer
Wizard”, or the
“Index”.

Underlined
references in the
topic pane are
hyperlinks.

Drag to expand the

panes to whatever
size suits you.

25 Getting Basic Information

 Go to particular objects or types
Excel’s Go To feature provides a quick way to move to a certain location in a
worksheet as well as a way to quickly select a cell or range. An advanced option
of this feature identifies particular objects or types in the worksheet. Click Edit,
Go To or hit F5 to open the Go To dialog.

Click the Special button on the Go

To dialog to open the Go To Special
dialog.

For example, in the

illustration at left there
are two drawn objects on
the worksheet: a square
and a circle.

Edit, Go To, Special,

Objects, OK directs Excel
to select all the objects on
the open worksheet.

Selection is indicated by
the empty selection boxes
appearing at the sides
and corners of each
object.

26 Getting Basic Information

IV. Arranging & Viewing the Worksheet

Adding & deleting rows and columns

Excel makes it easy to rearrange a worksheet. Along with dragging data ranges
to move them, you can add or delete rows and columns.

In the illustration below, add a column for February data by clicking the Column
C header to select it (the entire column appears in reverse video) and selecting
Insert, Column from the menus. Excel inserts a new, empty column to the left.

Inserting a row works in a similar fashion. The new row is inserted above the
row whose header you selected.

Delete a row or column by selecting its header and choosing Edit, Delete from the
menus.

Add multiple rows or columns at the same time by selecting more than one row
or column header before choosing Insert, Row or Insert, Column.

Delete multiple rows or multiple columns at the same time by selecting their
headers before choosing Edit, Delete. If your selections aren’t contiguous, hold
down the Control key as you select them. Because you can’t delete overlapping
selections you must delete rows and columns in separate operations.

27 Arranging & Viewing the Worksheet

 One- and two-way splits
If your spreadsheet holds a large amount of data, its common to lose your view
of the data headers when scrolling down or across the data. Excel provides a way
for you to “fix” headers in place so they always remain visible.

In the illustration below, headers to the left of a spreadsheet survey data are
fixed in place. This is accomplished by using the mouse to drag the vertical “split
box” into place. A horizontal split box is also available and works the same way.

If you choose, you can have both the vertical and horizontal splits in operation at
the same time. (The menu commands Window, Split also turn on a vertical and
horizontal split.) Double-click a split to return it to its docked position in the
document window.

Note that in the illustration above there are now two horizontal scroll bars at the
bottom of the document window, one for each side of the split. Adding a
horizontal split would display two vertical scroll bars. A double split would
result in four scroll bars, two horizontal and two vertical.

28 Arranging & Viewing the Worksheet

 Viewing multiple windows (and moving between them)
You can open multiple window views of the same worksheet or open and view
sheets from several different workbooks at the same time.

The illustration below shows two separate workbooks both open and visible at
the same time in the Excel workspace. Accomplish this with the commands
Window, Arrange, Tiled.

Other arrangement
options are
Horizontal, Vertical,
and Cascade.

Use the command Window, New Window to open multiple views of the same worksheet.
Then use the Window, Arrange command to position them in the workspace.

In the illustration at
left the two windows
into the same
worksheet are
arranged as a
cascade.

Click anywhere in a window view to make that window current. You can tell which
window is current because its title bar is highlighted and it has the scroll bars.

29 Arranging & Viewing the Worksheet

 Moving & selecting: power keyboard techniques
Although you can use the mouse to move anywhere and select anything in a
worksheet it’s often more efficient to leave your hands on the keyboard rather
than switching back and forth between the keyboard and the mouse. Below are
some of the most useful keyboard options for moving and selecting.

To accomplish this Use these keys

Return to the upper left-hand cell of the Control+Home

worksheet (A1).

Move to the edge of the current data region. Control+ arrow key

Move to the beginning of a row. Home

Move to the last cell on the worksheet (at the Control+End

intersection of the right-most used column and
the bottom-most used row in the lower-right
corner).

Move up/down/left/or right one screen. Page up/down/left/or

right

Move to the last nonblank cell in the same End + arrow key
column or row as the active cell.

Select and extend the selection to the last Control+Shift+ arrow key
nonblank cell in the same column or row as the
active cell.

Select the entire column. Control+Spacebar

Select the entire row. Shift+Spacebar

Select the entire worksheet. Control+a

If you have more than one workbook open at Control+F6

the same time, switch to a view of the next
workbook document.

30 Arranging & Viewing the Worksheet

 Zoom
Excel’s zoom option lets you adjust your view of the worksheet in or out. Adjust
either with the menu View, Zoom or by clicking the Zoom tool on the Standard
Toolbar.

The default is 100%.

Zoom out to get a good overview of a worksheet’s layout. Zoom in when you’re
working with graphical objects or an exacting layout.

31 Arranging & Viewing the Worksheet

 Custom views
Excel lets you save the current appearance of a worksheet or workbook in a
special “view” so you need not change the layout and print settings every time
you view or print the sheet or book.

To create a view, start by setting up the worksheet or entire workbook to appear

the way you want to view and print it. Then select View, Custom Views from the
menus to open the Custom Views dialog box.

Any views already created are listed in the Views box. Select a view in the list and
click the Show button to have Excel display that saved view.

To add a new view to the workbook, click the Add button and complete the Add
View dialog.

32 Arranging & Viewing the Worksheet

 Combining views & scenarios: The Report Manager
Excel’s Report Manager lets you combine any combination of saved views and
modeling scenarios into a report that you can print. Access the Report Manager
by choosing View, Report Manager2 from the menus.

To add a new report click the Add button and enter a name for the report in the
Report Name box that displays. Click Add.

In the Sheet box, click the

sheet you want for the first
report section.

In the View box, click the

view (if any) you want for
the first report section.

In the Scenario box, click

the scenario (if any) you
want for the first report
section.

To add another section to

the report click Add.

Repeat until you’ve created

all of the sections you want
in the report.

Excel prints the sections of a report in the order in which they’re listed.

2If the Report Manager isn’t available, select Tools, Add-ins and add it as an option to your Excel
configuration.

33 Arranging & Viewing the Worksheet

V. Calculations

Recommendations
Calculations express relationships between values, both known and variable.
Avoid using numbers (actual data) in Excel formulas. Instead use cell references
that point to cells that hold the data. By keeping each data item in a unique
location in the worksheet you can much more easily change an item if necessary
and have the change be reflected wherever the item is used throughout the
worksheet.

In the illustration below a tax rate value is stored in Cell B1. The calculations in
Column D all use that tax rate value. Instead of including the actual value of 5%
in the formulas, a reference to that cell is made. The formulas in Column D are:

=(B4+C4)*$B$1
=(B5+C5)*$B$1
=(B6+C6)*$B$1

Should the tax rate change from 5% to 6% only the value in the tax rate cell, B1,
must change. The formulas that use the tax rate refer to the cell, so they need not
be changed.

34 Calculations
 Copying formulas: relative and absolute addressing
You may often want to use the same formula in one or more places in a
worksheet. It’s most efficient to build the formula one time and then copy it to
the other locations where you need it. If you copy a formula, you must
understand Excel’s relative and absolute addressing.

For copied formulas Excel uses relative addressing as the default. That is, Excel
adjusts the cell references in your source formula when the formula is copied to a
new location.

In the illustration at left below, the formula in Cell D4 is used to calculate the
total sales in January and February for Dept 1: =(B4+C4). We need the same
basic formula in Cells D5 and D6 for Depts 2 and 3. Although we could enter two
new formulas, it’s easier to copy the formula in D4 to these locations.

Point with the mouse to the

“fill box” at the lower right-
hand corner of Cell D4 and
drag down through Cells D5
and D6 to copy the formula.

For each copied formula, Excel automatically adjusted the cell references to refer
to the data in the correct row. This is Excel’s default mode when copying
formulas. It’s known as relative addressing.

35 Calculations
 There may be occasions when you want to override Excel’s relative addressing
default. In the example below, the formula in Cell E4 (in the Tax column)
multiplies the Dept 1 Total Sales value (Cell D4) and the 5% value (Cell B1). The
result is correct for this row.

However, if we copy the

formula in E4 down to E5
and E6 for Depts 2 and 3, the
result for those rows is not
correct.

In the absence of any other

instruction, Excel has again
used relative addressing in the
formula copy. The formulas in
Cells E5 and E6 read as follows:
=D5*B2
=D6*B3

The first reference in each of these formulas is OK. We do want the copied
formulas to refer to the Total Sales values for their respective rows (Cells D5 and
D6). The problem is with the reference to Tax in Cell B1. The master formula in
Cell E4 correctly reads =D4*B1. But the copied formula in E5 refers to Cell B2 (an
empty cell), resulting in a calculation of $0.00. And the copied formula in E6
refers to Cell B3 (which holds the text value “Jan”), resulting in a #VALUE error
statement.

To make sure the copied formulas in this case refer to the correct Tax cell, use
absolute addressing for the reference to Tax. Absolute addressing in a formula
fixes the reference to a cell no matter where the formula is copied. Absolute
addressing can apply to a single cell, a range of cells, or to one, several, all, or
none of the cells referred to in a formula.

Specify absolute addressing with a dollar sign (an arbitrarily selected symbol)
before the row reference and the column reference. For example, a relative
reference in a formula to the Tax value in Cell B1 is written as B1. An absolute

36 Calculations
 reference in a formula to the Tax value is written as $B$1. (It’s also possible to
“fix” just the row but not the column by writing B$1 or just the column and not
the row by writing $B1.)

Here we specified absolute

addressing to refer to the Tax
cell in the “master” formula in
E4. Copying the master formula
to E5 and E6 results in correct
values for those formulas.

Tip: When positioned over a cell or range reference on the formula bar, the F4
key cycles through the four addressing options: completely relative, completely
absolute, absolute row but not column, and absolute column but not row. That is:
B1, $B$1, B$1, and $B1.

37 Calculations
 Using built-in functions
Excel includes a large number of built-in formulas, called functions. If you
already know a function you can enter it directly in your worksheet.
Otherwise, click the Function button on the Standard Toolbar or choose
Insert, Function from Excel’s menus to open the Paste Function dialog.

The left-hand side of the dialog displays the various categories of functions. Click
a category to see the list of functions in that category in the list at right. Click a
particular function in the list at right and click the OK button to open a dialog
that will help you complete that particular function.

For example, selecting the Financial category and the PMT function opens this
dialog. For whatever box your insertion point is in, a description is displayed.

38 Calculations
 Excel remembers the functions you last used and groups them in a category
named Most Recently Used. If you’re not sure what category a function you want
might be in, select the All category. All the functions will be listed in the box at
right in alphabetical order.

When you’re working with the dialog for a specific function you can get more
help about that function by clicking the online help box at the lower left-hand
corner of the dialog.

When the Office Assistant

appears, ask for help with the
specific function to open the
Excel help system on that
topic.

Tip: Another way to access Excel’s functions is to start entering a formula by

typing an equals sign and then using the drop-down list of functions that
appears in the Name Box on the Formula bar.

39 Calculations
 Analysis Toolbox
Excel includes a set of data analysis tools called the Analysis ToolPak that you
can use to save steps when you develop complex statistical analyses. The
commands Tools, Data Analysis3 open the Data Analysis dialog that displays the
list of statistical tools.

Select a tool from the list and then provide the data and parameters (if necessary)
for the analysis. The tool displays the results in an output table. Some tools
generate charts in addition to output tables.

For example, to use the Descriptive Statistics tool, first assemble the data to
analyze on your worksheet.

3Excel’s Analysis Tools are an add-in. If they’re not available in your installation of Excel, click
Tools, Add-Ins and select Analysis Toolpak. If the Analysis Toolpak isn’t an option, rerun Excel’s
setup program.

40 Calculations
 Invoke the Descriptive Statistics tool from the Analysis Tookpak. A dialog specific
to that tool opens.

Complete the dialog and click OK to let the tool generate results. In the
illustration above, we specified an output range in the same worksheet for the
results data.

41 Calculations
 Referencing across sheets or books
When you work on a model you may have occasion to want to refer to a cell or
range that’s located in another worksheet or even in another workbook. Excel
provides a means of doing this.

In this illustration two

related but separate
workbooks are open.
The one at right holds
sales data for
Departments 1-3. The
one at left holds sales
data for Departments
4-6.

To copy the data for Dept

4 January sales from Book
2 into Book 1:

1. Open both books and

arrange them side by side.
2. Click in Cell B7 in Book
1 and enter an equals sign
to begin a formula.
3. Click the cell whose
data you want (B3) in
Book 2.
4. Hit the enter key.

A reference to a cell or range in another workbook (an “external reference”) has

this format:

=[WorkbookName]SheetName!CellReference

42 Calculations
 With the method above you point at the cell you want in the other workbook and
let Excel build the external reference for you. If you prefer, you can enter the
reference yourself. And the other workbook need not be open.

Note that if you make the reference to another workbook cell a relative reference
(not an absolute reference), you can copy the external reference to adjacent cells.
The copies in the adjacent will also be external references, but relative.

In the example above, we wrote a formula to make Cell B7 in Book1 an external

reference to Cell B3 in Book 2. The contents of Cell B7 is =[Book2]Sheet1!B3.
Then we used the Cell B7’s fill box to drag and fill the rest of the Jan/Feb Dept 4-
6 data ranges. Each cell in the range is an external reference to a cell in Book2,
relative to the location of the first copied cell, Cell B3.

A reference to another sheet in the same workbook has this format:

=SheetName!CellReference

43 Calculations
 IF and the Logical Functions
Excel’s IF function is probably the most important of the set of Excel’s logical
functions. Excel’s other logical functions are AND, NOT, OR, FALSE and TRUE.

IF checks a condition that must be either true or false. If the condition is true, the
function returns one value; if the condition is false, the function returns another
value.

The IF function has three arguments: the condition you want to check, the value
to return if the condition is true, and the value to return if the condition is false.
The syntax of the IF statement is:

=IF(logical_test,value_if_true,value_if_false)

For example, the formula =IF(12>2,”Correct”,”Not Correct”) would return the

text value Correct. The formula =IF(12<2,”Correct,”Not Correct”) would return
the text string Not Correct.

The condition part of the IF statement can use any comparison calculation
operator. That is, =, >, <, >=, <=, or <>.

Up to seven IF functions can be nested as value_if_true and value_if_false

arguments to construct more elaborate tests. However, a statement with more
than one or two embedded IF functions is hard to read and may be unnecessarily
complicated.

For example, using the data at left, you could use these nested IF statements to
return the correct grade:
If score is Return
IF(Score>89,"A",
> 89 A IF(Score>79,"B",
IF(Score>69,"C",
80 to 89 B
IF(Score>59,"D",
70 to 79 C "F")
)
60 to 69 D )
< 60 F )

That is, in an IF statement in place of the third paramater is another IF statement.

Written on a single line, the statement would look like this:

IF(Score>89,"A", IF(Score>79,"B", IF(Score>69,"C", IF(Score>59,"D", "F"))))

Only the first part of the statement would be evaluated for a score of 90, but the
entire statement would need to be evaluated for a score of 58.

44 Calculations
 If any of the arguments to IF are arrays, every element of the array is evaluated
when the IF statement is evaluated.

The AND function can handle up to thirty conditions that can evaluate to true or
false. This function requires that all its arguments evaluate as true in order for
AND to evaluate to True. For example, the formula =AND(1<5,10>2) evaluates
to True. But the formula =AND(1<5,12<2) evaluates to False.

Like the AND function, the OR function can handle up to thirty conditions.
However, the OR function requires that only one of the arguments evaluate as
true in order to return true. So =OR(1<5,12<2) evaluates to True.

The NOT function changes a TRUE outcome to a FALSE one and vice versa. For
example, =NOT(12>2) returns False.

Excel has additional functions you can use to analyze your data based on a
condition. For example, to count the number of occurrences of a string of text or
a number within a range of cells, use the COUNTIF worksheet function. To
calculate a sum based on a string of text or a number within a range, use the
SUMIF worksheet function.

45 Calculations
VI. Debugging

Testing your model (with small numbers)

During the process of building a spreadsheet model get in the habit of testing it.
It’s easiest to spot possible errors if you use small numbers and numbers (say
multiples of 10) that are relatively easy to spot check.

For example, enter a few small, simple values in the model below makes it easy
to spot any errors in formulas. Then replace the test values with actual values.

46 Debugging
 Auditing features
Excel includes an Auditing Toolbar to help you track how values in your
worksheet are related. Turn on the Auditing Toolbar with Tools, Auditing, Show
Auditing Toolbar4.

The four auditing features accessible from the toolbar are trace precedents, trace
dependents, trace error, and circle invalid data.

The example above shows a trace of precedents for Cell E4.

The example above shows an error trace for Cell E6.

4The Auditing Toolbar does not appear in the list of toolbars that displays when you right-click
the toolbar area at the top of a worksheet.

47 Debugging
 Handling error values
If a formula can’t properly evaluate a result, Excel displays an error value. An
error value can be the result of using text where a formula expects a numeric
value, deleting a cell that’s referenced by a formula, or using a cell that’s not
wide enough to display the result. An error value might not necessarily be
caused by the formula in the cell in which the error value displays. For example,
if a formula evaluates to #N/A or #VALUE!, a cell referenced by the formula
may actually be the source of the error.

Error values and their explanations:

#####
The numeric value or result of the formula entered into a cell is too wide to
display within the cell. Resize the column by dragging the boundary between the
column headings. This error also displays if you use date arithmetic that results
in a negative value. Dates and times in Excel must be positive values.
#VALUE!
Displays when the wrong type of argument or operand is used. For example, if a
formula references a text value instead of a number.
#DIV/0!
When a formula divides by zero.
#NAME?
When a formula contains unrecognized text. For example, if a formula references
a named cell but the name has been deleted.
#N/A
When a value isn’t available to a function or a formula. If certain cells on your
worksheet will contain data that isn’t yet available, enter #N/A in those cells.
Formulas that refer to those cells will then return #N/A instead of attempting to
calculate a value.
#REF!
Displays when a cell reference isn’t valid. For example, if you delete a cell that a
formula refers to the #REF! error message displays.
#NUM!
Displays when a problem occurs with a number in a formula or function. For
example, if you use an unacceptable argument in a function that requires a
numeric argument, the #NUM! error message displays.
#NULL!
Displays when you specify an intersection of two areas that do not intersect. For
example, if you specify a range incorrectly or attempt to use a cell reference to a
cell that doesn’t exist the #NULL! error message displays.

48 Debugging
VII. Data Management Essentials

Sort
Excel recognizes a list or data set if the data in the list is contiguous, bordered by
blank cells or an edge of the worksheet, and has labels that are differentiated in
some way from the data. In the example below, the labels are bolded and have a
border. To sort a list like this, click any cell in the list and choose Data, Sort.

Excel opens the Sort dialog that permits a sort on

up to three levels at a time, with an Ascending or
Descending specification for each.

If your list has headers, Excel uses those header

names to ask how you want the data sorted.

The Options button opens the Sort Options dialog

that lets you specify a particular sort order other
than Excel’s default. (See Tools, Options, Custom
Lists to specify your own sort order.)

In the Sort Options dialog you can also

specify whether you want Excel to consider
case (upper case or lower case letters)
when sorting your data and whether you
want a left-right sort instead of the default
top-to-bottom sort.

49 Documenting
 Subtotal
Like the other list management features in Excel, the Subtotal recognizes data in
an Excel list format and will operate on that list if you click on any cell in the list
before getting started. When you plan to use the Subtotal command, always first
consider whether and how to use the Sort command. Excel can’t effectively
subtotal unsorted data.

Choose the commands Data, Subtotal to

open the Subtotal dialog box.

The default subtotal function is sum.

Other functions available include Count,
Average, Max, Min, Product, Count
Nums, StdDev, StdDevp, Var, and Varp.

The dialog allows you to determine how the subtotal will be organized. Excel
creates subtotals and puts them in an outline format so they can be easily
collapsed for a summary view. In the example below, the spreadsheet window is
split to see the Sales Division column at left and the Sales Rate totals by division.
There are three outline views. Selecting the “level 2” outline button shows a
summary format without the supporting data.

Remove subtotals with Data, Subtotals, Remove All.

50 Documenting
 Outline
Excel provides a convenient outline feature that facilitates showing your data in
summary or detail fashion. The Outline view displays automatically after you
use the Subtotal command. Or, you can create an outline automatically (Data,
Group and Outline, Auto Outline) or by hand. To create an outline manually, select
the data to outline and choose Data, Group and Outline, Group.

In the example at left, selecting the range

A1:B3 and applying outlining has put Jones’
figures in one outline group, indicated by
the outline symbols at the left of the row
headers. Smith’s figures have also been
outlined.

Clicking the outline “1” symbol at upper-left

collapses the outline so only the summary total
figures display. Clicking the outline “2” symbol
redisplays the detail data.

An outline can encompass up to eight levels of detail.

Remove outlining from the spreadsheet with Data, Group and Outline, Clear
Outline .

51 Documenting
 Autofilter
Excel’s Autofilter makes it easy to make on-the-fly groupings of list data. Click
any cell in the list and choose Data, Filter, Autofilter to turn on the Autofilter. The
Autofilter establishes drop-down arrows to the right of each header in your list.
In the illustration below, drop-down arrows are displayed to the right of the
column list headers Sales Division, Market, and Item Name. You can see from
the drop-down list for Sales Division that the four different entries in that
column are available (Central, North East, South, and West) plus a couple of
other options ((All), (Top 10), and (Custom)). Selecting “South”, for example, will
restrict the display of data to only records where Sales Division is South.

With the Autofilter, Market and Item Name can also be restricted in a similar
fashion to see a very focused view of the data in the list. Here, for example, only
one record in the list passes the filter where Sales Division is South, Market is
Charlotte, and Item Name is WHTHS- APL JC 46 OZ CAN.

The Autofilter provides a view of the data only; it doesn’t affect the underlying
data itself in any way.

Toggle off the Autofilter with the same commands used to turn on the Autofilter:
Data, Filter, Autofilter.

52 Documenting
 Data Table
A data table allows you to quickly generate values for a what-if analysis and
view the results together in a condensed format. Excel provides for both one-
input and two-input data tables. A data table is simple to use if you know how to
set it up and execute it properly.

The illustration at left shows the setup

for a one-input data table. When
executed, this table will show the
monthly payments on a $200K loan at
interest rates ranging from 6.5% to
10%.

Cell C12 holds a PMT function. The

PMT function refers to Cell C9, interest
rate, which is a blank cell. Cells
B13:B20 hold input values. These
values will be substituted one at a time
into Cell C9 when the data table is
executed.

To execute the data table, highlight the range encompassing the formula, the cells
beneath it, and the input cells at left. Then choose Data, Table to open the Table dialog.
Our example data table is in columnar format,
so we use the Column input cell text box to
identify the “dummy” input cell: C9. This is
the cell our formula refers to. Click OK to
execute the data table.

Excel executes the formula once for every input value in

the data table and puts the results in the column beneath
the formula.

This data table is in column format. Alternatively, you

can arrange a data table in row format.

53 Documenting
 Excel’s two-input data table has a different layout. As the name implies, it can
take two inputs to its formula instead of one. One of the inputs goes down the
left-hand column of the data table range and the other extends across the top row
of the range. The formula that references both column and row values is
positioned at the upper left hand corner of the range.

In the two-input data table example above, the formula is located in cell A10.
This is again a PMT function. The formula references both Interest Rate (Cell D6)
and Term (Cell D7). Values to be substituted into the formula for Interest Rate
when the data table is executed are located in Column A. Values to be
substituted into the formula for Term when the data table is executed are located
in Row 10.

As for the one-input data table, start execution by highlighting the entire data
table range (in the example above, A10:F16). Choose Data, Table and complete the
Table dialog.

In the two-input data table case, both

the Row input cell and the Column input
cell must be identified.

Click OK to execute the data table. Excel executes the PMT function formula
multiple times to fill in the values for every cell in the data table matrix.

54 Documenting
 Pivot Table
An Excel pivot table allows you to view an Excel list in cross tab format and to
manipulate the data within that format for whatever view you need. Along with
providing ways to view the data, the pivot table also provides methods for
creating summary calculations about the data.

Sometimes data in list format isn’t easy to interpret. To view this data in pivot
table format, click anywhere in the list and choose the commands Data, Pivot
Table & Pivot Chart Report.

The three-step Pivot Table Wizard opens to walk you through creating the pivot
table from this data.

Step 1 lets you specify where your data comes from. In most cases your data will
reside in a “Microsoft Excel list or database”.

Step 2 lets you specify the range of your list. If you start the Wizard with one of
the list cells current the Wizard will automatically select the range that
constitutes your list and surround it with a blinking marquee.

Step 3 is the final step. Excel creates a Pivot Table for you, guessing at the best
layout. If you choose the Layout button in Step 3, you can choose the layout
yourself. And if you choose the Options button in Step 3, you can make your own

55 Documenting
 choices with regard to the display of totals and formatting and how you want the
Pivot Table to operate.

Each of the variables in your list are displayed as “field buttons” at the right of
the dialog. Drag any field button onto the pivot table structure that’s pictured.
Fields you drag into the data area are summarized using (by default) the sum
operation. Fields you drag to the row and column areas provide the headers for
your pivot table.

The page area allows a 3-

dimensional pivot table using
whatever field you select as the
page. In the example at right,
“Division” is the page field.

56 Documenting
 When you have a pivot table open the pivot table toolbar displays. The toolbar
makes it easy to return to the layout step in the Wizard as well as to refine the
specifications for particular pivot table fields.

These two buttons on the Pivot Table toolbar are key:

Return to the layout Refine the selected

step in the Wizard. pivot table field.

Although returning to the layout step is convenient, you can also change the
layout by using the mouse to drag the field headers on or off the pivot table or to
change their locations.

Open the Pivot Table Field dialog by

selecting a pivot table field and clicking the
field button. Use the options in this dialog
and in the advanced dialog associated with
it to refine the specifications for a pivot
table field.

Check Excel’s online help for more

information about how to use this
powerful tool effectively.

57 Documenting
VIII. Documenting
Adding comments to cells
Documenting your worksheets makes them easier to use. Appropriate cell labels,
list headers, cell and range names, and even entire pages of explanation can be
useful. In addition to these more obvious ways of documenting a spreadsheet,
Excel also offers cell comments. A cell comment is a segment of text that’s
associated with a particular cell and can be displayed with the spreadsheet or
made visible just when the user wants to see it.

To add a comment to a cell, click the cell and choose Insert, Comment. Excel
provides a text box that floats on top of the worksheet in which to enter your
comment. Note that whatever you’ve entered in Tools, Options, User Name
automatically appears at the top of the comment text box as the author. You can
erase this author designator from the text box entry if you choose.

To remove a comment from a cell, click the cell and choose Edit, Clear, Comments.
To remove all comments from a worksheet at the same time, choose Edit, Go To,
click Special, and then click Comments. Then select Clear and Comments on the edit
menu.

You have a number of choices in how comments are displayed in your

worksheet. By default, a cell with a comment will have a small red triangle in its
upper right-hand corner and when you move the mouse pointer over the cell the
comment will display.

58 Documenting
 To reset the comment display options choose Tools, Options and select the View
tab. To hide comments, even when you rest the pointer over the cells that contain
them, and also to clear the comment indicators from the upper-right corners of
the cells with comments, choose None.

To display comments when you rest the pointer over cells that contain them, and
also to show the comment indicators, choose Comment indicator only.

To display both comments and indicators regardless of the mouse pointer

position, click Comments & indicator.

Hide an individual comment by right-clicking the cell that contains it and then
choosing Hide Comment. To display all individually hidden comments, double-
click the Comments entry on Excel’s View menu.

File, Page Setup opens the dialog box below where on the Sheet tab you can choose
how you want any spreadsheet comments to print. The default is not to print
comments but you can choose to have them printed as they appear on the sheet
(which might obscure parts of your spreadsheet) or at the end of the spreadsheet
printout, similar to end notes.

59 Documenting
IX. Format Tips
Row & column autofit
Excel rows and columns have a standard height and width. You can check on the
height of any row and the width of any column by clicking its header (or any
cell) to select it and choosing Format, Row, Height or Format, Column, Width.

To change the height of a row or the width of a column, enter a different value
(zero to 409 for a row or zero through 255 for a column). For a row, this number
represents the row height in points. If you set the row height to zero, the row is
hidden. For a column, this number represents the number of characters that can
be displayed in a cell formatted with the standard font. If you set the column
width to zero, the column is hidden. You can also change row height and column
width by dragging the borders of the row and column headers.

Or use the auto-adjusting

feature in Excel. For example,
the illustration at left shows
truncated labels in Column A
and a truncated value in Cell
B3. To quickly adjust column
widths to accommodate the
widest value in that column,
double click on the divider
between column headers.

For example, double-clicking

the divider between the
Column A and B headers auto
expands the width of Column
A so all labels display
properly. Double-clicking
between B and C auto
expands the width of Column
B so Cell B3’s value displays.

60 Formatting
 Autoformat
There are lots of manual formatting options you can apply to your spreadsheet,
but Excel also offers an auto format option. This option works best if your
spreadsheet has a pretty standard layout with header rows and columns and
data in a matrix sort of format. You can select a subset of a range and apply an
Excel auto format to it or you can autoformat an entire spreadsheet range at once.
If you don’t like an auto format you’ve applied, you can use Excel’s undo option
to remove it.

A simple spreadsheet like the one

at left would be suitable for
application of an auto format.

Click inside your spreadsheet

range and choose Format,
Autoformat to open the Autoformat
dialog.

Scroll down to see all

the available formats.
Formats are grouped
by style: Simple,
Classic, Accounting,
Colorful, and 3-D
Effects.

The last option is

“None”. Use this
option to remove a
previously-applied
Autoformat.

61 Formatting
 The illustration below show some of Excel’s autoformats.

The unformatted data.

Classic 2 Autoformat

Classic 3 Autoformat

List 2 Autoformat

62 Formatting
 The formatting toolbar: highlights
Turn on Excel’s formatting toolbar for easy access to the most useful format
options. Use View, Toolbars to see a drop-down list of toolbars and click the
Formatting option to display the formatting toolbar.

At the left-hand side of the formatting toolbar are options that control the look of
your text: Font face and size; bolding, italicizing, and underlining; justification;
and the very useful “center across selection”.

In the illustration at left, the easiest

way to center the title over the data
beneath it is to drag over the cell
with the title and the cells to the
right of it in which you want the
title centered and to click on the
“center across selection” button.
Excel handles centering for you.

At the right side of the formatting toolbar are buttons to control the format of
numbers, indentation, cell borders, fill color, and text color.

Clicking the border button, for example, displays a drop-down graphic of the cell
bordering options that are available.

63 Formatting
 The border option you select is applied to whatever
cell or range you have currently selected.

The fill color button lets you select a background for whatever cell or
range you’ve selected from a palette of 40 colors.

The font color button lets you select a color for the entries in whatever cell
or range you’ve selected from the same palette of 40 colors.

Don’t forget the format painter button, that appears by default on Excel’s
standard toolbar instead of the formatting toolbar. If you want to copy an
existing format, make the cells with that master format current and click
the format painter button. Then click any cell or drag over any range of cells you
want to apply that format to. If you want to copy a format from a master cell to
several non-contiguous cells or ranges, double-click the format painter button to
“capture” the format. Then any cell or range you select will have a copy of the
master format applied. Hit the escape key to turn off the format painter.

Create your own toolbars (or add or delete items from any existing toolbar) with
View, Toolbars, Customize.

64 Formatting
 Conditional Formatting
Excel offers conditional cell formatting. This allows you to set up a special format
that Excel applies only if the content of a cell meets requirements of the condition
you set.

To turn on conditional formatting, select the cell or range you want the
formatting to be applied to and choose Format, Conditional Formatting.

The dialog that displays allows you to format by cell value or by formula, to set
the condition you want to test for, and then to select the cell format you want to
apply when the condition is met. Clicking the Add button on this dialog expands
the dialog so you can set additional conditions and formats.

In the illustration at right a bold, shaded,

bordered format was pre-applied to
format any cell in the A column with
content greater than or equal to 500.

To find any cells on the

worksheet that have
conditional formats, choose
Edit, Go To. Select the Special
button and click Conditional
formats.

65 Formatting
 The drawing toolbar
You may want to add special graphical effects to your spreadsheets such as
arrows, shapes, or callouts. These options are collected on Excel’s drawing
toolbar.

The example below shows one of the many “callout” shapes available for adding
comments to a cell or range.

In this variation, a shadowed text box contains the text, with an arrow shape
taken from the drawing toolbar’s Autoshapes button. WordArt is also included.

The graphical objects you can create using the drawing toolbar generally “float”
over the surface of your spreadsheet. Use the Draw, Order commands (available
from the drawing toolbar) to control the primacy of overlapping images.

Along with drawing basic shapes and selecting shapes from Excel’s autoshapes
archive, you can also copy and paste images from other applications or use
Excel’s Insert, Picture menu options.

66 Formatting
 Styles: changing and creating (within a workbook)
A formatting style helps you apply several formats in a single step. A style can
also help ensure that cells have consistent formatting. Excel provides styles such
as the number styles you can use to format numbers as currency, percentages, or
with commas separating thousands. You can create your own styles to apply
features such as a font and font size, a number format, and a cell border and
shading.

To create a new style choose Format, Style.

In the Style name box, enter a name for the new style you’re defining. To change
the format elements for the style (number, alignment, font, etc.), click a format
element and click the Modify button. The Format Cells dialog opens to let you
select how you want to define that element.

For example, in the illustration above the new style is named MyStyle. The font
specified for the style is Albertus Medium 10, Bold Italic Red. Clear the check box
for any format element that you don't want the style to include. Click OK to
complete defining the style and applying the style to the selected cell or range.
To just define the style (without applying it), click Add and then Close. The style
is added to your list of styles.

To modify an existing style, open the Style dialog, select an existing style, and
modify any format element as described above.

To apply a style to the selected cell or range, choose Format, Style to open the
Style dialog. Click the down arrow at the right of the Style name box to see the
available styles. Choose the style you want from the drop-down list and click
OK.

67 Formatting
X. Charting basics
Providing a graphical look at the data in your spreadsheet can often tell a story
that’s not as obvious by just looking at numbers and text. The fastest way to
create a chart in Excel is to highlight the data to chart and hit the F11 key. Excel
creates its default column chart and puts it on a new page in your workbook.
You can then go on to edit the chart as you like.

If the data you want to chart isn’t contiguous, hold down the Control key as you
select each data range.

To create a chart and have

more control over the
process during creation
(instead of creating and
then editing), use Excel’s
Chart Wizard. You can
invoke the Chart Wizard
with the menu commands
Insert, Chart or by clicking
the Chart Wizard button on
the standard toolbar.

The Chart Wizard divides

chart creation into four
steps and walks you
through each one. In the
first step you select the
type of chart you want.
There are lots of “sub-
types” within each type.

68 Charting
 Step 2 lets you select (or
confirm) the data to chart. If
you’ve already highlighted
the data to chart before
invoking the Chart Wizard,
that information will
appear automatically in the
Data range box.

Excel makes its best guess

about how to arrange the
data series: row-wise or
column-wise. If you don’t
like the choice Excel has
made, click the alternative
option. The thumbnail
sketch of the chart that
appears in the dialog will
change to reflect your
choice.

Step 3 offers many

customization options
for the chart. Note the
six different tabs in
this dialog that control
chart titles, axes and
their labels, gridlines,
the chart legend, data
labels, and (for some
chart types) a data
table5.

5The “data table” offered in Step 3 of the Chart Wizard is not the same Data Table you’d use to
analyze data sensitivity. Instead, it’s a tabular view of your data that appears below the
graphical, chart view. It’s available only for certain chart types, not all.

69 Charting
 The final step lets you select where Excel should generate the chart: On a new
worksheet or in another sheet you select.

Once your chart is created you can return to the Chart Wizard at any time to
make changes you your chart. Click the chart to select it and then click the Chart
Wizard button on the standard toolbar.

Most editing changes you can easily make without returning to the Chart
Wizard. For example, the chart below mistakenly includes the Totals row of
data. To remove the Totals data series from the chart, click the plot area to select
it, right click, and select Source Data from the drop-down list. Then redefine the
range of data to chart, this time excluding the Totals row of data.

Expenses

Totals

Personnel

R&D March
February
Accounting January
Marketing

Sales Expenses

0 50 100 150 200 250 300 350

Personnel

R&D
March
Accounting February
January
Marketing

Sales

0 20 40 60 80 100

70 Charting
 Any time you have the chart selected, Excel’s menu bar changes to show only
chart-relevant menu items. Select options from the menus or select items within
the chart, right-click, and select the option you want from the drop-down menu
that displays.

Common changes you might make to a chart are to:

♦= Add a title, an x-axis label, or a y-axis label
♦= Change the formatting of the titles and labels
♦= Change the chart type to better display your data
♦= Add a data label to a critical data value
♦= Change the data series marker colors and patterns
♦= Change the gridlines in the plot area (behind the data series)

First-Quarter
Expenses
100
90
80
French Francs

70
60 55
January
50
40 February
30 March
20
10
0
Sales Marketing Accounting R&D Personnel

If your chart is on the same spreadsheet with your data you can move and size
the chart by dragging with the mouse.

To delete a chart on a spreadsheet with data, select the chart and hit the delete
key. To delete a chart on a sheet by itself, delete the sheet (make it current and
select Edit, Delete Sheet.

71 Charting
XI. Printing basics
Fit to page
If you click the print button on Excel’s standard toolbar or choose File, Print from
the menus, Excel will make all printing choices for you and print the active
worksheet using its printing defaults. In the common situation where you have
just a little more than a single page to print, Excel will print to two pages unless
you direct it otherwise.

Choosing the command File, Page Setup opens the Page Setup dialog box. The Page
tab includes the Fit to page option. In our example, you can direct Excel to print
the entire spreadsheet to a single page and Excel will reduce everything in size
just enough to fit it on a single page. For whatever your print situation, you can
specify x page(s) wide by x page(s) tall and have Excel make the appropriate
sizing adjustments for you automatically.

Note that this tab also lets you control printing in portrait or landscape mode.

72 Printing basics
 Headers and footers
Also available from the File, Page Setup menu options on the Page Setup dialog is a
means to control the headers and footers that you can print on all your
spreadsheet pages. Headers and footers are good places to include your name,
the file name, the page number, the date, company name, and any other
information that you want to have appear on every page of printout.

To create a custom header click the Custom Header button on this tab and follow
the prompts that display. Click OK to return to the Header/Footer tab pictured
above and to see a sample of how your header will print. Create a custom footer
by clicking the Custom Footer button, following the prompts, and returning to the
Header/Footer tab.

Convenient built-in options available for your header or footer can be inserted by
using the buttons pictured above that display in the “Header” and “Footer”
dialogs. They control the font and the insertion of information such as the page
number, the date, the time, the file name, and the spreadsheet tab name.

Click the Print Preview button on the “Page Setup” dialog to get a better preview
of your print options, or click the Print button to go to directly to the Print dialog.

73 Printing basics
 Printing part of a worksheet
You may want to print just part of a worksheet and not the entire sheet. To do so,
select the range you want to print and choose File, Print from the menus. The
Print dialog opens.

In the Print what section of the dialog click Selection and then click the OK button.
Excel prints just the range you selected.

74 Printing basics
XII. Other resources
Many excellent guides to Excel are available. If you’re a frequent user of the
program you’ll want to have at least one comprehensive reference on hand.
Reference guides I’ve found useful include:

Excel 2000: The Complete Reference

Osborne
Kathy Ivens, Conrad Carlberg
ISBN 0-07-211967-5

Running Microsoft Excel 2000

Microsoft Press
Mark Dodge, Craig Stinson
ISBN 1-57231-935-6

Those looking for a more tutorial approach to learning Excel might want to check
out:

Microsoft Excel 2000 Learning Kit

Microsoft Press
LearnIT, Mark Dodge, Craig Stinson
ISBN 0-7356-0914-4
Print and multimedia.

Microsoft Excel 2000 Step by Step

Catapult, Inc.
ISBN 1-57231-974-7

You can obtain many of these and similar materials at your local bookstore or by
contacting the vendor. Or check these web-based sources:

Microsoft Press http://mspress.microsoft.com/

Amazon http://www.amazon.com
FatBrain http://www.fatbrain.com/
Barnes & Noble http://metacentric.com/npub/bntraprs.htm

75 Other resources