Why I like to put space in Excel formula

I like to put spaces in the Excel formula that I write and I am often asked why.

The short answer is that it makes them easier to read and understand.

The longer answer is that it's one of those things that I've learnt from being a programmer and have chosen to implement in my Excel formulae. Specifically, programmers are encouraged to use white spaces in order to increase readability.

For starters, packing line after line of code is fatiguing and difficult to read. Next, grouping some lines of code together while breaking other bits out with white space - or blank lines - can help to show related areas of code. Related to that, indentation - the use of tabs - is also used in order to show related lines of code.

Given the limited area available to implement white space in Excel formula - it's just the formula bar area - how can space be used to increase the readability of Excel formula?

First a quick caveat: the Excel formula bar does facilitate entering formula over multiple lines and we'll address that another day because it is a great option for very long formula. But today we'll focus on single line formula.

I've previously shown a formula that allows the joining of text and a date and it is shown below.

=A1&" "&TEXT(A2,"dd/mm/yy")

This formula is made up of three parts but it can be difficult to see these three sections and understand what the formula designed to do. So let's put some spaces in there to clarify that.

= A1 & " " & TEXT(A2,"dd/mm/yy")

That's better. Now we can clearly see that there is the text bit (A1) a static spacer (" ") and the date converted to text (TEXT( ... )).

We can probably go one better. The TEXT function takes two arguments - a value (in this case a value that represents a date) and a text format (which in this case is 'dd/mm/yy'). So why don't we show that with a space as well.

= A1 & " " & TEXT(A2, "dd/mm/yy")

That's quite readable now. What do you think?

Remember that when you are writing your formula it is easy to understand what the formula is trying to do. But six months down the track this understanding may not come so naturally to you. Or it might not even be you trying to read and understand the formula - it might be someone else. Do yourself a favour and put some space in your Excel formula to help your future self. Or that future other person.