Quite a long time ago, I wrote a post Fill in the blanks – quickly to talk about the tip to turn a human-reading-freindly table into an Excel-friendly table, as shown below:
Have you ever thought about why we are doing this? Of course you know because we need the “right” table for further analysis. Let me rephrase my question: Where is that Excel-unfriendly table on the left coming from? Bingo. #PivotTable in #Excel itself.
Most of the time (in my experience) your colleagues summarized / aggregated data in a pivot table, then copy and paste it as value before sharing to you… Sound familiar?
This kind of problem is not uncommon, even nowdays. Why I am saying this? Because there is an easy way to fill in the blanks in Pivot Table itself but not many people are aware of it yet.
Go to Design under PivotTable Tools –> Report Layout –> Repeat All Item Lables
Note: Make sure the pivot table is selected in order to have the PivotTable tools on the ribbon activiated
Here we go!
As simple as this.
Did you know that this function is available since Excel 2010? Yes, it has been sitting there on the ribbon for 7 years! 🙂
I always wonder… How many years it takes for users to be aware of the exisitence of some new cool features/functions in Excel? Probably when new turns old. 😛
Do you know any cool functions/features in Excel that has been left unnoticed for too long? Please share with us in comment.