How to Create a Pivot Table in Excel? — The Easiest guide
If you ask anyone with decent experience in using Excel, about the most useful Excel feature, they will vouch for the Excel Pivot Table. It is one of the most searched Excel features on the internet, and for good reason.
Related:
Creating A Dynamic Pivot Chart Title Using Slicers
Dashboards In Excel Using Pivot Tables, Pivot Charts And Slicers
In this guide, let’s see what makes the Pivot table one of the most popular and powerful Excel features.
This guide covers
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.
What is the use of a Pivot Table in Excel?
The Excel Pivot Table function is an essential part of data analysis in Excel. Before the Pivot Table 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 an Excel Pivot Table work?
All Pivot Tables start life as a boring old range of data. But once you create a Pivot Table, Excel takes a quick look at the data and stores it in its cache.
This is called the Pivot cache and it is responsible for the super fast calculation of summaries that Pivot Tables are known for.
Each time you add or remove data from the Excel Pivot Table, Excel does not deal with the source data, rather it uses this Pivot Cache as a quick shortcut.
Also Read:
Introduction To Power Pivot and Power Query In Excel
Getting Started With Power Pivot: Advanced Excel
Excel Crash Course – Learn Pivot Tables In 1 Hour
How to Create a Pivot Table in Excel?
Step 1: Turn the Data Range into a Table
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.
For example:
A few golden rules about your data range or table before you create an Excel Pivot Table 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.
Step 2: Open the Create Pivot Table Wizard
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.
Step 3: Select the Source Table or Range for the 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.
Step 4: Set the Location of the Pivot Table
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. Hurray!
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. I’ll show you how to do this in the next section.
How to Add Data to an Excel Pivot Table?
Using the Pivot Table Fields panel you can now start to manipulate your data.
Four Quadrants
A pivot table is based on these four quadrants:
- Filters
- Columns
- Rows
- Values
We’ll see what each of these quadrants mean in a minute.
These four quadrants are the key to manipulating the data in your Pivot Table. You can now start to drag the values at the top of the Excel Pivot Table Fields section into the quadrants below.
Values:
The values quadrant is what decides the type and value of calculations that the Pivot Table should display. It is the meat of a Pivot Table so to speak.
In the following image, the area bordered in red is the Values area in a Pivot Table.
Rows:
The rows quadrant is what decides the rows that the Pivot Table should display. The Rows are used to slice the data in a suitable way that we are looking for.
For example, you want to look at the total sales that occurred in different months. For this, you need to drag Months in the Rows quadrant.
In the following image, the area bordered in green is the Rows area of a Pivot Table.
Columns:
The columns quadrant is what decides the columns that the Pivot Table should display. That is columns are used to further dice the data into a suitable format.
For example, you want to look at the total sales that occurred in different months across different departments. For this, you need to drag Months in the Rows quadrant and further drag Departments into the Columns quadrant.
In the following image, the area bordered in blue is the Columns area of a Pivot Table.
Filters:
The filters quadrant is optional and is used to further drill down your Pivot Table. For example, you may want to look only at the sales value of the Detroit Branch.
This can be done by dragging the Branch field into the filter quadrant. Now, you can select the branch you are looking for from the drop-down list and view only its data.
Value Field Settings
How do you change what’s happening in your value field away from displaying the 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.
Analyse data using Pivot Table
Depending on which quadrant you pick, the table will format differently so there are a few rules 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.
Sales Values across Months
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 Excel Pivot Table 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.
Sales Values across months in Each branch.
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:
Sales Values across months in Each branch for each department.
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 the 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:
What are the Benefits of Pivot Tables?
All that an Excel Pivot table does is help you effortlessly slice and dice your data. A normal Excel sheet or table might not suffice for your data needs.
Suppose you need to quickly find out the Bread sales in January that occurred in the Detroit region, manually doing it each time using filters or formulas can be painstaking.
Or if you need to quickly look at the top 5 performing regions in the coffee department, using an Excel function is akin to taking the roundabout way to reach your destination.
An Excel Pivot Table achieves all this and more in just a few clicks.
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.
All this and so much more is explained in this video:
In this video our Excel expert Toby shows you everything you need to know about Slicers & Filters :
Suggested Reads:
How To Use Excel Countifs: The Best Guide
Excel Sumifs & Sumif Functions – The No.1 Complete Guide
How To Protect Cells In Excel Workbooks-the Easiest Way
FAQs
What is the use of a Pivot Table in Excel?
An Excel Pivot Table is used to summarise data in a reorganised format. While doing this, you can sort, filter, sum, count or even average your values across different fields.
What is a Pivot Table formula?
In a Pivot Table, under the value field settings, you will find summary functions to find SUM, AVERAGE, and COUNTS of values for the fields.
If they are not enough you can create your own formula to find the required value. These are called Pivot Table formulas
Let’s wrap up
That’s enough Pivot Tables for today, isn’t it? In this guide, we looked at the basics of creating and using pivot tables.
The key takeaway from this tutorial is that an Excel Pivot Table is a very versatile tool to drill down and look at your data.
There are more interesting things to do with them and we’ll deal with them in later advanced guides. If you find this guide useful, check out our Excel courses for more high-quality comprehensive guides on advanced Excel topics.
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 100+ IT training courses.
Other Excel classes you might like:
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.