Create and format PivotTables and charts

Create PivotCharts

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

PivotCharts help you make sense of large amounts of complex data. While a PivotChart shows data series, categories, and chart axes the same way a standard chart does, it also gives you interactive filtering and zoom controls right on the chart, so you can quickly analyze a subset of your data, and see comparisons, patterns, and trends.

Create a PivotChart on a new worksheet
  1. Select a range of cells or a table.

  2. Select Insert > PivotChart InsertPivotChart .

  3. In the Create PivotChart box, under Choose the data that you want to analyze, choose Select a table or range, and then in Table/Range, verify the cell range.

  4. Under Choose where you want the PivotChart to be placed, select Newworksheet to place the PivotChart in a new worksheet.
    Or, select Existing worksheet, and then select the Location.

  5. Select OK.
    A new worksheet opens, with a PivotTable placeholder, PivotChart placeholder, and PivotChart Fields pane.

  6. In the PivotChart Fields pane, select the fields you want to include in your PivotChart.

  7. Drag the items you want from the field list into the LEGEND (SERIES) area.

    Note: You can also drag the items from the LEGEND (SERIES) area into the AXIS (CATEGORIES) area.

    Pivotchart legend
    Think of the LEGEND area like COLUMNS in the PivotTable Fields box, and AXIS like ROWS.
    The PivotChart and PivotTable are created simultaneously. Any changes to one are reflected in the other.

Change PivotChart style
  1. Select a PivotChart.

  2. Select Design > Chart styles.

Want more?

Discover more courses like this at LinkedIn Learning

Create a PivotChart in Excel 2016 for Windows

Notice that on the Insert tab and not near pivot table off to the left but way over here under Charts we've got an option here called PivotChart.

Use pivot charts to graphically summarize data and explore complicated data.

Let's create a pivot chart. Now we will get a pivot table at the same time.

Now we could put this on the same worksheet. I've got data off to the right there.

Or we'll just put it on a new worksheet.

How about a new worksheet? We'll simply click OK.

Now, what's a little bit different here is that the pivot chart fields list, first of all, does not say pivot table fields list, instead of seeing row and column areas we're seeing axis category and legend series here.

But think of these as being row and column.

So I'm going to check the box for salesperson.

We see a pivot table off to the left.

We see a chart without much in it.

I'm going to check the box for number of items now we're seeing a chart that's starting to make some sense.

And I'm going to drag the word product into the legend area, it's the same as dragging it into the columns area.

And there we are, we've got a chart.

I strongly suggest that when you create a pivot chart that you consider making it be a stacked column chart.

Now that's not a very large pivot table in terms of numbers there.

But the chart's a bit difficult to read.

I think we can make some sense out of it.

I wouldn't call it horrible, but let's immediately note because it is a chart we've got a Design tab and a Format tab just as we do with other charts.

So on the Design tab off to the right, change chart type and at least consider stacked column. Double click it.

And I think that's a lot easier to read.

And we can see at a glance who our best performer is.

And we can see it from the numbers too, but it's Sam Ramey well ahead of the others, and we can see that in the numbers too.

A pivot chart is in sync with the pivot table at all times.

Nothing about refresh or anything like that that you worry about.

Any change to the pivot table changes the chart.

So if we were to pivot this, I'm going to switch the two fields, salesperson and product, as we do this watch the chart.

I'm dragging product, ultimately it's the same as dragging it from column into row even though the labeling over here says legend and axis.

Bring product down here, and the chart for the moment probably isn't so great.

Drag salesperson into legend, let me see what's happening.

And we can see at a glance that the Media Armoire here is our best-selling item.

Based on what we're seeing here.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits
  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×