EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Excel Pivot Table
 

Excel Pivot Table

Madhuri Thakur
Article byMadhuri Thakur

Updated July 4, 2023

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 the details for areas of interest and explore more by changing the parameters.

It is known as a Pivot Table, letting 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,

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Pivot Table in Excel 1

The data is organized in the below form:

Pivot Table in Excel 2

Key Highlights

  • Pivot Table in Excel helps complex group 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?

You can download this Pivot Table Excel Template here – Pivot Table Excel Template

Example #1

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

Example #1

Solution:

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

Insert menu

Step 2: Click on Pivot Table

Pivot Table

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

PivotTable from table or range

Explanation:

Table/Range is the selected data table.

Next, we must 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, which will be displayed in the Location option in the dialogue box.

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

Pivot Table Fields

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.

Pivot Table Fields 2

At the top, the Pivot Table lists fields (data table columns). At the bottom of the Pivot Table Fields pane are four areas (Rows, Values, Filters, and Columns) where 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 by dragging them or 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.

Auditor field

The results are in the below table.

Auditor field 2

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

Filter section

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

Select Multiple Items

The result is displayed as shown below

result

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

Incorrect option

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.

Example #2

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

ALT + D + P

The PivotTable and PivotChart, Wizard dialogue box, open 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.

Microsoft Excel list or database

Note: By default, the first options for both questions are selected.

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

Step 3: Click on Next

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.

New worksheet

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

PivotTable Fields pane

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

field Quarter

The Pivot Table is created as shown below

Pivot Table is created

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.

Example #3

Solution:

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

Insert - Pivot Table

The Pivot table from the table or range dialogue box appears

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

Step 2

Note: The Table/Range is pre-filled as we had selected the data table.

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

Table or Range

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

field Brand

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

$ 60203

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

Toyota

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

$ 17770

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

Chevrolet and Toyota

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

$ 29463

How to Move a Pivot Table in Excel?

To move a Pivot Table,

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

How to Move a Pivot Table in Excel

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

Shortcuts for Pivot table in Excel

Things to Remember

  • Pivot tables do not change the values in the database.
  • We can insert Pivot Tables in the same 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: Pivot Tables can track and analyze hundreds of thousands of data points with a compact table. We can use Pivot Tables to compare, highlight trends, 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

Where is Pivot Table in Excel.png

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 a 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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Pivot Table Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Pivot Table Excel Template

EDUCBA

डाउनलोड Pivot Table Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW