A couple of silly little tricks, but ones I find useful.

Far too often I end up trying to decode a complicated Excel formula and wanting to make it easier to read and leave comments behind. The tricks below work in Excel 2010 and 2013. I don’t use 2007 any more but expect they would work in there. I have no idea on 2003 and older version as I haven’t used them in ages.

Taking a nice simple formula as a demo, getting the next weekday from a current day (cell A3). One way of doing this is to add 1 if Sunday to Thursday, 3 on a Friday, and 2 on a Saturday as Excel stores dates as number of days since 1-January-1900.

I recently accidentally stumbled upon how to put white space into a formula and to keep it preserved. All you need to do is add a – character within the formula. If this is somewhere in the formula then Excel wont reformat the white space (either spaces or new line characters).

Comments are reasonably straight forward, and I am sure you can do in many different ways. I like using the N(“<Comment>”) which evaluates to a 0. You can confirm that the comment evaluates to 0, by selecting it within the editor and pressing <F9>. You can read the documentation on the N function here.

So adding comments to the formula above to explain what is what ends up with:

While a little contrived example, I find it very useful when you are writing or pulling apart complicated formula.