Using Named Ranges Instead of References

I have always stated a strong case for using meaning range names in formulae instead of cell references. Not only does it make the purpose of the formula apparent. It also makes it a lot easier to troubleshoot them. This structured form of working has a lot of benefits as clearly described below. This tip was provided by Bruce Judd, Strategic Decisions Group.

"We use named variables in our Excel models almost exclusively. For example, we'd write the equation for revenue:
MarketSize*MarketShare*AveragePrice rather than with cell references: H12*K12*M12. These range names (MarketSize, MarketShare and AveragePrice) are defined on an input sheet or in a separate sheet where we are doing calculations. [We name the variable with a lower-case first letter if on the input sheet (e.g., marketSize), or if it is a time-series of data (e.g., for years 2001, 2002, 2003...), we begin it with a capital letter.]

When we type an equation in for the first time in an equation, we always type it in all lower-case letter. After you press Enter, Excel looks for a definition of each variable. If the variable has been defined, it converts it to the exact form of the definition (e.g., MarketShare, rather than marketshare). Using this trick, you can look at an equation and easily see which variables, if any, are not yet defined, because they have all lower case letters."