Pivot tables are great for summarizing and analyzing 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.
In my above example, the 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 the 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.
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 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.