How to pivot column and rows to create a more meaningful matrix report
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.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
"The best Excel course I've ever attended. Thanks!"- F. Swanepoel Sun International– F. Swanepoel – Sun International