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.

image

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).

image

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:

image

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s