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.
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.
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.
- Select any cell in the pivot
- Press Ctrl+Shift+8 – This selects the entire pivot
- Copy it by pressing CTRL+C
- Go to a new worksheet
- Paste as references – ALT+CTRL+V and L
- Select any cells containing 0 and press DELETE key
- Now, go ahead and insert any number of columns & rows in this new worksheet
- When your pivot changes (either due to refresh or new data), the copy worksheet changes too
- Bonus: You can format the new worksheet cells any way you want. It just works.
Here is an example of what you can do.
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.
- Cosmetic / formatting reasons. A blank column makes things easy to read
- To add commentary / notes / extra data
- 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.
In this case, you can use calculated items feature of pivot table like this.
- Select any region name in the column labels are of pivot
- Go to Home > Insert > Calculated Item
- Give your calculated item a name like “North by West %”
- Write the formula =North / West
- Click ok
- This new column will added to your pivot, like this:
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.
- Take a few deep breaths
- Insert your favorite expletive in this sentence “______ pivot tables” and shout it.
- Use Power Pivot
- If Power Pivot cant be used, copy the pivot as references and manipulate the layout as you wish.
Happy pivoting.
18 Responses to “How to insert a blank column in pivot table?”
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
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/
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.
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.
That is great. So simple a solution when you think about it... .. elegant.
[…] http://chandoo.org/wp/2015/04/16/how-to-insert-a-blank-column-in-pivot-table/?utm_source=feedburner&… […]
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
Hi Chandoo Anna,
You are superb.. !!
Its a cool tip and more likely in creating dashboards this event would do magic.
Super Machi 🙂
Thank you very much... its awesome tips
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 !!
THIS MAKE LIFE SIMPLE
My comments doesn't stay with the row when I filter the pivot. So I assume this only works if have a set report and no requirement to filter?
What is meant by "5.Paste as references – ALT+CTRL+V and L" ? The function as displayed does nothing "and L"
Hold alt, hold ctrl, press V.
Then let go of all 3 of those keys.
Then press L.
Thanks! I was trying to figure that out.
This doesn't work. The creation of the initial reference table works, but if you add more data to the pivot table and refresh, not ALL of the data updates in the reference table.
I have a pivot table with two columns 1) sum of a data column 2) count of the same data column.
I'd like to add another column with % of 1)/2).
Ex: 1) 17, 2) 20 so the 3rd column should be 85%.
Is this possible?
Thanks.
Love the tone and dialogue! Thanks for your help!