Marissa, one of my 12 fans, wrote me an email asking if there is a way to add column spacing in a pivot table. Apparently, she has a pivot table with lots of metrics and wants to add visual spacers between logical groups of metrics. Today, I'll show you a way to do this using a calculated field.
.
I start with this pivot table that outlines the ingredients used in a heart-healthy sandwich. I want to add spacers so that the vegetables are visually separate from the good ingredients.
.
If I try to simply add a column in the middle of the pivot (between Crusty Bread and Lettuce), I get this nasty message.
.
So I'll have to go another way.
.
Step 1: Insert a Calculated Field
With my cursor inside the pivot table, I go to the Home tab and click the Insert dropdown. There, I can select Insert Calculated Field.
.
Step 2: Build a Calculation that Returns Zero
I build a new calculation called Dummy that does nothing more than return a zero.
.
Step 3: Add the new Calculation to the Pivot
Once I press OK, the new calculation automatically jumps into my pivot table.
Notice on the PivotTable Field List, my new Dummy field is available along with my other fields.
.
Step 4: Adjust the Placement of the New Dummy Field
At this point, I use the PivotTable Field List to move my dummy calculation where I want the spacing.
Again, I want to separate all the awesome ingredients from the sissy vegetables. So I place my Dummy spacer appropriately.
.
Step 5: Format the Dummy Field to Show Blanks
Now I simply go into the number formatting of the Dummy field and click the Custom option.
While there, I enter three semicolons (;;;) as the format. This will ensure that nothing is shown in the field.
.
Step 6: Rename the field to a Single Space
I need to get rid of the default title for my spacer field (Excel called it Sum of Dummy).
To do this, I click on the field name and simply press the Space bar on the keyboard.
I've effectively renamed my field to a single space.
.
.
At this point, I've got a spacer in my pivot table.
.
If I need another spacer (let's say between Bacon and Doritos), I can select my Dummy field from the PivotTable Field List and add it again. I will have to go through the same number formatting and renaming I did in steps 5 and 6 for this new field. Note that Excel won't let me rename the field to the single space again because I've already used that name for my first spacer. That's ok – I can rename this new field to two spaces.
.
And of course, I can format the spacers just like any other pivot field. Here is my final pivot.
Bacon stands alone, the other good stuff next, then vegetables.
.
.
There you go Marissa. Thanks for the question.
This exercise made me hungry. I'm off to make a sandwich.
Nice tip! And if you don't want to create a calculated field, you could just add another copy of an existing value field. Then, change its heading, number format, and fill colour, as you described.
Too funny, Mike. I was just doing this yesterday, but in PowerPivot. Here's the method to do it the PowerPivot way: http://www.excelguru.ca/forums/entry.php?13-Creating-a-Spacer-Column-in-a-PowerPivot-PivotTable
Ken: Well look at that. Great minds do think alike!
Debra: Great point. I suppose you could also add a dummy field to your source data.
Hi Mike,
very usefull tip, thank You:). But I think if someone has a normalized data – where fields: Bacon, Doritos, Melty Cheese and etc. are the items of one field e.g. named "Meals", – a better way is to create Calculated Item "Dummy" in field e.g. "Meals" and then format it as You show.
best regards
I tried to operate the inert calculated field, but it is grey and I cannot do it.
I cannot do the move pivot table either
The file is not protected.
What is wrong?
( I am using excel 2010 )