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.

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.

How to Create a Pivot Table in Excel

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

Why Use a Pivot Table in Excel

Pivot tables enable efficient data analysis, allowing users to visualize numerical data, summarize details, and identify trends quickly. With Excel and pivot tables, you can:

• Compare product sales totals
• Combine and remove duplicates
• Filter out irrelevant data
• Calculate totals or percentages

How to Create a Pivot Table in Excel

Creating a pivot table in Excel is easy and provides immense value. Here’s a simple guide:

Step 1: Open MS Excel and Select the Data

Open your worksheet, then select the data range you wish to analyze. Ensure the data is organized with columns and headers.

Step 2: Go to the Insert tab

Go to the “Insert” tab and click on “PivotTable.” Excel will prompt you to choose where the pivot table will appear.

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

Create Pivot Table >> Ok

Step 5: Set Up Your Pivot Table

Excel will create an empty pivot sheet in Excel. Use the right-side panel to drag fields (column headers) into rows, columns, and values.

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 Delete a Pivot Table in Excel

Here are the steps to delete a Pivot Table:

Step 1: Select a Pivot Table

Select the Pivot Table which you need to be deleted.

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

Click and Select the Column row

Select a Column or Row

Step 2: Sort in ascending or descending order

Choose the sorting (ascending or descending) order as per your need.

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

Mastering pivot tables in Excel will revolutionize your data analysis capabilities. With Excel pivot tables, you can quickly identify patterns, trends, and anomalies in your datasets. Whether you’re new to Excel and pivot tables or an experienced user, understanding how to create a pivot table in Excel is essential for efficient data analysis. Make the most of your data by utilizing this powerful feature across both Excel and Google Sheets.

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)’