Document your formulas right in the cell!

Excel - Functions and Formulas

I’ve had worksheet amnesia so many times it’s not funny. I’ve learned not to make my formulas too complex or I’ll forget how they work. So now if the logic is arcane or I’m going to pass the worksheet to a client or co-worker I put documentation right in the formula. (Remember, what goes around comes around.)

If you’re creating a worksheet that is part of a large system or that has many integrated worksheets I recommend creating real documentation with data flow diagrams, etc. But, if you’ve just got a worksheet that contains some gnarly formulas, there are two things I use. The first is explanatory range names, like rngMonth. The second is putting text documentation directly in the formula. Magic you say! Watch how it’s done. If it looks useful, pass this newsletter on to someone else it can help.

Add formula descriptions right in the same cell as the formulas using the N function. The N function returns a 0 when used on text. In the following example, the formula is D1 * D2. Adding a zero which is returned by the text inside the N() function doesn’t change the result.

Use N() to put text documentation with a formula.

Use N() to put text documentation with a formula.

However, this example does show how you can get in trouble since if you move cells D1 or D2 the text description doesn’t update to reflect their new location. To prevent those types of errors, use a description like “Multiply units times price”.

This method works well for formulas with a numeric result, but it produces an error if you use it with a formula resulting in text. In the following example, the formula returns three text characters from the text in cell C7. Trying to add the zero from N() to the text result of the RIGHT() function results in an error.

Use N() to put text documentation with a formula.

Using +N( ) to add documentation to a text formula causes an error.

Here’s how to add a description to formulas that result in text. It’s a little more complex, but it’s worth it when you need documentation. To use N() with a text formula, use IF() to test if N()returns a zero, which of course it will, and then use an IF() function to produce the null or nothing result, “”. Use an ampersand, &, to join the null and the results from the text formula.

Join with an & and nest N( ) inside an IF( ) to put text documentation with a formula.

Join with an & and nest N( ) inside an IF( ) function to add documentation to a text formula.

Did you see something interesting in that screen shot? Formulas can have line breaks in them and still work. The line breaks allow you to put different segments of a formula on separate lines so they are easier to read. To put a line break in a formula, move the insertion point in the formula bar so it is at the end of a complete segment of formula, and then press Alt+Enter. To make the formula bar show multiple lines, just drag down its lower edge.

Share the power...