MVP #10: Making a Pivot table that has labels spread across several columns

, , No Comments
Hi there!
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.

Allons y!

Firstly, download the sample Excel data I used via this link 

Now you'll end up with an Excel sheet like this below



We are going to make a Pivot table from this.
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?
Easy.
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)


Congratulations!
You've just completed an advance Excel lesson.

Feel free to comment/hire me via the comment box below.

0 comments:

Post a Comment

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.

Thanks!