How to Create Pivot Tables in Excel

Q: How do I change a value from Sum to Count or Average?

Click the field in the Values area and choose Value Field Settings on Windows, or click the i information icon next to the field on Mac. In the dialog, pick the calculation you want — Sum, Count, Average, Max, Min, and more — then click OK. Excel updates every figure in that column instantly.

To create a PivotTable, select any cell in your data, go to the Insert tab, and click PivotTable, then choose where to place it and click OK. In the PivotTable Fields pane, drag fields into the Rows, Columns, Values, and Filters areas to summarize your data, and click Refresh under the Data tab whenever the source changes.

If you are someone who wants to quickly and efficiently analyze and manipulate large amounts of data in Excel, then Pivot Tables may be the solution for you. A Pivot Table is a powerful Excel tool that allows you to summarize and analyze large amounts of data, making it easier to spot trends and identify patterns. With Pivot Tables, you can easily group and filter data, calculate subtotals and percentages, and create interactive reports that update as your data changes. In this blog post, we’ll provide a step-by-step guide on how to create Pivot Tables in Excel, from selecting your data to customizing your final report.

Step 1: Prepare your Data

Before you can create your Pivot Table, you need to prepare your data by organizing it into a table with columns and rows. Your data should also include headings for each column to make it easier to interpret. Once you have your data ready, select the entire table and press Ctrl + T to create a table. This ensures that Excel recognizes your data as a table, making it easier to work with.

Step 2: Creating the Pivot Table

Method 1: Recommended Pivot Tables

Excel offers a Recommended Pivot Tables feature that allows users to create Pivot Tables based on Excel’s suggested table layouts. To access this feature, select any cell within your table and navigate to the “Insert” tab and select “Recommended Pivot Tables”. From here, you can explore the various Pivot Table options available based on your data. Select one, then click “OK” to create the table.

Method 2: Creating a Pivot Table from Scratch

If you prefer to create your Pivot Table from scratch, select any cell within your data table. Then, navigate to the “Insert” tab and click on “PivotTable”. This will bring up a dialogue box with a range of options to customize your Pivot Table. On both Windows and Mac, the dialog lets you choose whether to place the report on a New Worksheet or an Existing Worksheet before you click “OK”. For a more detailed walkthrough of this step, see our guide on how to insert a pivot table in Excel.

Step 3: Customizing your Pivot Table

Once your Pivot Table is created, you can begin customizing it to fit your needs. One of the most popular ways to customize your table is by dragging and dropping fields from the Pivot Table Field List to the appropriate areas — Rows, Columns, Values, and Filters. Drag a field to Rows to list categories down the left, to Columns to spread them across the top, to Values to summarize numbers, and to Filters to control which records the whole report shows. You can also customize the value field settings, such as Sum, Average, or Count: click the field in the Values area, choose Value Field Settings (Windows) or the i icon (Mac), and pick how the numbers are summarized. For a deeper look at arranging and analyzing your fields, see how to use a pivot table in Excel.

Step 4: Summarize your Data

With your Pivot Table created and customized, you can now start to summarize your data. You can group rows and columns to create a summary table, filter data to identify trends, and even create calculated fields that perform mathematical equations on your data.

Step 5: Refresh your Data

Last but not least, make sure to refresh your data to ensure your Pivot Table is up to date. A PivotTable does not update automatically when its source data changes, so refreshing is an essential habit. By clicking on “Refresh” or “Refresh All” under the “Data” tab — or by right-clicking the PivotTable and choosing “Refresh” — you can update your Pivot Table with the latest data. The same steps work on Windows and Mac. If you need to update the underlying sheet first, see how to refresh data in Excel.

Common Pivot Table Mistakes to Avoid

While creating Pivot Tables in Excel is a powerful tool, there are some common mistakes that can impact the accuracy and effectiveness of your analysis. Here are some tips to avoid these common mistakes:

Avoid duplicating your data within the Pivot Table
Avoid using the same field in multiple areas of the Pivot Table
Avoid numeric fields that have blank cells or text values
Don’t forget to refresh your Pivot Table to ensure accuracy
Ensure your data is accurate and complete

Additional Pivot Table Tips

Here are some additional tips that can help you get the most out of your Pivot Tables:

Create a PivotChart to visually represent your Pivot Table data
Use PivotTable Styles to quickly apply a professional-looking format to your table
Add a slicer to easily filter your Pivot Table data with clickable buttons
Use the drill-down feature to get more information on a specific data point
Create calculated fields to perform mathematical operations on your data

Pivot Tables in Excel are a powerful tool that allow you to quickly analyze and manipulate large amounts of data. By following the steps outlined in this article, you can create custom Pivot Tables that meet your data analysis needs. With these tips and tricks, you’ll be creating impressive and insightful reports in no time.

Frequently Asked Questions

Here are some common questions people may ask about creating Pivot Tables in Excel:

What are Pivot Tables?

A Pivot Table is a powerful data analysis tool in Excel that allows users to quickly summarize, organize, and analyze large amounts of data. It is a dynamic table that allows users to manipulate data and generate insights that might be hard to see otherwise.

What kind of data is best suited for Pivot Tables?

Pivot Tables are best suited for data that is organized into tables with columns and headings. Pivot Tables work well with both numeric and categorical data. They are especially useful when dealing with large amounts of data that requires data summarization, consolidation, and aggregation.

How do I add fields to Rows, Columns, Values, and Filters?

After you insert the PivotTable, the PivotTable Fields pane appears on the right (Windows) or in a floating panel (Mac). Tick a field’s checkbox or drag it into one of the four areas: drag it to Rows to list categories down the side, to Columns to spread them across the top, to Values to calculate totals or counts, and to Filters to limit the whole report to certain records. You can rearrange fields at any time by dragging them between areas.

How do I change a value from Sum to Count or Average?

Click the field in the Values area and choose Value Field Settings on Windows, or click the i information icon next to the field on Mac. In the dialog, pick the calculation you want — Sum, Count, Average, Max, Min, and more — then click OK. Excel updates every figure in that column instantly.

Can I create a Pivot Table from multiple sheets?

Yes. The simplest approach is to combine the sheets into one master table — for example with VLOOKUP or INDEX-MATCH — and build the PivotTable from that. In Excel 2013 and later (including Microsoft 365 on Windows and Mac), you can also add each range to the Data Model, define relationships between them, and create a single PivotTable that pulls from multiple tables without merging them by hand.

How do I update a Pivot Table with new data?

To update a Pivot Table with new data in Excel, you need to refresh the Pivot Table. To do this, select your Pivot Table and click on the “Refresh” button under the “Data” tab. Alternatively, you can right-click on the Pivot Table and select “Refresh” from the popup menu. Note that a PivotTable never refreshes on its own — you must refresh it after the source data changes.

Can I sort and filter data in a Pivot Table?

Yes, you can sort and filter data in a Pivot Table in Excel. To sort data, click on the column heading and select “Sort A to Z” or “Sort Z to A”. To filter data, click on the column heading and select “Filter”. You can then select the values you want to display or hide in the Pivot Table.

If you decide you no longer need a report, you can also delete a pivot table in Excel, and for a broader reference you can read our complete guide to Excel pivot tables.