How to Create a Pivot Table in Excel?

Pivot tables are a well-known, often misunderstood feature of Excel. They have a reputation for being one of the more advanced features of the software, but in fact, making pivot tables is quite straightforward.

Pivot tables make your data more intuitive and straightforward. This is great if you deal with large amounts of data and want to organize it in a more readable way or if you need to analyze data that others can easily interpret at a glance.

In this article, you’ll learn how to make a pivot table in Excel and how and when to use a pivot table. We’ll also show you how to effectively customize your pivot tables so that you receive the exact results you need, and also how to update your pivot table after any changes to your original dataset.

What is a pivot table?

Pivot tables summarize your data in a chart that lets you interpret what the data actually means. They essentially reorganize your data by pivoting the columns into rows and rows into columns, so that you can see your data in a different way.

What can I use a pivot table for?

There are many ways you can use a pivot table. In general, pivot tables are used to effectively summarize, calculate and analyze large amounts of data in a format that lets you easily identify key patterns, trends, and comparisons.

• Summarize data in a more user-friendly way for others to see.
• Compare multiple rows and columns of data for report analytics.
• Group and sort data to find trends in numbers.
• Apply advanced calculations to your data easily.

For a more in-depth example, let’s say you have a dataset containing the total sales of each team member across multiple departments of your department store. Rather than manually adding up the total sales for each department, you can use a pivot table to automatically organize, combine and total the data for you. Not only does it save you time, but it also presents the data in a more readable way, so you can easily see everything you need at a glance.

How to create a pivot table in Excel step-by-step?

Creating a pivot table in Excel is more simple than you think. So what is the first step for creating a pivot table? Here’s a step-by-step of everything you need to do to create a pivot table in Excel from start to finish, using the example mentioned above as context.

Create data for a pivot table

1. 1. Add your data directly into your workbook, or import data from an external source by selecting Data > Get data and select data from your desired source file.

Here is an example of my data of team members’ total sales across each department.

1. 2. Highlight all of your data and click “Format as Table” located in the toolbar. Make sure your entire table range is selected. Choose your preferred table style.

Your data should look something like this:

Sort your data

It’s important that you sort your data according to your needs before turning it into a pivot table. This makes it a lot easier to manage as a final product.

1. 1. Click the “Data” tab and select “Sort”. You can choose which column you want to sort by, the contents to sort by, and the order to sort by. Once you’re done, press “Ok”.

Now that your data is successfully formatted and sorted, it’s time to create your pivot table.

Create a pivot table

1. 1. Click on one of the cells in your table and head to Insert > Pivot Table.

1. 2. Excel should automatically select your entire table, but make sure all of your cells are included in the range. You can also choose where the pivot table will be located. Once you’re happy with everything, click “Ok”.

Build a pivot table

It’s simple to build your pivot table to present your data as you want it.

1. 1. Add data fields to your pivot table “Row labels” box. These will appear as the different rows in the pivot table.

For example, in order to get the total sales for each department, I can simply tick the “Department” and “Total sales” fields.

1. 2. Drag the fields to the “Filters”, “Columns”, “Rows” and “Values” boxes to customize your pivot table. Here’s what each box means:

• Filters: Add a filter for your data. For example, if I drag “Department” to the “Filter” box, I can receive the total number of sales for a custom selection of departments.

• Columns: Add a field as a column in your pivot table. For example, if I drag “Name” to the “Column” box, I can easily see the sales of each team member and which department they are in.

• Rows: Add a field as rows in your pivot table. For example, if I drag “Name” to the “Row” box, I can easily see which team member is in which department, and how much they sold.

• Values: Add new calculations to your pivot table. For example, if I drag “Total sales” to the “Values” box, I receive the total sales for each department, and the total sales combined.

Customize your pivot table

With the foundations of your pivot table built, you can customize it further to receive specific insights. To do this, all you have to do is click on the “information” icon next to each field in the boxes to alter the type of data you receive.

1. 1. Click the “information” icon next to one of your fields in a box. Adjust the settings based on the type of results you wish to receive.

In this example, I’ll select the “Total sales” field in the “Values” box and adjust the settings. As a result, I can now see the percentage that each department makes up for the total combined sales.

Update your pivot table

Let’s say you want to update your pivot table to reflect the new changes to your original table of data.

1. 1. Add the changes to your original table of data. Make sure that the table format is applied to all of your cells.

In this example, I want to add a new sales member to the table. I’ve added their name, department, and total sales values to the worksheet, and made sure that the table formatting is applied to their row.

1. 2. Head back to the pivot table, right-click on a cell, and press “Refresh”.

As you can see, the pivot table now includes my new row of data.

Create a pivot table from multiple worksheets or workbooks

Creating a pivot table is simpler when your data is consolidated in one worksheet. However, if that isn’t possible, you should consolidate your data from multiple worksheets or workbooks into one master worksheet, using the “PivotTable and PivotChart Wizard”. Once you’ve done this, simply follow the same steps listed above. Read the Microsoft support page for more details on how to do this.

Conclusion

Despite their intimidating reputation, pivot tables are easy to create and use. They offer a range of impressive insights that can be applied to almost any circumstance, whether you’re looking to summarize your data for financial reports or find the latest trends in blog post views.

By the end of this article, you should now know exactly what pivot tables can do, how to create one from your workbook, and how to customize and update them to suit your needs.