How to Create a Pivot Table in Excel

Edited 4 months ago by ExtremeHow Editorial Team

Microsoft ExcelSpreadsheetOrganizationMicrosoft OfficeBusinessOffice ToolsData VisualizationProductivityReporting

Creating pivot tables in Excel is a powerful way to analyze, summarize, and present your data. Whether you're a seasoned professional or a beginner looking to learn Excel, pivot tables provide a great way to extract meaning from large datasets. This guide aims to provide a comprehensive understanding of how to create, manipulate, and use pivot tables, ensuring you can present your data in the most effective way possible.

What is a pivot table?

A pivot table is a data analysis tool that exists in many spreadsheet programs like Microsoft Excel, Google Sheets, and others. It allows you to extract the important parts of a large, detailed data set. You can think of it as a magical summary that turns extensive and complex data into digestible snippets of information, allowing you to easily “pivot” your data and look at it from different perspectives.

Why use pivot tables?

Pivot tables save time and effort. When using raw data, it can take a lot of processing time to obtain information, and manual calculations can be error-prone. With pivot tables, you can:

• Quickly summarize large data sets.
• Identify data patterns and trends.
• Create customized reports with different levels of detail.
• Efficiently filter data to display only relevant information.
• Perform calculations like sum, average, min, max, etc. on the data.

Preparing your data

Before you create a pivot table, make sure your data is organized correctly. Follow these steps to prepare:

• No blank rows or columns: Make sure your data is contiguous, and there are no blank rows or columns separating it.
• Column headers: Your data should have descriptive column headers, which will become the field names in your pivot table.
• Data types: Make sure your data types are consistent in each column. For example, one column should not contain a mix of text and numbers.

Step-by-step guide to creating a pivot table

Step 1: Select your data

Open your Excel workbook and select the entire range of your data, including the column headers. If your data is formatted as a table, you can click any cell within the table.

Step 2: Go to Pivot Table option

Go to the Insert tab on the Excel ribbon. In the Tables group, you will find the PivotTable button. Click on it to open the Create PivotTable dialog box.

Step 3: Create Pivot Table dialog box

After you click on the PivotTable, a dialog box will appear:

• Select the data: Excel will automatically select your data range. You can also adjust the range manually if necessary.
• Choose where to place the pivot table: Decide whether to place your pivot table on a new worksheet or an existing worksheet.

Click OK to proceed.

Step 4: Pivot Table Field List

Upon clicking OK, you will be taken to a new worksheet with the PivotTable Field List pane on the right side of your screen. This pane is used to create and customize your pivot table.

Step 5: Creating your pivot table

In the PivotTable Fields list, you'll see all your column headers, called "fields." Below these, you'll find four areas for dragging and dropping these fields:

• Filter: This area contains the fields by which you want to filter the data.
• Columns: Place any fields here that you want to display as column headings in your pivot table.
• Rows: Place fields here to display them as row labels in your pivot table.
• Values: This is the field where you put the fields you want to summarize, like sum, count, etc.

For example, if you're analyzing sales data, you might have "Products" in rows, "Sale Date" in columns, and "Total Sales Amount" in values.

Step 6: Customizing the Values

To customize how the values are displayed, click the dropdown next to the field in the Values area. You can choose:

• Value field settings: Lets you change the function that summarizes the data, such as sum, count, average, etc.
• Show Values As: Allows you to display values as a percentage of a total, a difference from the previous value, etc.

Step 7: Formatting your pivot table

Once your pivot table is set up, you can format it for presentation. You can:

• Format cells: Adjust number formats, fonts, colors, borders, etc.
• Design tab: Use the PivotTable Tools Design tab to apply predefined styles and color schemes.

Advanced features of pivot tables

Data grouping

You can group more detailed data such as dates or numbers into categories. Simply right-click on the field labels in the pivot table, select "Group" and define your categories.

Scroll down for details

Double-click any summarized data point to create a new sheet that splits out the details of that data point from your dataset.

Pivot chart integration

Excel provides the option to integrate pivot charts with pivot tables. To insert a pivot chart, select your pivot table and go to the Insert tab, then click Pivot Chart.

Refreshing data

If the underlying data changes, you can refresh your pivot table by right-clicking the table and selecting "Refresh."

Practical example

Imagine you have a dataset with sales data for different products across multiple states. For example, your data might look like this:

Product Name | Sales Amount | State | Date
,
Product A | 1500 | NY | 01/01/2023
Product B |2000 |NY |01/01/2023
Product C | 1000 | CA | 01/01/2023
Product A | 1200 | TX | 01/02/2023
Product B | 1800 | NC | 01/02/2023

You choose to create a pivot table to view the total sales of each product in different states, summarized monthly. You can do the following:

• Drag the product name to the Rows area.
• Drag the state to the columns area.
• Drag the sales amount to the Values area.
• Right-click on the date field and choose to group the data by months.

This will generate a comprehensive table showing how products are performing month over month in each state, giving you valuable information at a glance.

Conclusion

A vital part of data analysis in Excel are pivot tables, which provide extreme flexibility and power to quickly extract meaningful information. They can handle huge datasets and return important summaries and visualizations with minimal manual effort.

By following these steps, you can create your own pivot tables to explore different aspects of your data, highlight trends, and support decision-making processes in your personal or business projects.

Remember to regularly explore and experiment with the features Excel offers for pivot tables, as these skills can significantly enhance your data management and analytical capabilities.

  Share This Article

If you find anything wrong with the article content, you can request an update