Phillip Burton

In my previous blog article, I described:

In this blog article, I will describe:

What format should your data be in?

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

Microsoft Excel: Pivot Tables, Power Query, Power Pivot/DAX

Last Updated January 2023

  • 226 lectures
  • All Levels
4.5 (621)

Microsoft Excel Pivot Tables, Get and Transform (Power Query) and Power Pivot (DAX) for advanced data analysis. 70-779. | By Phillip Burton

Explore Course

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:

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:

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:

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.

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:

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?

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:

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:

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

Page Last Updated: February 2021

Top courses in Excel

Excel students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo

Courses by Phillip Burton