Informative Services Group

Articles about Computers and Computing

Articles about Computers and Computing


Informative Services Group Home

Web Site Design & Deployment Web Sites

IT Consulting & Project Management Consulting

Accounting Services & Packages Accounting

Digital Life and Living Digital Life

Articles about Computers and Computing Articles

Project Management Projects

Internet Technology & eLearning eLearning

Contact Us Contact Us

The Golden Rules for Correct Calculations

Written on September 13, 1998 by Robert & Karen Vanderzweerde

Appeared in Greenmaster Magazine on October/November 1998

What’s a spreadsheet?  Spreadsheet software is the computerized version of a tool used by accountants in "olden days" – a large piece of paper with many columns.  Accountants used columnar paper (legal size paper with up to 8 columns ruled on the page) to summarize data.  When they needed more room, they would tape two (or more) pieces of this paper together.  Eventually, paper manufacturers started to make the 17" x 14" paper so the taping was no longer required.  This special paper was nicknamed a "spreadsheet" because it had to be folded to fit in a file and then spread open to review the data.  If you wanted to summarize monthly data for an entire year, then you had to use a spreadsheet.

Fortunately, the computerized version of this large piece of paper is easier to use.   Taping is not required, the size is unlimited, and the computer will perform computations for you!  Change a number, and the computer will automatically update your entire spreadsheet.  Sensitivity analysis, where you explore the impact of different factors (often best case, worst case, and most likely scenarios) is now painless.

The first computerized spreadsheet was a program called "Visicalc", and it revolutionized personal computing.  Prior to Visicalc, there was no serious business software to run on the new personal computers designed by companies such as Apple and Osborne.  Visicalc changed the market.  Large computers running business applications (mainframes and minicomputers) had nothing like Visicalc.  Since budgeting, forecasting and analysis are important but time consuming tasks in most businesses, this wonderful new tool fueled business spending on computers that had previously been targeted to home applications.  Spreadsheets even preceded word processing software.

Spreadsheets have evolved from the early, limited versions of the 1980’s.   The two most popular spreadsheet software programs in use today are Excel (part of the Microsoft Office Suite) and Lotus 1-2-3 (part of the Lotus SmartSuite), although there are others.

On the electronic spreadsheet, rows are identified by numbers; columns by letters.   A1 is the first entry, or cell, and it is in the upper left corner.  Beside A1 is B1; under A1 is A2.  The pattern repeats.  After column Z comes AA, etc.   The cursor will look like a box the width of a column in a spreadsheet program.   You can enter numbers, text, or formulas in each cell.  The commands may vary, depending on the software you use, but all have similar functionality.

How you design your spreadsheet will determine how easy it is to update and whether it will calculate properly.  Some tips to prevent common design problems follow:

Anticipate changes in your formulas

If you have three or four lines and need to total them, how you define the formula for the total will determine if your spreadsheet will still add if you add another row or column.  Learn how to use the SUM function in your software, and make sure the range is large enough so that if you insert a new row or column at the end, it will still be included in the range specified in the SUM function.  Since SUM is probably the most used function in spreadsheets, there is a shortcut key for it in the standard toolbar.   In Excel, the sum function button looks like å .   Remember that if your spreadsheet has totals in both directions (down and across), if you insert a new line or column the SUM function will only be updated in one of the two directions.  You must copy the formula into the other.

Manually spot check your work

It is easy to type B13 when you mean B113.  Whenever you first build a spreadsheet, print it and test check the adds and key calculations.  You can then rely on your spreadsheet if you make minor changes, but if you make major modifications, re-check it.

Keep changes to a minimum

Sometimes there are factors that are used repeatedly in a spreadsheet, such as interest rates, gross margin percentages, commission rates, inflation factors, etc.  If you play with these factors to see the impact on your budget, you must find each spot in your spreadsheet where the factor is used and change it.  This can easily create an error if you miss some of the places where the factor is used.  An easier way is to specify the factor in one spot of your spreadsheet and then refer back to the one cell every time you need the factor in your calculation.  For example, if your budget calls for interest calculations, put the interest rate in cell B1.  Assume the interest rate is 7% on a $200,000 loan.  On the interest line of your budget, rather than using the formula =200000*7%/12, use the formula =200000*$B$1/12.  Copy the formula across all the months of your budget.  Do the same thing every time you need to calculate interest.  If you need to make the interest rate 8%, then just change the number entered in B1 and the spreadsheet will update itself.

Understand relative and fixed references

When you enter a formula, it is assumed to be a relative reference.  This means that if you copy the formula to another cell, the formula will be changed accordingly.   A formula to total a column of numbers might be, for example, =SUM(B5:B20).   This would total rows 5 to 20 in the second column – in a typical budget spreadsheet, the first column is used for text, and the second column, column B, is the first month of the year.  This formula is also true for columns C, D, etc. for the remaining months of the year.  You can enter the formula in column B and then copy it to the remaining columns.  Because the computer assumes a relative reference, it will change the formula to read =SUM(C5:C20) in column C, =SUM(D5:D20) in column D, and so on.   Sometimes, this is not the result you need.  In our interest example, we want the B1 in the formula to always stay B1, and not become C1, D1, etc.  By putting dollar signs around the column and row names ($B$1), the reference will remain fixed.   Thus, when we copy our formula =200000*$B$1/12 into another column, the formula will always stay the same.  You can have appropriate variations as required: $B1, B$1, $B$1.  When preceded by $, the row or column will stay the same; without $, the row or column will change when copied.

Don’t re-copy numbers

Any time you have to manually re-copy numbers in your spreadsheet, you set yourself up for errors.  Learn how to link spreadsheets together (it’s easy!) if you need to prepare summaries of more detailed spreadsheets, or if you need to combine numbers from different spreadsheets.  Learn how to use the copy command so that you don’t endlessly re-type.

Format your spreadsheet

Take a few moments to make your spreadsheet easy to read.  Blank lines, underlines, and appropriate numeric formats make your spreadsheets easier to read.   Use the right align button to make column headings line up with your numbers.   Use spell check.  Adjust the column width on your columns to make sense for what the column contains.  Adjust the row height to draw attention to important totals.

Use the chart wizard

Sometimes a picture is worth a thousand numbers.


Take a few moments, and a few lines of your spreadsheet, to document what it does, key formulas, and key assumptions.  You can exclude the documentation from your print range.  If your spreadsheet is important, back it up on diskette.  Use meaningful file names so you will know what each spreadsheet does – the latest versions of the software allow names longer than eight characters.

Save the environment

Use the print preview function to see if your spreadsheet will print the way you want.  Adjust column sizes and margins to make it fit on the page, or use the Scaling "adjust to fit" command in your Page Setup.  If you have many columns, print landscape (sideways) on legal paper (if your printer can do this).  If you have many rows, print portrait (regular) on legal paper.

Copyright 2007-2011 © Informative Services Group. All rights reserved.