Have you received workbook from others that carries underscore _ as if a space in their worksheet names?  Did you wonder why people use underscore when we can actually use space in worksheet name?

Well… did you know… long long time ago, #Excel did not allow space in a worksheet name.   If you knew it, you may think it maybe a habit for those who having been using Excel for years.   Yes it could be… but I believe the people who stick to no_space_rule in naming worksheet indeed understand the advantage of avoiding space in worksheet name.

What’s the difference?

Many people don’t know what is the (subtle) difference, except the fact that one has space and one has no space on it.

You will see the difference when you need to write a formula to refer to a different worksheet, only if you pay close attention enough.

Now, let’s watch the following GIF to see if you can spot the difference? (Hint: Play attention to the formula bar)  

Alright… A static image for easier reference:

Did you see it?

Bingo!  When there is space in worksheet name, Excel will wrap the sheet name with ‘ (single quote).

Why does it matter?

Well…… you may be thinking: “I have never, and will never type the formula by myself.  I will only point to the other worksheet; point to the cell reference I need (with mouse); and Excel will do that ‘wrapping ‘ automatically for me.  So why do I care?”

You are right if you don’t need to use any function or formula that refer to other sheets.  Otherwise, you may be frustrating by getting errors without any ideas.

Before looking into the two functions (HYPERLINK and INDIRECT – these are the two functions on top of my mind) that require a worksheet name, let’s do a little experiment to see what if I add / remove the ‘ ‘ wrapping the worksheet name:

Result:

When I manually input the ‘ ‘ (single quote) to wrap the worksheet name with no space (i.e. ‘HasNoSpace‘!A1) in the formula bar, Excel refused to take it and remove the single quotes without any signal.

On the contrary, when I removed the single quotes that wrapped the worksheet name with space (i.e. Has Space!A1), Excel opened following dialogue box, requesting me to select a file with an intention of locating the file that carries the sheet name…

I said no to Excel by clicking “Cancel”.  In return Excel refused to cooperate and gave me error #NAME?.

Single Quote ‘ is needed

Up to this point, you should realize that when you refer to a cell on a worksheet where the sheet name carries space,  you need to wrap the ‘worksheet name’ with single quote to make the formula work.

Let’s continue with a few experiments.  You may download a Sample File to follow along.

Experiment 1 – using HYPERLINK function

I input the worksheet names into cells; used HYPERLINK function to create hyperlink to A1 of the sheets.

Here’s the formula:

=HYPERLINK("#" & A15 & "!A1")

Read my previous post if you would like to understand how the HYPERLINK function works.

Result:

The formula works for HasNoSpace but not for Has Space. The missing piece in the formula was the single quote ‘ to wrap the worksheet name.

Experiment 2 – using HYPERLINK function, with slight modification to the formula

The slight modification was to concatenate ‘ before and after the worksheet name:

Here’s the formula:

=HYPERLINK("#'" & A20 & "'!A1")

Result:

The formula works regardless of presence/absence of space on sheet name.  Initially I thought Excel would not take the ‘HasNoSpace‘!A1 as it refused to take ‘ in formula bar.  I was wrong about that.   (I am glad that I am wrong :P)

Experiment 3 and 4 – using INDIRECT function

Here’s the formula:

=INDIRECT(A25 & "!A1") 'for Experiment 3
=INDIRECT("'" & A30 & "'!A1") 'for Experiment 4

Consistent results:

The formula did not work when we omitted the single quote ‘ for Has Space.  It worked in both cases when we have considered the presence of space by wrapping the sheet name with ‘.

Now, we should be able to say:

To play safe, always wrap the sheet name with ‘ when using formula to refer to another sheet. 

Having said that, we should appreciate the…

Advantage of avoiding space in worksheet name

As it should be clear by now: When we have applied no_space_rule in naming worksheets, we are indeed making our life easier.  Why?  Simply because we don’t have to worry about the single quote ‘.

Frankly, can you really see if it is “” or “‘” at a glance?  Stop hurting your vision please. 😛