A workbook with a good design is more effective in conveying information than a formless mass of data and labels. Here are some basic rules for designing a workbook:
1. Provide a clear title. Each worksheet should have a clear, meaningful title so the user
can determine, at first glance, the purpose and content of the worksheet. Charts should have appropriate titles and labels (see Lesson 7).
2. Use descriptive labels. Use clear and descriptive labels. Columns and rows must
contain proper labels so that the user can determine the content of the column or row at a glance. Avoid abbreviations unless they are universally understood. Sheets should also have easily understood names.
3. Set up assumptions or variables. If the worksheet contains an assumption (or
variable) used in more than one formula, but which may change periodically (such as an interest rate in a financial analysis model), place the assumption or variable in a separate input block in the worksheet. The formulas that use this value should then refer to a single cell address where the required input is located. When the
assumption changes, you need only change one cell (the input variable) instead of having to modify all the formulas that used that value. If a model has a large number of variables, consider collecting them together and placing them into their own sheet. Changes to any of the assumptions can then be readily modified on the assumption sheet.
4. Use appropriate format. Format cell values to conform to standard conventions: for
instance, dollar amounts with thousands commas, with or without cents. For a column of dollar amounts, show only the top and bottom values with dollar signs. Dollar amounts should be clearly distinguished from quantities, and percentages formatted to display the percent symbol.
5. Use underlines. To set off totals and subtotals, use single underlines (or hyphens).
Enter double underlines under the final column total. Excel provides an easy way to underline.
6. Enter a date stamp. Knowing when a worksheet was created or last updated is just as
important as knowing the date of your business correspondence. Adopt the habit of entering these dates in cells at the top or bottom of the worksheet.
7. Use a descriptive workbook name. To make it easy to manage your workbooks,
especially the opening of workbooks, save the workbooks under a descriptive name. 8. Provide a file stamp. Enter the folder and filename in a cell at the top or bottom of
each worksheet so it will appear on the printout; you will then be able to find the workbook file quickly on your hard disk.
9. Document using Document Properties. When creating a new workbook, always
enter information in the Document Properties dialog box. After making significant changes to a workbook, always update the Document Properties.
EXERCISE 5-9
Critique the design of a workbook
This exercise enables you to critique the way rules of good design have been implemented for a workbook.
1. Open the workbook CT2L1P1S.XLS. This workbook contains an income statement model in multiple sheets.
2. Critique how well this model has implemented the following design considerations: • Block layouts
• Rows and columns • Multiple sheets • Title • Descriptive labels • Assumptions or variables • Format • Underlines • Date stamp • File stamp
• Descriptive workbook name • Document Properties You will note:
Block layouts
Each worksheet is clearly organized into blocks. For example, range A1:D3 forms the title block, and range A5:D16 forms the body of the income statement.
Rows and columns
The rows and columns are logically organized.
Multiple sheets
The use of multiple sheets is logical: each sheet is devoted to a single purpose or division.
Title
The title for each worksheet is clearly presented.
Descriptive labels
Rows and columns are clearly labelled. The sheet names are short but adequate for the purpose. The workbook would be easier to read if the labels for the individual expenses were indented.
Assumptions or variables
No assumptions are required for the model. If they were, they would be placed either on a separate sheet or at the top left corner of the worksheet.
Format
The cell formats are appropriate. The dollar signs are shown in rows 6 and 16; the other amounts do not require dollar signs.
Underlines
The underlines are logically placed.
Date stamp
Although you can tell when the model was last updated (cell A19), there is no indication when the model was first created.
File stamp
Only the Consolidated sheet contains the file stamp (cell A18). Printouts of other sheets (Computers to Music) do not contain file stamps, and they may not be readily located on the hard disk.
Descriptive workbook name
The workbook name of CT2L1P1S.XLS does not give any indication of the content of the workbook. A more meaningful name should be used. (The workbook names used in this tutorial are designed for use in the exercises, rather than to indicate the contents of the workbooks.)
Document Properties
The Document Properties dialog box shows that this workbook does not contain any useful information in any of the boxes, with the exception of the Author field. This field only contains the name of the organization, rather than the individual who created this workbook. More meaningful information for Document Properties should be included with the
workbook.
TOPIC 5.8
Using Excel Help
Excel Help enables you to review some of the techniques learned in this lesson. Use the following steps to display the appropriate Help windows:
1. Click the Help icon.
2. Type worksheets, inserting, and click Search. 3. Select the topic Insert or delete a worksheet.
4. After reading the Help information, search other topics of interest to you. 5. If you have difficulty with any of the following topics, display the corresponding
Help information to review: • Worksheets, renaming
• Worksheets, copying and moving • Worksheets, page layout
TOPIC 5.9
Self-testing questions
1. An Excel workbook can comprise different types of sheets. What are they? What are their contents?
2. You have opened two workbooks, and you wish to compare range A1 to B9 between Sheet1 of these two workbooks. Without printing the ranges, describe how you can effectively make the comparison.
3. Briefly describe how you would enter information into Document Properties for a workbook to document the contents of the workbook.
4. You have created a workbook with two sheets containing data: Sheet1 and Sheet2. Describe how to name Sheet1 as “Main” and Sheet2 as “Subsidiary.”
5. You have a workbook containing four worksheets. Describe how to print all the sheets in the workbook.