Pivot Table in Excel, How do You Create One?
What is a Pivot Table?
Microsoft describes a Pivot Table in Excel (or PivotTable if you’re using the trademarked function name!) as “an interactive way to quickly summarize large amounts of data”. It’s a pretty good description.
The PivotTable function is an essential part of data analysis in Excel. Before the PivotTable came along you’d need multiple functions tied together in a complicated and convoluted way to perform the same action that just takes a few clicks in a Pivot Table.
To say they revolutionised the way the average Excel user performs data analysis is an understatement. They are such a big deal that they have their own Wikipedia page.
For the end-user, Pivot Tables are remarkably simple to use and easy to learn. There are hundreds of brilliant articles on how to create your first Pivot Table as well as some excellent lessons on YouTube.
We’re going to contribute by showing you some of our highest rated videos that teach you how to create a Pivot Table in Excel. If for any reason you don’t get it the first time, don’t worry, we’ve included multiple Pivot Table tutorials to help you master this essential skill.
If you have an hour to dedicate to a Pivot Table tutorial, then start with the video below. This is a recording of a live class we held in 2019 that takes you through everything you need to know to start analysing your data using Pivot Tables. These live classes are all free as part of a Simon Sez IT membership.
If that’s too much, scroll down and we have some other, shorter videos taken straight from our Excel courses.
How does a Pivot Table work?
All Pivot Tables start life as a boring old range of data, for example:
You can create a Pivot Table in Excel from a range but we strongly recommend that you turn your range into a table as this makes it a lot simpler to add or remove data later on.
A few golden rules about your data range or table before you create a PivotTable with it:
- Every column should have a header. If one is missing, you won’t be able to create a Pivot Table.
- There should be no empty rows. There can be the odd empty cell, but no full empty rows. This can mess up a few things.
Once you’ve turned your range into a table (use Ctrl-T to do this quickly!) you then need to select a cell in that table, go to Insert on the ribbon and select Pivot Table on the far left.
This brings up the Create Pivot Table Wizard where you can start selecting your Pivot Table options.
Creating a Pivot Table
The first option you’ll notice is that Excel is asking you to select the table or range. Because we have already created a table and we were clicked into that table when we chose to insert the Pivot Table, Excel has done the hard work for us and has selected that table as our range of data.
You can also select whether you create your Pivot Table in a new or an existing worksheet. Once you hit OK, you’ve created your first Pivot Table.
What you’ll see next is a blank table to the left with a set of options on your right. These options are the Pivot Table fields and this is where the magic starts to happen.
Adding Data to a PivotTable
Using the PivotTables Fields panel you can now start to manipulate your data. There are four quadrants a Pivot Table uses:
- Filters
- Columns
- Rows
- Values
You can now start to drag the values in the top of the PivotTable Fields section into the quadrants below:
Depending on which quadrant you pick the table will format in a different way so there are a few rules to try to stick to:
- Numbers nearly always go in the Values quadrant. This allows you to perform calculations, summaries, averages etc. all from within your Pivot Table.
- Dates often go in the Rows column because…
- Anything you put in the rows column will become the row headings, anything in the columns quadrant will become column headings so for ease of use put the data with more options in the Rows column (it’s easier to scroll down rather than across for ages).
- The Filters quadrant does what you’d expect, it applies a filter to the entire dataset. Super useful if you just want to show something specific.
Say you have dates in your rows quadrant and a set of corresponding values in your values quadrant. Excel will automatically do a couple of things.
- It will condense the dates into months, quarters or years (depending on the data set).
- It will sum the values in the values field.
This is the Pivot Table starting to work. From your dataset, it’s now summarising that data by month for you. All within a few clicks.
Of course, you may not want that exact data and you may not want to add it together. The amazing thing about the PivotTable function is just how flexible it is. You can drag and drop, remove and change the data within those quadrants as much as you want and you’ll start to see just how powerful Pivot Tables are.
The data in our example is sales data. Above we’re seeing sales by month which is useful. But what if we wanted to see sales by branch and by date? Easy, we just drop the branch data into the rows column, under the date and we get a breakdown of that as well:
If we wanted to get even more detail we could then add the departments to the column data and we’d see a summary of sales by branch, by department and by date! You can quickly see that with very little effort on our part we can now draw really meaningful insight from our dataset:
Value Field Settings
How do you change what’s happening in your value field away from a sum? Simple, you need Value Field Settings.
To access these select any value in your Pivot Table, go to analyze on the ribbon and select “Field Settings”. Alternatively, click the little down arrow in the value quadrant and select “Value Field Settings”.
This brings up the options you have in relation to your values. You can average instead of sum, you can count or use Min or Max. You can then select how you show the values including adding some calculations and changing the number format.
All this and so much more is explained in this video:
Slicers and Filters
If you’ve grasped the basics and you’re ready to conquer some more advanced pivot table tutorials, then you need to know all about Slicers and Filters.
By filtering and slicing your data in a Pivot Table you can start to get analyse specific areas of your dataset and pull out interesting patterns. Plus, if you want to create an interactive dashboard that others can use, you’ll want to master these functions.
In this video our Excel expert Toby shows you everything you need to know:
If all the above isn’t enough Pivot Table for you, then we’ve got an extended Pivot Table video here for you. It’s 40 minutes long, so get comfy.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 90+ IT training courses.
Other Excel classes you might like:
