All About PivotCharts

show more Using PivotCharts provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Using PivotCharts

When you're working with Pivot Table data, invariably it's about the numbers. And as we work with numbers in Excel sometimes it makes sense to have a chart as well. You can create a Pivot Chart along with a Pivot Table. Also at any time when working with an existing Pivot Table, you can instantly create a chart. For example, if we go to the Slicers sheet in this workbook, Pivot Tables. If we want a Pivot Chart based on the Pivot Tables that's visible simply click within the Pivot Table and press Alt+F1 and there's a chart.

One suggestion with Pivot Charts, make them be stacked column. Not always the best solution, but many, many times even when a Pivot Table doesn't seem to have that much data and a Pivot Chart looks fairly crowded as this one does. Let's go to the Design tab in the ribbon when the Pivot Chart is selected and the second button from the right, Change Chart Type, Stacked Column, possibly stacked the bar, Double-Click tends to create a cleaner looking chart, not so crowded. If you have created a Pivot Chart it is in sync with the Pivot Table at all times, so if for example, if we go into the Pivot Chart fields to the right and change the location of Product and Salesperson.

I'm going to drag product down here. Notice the look of the chart right now, fairly crowded and the labels look terrible across the bottom and put Salesperson here. Also note that the wording looks different than it does typically here. Instead of seeing column here, we see the Legend (Series) and instead of row we see Axis (Category), but I think you can see here the chart will at times compliment the data to give us that quick visual read that we want. Let's go back to the worksheet called PivotChart and show how when we're creating a Pivot Table we might want to create a Pivot Chart as well.

INSERT tab, we do see the choice Pivot Table off to the left but off to the right in the Charts group we see PivotChart, click it. And we're on our way to be creating a Pivot Chart. Now, although we don't see the term Pivot Table at the top, we will be creating a Pivot Table as well. Suppose we want both of these on this worksheet. Choose where you want the Pivot Chart to be placed; Existing Worksheet, Location, how about J1? That's the upper left hand corner, click OK and we're about to see a Pivot Chart.

These screens tend to be a bit busy so let's scroll rightward, and chart fields, let's say, we want to see Salesperson, down the left hand side. Product, now I'm dragging this into and area called Legend. This is analogous to column and let's say, we're tabulating; this time maybe Total Cost, drag that to the VALUES area and we got a Pivot Table as well as a Pivot Chart. As you would expect changes to the Pivot Table immediately occur within the Pivot Chart as well.

If the Pivot Chart is selected, the terms we see here as I pointed out earlier are bit different but the concept is the same. If we click within the Pivot Table as I'm doing now, we see the term Columns and Rows. Again, we can change the order this way, Product, Salesperson, drag product into the row labels area maybe, drag Salesperson in the Columns. When we are looking at data like these two, particularly with the large, large numbers, one of their feature here that you want to have control over is when the numbers are looking like this, a bit difficult to read.

First of all do we really need the decimals here? Perhaps not, Right-Click anyone of these, chose Number Format and from there choose an option that doesn't show decimals and yet does show commas as a thousands separator. And so that cleans up the list a bit, and now we've got our Pivot Chart and Pivot Table together. Remember, they're always in sync. You can create a Pivot Chart instantly by simply clicking in a Pivot Table and pressing Alt+F1 or you can create a Pivot Chart and Pivot Table together from your source data.

Using PivotCharts
Video duration: 3m 59s 6h 32m Appropriate for all


Using PivotCharts provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...