Add Column Spacing in a PivotTable

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.

12 thoughts on “Add Column Spacing in a PivotTable

  1. Debra Dalgleish

    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.

  2. Paul

    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

  3. Gabriel

    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 )

  4. Gokulkumar

    I am not able to accomplish this with groups and sub groups as column labels as the lay out collapses

    PLease help

  5. datapig Post author

    Gokulkumar: Click inside your Pivot Table and then go up to the Excel Ribbon and select

    Design>Report Layout>Show in Tabular Form

  6. Arindam

    Hi,

    I have a pivot table where I have the name of some Distributors in Rows and revenue in Columns. I have 4 Financial quarters data in the Columns. Now I want to add a column within the pivot, which will show the total of Quarter 1 and Quarter 2. Is that possible? Please explain in steps

  7. CMcR

    Is there another option for (;;;)? I keep getting #VALUE! error. (I’m using Office 365 – Excel)

Leave a Reply

Your email address will not be published. Required fields are marked *