If possible, keep all constant values together in one area of the worksheet,
clearly distinguished from decision variables and formulas. An important
principle of good spreadsheet design is to keep just one copy of each
constant value. That is, enter a constant value in only one location
in the worksheet. Then if you use the value in another cell, use a cell
reference that refers to the constant value's unique location.
Example: You enter the constant value of 6% for sales tax in cell E5.
When you write a formula in your worksheet that requires sales tax, reference
E5 in the formula instead of "hard coding" in the 6% value.
Do refer to the sales tax value like this: =subtotal*E5
refer to the sales tax flue like this: =subtotal*6%
Better yet, assign cell E5 a name, like SalesTax. Then use that name
in any formula you write. The formula in the example above would then
read like this:
=subtotal * SalesTax
Return to Top
Are there existing row or column structures in the data that you can
exploit in your model's layout? For example, the data for the worksheet
below was available in a tabular format that translates directly into
this useful model layout.
Return to Top
The Final Report
Give some thought at the start to what you might want a final report
or reports to look like. What quantities should be computed and displayed
in the reports? What's the most logical way in which to arrange them?
What will the reports' readers be looking for? Is there information that
should appear together on a single page? Do readers your readers need
the option of seeing underlying formulas?
Return to Top
In an obvious place in the worksheet include general information that
describes what the model is for, where the data is from, who the output
is going to, etc.
When entering data, use descriptive labels to document data items.
Not only can you include labels as text in worksheet cells, you can
also name cells or groups of cells (ranges) and use those names in place
of row-column references. This can make your worksheet much easier to
read and modify.
:Rules for naming a range
LongName 1 2 3
Here are three methods for naming a range:
Method One: Use Insert, Name, Create to create names for cells
automatically based on their row or column headings. Select the range
to name and issue the commands to open the dialog box pictured above.
In the dialog, indicate where Excel should find the names to use.
Method Two: Use Insert, Name, Define to create a name for a
range you specify.
For example, if you select the range A1:B3 in the example pictured
above and issue the Insert, Name, Define command, Excel presents
a dialog box that assumes you want to name the entire range Jan.
If this isn't what you want, you can change the current selection.
Method Three: Use the name box on the Excel 5 formula bar.
In the example illustrated above you might highlight the range A1:B2,
click in the part of the formula bar that now holds the cell reference
A1, and replace that cell reference with a name. For example, you might
enter the name Constant_data to replace the A1 reference.
Documenting a Single Cell
If you want to document a cell but don't want to display the documentation
directly in a cell of the worksheet, use a comment. A comment is associated
with a cell, but not part of it.
To create a comment, select the cell in question and enter the commands
Insert, Comment. Excel displays a small text box in which to
enter the comment. In the illustration above, note that the comment
author's name is included. The name is derived from the user information
entered in Excel's Tools, Options dialog box, on the General
Select Tools, Options and then the View tab to see your
options regarding how comments are displayed. You can choose to display
no comments, the comment indicator only (a red triangle at the upper-right-hand
corner of a cell with an associated comment), or both the indicator
and its comment.
If you have the first (no comments) option selected, you can still
see what cells have comments associated with them by hitting F5 (which
opens the Go To dialog), hitting the Special button, selecting
"Comments", and hitting OK. Exel selects every cell
that has an associated comment.
If you have the second (comment indicator only) option selected, Excel
shows the text of the comment when you position the mouse pointer over
The Sheet tab on the Page Setup dialog lets you select
how you want comments printed. Your choices are not to print comments
at all, to print them as they're displayed on the worksheet, or to group
them all together and print them at the end of the printout.
Return to Top