fbpx
Search
Close this search box.

How to insert a blank column in pivot table?

Share

Facebook
Twitter
LinkedIn

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.

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

18 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

  10. Chantelle says:

    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?

  11. FAM says:

    What is meant by "5.Paste as references – ALT+CTRL+V and L" ? The function as displayed does nothing "and L"

  12. Mel says:

    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.

  13. Amit says:

    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.

  14. Sharon says:

    Love the tone and dialogue! Thanks for your help!

Leave a Reply