Design & Audit Tips, Part II   

  Back  Forward  "Design & Audit" Home    Excel Review Home

 

II. Data Entry and Organization

Organize your spreadsheet model around your data. Start building the model by entering the data you know about. You don't necessarily need to know how you're going to use all the data before you enter it. At the start, just get the data into the spreadsheet. Excel makes it easy for you to change things around later if you need to.

Constant values
Row-Column Structure
The Final Report
Documentation

 

Constant values

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

Don't 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

 

Row-Column Structure

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

 

Documentation

General Documentation

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.

Documenting Data

When entering data, use descriptive labels to document data items.

For example:

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

  • A range name can be of any length but can't contain spaces, commas, or hyphens.
  • A name must start with an underscore or a letter and shouldn't look like a cell reference.

    Examples of valid range names:

    Cost.of.Goods
    _Old_Data
    LongName1234
    OK1

    Examples of invalid range names:

Cost-of-Goods
Old Data
LongName 1 2 3
A1

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

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

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