EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Excel Pivot Table
Secondary Sidebar
Excel Functions
  • Excel Tips
    • Recover Document in Excel
    • Excel Shortcut to Select Row
    • Excel Header Row
    • Excel Insert Button
    • Excel Translate
    • Budget in Excel
    • KPI Dashboard in Excel
    • Blank Invoice Excel Template
    • Write Formula in Excel
    • Auditing Tools in Excel
    • Autofit Row Height in Excel
    • Formatting Text in Excel
    • Add Rows in Excel Shortcut
    • Divide Cell in Excel
    • Compare Two Columns in Excel for Matches
    • Excel Troubleshooting
    • Unprotect Excel Workbook
    • Subtract Date in Excel
    • Carriage Return in Excel
    • Negative Numbers in Excel
    • Worksheets in Excel
    • Excel Match Multiple Criteria
    • Exponential Moving Average Formula
    • Sentence Case in Excel
    • Excel Reverse Order
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Percent Change in Excel
    • Drag and Drop in Excel
    • Shade Alternate Rows in Excel
    • Excel Database Template
    • Inverse Matrix in Excel
    • New Line in Excel Cell
    • Format Cells in Excel
    • Excel Text with Formula
    • Project Management Template in Excel
    • Vlookup vs Index Match
    • Embedded in Excel
    • Count Colored Cells in Excel
    • Excel Hacks
    • Column Header in Excel
    • Database Function in Excel
    • Excel Spell Check
    • Linear Interpolation in Excel
    • Convert Excel to CSV
    • Excel Not Responding
    • Delta Symbol in Excel
    • Excel Mortgage Calculator
    • Excel Show Formula
    • Break Links in Excel
    • VLOOKUP Examples in Excel
    • Alternate Row Color Excel
    • Checklist in Excel
    • Microsoft Office Tips
    • Excel vs Access
    • Excel Change Case
    • Count Characters in Excel
    • Formula Bar in Excel
    • Invoice Template in Excel
    • Find and Replace in Excel
    • PowerPivot in Excel
    • Wildcard in Excel
    • Line Break in Excel
    • NULL in Excel
    • Text Formula in Excel
    • Family Tree in Excel
    • Insert Calendar in Excel
    • Page Setup in Excel
    • Search For Text in Excel
    • Switching Columns in Excel
    • 3D Cell Reference in Excel
    • Roadmap Template in Excel
    • Cell References in Excel
    • Excel Automation
    • Matching Columns in Excel
    • Evaluate Formula in Excel
    • How to Find Mean in Excel
    • How to Add Cells in Excel
    • Project Timeline in Excel
    • TODAY Formula in Excel
    • Poisson Distribution in Excel
    • YEAR Formula in Excel
    • REPLACE Formula in Excel
    • Create Spreadsheet in Excel
    • SEARCH Formula in Excel
    • HYPERLINK Formula in Excel
    • Pivot Table with Multiple Sheets
    • COLUMNS Formula in Excel
    • DAY Formula in Excel
    • RIGHT Formula in Excel
    • INDIRECT Formula in Excel
    • Excel IRR Formula
    • Pivot Table Sort
    • OR Formula in Excel
    • Pivot Table Formula in Excel
    • How to Edit Drop Down List in Excel?
    • WEEKDAY Formula in Excel
    • Chart Wizard in Excel
    • Time Difference in Excel
    • Lookup Table in Excel
    • SUBTOTAL Formula in Excel
    • LOOKUP Formula in Excel
    • Themes in Excel
    • Group Worksheets in Excel
    • Excel ISNUMBER Formula
    • 3D Maps in Excel
    • VLOOKUP Tutorial in Excel
    • Moving Columns in Excel
    • Grouping Columns in Excel
    • Excel Formula of Percentage
    • Excel Leading Zeros
    • Timesheet in Excel
    • Nested IF Formula in Excel
    • SUMIF Formula in Excel
    • Concatenation in Excel
    • Excel Insert Page Break
    • LEN Formula in Excel
    • Insert Comment in Excel
    • Excel Print
    • Fractions in Excel
    • Excel Add a Column
    • Borders in Excel
    • Excel Sum by Color
    • Excel vs Google Sheets
    • Calculate Percentage Increase in Excel
    • Excel Format Phone Numbers
    • Excel Search Box
    • Excel vs CSV
    • Excel vs Numbers
    • Excel Concatenate Date
    • Excel Bullet Points
    • Calculate Compound Interest in Excel
    • Find External Links in Excel
    • Excel Comma Style
    • Excel Remove Leading Spaces
    • Combine First and Last Name in Excel
    • How to Calculate Ratio in Excel
    • SUMPRODUCT Function with Multiple Criteria
    • Create Excel Template
    • Excel Greater than or Equal
    • Excel Keyboard Shortcuts
    • Excel Row vs Excel Column
    • Excel Operators
    • Excel Text Compare
    • Excel Uppercase Function
    • Excel Auto Numbering
    • Watermark in Excel
    • Excel Delete Row Shortcut
    • Excel Word Count
    • Row Count in Excel
    • Excel Grade Formula
    • Excel Percentage Difference
    • Excel Rows and Columns
    • Excel Group
    • Excel Move Columns
    • Excel Row Height
    • Excel Sparklines
    • Excel Drawing a line
    • Multiple IFS in Excel
    • Excel Strikethrough
    • Numbering in Excel
    • Share Excel Workbook
    • Excel Shortcut For Merge Cells
    • Excel OneDrive
    • Excel Track changes
    • Excel Hide Formula
    • Excel Filter Shortcuts
    • Excel 3D Reference
    • Protect Excel Workbook
    • Excel Moving Averages
    • Excel Row Limit
    • Excel Absolute Value
    • Excel Print Area
    • Excel Format Painter
    • Excel Separate text
    • Excel Autofit
    • Excel Columns to Rows
    • Excel Paste Shortcut Option
    • Excel Rows to Columns
    • Excel Insert Row Shortcut
    • Excel Extensions
    • Excel Unmerge Cells
    • Divide in Excel Formula
    • Excel Compare Two Columns
    • Not Equal To Excel
    • Excel Remove Duplicates
    • Excel Remove Spaces
    • Excel Calculate Age
    • Copy Excel Sheet
    • Excel Conditional Formatting Based on Another Cell Value
    • Excel Cell Reference
    • Excel Split Cell
    • Excel Circular Reference
    • Highlight Every Other Row in Excel
    • Errors in Excel
    • Excel Delete Pivot Table
    • Excel Pivot Table
    • Subtraction in Excel
    • Count Unique Values in Excel
    • Excel Highlight Duplicates
    • Excel Data Formatting
    • Random Numbers in Excel
    • Basic Excel Formulas
    • Page Numbers in Excel
    • Excel CAGR Formula
    • Excel Combine cells
    • IFERROR with VLOOKUP in Excel
    • Column Sort in Excel
    • Print Comments in Excel
    • Cheat Sheet of Excel Formulas
    • Remove Hyperlinks in Excel
    • Excel Drop Down List
    • Remove (Delete) Blank Rows in Excel
    • Relative Reference in Excel
    • Subscript in Excel
    • Superscript in Excel
    • Excel Insert Multiple Rows
    • Excel Absolute Reference
    • Unhide Columns in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course

Excel Pivot Table

By Madhuri ThakurMadhuri Thakur

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,

Pivot Table in Excel 1

The data is organized in the below form:

Start Your Free Excel Course

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

Pivot Table in Excel 2

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?

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. We want to count the properties according to their status using the Pivot Table.

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

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

Auditor field

It results 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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)

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

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 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 as shown below with the Fields pane on the right side.

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 as shown 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 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

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 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
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Finance Training (18 Courses, 7+ Projects)4.8
24 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Pivot Table Excel Template

EDUCBA

Download Pivot Table Excel Template

EDUCBA

डाउनलोड Pivot Table Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more