Today I want to share some tips and guidelines to follow with formulas in your spreadsheet. If you follow these, your user will be better able to understand your calculations, and you will have a much easier time modifying and scaling your spreadsheet.
Simplify, Simplify, Simplify
Formulas should be easily understood at first glance.
Avoid more than 2 levels of nesting in a formula. If you find yourself nesting 3 levels or more, use helper columns or intermediate formulas.
Rule of Thumb: Use your thumb to measure the length of the formula on your screen. If the formula is longer than your thumb, the formula is too long.
Formulas With A Column Reference
Don't reference the entire column, for example, SUM(A:A). You force the spreadsheet program to do unnecessary work by examining the thousands of blank cells in the column that could have been ignored while doing the calculation.
Use Named Ranges. This will make your formulas easier for the user to understand, and minimize errors in the spreadsheet. You can find an excellent tutorial about named ranges - as well as many other useful Excel tips and tools - at Contextures, the website of Debra Dalgleish.
Constants & Variables
Avoid hard coding - this means do not embed values in formulas. Instead, use a cell reference. I talk about this in my blog post - Spreadsheet Mistakes 3 - Formatting, Functions & Formulas.
Enter values in one place, one time. Have a Constants worksheet, similar to your Variables worksheet, where you can put all constant values in one place. Use cell references to refer to the Constant value, just as you would refer to variable values.
Always Keep the User in Mind
Don't assume the user will understand your work.
Always ask yourself: "How can I make this as easy as possible for the user?"
Too much information is not always better. Use a summary worksheet. If there is a lot of detail the user must know, have a separate worksheet with the details for the user to reference.
If you are not sure what the user wants: ASK THE USER!
Minimize the Number of Unique Formulas
A unique formula is a formula that appears only one time on a worksheet. If a worksheet has too many unique formulas, it is very likely that many of them are hardcoded. Consider replacing these with one formula that contains a cell reference instead of a hardcoded number.
Please note that if the same formula appears more than once, it counts as one formula. The picture below illustrates the concept.