How to pivot column and rows to create a more meaningful matrix report.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 01:59
- - Skill Level: 4
- - What's my skill level
Pivot Column and Rows
The real power of pivot tables is the ability to combine row and column labels to generate meaningful reports.
So from this dump of data we have a very simple report here showing the Bill in months. If I go and perhaps I take the day of the week and I put it in my column label, with one click, I suddenly have a very useful report which tells me that in January, my day 1 (Monday) – so much was spent, day 2, day 3 etc.
The power here is that you can now go and add another aspect to the report, so, for example, you could go over here. You’ll see if I take type- type says whether it was a call or a sms or a text? If I grab, put it in front of the month and let go. So now very quickly I’ve got a row label and a column label which splits between my calls and sms’s, by month and by day of the week.
Perhaps I want to take the time and put it here and you can see now closer together we can see the split between call and sms. Let’s keep adding to this report and let’s go here and we’ll see the split between weekday and weekend, grab it and I want to put it before my day of the week, I let go and now you’ll see where we have information for the weekdays and the weekends and because the sub-totaling is there, you can see we have got sub totals for each of them. Perhaps the time should move up, perhaps that’s a better reflection of the report. As you can see, it is extremely easy to turn that into significantly more meaningful reports.