There is often a need to visualise your data in order to see a trend or give someone else a good overview. For all these cases, Excel provides PivotCharts that you can create very easily from your previously created PivotTable (For a guide on how to create a Pivot Table, see my previous post).
Creating a Pivot Chart
In order to create a Pivot Chart, you must click into your Pivot Table and then click the PivotChart button under the Insert ribbon. What you will see is a wide choice of different charts for visualisation.
For our purposes, I will use again my trusted randomly created data set with no trends whatsoever in it (you can download it here) and create a column chart of Sales and Profit by Item Type, which will look what you see below.
What you will find is, that if you change the values in the Pivot Table, the chart will adjust itself, too. In order to visualise two different things from the same data set, you would have to (to the best of my knowledge) create two separate Pivot Tables with their respective charts associated.
Now the last thing, I wanted to discuss before I close this entry off is how to drill a little bit deeper here. It is nice that we can see now all our sales by item, but what if we want to know, how much of our items wer sold in say Brazil and how much was sold in March.
Filters and Slicers
There are two options here; Filters and Slicers. If you want to use filters, it is very easy. Just drag and drop the country into the box named filters. Over the Pivot Table you will then see a drop-down menu that allows you to look at the numbers by country.
Alternatively, if you want to have something more visually pleasing and usable with less clicks, slicers are your tool of choice. Under the Analyze ribbon of the Pivot Table Tools, you find a button called “Insert Slicers”.
Click on that and choose countries. What you will find is a very nice button driven graphics user interface, that allows you to filter those very fictional sales numbers by country.
PivotCharts allow you to easily visualise your data in an effective manner. If you want to have a dynamic deep dive into the collected data by filtering it over different parameters (such country or date), you can utilise slicers or filters.
I hope this short introduction was helpful to get you started. There is a whole plethora of customisations in the sections I just covered. The best thing to do here is to take your data and try to get analysin’ 😉
I hope you enjoyed reading this section as much as I enjoyed writing it. Leave a comment if you would like to add something.