Excel Pivot Table

What is the Pivot Table in Excel?

A Pivot Table in Excel summarizes large amounts of data by organizing the data into small conclusive tables. Pivot Tables can help create reports and charts to understand trends. It also allows data filters to view just the details for areas of interest and explore more by changing the parameters.

It is known as a Pivot Table as it lets the user rearrange the rows and columns around the data to arrive at the desired summary. Users can also view total sales for different products, show product sales in percentages, get employee headcount in different departments, etc.

For example, when we create Pivot Table for the data below,

The data is organized in the below form:

Key Highlights

• Pivot Table in Excel helps group complex data in multiple ways to draw meaningful conclusions easily.
• We can rotate the data in the large data set to view it from different perspectives.
• We cannot add, subtract or modify data while creating a Pivot Table.
• We can use Pivot Tables for creating custom reports with appropriate formatting.

How to Create a Pivot Table in Excel?

Example #1

The table below shows a list of auditors with the properties they marked as correct and incorrect. We want to count the properties according to their status using the Pivot Table.

Solution:

Step 1: Select the data table and click on the Insert menu

Step 2: Click on Pivot Table

A dialogue box PivotTable from table or range is displayed as shown below

Explanation:

Table/Range is the selected data table.

Next, we have to select whether we want the Pivot table in the New Worksheet or the Existing Worksheet.

Here, we select the Existing Worksheet.

Now, we have to specify the location (cell) for the Pivot Table. For this, click the desired cell and it will be displayed in the Location option in the dialogue box.

Now, click OK and the below Pivot Table will be created.

The above Pivot Table has no data. To enter data into it click anywhere on the Pivot table and we can see a Pivot Table Fields pane on the right side of the Excel Window as shown below

At the top, the Pivot Table has a list of fields (columns of the data table). At the bottom of the Pivot Table Fields pane, there are four areas (Rows, Values, Filters, and Columns) in which we need to place the data fields.

• Rows: Data that is taken as a specifier
• Values: Count of the data
• Filters: Filters to select the desired data field
• Columns: Values under different conditions

We can place the data fields into the desired area either by dragging them or by clicking the checkbox next to the data field.

Step 3: Drag the Auditor field to the area Rows, Property_ID to Values, and Status to Filters.

It results in the below table

The table shows the Total count (17) of the Property_IDs checked by the auditors.

Now, we want to count the number of Property_IDs marked as Correct

Step 3: Click on the Filter section dropdown in the table

Step 4: Click on the Correct checkbox > Select Multiple Items > OK

The result is displayed as shown below

The above table shows the total number of Property IDs marked as correct to be 13.

Step 4: Select the Incorrect option from the filter (dropdown) to get the below result

The above table shows the total count of Incorrect Property IDs.

Example #2

The table below shows sales of Product 1, Product 2, Product 3, Product 4, Product 5, Product 6, Product 7, Product 8, and Product 9 in the year 2017 in quarters- Q1, Q2, Q3, and Q4. We want to find the total sales of all the products using the Pivot Table.

Solution:

Here, we will use the alternative method to create the Pivot table. For that,

Step 1: Press the keys ALT + D + P on the keyboard

The PivotTable and PivotChart Wizard dialogue box opens up. It asks two questions-

1. Where is the data you want to analyze?
2. What kind of report do you want to create?

Step 2: Select the first option for both questions, i.e.,

1. Microsoft Excel list or database and
2. PivotTable

And click Next.

Now, Excel asks for a range of data. As we had already selected the data, therefore, it is prefilled.

Step 3: Click on Next

Now the dialog box asks us whether we want our pivot table in the same worksheet or a new worksheet. So,

Step 4: Select the New worksheet and click on Finish.

Now a Pivot Table is created with the PivotTable Fields pane on the right side of the Worksheet.

Step 5: Drag the field Quarter in the area Columns, Year in Filters, Product in Rows, and Sales in Values.

The Pivot Table is created as shown below

The above table shows the Total Sales of 9161.

Example #3

The below table shows a list of brands with their model, color, mileage, and price. We want to find the total price of all the Models of a Brand using the Pivot Table.

Solution:

Step 1: Select the data table and click on Insert > Pivot Table

The Pivot table from table or range dialogue box appears

Step 2: Choose Existing Worksheet, specify the location by clicking on the desired cell, and click OK.

The Pivot Table is created as shown below with the Fields pane on the right side.

Step 3: Drag the field Brand in the area Filters, Model in Rows, Color in Columns, and Price in Values.

The Pivot Table is created as shown below with a total price of $ 60203.

Step 4: Click the dropdown (filter) and select Toyota > Select Multiple Items > OK

Excel creates a Pivot Table showing the total price ($ 17770) for all the models of Toyota

Now, we want to view the total price for Chevrolet and Toyota together

Click the dropdown (filter) and select Chevrolet and Toyota > Select Multiple Items > OK

Excel creates a Pivot Table showing the total price ($ 29463) for all the models of Chevrolet and Toyota

How to Move a Pivot Table in Excel?

To move a Pivot Table,

1. Select the Pivot Table > PivotTable Analyze > Move PivotTable

2. Specify the new location and click OK

Shortcuts for Pivot table in Excel

Below are the shortcuts we can use while working with Pivot Table

Things to Remember

• Pivot tables do not change the values in the database.
• We can insert Pivot Tables either in the same worksheet or a new worksheet.
• For convenience, we add pivot tables in a new worksheet.
• We can create Pivot tables with up to 500,000 records.

Frequently Asked Questions (FAQ)

Q1) Why use a Pivot Table in Excel?

Answer: We can use Pivot Tables to track and analyze hundreds of thousands of data points with a compact table. We can use Pivot Tables to make a comparison, highlight a trend, or show relationships between parameters. Also, we can prepare multiple reports using the same Pivot Table.

Q2) Where is Pivot Table in Excel?

Answer: To locate the Pivot table,

Step 1: Select the data

Step 2: Click on Insert

Step 3: Select Pivot Table

Q3) Is there a limit to the number of rows in a Pivot Table in Excel?

Answer: A Pivot Table can display a maximum of 100,000 rows. Therefore, we cannot visualize more than 100k rows.

Q4) How many values can a Pivot Table handle?

Answer: Pivot tables can handle up to 1,048,576 items.

Q5) What are the disadvantages of Pivot table in Excel?

Answer: The disadvantages of the Pivot table are:

1. We cannot use the aggregate function with the Pivot table
2. Pivot tables do not support conditional formatting.
3. The Pivot Table does not work if there are blank rows or columns

Recommended Articles

The above article is a guide to creating a Pivot Table in Excel. For more such information, EDUCBA recommends the below-given articles.

1. PowerPivot in Excel
2. Pivot Table Formula in Excel
3. VBA Refresh Pivot Table
4. Excel Delete Pivot Table