How to Create a Pivot Table in Excel: A Step-by-Step Guide

A PivotTable is a helpful tool in Excel that lets you calculate, summarize, and analyze your data. It helps you see comparisons, patterns, and trends. How PivotTables work can vary slightly depending on the version of Excel you are using.

Creating a Pivot Table in Excel can transform the way you analyze and summarize data. If you’re dealing with large datasets, Pivot Tables provide a dynamic and powerful tool to effortlessly organize and extract meaningful insights. Whether you’re managing sales figures, tracking project timelines, or analyzing survey responses, Pivot Tables help you make data-driven decisions quickly.

How to Create a Pivot Table in Excel

In this article, we’ll explore the process of creating a Pivot Table in Excel, ensuring you maximize your efficiency and productivity. Additionally, we’ll touch on how to create a Pivot Table in Google Spreadsheet, offering a comprehensive understanding of this tool across different platforms. Let’s start learning how to create a Pivot Table in Excel, and how to make a Pivot Table in Google Spreadsheet to streamline your data analysis and reporting tasks.

What is Pivot Table in Excel

A Pivot table is a summary of your data package. The word ‘Pivot‘ in the Pivot table means to rotate the data in Excel to view it from a different perspective. Creating a Pivot table doesn’t mean adding, subtracting, or changing the data, it simply means reorganizing it so you can easily work with useful information.

Data Format Tips:

• Use clean, tabular data for the best report.
• Better to organize your data in columns, instead of rows.
• Ensure all columns have headers, with a single row of unique, non-blank labels for each column. Avoid double rows of headers or merged cells.
• Format your data as an Excel table (select anywhere in your data and then select Insert > Table from the ribbon).
• If you have complicated or nested data, use Power Query to transform it (for example, to unpivot your data) so it is organized in columns with a single header row

What are Pivot Tables used for

Pivot tables are tools meant to simplify the process of summarizing large datasets efficiently. They enable users to gain insights, visualize, and analyze numerical data comprehensively.

Below are some pivot table uses:

1. Comparing Sales Totals of Different Products
2. Showing Product Sales as Percentages of Total Sales
3. Combining Duplicate Data
4. Getting an Employee Headcount for Separate Departments
5. Adding Default Values to Empty Cells

How to Create a Pivot Table

In this section, we’ll walk you through the steps to create a pivot table in Excel, making it simple for you to organize and analyze your data effectively.

Step 1: Open MS Excel and Select a Cell

Step 2: Go to the Insert tab

Go to the Insert Tab

Step 3: In the Tables group, click on the Pivot table tool

Create Pivot Table >> Ok

Step 4: Fill the Dialog Box

A dialog box would open where we have to fill in the two choices for the data to be analyzed and the place where we wish to have the pivot table. After filling in the options, click on OK.

Note: By default the data location of the pivot table will be a new worksheet.

Step 5: In the new sheet, we can see the pivot table and other options

Pivot Table Created

How to Build a Pivot Table Report

On the left side of the sheet, a new empty pivot table has been created where the summary would be shown. On the right side, we can see the FIELD NAME which are the headers of the columns of our data set. FIELD NAME is to be dragged to empty boxes i.e. Filters, Columns, Rows, and values to show their corresponding values in the Pivot Table.

Let’s drag the FIELD NAME into the boxes and see their effects individually. 

Drag the Field Name

Step 1: Add Pivot Table Fields

Values sum up all the entries in the FIELD NAME dragged in it. Here, as Sales are dragged here, our pivot table shows the sum of all the sales that took place. 

Drag the Fields between areas

Step 2: Sum of Sales Appeared

Sum of Sales Appeared

Step 3: Add More Fields

We can add as many FIELD names as we require in Values. Individual sums would be shown then.

Add More Fields

Step 4: Drag to Get Sum

Dragging fields into values will give you the sum of values as a result.

Sum Calculated

Step 5: Returned to Total Count

If the entities in the column can’t be summed, it will give us the total count of the entries present in that column.  Here as Country and Product do not contain numeric values, it returned the total count of each column.

Total Count

Step 6: Drag Fields into Values

Dragging Fields into Values.

Drag Fields into Values

Step 7: Count Not Found

In the below image, you can find the Count of the Values.

Count Not Found

Step 8: Data Gets Grouped

The data in the pivot table gets grouped (Row-Wise) by the Field Names dragged to the Rows Area. 

In this example, we have grouped the sales by the countries. 

Data Gets Grouped

Step 9: Fields dragged to Rows

In the below image, the fields are dragged to Rows.

Fields dragged to Rows

We can drag as many Fields as we require in this region.

How to Create Pivot Table Columns Area in Excel

Creating Pivot Table using the Column Area in Excel

Step 1: Use Discount Band

The data in the pivot table gets grouped by(Column-Wise) by the Field Names dragged to Columns Area. As here, row-wise, our data is grouped by Countries and column-wise, it is grouped by Discount Band. 

Use Discount band

Step 2: Fields Dragged to Columns

The Fields are dragged to Columns in the below image.

Fields Dragged to Columns

This area can accommodate many Fields. 

