Microsoft Excel: Design Error-Free Worksheets

By Helen Bradley | Posted July 29, 2009

No one deliberately creates a worksheet that contains errors. The sad truth, however, is that many people unwittingly do just that and end up with a worksheet that doesn't give correct results. This can be a big problem for small business owners as who rely on accurate worksheet data to bill customers, create budgets and even price products.

Data area
Providing constant values in a data area ensures it can be easily verified and edited.
(Click for larger image)
.

If your worksheets contain mistakes, or if mistakes creep in over time, then you could be making decisions based on wrong information. In this article I’ll show simple ways to avoid problems.

Analyze the Risks

Some of the hardest errors to find occur in spreadsheets that hide the values they use to calculate results. For example, consider the situation where you base your calculations on a tax rate of 6.5 percent. If you calculate tax in a cell by multiplying one cell's value by 6.5 percent, it will be difficult to trace the tax value later on to see if it’s correct, because it’s buried inside a cell formula and thus not visible when you look at the worksheet.

Create name
You can use the Name tool to name cells based on the data adjacent to them.
(Click for larger image)
.

A better design method is to place the tax rate in a cell by itself and to add a description of that value in the cell to its immediate left. Then use the value in that cell by referring to that cell every time you need that number to make a calculation.

Placing the value in a cell where you can see it lets anyone look at the cell and instantly know the number being used in the calculations. If the value is incorrect or if it changes over time, it’s easy to alter the value by simply replacing the number in the cell. On the other hand if you bury values like the tax rate inside a cell's formula it is very difficult to find and update the value.

Worksheet Data Areas

Creating a data area for a worksheet is a simple and very effective way of tracing the data that you’re using. To do this, take out all the known constant values such as tax rate, markup percentages and other values that you use consistently in your calculations and place them in a separate area of your worksheet. In the cells to the left of the numbers, type a description of each of them.

Name in formula
Once you create a name, you can use it in your formulas.
(Click for larger image)
.

When you create a data area you can automatically name the cells containing the numbers and use their descriptions in your formulas. To do this, select the cells containing the values and the cells to their immediate left and choose Insert > Name > Create. Select the Left Column checkbox and click OK. This names the cells with the labels to their left.

You can now use the cell names in your calculations. So, for example, if cell C8 contains the current State Tax rate, and if that cell is called State_Tax you can write a formula to calculate tax on the value in cell F21 in this way:

=F21*State_Tax

Worksheet Constants

Constant
You can create constant values using the Name tool in Excel.
(Click for larger image)
.

You can also create constant values to use in a worksheet by referring to it by name. To do this, choose Insert > Name > Define. In the Refers to: area, type a value such as 55 percent for a markup rate. In the Names in: workbook area, type a name for this value such as MarkUp and click OK.

You can use this value in any calculation by referring to the word MarkUp and Excel will use the constant value that you have defined.

If you have a lot of named values in your worksheet you can create a list of the names and their current values by clicking in a cell that will become the top left corner of your names list. Next choose Insert > Name > Paste > Paste List to paste a list of the range names and the cells or constants they relate to.

Documentation

List of names
When you use multiple names on a worksheet, you can create a list of them with the Names tool.
(Click for larger image)
.

Whenever you create a worksheet solution for your business you should document it. Add a new blank worksheet into the workbook. Click in it and type the details of the worksheet such as the date and who created the worksheet and any assumptions upon which it has been based.

To add large pieces of type, add a textbox to the worksheet by displaying the Drawing toolbar. Next select the TextBox button, and click-and-drag to draw a TextBox on the screen. Now you can type into the text box.

If the worksheet contains complex calculations, explain the basis of these calculations and where they occur in the workbook. In the future, this will help you understand the worksheet and where it might need updating when circumstances change.

If you know there are problems in the worksheet, or if you know it might fail under some circumstances, note them in the documentation so that anyone working on the worksheet will have that information.

If you don't know what to include in the documentation, ask yourself what you would need or want to know about the worksheet if you had to work on it in two or three year’s time. When that time comes you will be glad you took the time to create your documentation.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!


Comment and Contribute


     

    Get free tips, news and advice on how to make technology work harder for your business.

    Submit
    Learn more
     
    You have successfuly registered to
    Enterprise Apps Daily Newsletter
    Thanks for your registration, follow us on our social networks to keep up-to-date