Let’s get it out there, you have been creating sheet tabs since the dawn of time and you are gagging to find out the ‘right’ way. Well…… let me help. This sadly is another little diddy from the world of spreadsheet creation and attention deficit disorder. Pop quiz – have you ever noticed when you create a new sheet that sometimes the single quotation mark appear in your formula reference? Why is this? Have you ever asked yourself that question? Join the majority of other Excel users who probably are not interested in the how’s and why’s. Here is why it is important. Take this simple file structure:
The first two sheets have the tab names CY Budget and PY Budget which are repositories for the current and prior year budget data. Now we are on the calcs sheet and we want to ‘tap’ our current year budget for data. Take this simple example:
='CY Budget'!J1
Do you notice the single quotes after the equals sign and before the exclamation point. The only reason these puppies even exist is because the user got jiggy with the space character between the words. In olden days what IT people implemented was an underscore between the elements CY_Budget. However, in more modern times the standard has been to join the words together with capitalisation for each word. As in:
CYBudget
The eye will read through the lack of a space regardless. Making the space unnecessary. Now our formulation becomes:
=CYBudget!J1
Notice there is no single quotation marks? Our sheet structure becomes:
Big deal Marcus, it is just a couple of extra characters in my formulation. Well the world is a fluid environment and so too is your spreadsheet. At times you have to manipulate formula and the single quotes right next to the exclamation mark ('!) can be quite difficult to see when it comes to larger formulas. See below:
=SUMIFS('CY Budget'!$H$2:$H$45,'CY Budget'!$B$2:$B$45,$B2,'CY Budget'!$C$2:$C$45,$C2,'CY Budget'!$D$2:$D$45,$D2)
Removing the space in-between the CY and the Budget produces the following result in the formula:
=SUMIFS(CYBudget!$H$2:$H$45,CYBudget!$B$2:$B$45,$B2,CYBudget!$C$2:$C$45,$C2,CYBudget!$D$2:$D$45,$D2)
It is a lot cleaner and changing the formula becomes a whole lot simpler. There are also 8 less characters in the formulation #quickwin. Now you have ‘the knowledge’ go forth open all your active workbooks and take a samurai sword to all the spaces in your sheet names. Good job peeps.
