Unlike programming, it is very hard to add an explanation directly into a cell so that a user (or yourself) can remember what the formula in the cell is trying to do. You can add a comment but we find that this is not as efficient.
An alternate way to do this is to use the N function.
The N function “Converts non numbers values to a number, dates to a serial number, TRUE to 1, anything else to 0 (zero)”
You can add this to the beginning or end of your formula and simply type in the text explaining what the cell is doing. The effect on Excel is to add zero but it makes it easier to remember what you are trying to achieve. An example could be
= N(“this cell looks up the relevant department name and pulls through the department code”+ VLOOKUP(B10, $A$100:$C$110,3,false).
If you combine this with the ALT- ENTER trick (explained in a previous post but it allows you to put parts of a formula on different lines within the cell), it will look like this in the formula
= N(“this cell looks up the relevant department name and pulls through the department code”+
which is much easier to read especially if the formula part is a big nested if.
Learn more at our live advanced Excel courses in South Africa. Visit www.AuditExcel.co.za Live Excel Training page