What is a Pivot Table in Excel? Everything You Need to Know
Get a handle on your data fast. Create analyses simply in Microsoft Excel, without the need for formulas. In this article, we’ll see how you can create your PivotTable in less than a minute. You can then expand it and group sets of data together. If you haven’t created PivotTables, and you use Excel, this is for you. Let’s look at various answers to the question: What is a PivotTable?
A PivotTable is a summarized analysis
A PivotTable is a summary of data contained in a Microsoft Excel spreadsheet.
My source data has a data set with 253 rows, containing details of some CDs and other audio files.
This is perfect data to use for a PivotTable. Your data source can be a few rows, or it can be hundreds of thousands of rows. The more, the better.
A PivotTable is a deduplicator
PivotTables allow you to view a list without any duplicates. In my data, I have 253 rows of data about audio files, which come from 20 different albums. An Excel Pivot Table allows you to quickly list those 20 albums or any other analysis you might want.
Last Updated May 2021
Get Expert certified! Levels 6-10 for Microsoft Excel 2010, 2013, 2016 and Excel 2019. Covers 77-728, 77-427 & 77-428. | By Phillip BurtonExplore Course
A PivotTable is time-saving
To create a list of those 20 albums, you can create a Pivot Table. All you have to do is:
1. Highlight the data.
2. Go to the Insert tab and then click create PivotTable.
After you click OK, a new spreadsheet appears in your workbook. It includes a blank PivotTable and a PivotTables Field pane:
3. Go to the new PivotTable Fields pane. Drag and drop the Album field from the PivotTable Fields List into the Row Fields.
And that’s it! You have now created a Pivot Table and started your analysis in a few seconds.
PivotTables allow you to count the number of entries
PivotTables allow you to show not only the album names but also how many times they occur.
You can do this by adding the album field into the values area.
Now you can see how many entries there are per album, together with a Grand Total.
PivotTables are quick and easy
The above might seem like a lot of steps, but you can do it in less than a minute.
If you are not sure what might be important in your data, then you can go to Insert — Recommended PivotTables. Excel then can generate a list of suggested PivotTables.
This can be a good way to create a PivotTable if you are unsure of the data. But Excel often doesn’t suggest anything useful:
Your Recommended PivotTables will sometimes give you some useful examples. If you were running a store, your source data might be the items sold. It could show the total sales per salesperson or the number of items sold per country.
PivotTables allow you to error-check data
PivotTables allow you to error check. Suppose you had two albums, “a musical offering” and “a musical ofering.” The second has a spelling error. A PivotTable analysis helps you spot this error and correct the data.
You can then sort the results by album order or by the number of occurrences. To sort it, click inside the PivotTable in the relevant column, and go to Home — Editing — Sort & Filter — Sort.
If an album has many entries, then it is probably good data. If it is only there once, then it may be a typo that needs to be corrected.
PivotTables are not formula-intensive
I could create a calculation like PivotTables using Excel formulas. But I would have to use more than one COUNTIF or COUNTIFS functions, or maybe use an array formula.
A PivotTable gives you a quick answer when you create it. But the PivotTable doesn’t change when the data changes. It doesn’t automatically recalculate based on updated data.
- This can be bad because now the analysis is out of date. If you rely on an outdated analysis, this may lead you to wrong conclusions.
- This can also be good. Excel is not spending time re-evaluating the formulas. You can reduce the amount of time needed to recalculate formulas.
You can easily update the analysis when you need to. Right-click on the PivotTable and selecting “Refresh.” You can also do this by going to PivotTable Analyze — Refresh in the Excel ribbon.
Your PivotTable will then update based on the latest data.
PivotTables let you create an analysis with more than one field
Suppose you wanted to create an analysis based on two fields: the album’s name and the Purchase Date. That’s no problem for a PivotTable. All you need to do is to drag the second field, the “Date Purchased” field, into the row field.
If you drag a text field, then you will get that text field included in the Analysis.
In Excel 2013 and later, when you drag a date field into the Rows area, three fields are automatically added. These are the Year, Quarter, and Month of the date.
By adding fields, you can get an analysis for each album and an analysis per album per year. You can then drill down into quarters and months by clicking on the plus sign next to the year.
If there are more row fields than you want to include, you can always delete one. You can either drag the fields out of the Rows section or right-click on them and go to “Remove Field.”
You also use this context menu to adjust the order of the fields. You can also do this by dragging the fields in their new position. For now, I will delete the “Date purchased” field (which currently shows the month) and the Quarter field. This leaves the Album and Year fields.
PivotTables are customizable
There are three different ways of viewing the PivotTable analysis. These are useful when you are viewing an analysis with multiple row fields.
- The default view, which you can see above, is the “Compact Form.” All of the row fields are shown in a single column, with the head “Row Labels” (in the above picture in blue).
- A second view is the “Outline Form.” It shows each row field in a separate column.
The totals for each album are shown above the data that it totals.
- A third view, and one I use more often, is the Tabular Form. The total for each section is shown below the data:
You can change between these different forms by going to PivotTable Design — Layout:
PivotTables can have conditional formatting
The measures in the Value area can include conditional formatting. You can show bars behind each number, which graphically show how many items there are per row.
To do this, highlight the values and go to Home — Styles — Conditional Formatting — Data Bars. You can then select what color data bars you want to show.
Alternatively, you can use a red-green or hot-cold background, which shows the same thing in a more colorful way.
PivotTables can help you turn a row field into a column field
The word “pivot” means a central point on which a machine turns. This is where the PivotTable gets its name. If one field goes down and another goes across, this second field is “pivoted” 90 degrees across.
In a PivotTable, you can have the album’s name going down, and the Purchase Year going across. All you have to do is to have the “Years” field in the columns area of the PivotTable.
It can be more complicated to do this if you have a lot of columns going across. If you have a limited number of columns, you can see more of your analysis across a page. Because of this, your analysis may be easier to understand.
You can group PivotTable categories together
Suppose you wanted to group albums into various eras, such as Romantic, Classic, and Baroque, or you wanted to group years into decades. Well you can, in your Pivot Tables, group these items together.
You could categorize items by adding an extra column to the original data. You can also do this directly in the PivotTable.
1. You can drag the relevant album names into adjacent cells.
2. You should then highlight the album names you want to group together.
3. Right-hand click on them and then select “Group.”
These albums are now grouped together into a new “Group1” group, with the other albums being shown in separate groups. You can then group the remaining albums and rename all your groups.
So what are PivotTables to me?
To me, PivotTables are an essential part of my Excel weaponry. When I receive new data, PivotTables allow me to get an initial view of that data that allows me to see if there are any gaps. This has happened to me in the past.
It allows me to see what may be important in the data, and to compare two different columns. I can see if there are any correlations or comparisons between them.
Want to learn more about PivotTables?
Would you like to find out more about PivotTables and similar topics? This is just one of many topics in my Udemy course “Excel in Microsoft Excel 2: Intermediate to Expert Advanced,” together with advanced conditional formatting, formulas such as COUNTIFS, VLOOKUP and MATCH, and Visual Basic for Applications.
Why not have a look at my course for PC and see what you could learn? I have a course for Mac users as well. Alternatively, take a look at my next blog post about PivotTables. I will go into more detail so you can learn about how to create PivotTables and what data structure you need to create a PivotTable. I will discuss how you can customize PivotTables by using Field Settings, such as changing the aggregation (SUM and COUNT) and number format. And I will cover how you can enhance PivotTables to make a dashboard in Excel.
Thank you very much for reading this, and I hope you will enjoy the next post in this series. I also hope that you will find out how easy PivotTables are to use in your analysis very soon.
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 for Business.