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

- First, when you’re writing a formula you don’t need to remember that the margin is in cell A2; you just type “Margin”.
- 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.
- 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.