Comments and Whitespace in Excel Formulas

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.