Don’t Let Bad Spreadsheet Design Hurt Your Career, Part 2

Bad spreadsheet designs can hurt your career. I’ve seen it happen.

So in Don’t Let Bad Spreadsheet Design Hurt Your Career, Part 1, I began to discuss problems in a workbook sent to me by an Excel user I call Randy. I hope this will help you to find and fix similar problems in your own workbooks.

Range Names

Randy’s workbook has more than a dozen worksheets, with many formulas that link to data in other worksheets in the workbook. But the workbook doesn’t use range names at all.

This is a problem for at least four reasons. First, cell addresses in formulas give no hint about what value you intend to link to. But using a range name, on the other hand, provides some idea about the type of data you intended to use. And that information will reduce the errors in your formulas.

Second, using range names often is easier to do. For example, if the range name CurMonth contains the date serial number for the current month, it’s very easy to enter =CurMonth in any cell that needs to use that value.

Third, as you create your workbook, you occasionally will need to change the source range that formulas link to. If those formulas use only cell addresses, you’ll need to change every single formula. But if those formulas use range names, you simply redefine the range name to point to the new source range.

Finally, it’s not unusual for large workbooks to be split into several interlinked workbooks. This is extremely risky to do if one workbook links to another workbook using cell addresses.

The reason this is risky is obvious when you think about it. Suppose BookB.xls contains the formula:
=[BookA.xls]Sheet1!$A$1

If you close BookB.xls, insert a new row 1 in BookA (moving the old row 1 to row 2), then open BookB.xls again, BookB now should link to cell A2, because that’s where the original data now is. But BookB still links to cell A1, because it was closed when the changes were made. However, if BookB links to range names in BookA, there are no errors at all.

So, learn about range names and use them.
I’ll see you in the next post.

Read More