What’s wrong with using Excel range names

  • Tagged with:
  • Excel,
  • named ranges,
  • OFFSET,
  • Spreadsheets,
  • Tips & Tricks,

I’ve been talking to a bunch of people about Sumwise recently and when they ask me to give them an example of what it does, I generally say something like this:

In Excel you might write the formula =Sum(X31:X37) to add up the items under the Revenue heading. One problem is that this is cryptic and doesn’t mean anything unless you confirm that the range X31:X37 refers to the items you want. And what if the items actually extend to row 38? It is very hard to check and correct this unless you carefully check and review your formulae.

In Sumwise though, there is no A1-style grid. Rather, rows and columns are referred to by user-defined labels such as RevenueHardware, or Jan. In Sumwise, the formula =Sum([Revenue.]) would add up all the rows underneath the Revenue row. The formula means what it says and does not need to be translated to be understood or checked.

The usual response to this is “don’t Excel’s range names do the same thing?” The short (and long) answer is NO! In terms of making your spreadsheet model more understandable and reducing potential errors, Excel’s range names actually compound, rather than solve, the problem.

The key issue is that Excel’s range names need to be set up and then constantly checked and maintained to ensure that they are current and accurate. If I have named a whole range of rows Revenue, I need to be sure at all times that this range name references the correct rows. What if I add another row to the end? What if I move things around? What if I change my Revenue section to Expenses? All of these things can impact the definition of the range name. Ultimately, it is just another thing that can go wrong, and another thing that needs to be checked and maintained.

This view is supported by some research presented at EuSpRIG’s 2009 conference which found that the use of range names in Excel models actually reduces one’s ability to find and fix errors in an Excel model.

Of course, there are good uses for range names (global variables is one) and there are ways to get range names to automatically extend and adapt to model changes (dynamic ranges using OFFSET). But, on the whole, and especially in the hands of non-experts, they are pretty dangerous.

No comments yet

Be the first

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

rushessay | mac recuperar archivos borrados del usb