Have you ever opened an Excel spreadsheet with big formulas doing all kinds of things, pointing to all kinds of cells, that literally hurt your head to read? Here's an example of a formula I wrote to get free cash flow:
=(S153*(1-S156))+S72+S150+('Balance sheet'!T165-'Balance sheet'!S165)
It's great, it does exactly what I need it to do, but it's not very user friendly for others who are trying to understand it. In my defense, it doesn't hurt my head...
Here's where an excellent feature in Excel, the Named Range, can save the day. It's a friendly way to refer to a cell or range in plain language instead of the traditional cell reference style as in my formula above. Names can be used almost anywhere Excel requires a cell reference like formulas, charts, macros, etc.
Names make it easier to...
Move to a specific cell
Write formulas
UNDERSTAND formulas
Validate data
Write macros
and much more
If I had used named ranges in my formula, it would read more like this:
=(NetIncome * (1-TaxRate)) + Depreciation + Amortization + WorkingCapital
You might disagree with how I'm calculating free cash flow, but at least you could read the formula and argue with me without needing a deep understanding of my spreadsheet. It's hard to believe this is the same formula.
There are several ways to name a range, but the easiest is simply highlight the cell or cells you want to reference, type the name in the Name Box (small box just to the left of the formula bar), and press enter. Just remember that names can't have spaces in them, and you're cooking with gas.
Once you name a range, you can refer to it anywhere you'd like in the spreadsheet. If the value(s) in the named range changes, any formulas you have referencing the name will automatically update. You can quickly navigate to your named range by pressing F5 to jump to it, or use the drop down arrow in the Name Box and click on it.
This is just the beginning of how useful named ranges can be. If I've saved just one person's head from hurting while reading a formula, my work here is done.
What's in a name? The foundation for a great spreadsheet!
Comments