How to Create a Pivot Table in Excel: A Step-by-Step Tutorial

In my previous blog article, I described:

• What a PivotTable is
• How it is the first weapon in my Excel armory when handling a lot of new data
• How to create simple Pivot Tables, including using Recommended PivotTables

In this blog article, I will describe:

• How to create more advanced PivotTables,
• What condition your initial data needs to be in to use
• How you can expand your data, using PivotCharts, to create an Excel dashboard.

What format should your data be in?

First, you need some data. Here are some ideas of data you can use:

• It could be sales data — for example, your company’s stats on salespeople with their total sales per month. 
• It could be financial data with detailed transactional information.
• It could be website visitor data.

For this article, I am using data on the audio files I own, with the column labels of Filename, Album, Size, and Date purchased.

You must divide the data that you use into rows and columns. Here are some tips for creating the correct format:

• Each row needs to be about a similar item. For example, in my data, each row represents a different audio file.
• Each column needs to represent an attribute of the items. In my data, I have:
  • a numeric column for an ID number, 
  • a text column for the filename, and
  • a date/time column for the date purchased.

• The first row in your data needs to be a label for the item attributes. In my example, I have row headers for Filename, Album, and Date purchased. I have the row headers in a different color and font to distinguish the heading. It looks nice, but it is not a requirement.
• There should be no merged cells. If columns C and D share a cell, it can be difficult for Excel to know which column data is in when creating the PivotTable.
• Ideally, there shouldn’t be any gaps in the rows or columns. If I want to show a gap in the columns for presentation, I would still add a short column heading, but with the same foreground color and background color, so you can’t see the text.

• If there are gaps in the rows, it can cause one of these issues:
  • Your PivotTable might only use the data up to the gap, which means it won’t use all of your data.
  • This blank row will be in the PivotTable as if it was actually data. The filename, for example, would show up in the PivotTable as “(blank).”

Let’s create a PivotTable

Before creating the PivotTable, first highlight the source data. If you don’t, Excel will guess where the source data range actually is and may get it wrong. This is especially the case if there are gaps in the data. So let’s create a PivotTable by highlighting the range and going to Insert -> PivotTable and then clicking OK.

By doing this, we create a new spreadsheet in our Excel workbook. It contains the outline of a PivotTable on the left-hand side and a new pane on the right-hand side with the PivotTable Fields list at the top, and four areas below it.

Let’s create our first analysis

From here, we can drag a field down. Let’s drag and drop the Album field to the Rows area. If you want the analysis going across instead of going down, you can drag it into the Column area instead.

From just a simple drag of a field, you can see an analysis and that we have got around 20 different albums. At this stage, we have no idea how many rows each album is in the data, so let’s drag the Album field down again, but this time into the Values area.

So now you can also see the number of times each album occurs in my data set.

Now let’s find out each album’s size in bytes. To discover this, we can drag the “Size” field down to the Values area as well.

It’s looking good, but let’s look at the data differently. Instead of summing the album file sizes, let’s find the smallest file size. To do this, I can:

• Click on the drop-down arrow next to “Sum of Size.”
• Select “Value Field Settings.”
• In the new dialog box, change the “Sum” summarization to “Min,” and click “OK.”

Success! I can also add the Max of Size. To do this, I drag the “Size” field again to the Values area and then change it from “Sum of Size” to “Max of Size” in the same way.

This is looking like a useful analysis.

Expanding your data

Suppose you expanded your source data, adding an extra 1,000 rows. Unfortunately, your PivotTable won’t know you did that and will reference the same number of rows you had when you created the PivotTable.

To change the data range that your PivotTable should use:

• Click inside the PivotTable.
• Go to PivotTable Analyze -> Change Data Source -> Change Data Source. (In Excel 2010 and earlier, you need to go to PivotTable Tools -> Options -> Change Data Source -> Change Data Source.)
• Update the data source range in the dialog box that will appear with the current range highlighted.

If you need to expand your data regularly, then you should consider converting your existing range into an Excel Table. To do this, highlight the data and go to the Insert tab –> Table and click OK.

New PivotTables can use this Table object, and you can change the data source of existing PivotTables as well. The PivotTable is now linked to a Table that can automatically expand when you add extra rows, instead of linking to a range, which often does not automatically expand. Your PivotTable will always link to the correct number of rows. When you refresh the PivotTable, you will then get the updated analysis based on the expanded data set.

Calculating percentages

Now it will be useful to find out what each of these “Count of Album” figures is as a percentage of all the data. You can do this with formulas, but you can also do it in the PivotTable itself.

