Cell References and Names
Introduction
Probably the most impactful thing we can do to speed development and maintenance, and at the same time reduce errors in Excel is to make our projects as understandable as possible.
Make projects as understandable as possible
Even when reworking simple projects, one of the biggest investments in time is trying to understand what was done before and what was intended. And when working on new, complex projects, we can easily forget what we did at the beginning of the project, or remember it poorly. That will cost us time to get back up to speed, and may cause errors.
There are several things we can do to make our projects understandable: follow standards, maintain structure, declutter, reduce complexity are a few. But the most important is documentation.
The most important is documentation
There are several ways to document a project. We can create end users and technical manuals. We can use labels. We can add comments or use data validation as comments. And we can eliminate one of the most confusing aspects for Excel: cryptic cell references.
We can eliminate one of the most confusing aspects for Excel: cryptic cell references.
Cryptic Cell References
Cell references were designed for machines. They are easy for machines to understand and in the early days of spreadsheets, when PCs had only a fraction of a phone's memory and processing capability, making spreadsheets easy for the machine was essential. Today's PCs are far more powerful allowing Excel to evolve from easy for the machine to easy for the human. Names are easy for humans and they make our projects more transparent and quicker to understand by making our formulas self-documenting.
Names make our formulas self-documenting
Since 2010 my projects have been cell reference free, thanks to names and structured references.
Structured References
Perhaps the biggest argument against names is they can be tough to manage. That is totally not true when names are created from Structured References. Structured References require NO maintenance.
Structured References require NO maintenance.
NOTE! To learn about Structured References click: Microsoft: Structured References in Excel
Structured References are based on table column headings. They are fully dynamic. And if we decide to change a column heading, all formulas that use that structured reference auto-magically change with it.
Named Ranges
The fast majority of names in TBM projects come from structured references. A few come from named ranges. Creating named ranges is easy. One way is to create several names using Excel's Create Names from Selection option.
Create Names from Selection
Named properties makes formulas self-documenting and transparent.
Creating names is quick and easy. At right is a short movie showing selection of a table's first two columns (not the headers), and then using ribbon menu option Formulas > Create from Selection. with only Left column checked.
We can also use keyboard shortcut Ctrl-Shift-F3
Whichever method you use, this creates a named range matching this table's Name column and pointing to the associated cell.
Reducing Errors
A side effect of names is Excel recognizes when we fat finger them. Fat finger is a term for hitting the wrong key, aka, making a typo. When keying names, if we misspell them, Excel throughs a #NAME? error. If we mistype a cell address, it is very likely Excel will accept the wrong address without a word of warning.
Excel not only warns us when we misspell a name, but it also helps us select the correct name as we type. It doesn't do that with cell addresses.
Summary
Names add documentation to our project making complex projects far easier to understand which speeds development, auditing, and maintenance, while at the same time reducing error potential and improving transparency.