If you already have a pivot table in your worksheet then you can insert a pivot chart by using these simple steps. Select any of the cells from your pivot table. Go to Insert Tab → Charts → Pivot Chart and select the chart which you want to use. These pivot charts use a reversed secondary axis to place position 1 rankings at the top of the chart. To be able to do this, we’ll need to edit the secondary axis. Right click on the secondary axis in your chart, and choose “ Format Axis “. To create a Pivot Chart in Excel, select the data range. Then click the “Insert” tab within the Ribbon. Then select the “PivotChart” drop-down button within the “Charts” group. If you want to create a PivotChart only, then select “PivotChart” from the drop-down or if you want to create both a PivotChart and PivotTable, then.

Here’s how to build a pivot table in Excel:

1. Gather your data.
2. Create a table.
3. Head to Insert > Pivot Table and add to a new sheet.
4. Add axis fields, values, column labels and filters.
5. Add filters by dragging important fields into “Filters”.
6. Use sort to arrange your table in whatever order you choose.

I’m going to take the process step by step in my installation of Excel 365. The process will also be more or less exactly the same for versions 2013, 2016 and 2019.

Gather your data and create a Master table

Pull down some keyword data using your keyword research weapon of choice. Today, we’re using SEMrush. Here’s an animated gif of the process I used with SEO Tools for Excel and the SEMrush API:

Using SEO Tools for Excel to export data via the SEMrush API.

You don’t have to use tables, but I really like to. Amongst other reasons, tables seem to be highly performant, less work for you in this particular use case and they can be fun to name.

Once you’ve got your data, make a Table by selecting the whole dataset (CTRL+SHIFT+DOWN then RIGHT), then CTRL+L creates the table:

Name the table something memorable, like “rankings”.

Create a Pivot Table on a new sheet

Now we have all of our data nicely arranged in one place, let’s get to the fun part. We’re going to add a pivot table to a new sheet.

Head to Insert > Pivot Table and add to a new sheet, just like this:

When you click “OK”, you’ll be presented with a blank Pivot Table “field list” and a Pivot Chart “filter pane” on the right of your screen and a very blank looking space on the left called “PivotTable1”.

Add axis fields, values, column labels and filters

If you’re new to Pivot Charts, you’re about to experience a bit of a penny drop moment. We’re going to look at which items of data should be placed where and you’ll see very quickly how a pivot table works.

The PivotTable Field List uses drag and drop functionality to enable you to populate those little white squares with values. As you add values, the table on the left begins to form.

Start by picking up your keywords by dragging the “keyword” field into the “Rows” box. Next, drag and drop your search volume figure into the “Values” box.

Provided you’re looking at “Sum of Search Volume” and not “Count of Search Volume”, your table on the left will start to make a lot of sense:

You’ll very quickly notice that you’ve created a thing of beauty.

A pivot table with all of the keywords in your list and all of their corresponding search volume values.

I call this the pivot-table-penny-drop-moment. Having all of your values in a pivot table might not be what you intended, though, which is where the Filters come in.

As you can see, I dragged the “Position” field into “Filters”. This adds a helpful drop down above the Pivot Table, which I can use to filter out the very low search volume values.

To create a filter, follow the yellow arrow (in the screenshot above) to the filter drop down and check the “Select Multiple Items” checkbox. You’re now free to clear out any irrelevant data from your table, in my case, low search volme data.

Finally, drag down the “Position” values into “Values” and you’ll have a pivot table, with keywords, search volume and rankings ready to build a chart.

Create your pivot chart

Head to Insert > Column or Bar chart to insert your chart. It may look a little rough around the edges though, so let’s make it look a lot nicer than this:

First, we need to organise the keywords by search volume so we can look at our chart as a tail graph. Highlight your search volume data (the column you’d like to sort in volume order) and select “Data > Sort“.

This will improve matters slightly, but there might be a few too many keywords in the chart. Try filtering out the lower volume terms, at least for the time being.

Sort out the rankings by keyword

The only remaining challenge is to arrange the rankings in such a way that they make sense, visually.

You should never compare fundamentally different types of values on the same chart axis, so let’s create a secondary axis for the ranking values.

First, you need to select and format the rankings data series. You could use your right mouse button on the chart and select “format data series”, but that’s fiddly and unnecessary.

Instead, select your chart and navigate to the “Format” pane.

You’re looking for the “Current Selection” drop down on the far left-hand side of the screen. Select the drop-down and click your rankings data series, now, select “Format Selection”.

We’re going to place the data on a secondary axis, and change the chart type to a line chart. Finally, we’ll remove the lines in the rankings chart to leave only the markers.

Dealing with the secondary axis and changing chart type

Your pivot chart, by default, shows both data series as bar charts. We need to select our series once more (using the Format Selection drop down), and then right mouse button click on “Change Series Chart Type”.

Select “Custom” from the options in this dialogue and select the Stacked Line option for your secondary axis:

These pivot charts use a reversed secondary axis to place position 1 rankings at the top of the chart.

To be able to do this, we’ll need to edit the secondary axis.

Right click on the secondary axis in your chart, and choose “Format Axis“. Setting your minimum value to “1.0” will exclude all of the zeroes in your rankings data and setting a maximum of say, 15 would exclude any ranking higher than 15th. You choose the range that’s right for you.

Create A Pivot Chart In Excel 2013

Finally, scrolling down these options will reveal a checkbox: select “values in reverse order” and you’re almost done.

How To Do A Pivot Chart In Excel 2016

Finishing touches

One tiny point left to do, we should change the chart type so our secondary data makes a little more sense.

Select the rankings data bars and navigate to “Design > Change Chart Type”. Select the line graph option with visible data markers in the line. Now, take out the line colour from inside “Format Data Series > Line Colour” and you’re done.

Here’s the chart I reproduced while I was writing the blog post:

And there you have it. How to make a pivot chart and table in Excel. Have fun making your own!

This Excel tutorial explains how to create a pivot table in Excel 2016 (with screenshots and step-by-step instructions).

See solution in other versions of Excel:

What is a Pivot Table?

A pivot table is a tool that allows you to quickly summarize and analyze data in your spreadsheet.

How To Do A Pivot Chart In Excel 2013

You can use a pivot table when:

See Full List On Wikihow.com

• You want to arrange and summarize your data.
• The data in your spreadsheet is too large and complex to analyze in its original format.

If you want to follow along with this tutorial, download the example spreadsheet.

Steps to Create a Pivot Table

To create a pivot table in Excel 2016, you will need to do the following steps:

1. Before we get started, we first want to show you the data for the pivot table. In this example, the data is found on Sheet1.

2. Highlight the cell where you'd like to create the pivot table. In this example, we've selected cell A1 on Sheet2.

3. Next, select the Insert tab from the toolbar at the top of the screen. In the Tables group, click on the Tables button and select PivotTable from the popup menu.

4. A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to F16 in Sheet1 as indicated by Sheet1!$A$1:$F$16.

   Your pivot table should now appear as follows:

5. Next, choose the fields to add to the report. In this example, we've selected the checkboxes next to the Order ID and Quantity fields.

6. Next in the Values section, click on the 'Sum of Order ID' and drag it to the Rows section.

7. Finally, we want the title in cell A1 to show as 'Order ID' instead of 'Row Labels'. To do this, select cell A1 and type Order ID.

   Your pivot table should now display the total quantity for each Order ID as follows:

   Congratulations, you have finished creating your first pivot table in Excel 2016!