How to fill blanks in Pivot Table [Excel Quick Tip]

How to fill blanks in Pivot Table [Excel Quick Tip]

Pivot tables are great for summarizing and analysing data.

I use it very often and I believe you too.

But there is a small problem…

…i.e. pivot tables do not show repeated column labels or content.

Meaning – we need to fill blanks in pivot table to do v-lookup

Ok..its difficult explain so here is an example.

pivot-table-blank-lable

In my above example, region “East” has appeared once and similarly other regions as well.

This is perfectly fine if you are just summarizing but when it comes use the same table for doing v-lookup based on region & product combination then it’s a problem.


Traditional Method


Possibly you may have used this method.

Step1: Convert pivot table into a normal table by using paste special values.

Step2: Select entire table and hit Ctl+G  for Go to option→ Special → Blanks → Click ok → Equal to above → Ctl+Enter

Step3: Select entire table again Ctl+C → Paste Special → values

Oh finally we have filled our parent labels for respective sub labels

Step4: Insert a blank column on the right side and by using concatenate formula you will join both region and product together to have unique lookup value and then you do the v-lookup

What a mess…!!

Here is a result out of the above steps.

Repeat labels using go to special

 


How to fill blanks in Pivot Table using excel builtin method


Note: this method only works with Excel 2010 and above, in case if you are still using Excel 2007 then you need to follow the traditional method as explained above

Step1: Place your mouse pointer anywhere in your pivot table

Step2: Head over to design section from the menu bar note: this menu will not show up in case if you don’t follow the first step

Step3: Under design, click on ‘report layout’ 

Step4: From the drop-down select ‘repeat all item labels’  

That’s it

how to fill blanks in Pivot Table


How to deselect Repeat all item labels


Under the report layout section there is an option to deselect i.e. ‘Do not repeat item labels’ this would bring back your pivot table to default settings.

Also read: Top 10 reasons for excel formulas not working


Conclusion


That comes to an end of our quick tutorial on how to use excel repeat item labels to fill blanks in pivot tables. If you like it please share, in case if you dont like please let me know your feedback on comments selection.

Also, do let me know if you have any topics that want me to cover.

Leave a Comment