Excel shortcuts

How to Use Pivot Tables in Excel: A Step-By-Step Guide

Pivot tables are one of the most powerful features in Excel. They allow you to quickly and easily summarize large amounts of data. In this article, we'll show you how to use pivot tables in Excel. We'll start with a brief introduction to pivot tables, and then we'll walk through a step-by-step example of how to create a pivot table in Excel.

What is a Pivot Table?

A pivot table is a tool that allows you to summarize and analyze data. It's a bit like a cross between a spreadsheet and a database. You can use pivot tables to answer questions such as:

  • How many products were sold in each month?
  • How many customers bought each product?
  • What was the total sales for each product?

Pivot tables are very flexible. You can use them to summarize data in many different ways. For example, you can use a pivot table to calculate the average, minimum, or maximum value for a given data set. You can also use a pivot table to calculate the sum or count of a given data set.

How to Create a Pivot Table in Excel

Now that we've seen a few examples of what pivot tables can do, let's walk through a step-by-step example of how to create a pivot table in Excel.

For this example, we'll use the following data set:

MonthProductSalesJanuaryProduct A100JanuaryProduct B200FebruaryProduct A300FebruaryProduct B400MarchProduct A500MarchProduct B600

To create a pivot table, we'll need to select a cell in our data set. Then, we'll go to the Insert tab on the ribbon, and click PivotTable. This will open the Create PivotTable dialog box.

In the Create PivotTable dialog box, we'll need to select the data that we want to use in our pivot table. For this example, we'll select the entire data set. Then, we'll click OK.

This will open the PivotTable editor. The PivotTable editor is where we'll specify the structure of our pivot table.

In the PivotTable editor, we'll need to specify the Rows and Columns that we want to use in our pivot table. For this example, we'll use the Month and Product fields as our Rows, and the Sales field as our Column. We'll also need to specify a Value for our pivot table. For this example, we'll use the Sum of Sales value.

Once we've specified the structure of our pivot table, we'll click OK. This will insert our pivot table into the worksheet.

Now, let's take a look at our pivot table. As you can see, our pivot table has summarized our data by month and product. We can see that Product A had sales of 100 in January, 200 in February, and 500 in March. We can also see that Product B had sales of 200 in January, 400 in February, and 600 in March.

We can also use the pivot table to answer questions such as:

  • How many products were sold in each month?
  • To answer this question, we can add a Count of Product value to our pivot table. This will give us a count of the number of products sold in each month. As we can see, two products were sold in each month.
  • How many customers bought each product?
  • To answer this question, we can add a Count of Customer value to our pivot table. This will give us a count of the number of customers who bought each product. As we can see, one customer bought each product in each month.
  • What was the total sales for each product?
  • To answer this question, we can add a Sum of Sales value to our pivot table. This will give us the total sales for each product. As we can see, Product A had sales of 100 in January, 200 in February, and 500 in March. Product B had sales of 200 in January, 400 in February, and 600 in March.

As you can see, pivot tables are a very powerful tool. They allow you to quickly and easily summarize large amounts of data. In this article, we've shown you how to use pivot tables in Excel. We've also shown you how to create a pivot table in Excel. We hope you found this article helpful.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.