In this post I will guide you via lots of screenshots on how to make an Excel Pivot Table that is very dynamic (a dashboard indeed) and has data labels that are spread across several columns.
Firstly, download the sample Excel data I used via this link
Now you'll end up with an Excel sheet like this below
Select the cell A1. Go to Insert, Click on Pivot Table
You'll end up with a new sheet like this -
The parts I want you to notice are these -
Now you'll drag the fields from the Choose fields to add to report to different areas as shown below
You'll end up with a messy table like the one below -
What we want is something like this -
So how do we do this?
Just follow along.
Go to Design, Report Layout and Choose Show in Tabular Form
Again go to Design, Report Layout and choose Repeat All Item Labels
Sorry, I thought I had it highlight before taking the screenshot, it's the option below the highlighted one
Now you're nearly done.
Right click on Cell A7, Untick Subtotal "Region"
Again right click, but in cell B7, untick Subtotal "Rep"
Now you're done and should have something similar to that below (our goal)
You've just completed an advance Excel lesson.
Feel free to comment/hire me via the comment box below.