Once we have our pivot table built, we may want to create a chart or graph to see the results. Let’s use the data still from the previous pivot table posts.
Say we have our pivot table here:
This is a listing of all the gold medals won in each sport, by country in the 2012 Olympics.
If we want to see this visually, we can build a chart out of this.
To do this, put your cursor on the pivot table so your pivot table tool menu appears. Then go to Options and over to Pivot Chart.
Once you click on it, the wizard will pop up asking you which type of chart you want.
There are many different types and different variations within each. It is up to you to decide which type will best reflect your data. It usually depends on how much data you have and the type of data it is.
For this example, let’s just use a simple bar graph, which is the first option. I press the Stacked 3d Column graph and press ok:
The result is this:
Now you can decide, if you want to keep this chart or try a different type. For now, let’s keep it and just edit the chart a bit. When you click the chart, you will get a new toolbar at the top of your screen to edit it. It has many options to change your pivot table.
From here you can edit the Design, Layout, Format or Analyze it.
The first button let’s you change the look of the pivot chart.
Chart Styles – This is where you change the color of the lines and columns. Just click on the one you like or click the drop down and the right to see many more options.
Chart Layouts – Here you can change the layout of the pivot chart. It will still be a column chart but you can change how it looks.
Data– These buttons let you switch the vertical and horizontal axis on the chart. It would move country from the vertical to the horizontal axis just by pressing the Switch Row/Column button. If you press the Select Data button, it will bring up a wizard to allow you to manually add or remove fields.
Type – These buttons let you change the chart type. If you do not want a Column Chart anymore, you can change it to whatever one you want.
Here you can change the layout of the titles and labels.
Labels & Axes – HEre is where you tell if you want the chart to have a Title, Horizontal and vertical labels and a Legend. You can also tell where on the chart you want it to appear and you can format it. You can also choose to have gridlines on your chart here.
Here you change the formatting of the text on the chart
This whole toolbar is to change the look of the labels on the chart. You just click on any of the labels or legend and then you can pick the formatting to change the font color or style.
Here you can dig deeper into the numbers but inserting a slicer, refreshing the data or clearing different filters or field lists.
So when all is said and done, you can get a chart that looks like this:
The best part of this chart is that the pivot chart will update as the pivot table updates. So all you have to do is keep up with the data and the pivot table will update as you go!
Past Pivot Table posts:
So this ends my pivot charting tutorial. It is just a basic overview and there are many ways to dig deeper into this to get better results.
*** IF you found this useful and was looking for more help with pivot tables, Check out the Magic of Pivot Tables Course here