What’s Lurking in your Spreadsheets? (Part 2)

Note:  This series of posts is not intended to be a “How To” guide.  My purpose is to highlight some common issues and mistakes in spreadsheet development and how to avoid them and the resulting risk to your business.  Even if you don’t develop spreadsheet models yourself, hopefully this will help you know what to look for and questions to ask those who DO develop spreadsheets for your business.  If you would like more detailed “how to” instructions and examples on any of these topics, I refer you to a good Excel reference book like the Microsoft Excel 2010 Bible by John Walkenbach.

In a previous post I discussed the prevalence of poor spreadsheet modeling practice in most organizations, and the resulting risk to the business.  In that post, I covered one very common mistake – using fixed or hard-coded values or constants in formulas. 

This time I would like to cover another common type of spreadsheet error:  incorrect cell references or ranges.  You are probably familiar with Relative and Fixed references (even if you didn’t know what they were called).

•  “A1” is a Relative reference referring to Column A, Row 1.  If you copy or drag a formula to the right, the A1 becomes B1, C1, etc. If you copy or drag it down, it becomes A2, A3, etc.  In other words, the reference is “relative” to whatever cell you’re in.
•  “$A$1” is a Fixed reference.  No matter where you copy or drag it, it remains $A$1 and always refers to the same cell.   In other words, the reference is “fixed” no matter what cell you’re in.  And of course you can fix just the row (e.g., A$1) or just the column (e.g., $A1).

Each type of reference is appropriate in different circumstances.  The problem is that it’s easy to use the wrong type in a formula and not realize it.  Then when you copy and paste the formula somewhere else, you may end up referring to incorrect cells and getting incorrect results.  Things may appear OK but the calculations may be totally wrong!

It’s critical to review all the formulas in a spreadsheet model to make sure the correct types of references are being used.  You can also use Excel’s built-in auditing features, e.g., Trace Precedents, to look for mistakes.

Here’s another neat trick:  Press Ctrl and the accent key (it looks like ` and is next to the “1” key on the top left of your keyboard).  This turns on the Formula View which shows the formulas in every cell on your worksheet.  This is a great way to scan your formulas for errors, especially incorrect references.

Perhaps the best way to prevent incorrect references is to use Range Names. To name a cell or a range of cells, just select the cell(s), click in the Name box which can be found toward the upper left corner of the window, type in a name and hit Enter.  Then, whenever you refer to that cell or cells in a formula, you can use the Range Name instead of entering the cell reference.  Here’s an example, first with a normal reference to cell A2, and second where cell A2 has been named “Margin”.

a.      = SUM(B3:B6)*A2

b.      = SUM(Q4_Sales)*Margin

Why is (b) so much better than (a)?  Three main reasons. 

1. First, when you’re writing a formula you don’t need to remember that the margin is in cell A2; you just type “Margin”. 
2. Second, you don’t have to worry about using the wrong type of cell reference.  If you write a formula using “A2” (a Relative reference) instead of “$A$2” (a Fixed reference) and then you drag or copy it to other cells, you will end up with the wrong values.  If you write a formula using “Margin”, you can drag or copy at will with no concerns.
3. Third, it’s much easier to understand what a formula is doing.  In example (b), it’s easy to see that you are multiplying Sales by Margin.  Not so in example (a).

In future posts I’ll continue to point out other problems to look for in spreadsheet models, along with some best practices to reduce the risk to your business.

Meanwhile, I’ll close with the same question from last time:  Do you run critical parts of your business on spreadsheets?  If so, have you thought about where and how they are being developed, and what sorts of errors may be lurking inside?

Until next time…

Rob

_________________________________________________________________

Rob Ende is Founder and President of REanalyze Inc., a Supply Chain, Inventory Management and Analytics consultancy based on Long Island, New York.  Rob can be reached at 631-807-2339 or rende@reanalyzeinc.com.

© 2012 REanalyze.Inc.. All Rights Reserved.