Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Create a Pivot Table in Excel? — The Easiest guide

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

Using Getpivotdata In Excel

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 

Table Of Contents
  1. What is a Pivot Table?
  2. What is the use of a Pivot Table in Excel?
  3. How does an Excel Pivot Table work?
  4. How to Create a Pivot Table in Excel?
    • Step 1: Turn the Data Range into a Table
    • Step 2: Open the Create Pivot Table Wizard
    • Step 3: Select the Source Table or Range for the Pivot Table
    • Step 4: Set the Location of the Pivot Table
  5. How to Add Data to an Excel Pivot Table?
    • Four Quadrants
    • Values: 
    • Rows: 
    • Columns: 
    • Filters: 
    • Value Field Settings 
  6. Analyse data using Pivot Table
    • Sales Values across Months
    • Sales Values across months in Each branch.
    • Sales Values across months in Each branch for each department.
  7. What are the Benefits of Pivot Tables?
  8. FAQs
    • What is the use of a Pivot Table in Excel?
    • What is a Pivot Table formula?
  9. Let’s wrap up

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.

Learn Pivot Tables in 1 Hour – Video Tutorial

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:

Turn your Range into a Table to add or remove data later on
Turn your Range into a Table to add or remove data later on

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.

Select the Table you have created or the Range you need for the Pivot Table/ Pivot table in Excel.
Select the Table you have created or the Range you need for the Pivot Table

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.

Pivot Table Fields on the right is the place to add or remove data into your Pivot table
Pivot Table Fields on the right is the place to add or remove data into your Pivot table

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. 

Pivot table fields image 2
Add or remove Pivot table Fields into any of these Four Quadrants

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. 

pivot table summary image
The Values section displays the value of PivotTable fields

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. 

Rows Area displays the Rows of the Pivot Table
Rows Area displays the Rows of the 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. 

Columns Area displays the Columns of the Pivot Table
Columns Area displays the Columns of the 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.

pivot table value field settings
You can change the type of value to be displayed in the Value Field Settings

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.
Drag and Drop your desired Pivot Table Fields into relevant Quadrants
Drag and Drop your desired Pivot Table Fields into relevant Quadrants

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.
Months in the Rows Quadrant Displays the sum of sales across months
Months in the Rows Quadrant Displays the sum of sales across months

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:

Add Branch in the Rows Quadrant below the Months field, to display sales values for each branch for each month
Add Branch in the Rows Quadrant below the Months field, to display sales values for each branch for each month

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:

Pivot table rows and column summary
Add The Department Field to the Columns Quadrant to display the Sales values in Each month for each State across different departments

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:

  • What-If Analysis in Excel
  • Designing Better Spreadsheets in Excel
  • Logical Functions in Excel

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.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Finding Formula Errors in Excel 2010

Introduction to Power Automate

How to Use Microsoft Excel 2016 Options

Multiscreen Preview and Screen Size Settings in Dreamweaver CS6

Logical Functions in Excel (IF, IFS, AND, OR, COUNTIF, SUMIF)

How to Wrap Text in Excel? With 6 Simple Methods

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)