• First of all, let’s remove the Min and Max of Size analyses by dragging them out of the Values area (or clicking on them and selecting “Remove Field”). 
• Now let’s drag down the Album field again in the Values area, which makes a second “Count of Album” column.
• We can click on this new field in the Values area and go to Value Field Settings again.
• There are two tabs in this Value Field Settings dialog box. By clicking on this second tab, we can change how this calculates. 
• In this second tab, change the method of calculation by clicking on “% of Grand Total.” 
• You might also want to change this field’s name in the “Custom Name” section.

By doing this, we can see what percentage of each album’s entries are of the total. 

There are lots of other calculations that you can do in this second tab. It is worth exploring this tab when you are creating advanced PivotTables.

Adding a PivotChart

We can now create a graph, or chart based on this PivotTable. To do this, all we have to do is click inside the PivotTable and go to Insert –> Chart and select the type of chart we want to use.

The PivotChart and PivotTable interlink, so any changes made to the PivotTable will reflect in the PivotChart, and vice versa. In this example, I remove “Count of Album” from my PivotTable by dragging it out of the Values area. Once I do this, this field is removed from the PivotChart as well.

Filtering values

You can also filter data. For example, we have a typographical error in our list of albums, “a musical ofering” is misspelled. There are a few things I can do with data like this:

• I could correct it in the source data.
• I could group similar items together in my PivotTable, which I did in my previous article.
• I can remove it from my analysis. 

To remove it, I click on the drop-down arrow next to the “Row labels” in my PivotTable, and uncheck this item.

Selecting a category of data

In the previous article in this series, I categorized the albums into types, such as Music, Opera, and Audio. What if I only want to show only one of these types?

• First, I have to recategorize these albums. Have a look at my previous PivotTable article to see how to do this. 
• I’m also going to rename this new field to “Album Type.” One way is by clicking on a field and going to PivotTable Analyze (or PivotTable Options in Excel 2010) -> Active Field -> Active Field.

• Having grouped these albums, I could drag the “Album Type” field into the Filters area of the PivotTable (earlier versions of Excel, called this the Page section). This area will allow me to use a drop-down list to restrict what Album Types appear.

That worked, however, there is another, more user-friendly interface which Excel 2010 introduced called a Slicer. To add a Slicer, click anywhere inside the PivotTable and go to PivotTable Analyze –> Filter –> Insert Slicer, and select the field you want to slice.

This now shows a Slicer that contains the values in this field. You can then click on any of them (or more than one, by holding down Ctrl) to filter the PivotTable. For me, it is an easier way than having to go to the Filters area and more useful when you are creating a Dashboard.

Creating a dashboard in Excel

A dashboard usually includes more than one analysis. Let’s create a second PivotTable the same way that we created the first and put it in the same spreadsheet. This time:

• Drag the “Year of Date Purchased” field to the Rows area (see my previous article for how to add Dates to PivotTables)
• Drag the “Count of Album” field to the Values area.
• Create a Pie Chart based on this second PivotTable.

Our analysis is now looking like a Dashboard. Unfortunately, the Slicer only affects one PivotTable, not both. So if I have selected “Opera” as my Album type in the Slicer, this creates a filter in the first PivotTable and its PivotChart, but not the second PivotTable or its PivotChart.

To allow a Slicer to affect multiple PivotTables at the same time, right-hand click on the Slicer and go to Report Connections. 

Currently, there is only one checked PivotTable. When we check both PivotTables and click OK, the slicer is now connected to both PivotTables. When we change the selection in the Slicer, both the PivotTables and the PivotCharts change. We have now created a working Dashboard in Excel.

Adding a Timeline

Let’s develop our dashboard further by inserting a Timeline. Excel 2013 introduced this feature. To insert a Timeline, click inside a PivotTable and go to PivotTable Analyze –> Insert Timeline. Excel will then show all the available date fields.

Click the “Date purchased” field and then “OK.” We now have a new Timeline. As with the Slicers, we can right-click on the Timeline and select “Report Connections” to connect it to both PivotTables and their PivotCharts.

Now we can graphically restrict the range of dates in the PivotTables. We can look at a single year, or a group of years, or we can drill down, using the indicator in the top-right, to view the timeline by quarter, by month, or by day.

Thanks for reading this article

I hope that you enjoyed this article. There is a lot more functionality to explore in PivotTables. 

My course, “Analyzing and Visualizing Data with Microsoft Excel,” contains:

• Six hours of PivotTables and PivotCharts videos
• Plenty of practice activities, so you can be sure that you are learning
• Hours of videos on “Get and Transform” and PowerPoint, which will take your use of Excel to the next level

If this interests you, take a look at my course to learn even more. Thank you very much for reading this and keep learning!