Filter Area in Excel

The filter is an important feature in the pivot table. using which we can filter out the data based on the Field dragged into it.

Step 1: Filter the Total Sales

Here, we have filtered the total sales based on one particular product that is only that product is considered while calculating the sales. 

Filter the Total Sales

Step 2: Apply Features

You can also apply many features to the Product fields as shown below.

Apply features

Step 3: Final Output

Below is the final output of the above steps. In this way, using pivot tables, a summary of the data is achieved in the form of a matrix. There are many other tools and features of the Pivot Tables which can be explored. 

Final Output

How to Refresh a Pivot Table in Excel

Refreshing your pivot table is crucial to keep your data up-to-date. This simple step ensures that any changes or additions to your original dataset are reflected in your pivot table. Here’s how to do it:

Refresh the Pivot Table data manually

To manually refresh the data in a Pivot Table, follow these steps in Excel:

Step 1: Click inside the Pivot Table

Step 2: Go to the “Data” tab

Step 3: Click on Refresh Button

Look for the “Refresh” button in the “Data Tools” group. It may also be labeled as “Refresh All” if you have multiple Pivot Tables in your workbook.

Click on Refresh Button

Refreshing a Pivot Table automatically when opening the workbook in Excel

You can also automatically refresh data.

Step 1: Click inside the Pivot Table

Step 2: Go to Pivot Table

Go to the “PivotTable Analyze” or “Options” tab on the Excel ribbon, depending on your Excel version.

Step 3: Go for Option group

Look for the “Options” group, and within that group, locate and click on “Options” (or “PivotTable Options” in older versions).

Step 4: Select the Data Tab

In the PivotTable Options dialog box that appears, select the “Data” tab.

Step 5: Check the Box

Check the box that says “Refresh data when opening the file.”

Step 6: Click “Ok”

Click “OK” to save your changes and close the dialog box.

Click “Ok”

How to Copy a Pivot Table

Copying a pivot table in Excel is a simple and useful skill, especially when you want to use the same layout with different data or in another part of your workbook. This section will guide you through the easy steps to duplicate your pivot table quickly and efficiently, so you can continue analyzing data without starting from scratch each time.

Here are the steps to copy a Pivot Table:

Step 1: Select the entire pivot table

Step 2: Copy the pivot table.

Step 3: Choose the destination

Step 4: Paste the pivot table.

Paste the pivot table

How to Delete a Pivot Table

Here are the steps to delete a Pivot Table:

Step 1: Select a Pivot Table

Select the Pivot Table

Step 2: Press the “Delete” key

Click on Delete button

Step 3: Table Deleted

How to Sort a Pivot Table

Here are the steps to sort a Pivot Table:

Step 1: Select the column or row

Select a Column or Row

Step 2: Sort in ascending or descending order.

Click on Sort button to Sort Pivot Table

Tips & Tricks For Excel Pivot Tables

• Let Excel suggest Pivot Table layouts based on your data. Go to Insert > Recommended PivotTables.
• Group dates by month, quarter, or year for better trend analysis.
• Group items manually to create custom categories.
• Add slicers for a visual and interactive way to filter your Pivot Table data.
• Use multiple slicers for more granular control over your data views.
• Add calculated fields to perform custom calculations within your Pivot Table without altering the source data.
• Use complex formulas for sophisticated data analysis.

Conclusion

Creating a Pivot Table in Excel is an essential skill for anyone looking to effectively analyze and summarize large sets of data. By following the simple steps of selecting your data, inserting the Pivot Table, and organizing your fields into rows, columns, and values, you can transform raw data into meaningful insights. As we’ve seen, pivot tables allow you to easily organize, summarize, and interpret large sets of information, making them indispensable for anyone dealing with data analysis. Now that you understand the steps to create, build, design, improve, refresh, copy, delete, and sort pivot tables, you can start leveraging this essential tool to make better-informed decisions and streamline your data analysis processes.

Also Read

• Exploring Data with PivotTables in Excel
• Sorting Pivot Table in Excel
• How to Remove Pivot Table But Keep Data in Excel

How to Create a Pivot Table in Excel – FAQs

How do I create a PivotTable in Excel?

Follow the the steps given below:

• Select Your Data
• Go to the Insert tab
• Click on PivotTable
• Create the PivotTable

How do I create a PivotTable with rows and columns?

To create Pivot Table with rows and columns follow the steps given below:

• Select Your Data
• Go to the Insert tab
• Drag the rows
• Drag the Columns

What is a PivotTable in Excel used for?

A PivotTable in Excel is used for:

• Summarizing Data
• Analyzing Data
• Data Exploration
• Creating Reports
• Filtering Data

How do I create a pivot chart from Excel data?

Here are the steps to create a pivot table from the Excel data:

• Go to the Insert tab
• Click on PivotTable
• Customize the PivotChart

What is Pivot Table in Excel Formula?

Here are some of the Pivot table formulas:

• Sum: ‘=SUM(Sales)’
• Average: ‘=AVERAGE(Sales)’
• Count: ‘=COUNT(Quantity)’
• Product: ‘=PRODUCT(Quantity)’