How to insert a blank column in pivot table?

Posted on April 16th, 2015 in Excel Howtos , Learn Excel - 11 comments

We all know pivot table functionality is a powerful & useful feature. But it comes with some quirks. For example, we cant insert a blank row or column inside pivot tables.

So today let me share a few ideas on how you can insert a blank column.

But first let’s try inserting a column

Imagine you are looking at a pivot table like this.

insert-blank-columns-in-pivot

And you want to insert a column or row. Go ahead and try it. Here is what happens.

  • Excel gets mad thinking you are attempting anarchy and throws a stern, but very long & confusing warning message.

In fact the error message is so long, I can’t even fit it in one image on this blog. Here it goes, verbatim.

pivot-table-insert-column-warning-message

So how DO we insert a column in the pivot

The answer is simple.

Don’t

Don’t bother inserting the columns in actual pivot table. Instead, follow this approach.

  1. Select any cell in the pivot
  2. Press Ctrl+Shift+8 – This selects the entire pivot
  3. Copy it by pressing CTRL+C
  4. Go to a new worksheet
  5. Paste as references – ALT+CTRL+V and L
  6. Select any cells containing 0 and press DELETE key
  7. Now, go ahead and insert any number of columns & rows in this new worksheet
  8. When your pivot changes (either due to refresh or new data), the copy worksheet changes too
  9. Bonus: You can format the new worksheet cells any way you want. It just works.

Here is an example of what you can do.

example-pivot-with-blank-columns

 

But I want to insert a column in my pivot!!!

Okay, clearly you have a case of OCDIS (Obsessive Column Deletion / Insertion Syndrome).

Here is one way to technically insert a column inside the pivot table.

Before understanding the process, let’s pause and ask, “why do you want to insert a column?”

Here are few possible reasons.

  1. Cosmetic / formatting reasons. A blank column makes things easy to read
  2. To add commentary / notes / extra data
  3. To perform intermediate calculations on the data

If your answer is 1, the above approach (copying pivot and pasting as references) gives you most control over the layout and formatting. Go for it.

If your answer is 2, again above approach is still good.

If your answer is 3, you can use calculated item / fields is your best option.

If your answer is 3 & you are using Excel 2013 (or power pivot), you can use either Sets feature or MDX formulas to mimic blank rows. Unfortunately, I can’t explain this because squirrels know more MDX than me.

Let’s say you want to calculate certain percentage or similar…

Okay, so want to calculate North / West % in below pivot.

calculated-items-in-pivot-tables-explained

In this case, you can use calculated items feature of pivot table like this.

  1. Select any region name in the column labels are of pivot
  2. Go to Home > Insert > Calculated Item
  3. Give your calculated item a name like “North by West %”
  4. Write the formula =North / West

    create-calculated-items

  5. Click ok
  6. This new column will added to your pivot, like this:

calculated-items-example

As you can see, it works fine until we hit the grand total row. There our North / West % should be 96%. Instead it reads 386%. Clearly a number calculated by my 6 year old son.

Why is the total wrong? Because, pivot table grand totals are a simple sum of all the above values. So Excel went ahead and added up the four percentages.

How to fix this? One simple options is to turn off the grand totals. Note that even row level grand totals are off as the % was added to actual values.

If you must see the grand totals, then your best bet is to use Power Pivot. It allows you to define formulas (using DAX) and create powerful pivot tables.

So no easy way to insert columns then?

It took us a few minutes to get here, but that is the answer. There is no simple work around to this problem. Instead, here is a 4 step process you should follow.

  1. Take a few deep breaths
  2. Insert your favorite expletive in this sentence “______ pivot tables” and shout it.
  3. Use Power Pivot
  4. If Power Pivot cant be used, copy the pivot as references and manipulate the layout as you wish.

Happy pivoting.

 

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

11 Responses to “How to insert a blank column in pivot table?”

  1. Elias says:

    If it is just for layout cosmetic / formatting reasons try this.

    Create a new calculated field with value equal to zero
    =0

    Move/add the new calculated to the place you want
    Use the following custom format number:
    ;

    Now you have your new blank column.

    Regards

  2. Patti Thomas says:

    There is another way - I recently wanted to insert a blank column in a pivot table and found this: http://datapigtechnologies.com/blog/index.php/add-column-spacing-in-a-pivottable/

  3. Patrick says:

    Even simpler, you can just add a blank row to the data, and position as desired. So if data were structured as:

    Salesman | Region | Net Sales

    If you add a blank row before the last record, the Pivot Table will automatically add it to the table, with a blank row and column. Where the heading might read "(blank"), you can just type a space to replace it.

    • George Mount says:

      I frequently add "dummy" columns to my PivotTable data like this. They're always marked "X" so I know which not to use. Thanks for posting this simple solution.

  4. That is great. So simple a solution when you think about it... .. elegant.

  5. Subhash says:

    Hi,

    I have one problem if somebody has a solution.

    I have data for two months with supplier names, amount, different dates (some of data belongs to current month & some for last month.

    While preparing pivot, I have inserted supplier name at row label, date at column label and value at Values label and group column field by month (resulting amount coming monthwise in two columns.

    Now I want difference between two months.

    thanks,
    Subhash

  6. santosh says:

    Hi Chandoo Anna,

    You are superb.. !!

    Its a cool tip and more likely in creating dashboards this event would do magic.

    Super Machi 🙂

  7. Balakrishna says:

    Thank you very much... its awesome tips

  8. Amit Kumar says:

    Hii Chandoo,

    This complete guide about inserting a blank column in pivot table are quite helpful for me and saved my lots of time. I was puzzed with this problem from past few days and finally i got my answer and it worked nicely for me.

    Specially, the section of calculating certain percentage solved my problem. Thanks for this great help. Keep helping !!

  9. ZUR says:

    THIS MAKE LIFE SIMPLE

Leave a Reply