How to Create Pivot Table in Excel: Beginners Tutorial

By : Charlotte Miller Charlotte Miller
Hours Updated

⚡ Smart Summary

Pivot Tables in Excel summarize a large dataset into a compact report of totals, averages, and other aggregates. This page explains what a pivot table is, builds one step by step from sales data, creates a two dimensional pivot table, and generates a pivot chart.

  • 📊 Core Definition: A pivot table summarizes a large dataset with totals, averages, minimums, and maximums, showing only the relevant figures.
  • 🧱 Four Areas: Fields are dragged into Rows, Columns, Values, and Filters to shape the report.
  • 🖱️ Creation: Select the data, use INSERT then PivotTable, and choose the range that holds every record.
  • 🔍 Filter: The Row Labels drop down sorts and filters, for example to show only one customer.
  • 🔲 Two Dimensional: Placing a field on both rows and columns cross tabulates the data, such as employees against customers.
  • 📈 Pivot Chart: Excel builds a chart from the pivot table and updates it as the filters and fields change.
Read More

How to Create a Pivot Table in Excel

There will be times when you will be required to analyse large amounts of data and produce easy to read and understand reports. Pivot tables allow us to analyse such data and produce reports that meet our business reporting requirements.

What is a Pivot Table?

A Pivot Table is a summary of a large dataset that usually includes the total figures, average, minimum, maximum, etc. let’s say you have a sales data for different regions, with a pivot table, you can summarize the data by region and find the average sales per region, the maximum and minimum sale per region, etc. Pivot tables allow us to analyse, summarize and show only relevant data in our reports.

The Four Areas of a Pivot Table

Every pivot table is built by dragging the fields of the source data into one of four areas in the field list. Understanding these areas makes the steps that follow easier to picture.

Area What it does Example
Rows Lists the categories down the left side, one per row Company name
Columns Spreads a second category across the top Customer name
Values The numbers that are aggregated, using SUM, AVERAGE, MIN, or MAX Sum of SubTotal
Filters Limits the whole table to a chosen value Show one product only

💡 Tip: A field placed in both Rows and Columns creates the two dimensional table shown later. Change the aggregate in the Values area by clicking the field and choosing Value Field Settings, then pick Average, Count, Max, or Min.

Step by Step tutorial on creating pivot tables

The image below shows the sample sales data collated from North wind access database.

Creating Pivot Tables

You can download the sample Excel data here.

Download the above Excel Data File

As you can see from the above image, our spreadsheet contains a lot of data. Let’s say we want to create a summary of customers, group all of their orders by product, and show the quantities, unit price and subtotals for all the transactions.

  • Open the Excel file that you downloaded
  • Click on INSERT tab on the ribbon

Creating Pivot Tables

  • You will get the following window

Creating Pivot Tables

  • Click on the select table/range button as shown in the image above
  • You will get the following mini window

Creating Pivot Tables

  • Click in cell address A1
  • Press Ctrl + A on the keyboard to select all the data cells
  • Your mini window shown now appear as follows

Creating Pivot Tables

  • Click on Close button to get back to the options window
  • Click on OK button

Creating Pivot Tables

  • Select the following fields
    • CompanyName
    • Product Name
    • UnitPrice
    • Quantity
    • SubTotal
  • Your worksheet should now look as follows

Creating Pivot Tables

  • Note the above data has been grouped by customer company name, product name, unit price, sum of quantities and the sum of the subtotals.
  • Notice the drop down button next to Rows Labels. This button allows us to sort/filter our data. Let’s assume we are only interested in Alfreds Futterkiste
  • Click on the Row Labels drop down list as shown below

Creating Pivot Tables

  • Remove the tick from (Select All)
  • Select Alfreds Futterkiste
  • Click on OK button
  • You will get the following data

Creating Pivot Tables

RELATED ARTICLES

2-Dimensional pivot tables

A two-dimensional pivot table is a table that has fields on both rows and columns. Let’s say we want to list employee names as rows and use columns to represent customer names and fill in the cells with the total sales.

  • Activate the Sales Datasheet
  • Click on INSERT tab
  • Click on Pivot Chart & Table button
  • Select all the data. Excel should now remember the previous range so you just have to click on OK button
  • A new sheet will be created with the pivot table tools
  • Select the fields as shown in the image below

2-Dimensional Pivot Tables

Your pivot table will now look as follows

2-Dimensional Pivot Tables

Visualizing pivot table data using charts

With Excel 2013, you do not need to create the charts manually. Excel will create the charts for you as you create your pivot tables, change aggregate functions, apply filters, etc.

The chart below was automatically created for us from the simple pivot chart exercise that filtered data for Alfreds Futterkiste only.

Visualizing Pivot Table Data using Charts

FAQs

A normal table lists every record. A pivot table summarizes those records into totals and averages by category, and it can be rearranged by dragging fields without changing the source data.

A pivot table does not update on its own. Click any cell inside it, open the PivotTable Analyze tab, and choose Refresh. If new rows were added, update the source range first with Change Data Source.

Click the field in the Values area, choose Value Field Settings, and pick Average, Count, Max, or Min. The whole pivot table recalculates instantly with the new aggregate.

Yes. AI features such as Copilot in Excel read the data and build a pivot table from a plain request like “total sales by customer and product”. The user then adjusts the fields and the aggregate.

Yes. AI assistants read the summarized figures and describe the pattern in plain language, such as which customer or product leads sales. The user checks the summary against the pivot table before sharing it.

Summarize this post with: