EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Pivot Table Examples
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup 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+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Pivot Table Examples

By Madhuri ThakurMadhuri Thakur

Pivot Table Examples

Pivot Table Examples in Excel (Table of Contents)

  • What is Pivot Table Examples?
  • Examples of Pivot Table

What is Pivot Table Examples?

In this article, we are about to check some of the best examples and tricks of pivot tables. The pivot table is such a powerful and important tool excel has, which can do the work of hours in minutes for analysts. In this article, I am going to cover some of the best features of the excel pivot table through some examples.

Start Your Free Excel Course

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

Examples of Pivot Table

The data which I am going to use throughout this article is shown below:

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,452 ratings)
You can download this Pivot Table Examples Template here – Pivot Table Examples Template

Pivot Table Examples 0.1

Example #1 – Automatically Creating a Pivot Table

How good it would have been if you don’t need to worry about the questions like – “Which columns should be ideal for my pivot table?”, “Which columns should go under rows, columns, values, etc.?”. Do you feel it’s a fantasy? Let me take a moment to make you aware that this fantasy has become a reality in excel now. If you have excel 2013 or above installed in your system, it has an option called Recommended PivotTables. Let’s see how it works.

Step 1 – Select any cell in your data and click insert >Recommended PivotTables (You can see this option besides the PivotTable tab).

Pivot Table Examples 1.1

Step 2 – Click > Recommended PivotTable.

Pivot Table Examples 1.2

Step 3 – Excel will quickly analyze your data and come up with some of the recommended pivot table layouts.

Pivot Table Examples 1.3

The recommended pivot table option uses the actual data from your worksheet. Hence, there is a good chance that you’ll get a layout which you were looking for, or at least close to one of your interest.

Step 4 – Select any layout of your interest and click Excel created a pivot table on a new worksheet.

Pivot Table Examples 1.4

In the above example, we have seen the example of How we automatically create a table. Let us see another example in the Pivot Table.

Example #2 – Modifying Pivot Table

Once you create the pivot table, it is easy to modify the same. You can add some more fields in the layout to display more summary using the PivotTable Fields pane, which can be found at the right-hand side of your worksheet in which the pivot is.

Pivot Table Examples 2.1

Here, the column named Customer is added under Rows, and Branch is added under Columns. You can add the columns under the Rows or Columns pane by simply dragging them down to the respective field area.

On a similar note, you can also remove the field from the pivot table. Click on the column you wanted to remove, and there a pane will open, under which you need to click on Remove Field, and the field will be removed from the pivot table. Or you can simply drag the field out of the pivot table pane, which yields the same result.

Pivot-Table-Examples-2.2

If you add any field under the Filters section, it will appear at the upper part of the pivot table as a drop-down list, which allows you to filter the displayed data by one or more than one item.

Pivot Table Examples 2.3

The above figure shows the example of the Filter fields. I have added the Date under the filter field and can use this column to filter my pivot data. For instance, I have filtered the data for 27-Nov-2018. It means that my pivot table will now only show the data for 27-Nov-2018.

Pivot Table Examples 2.5

Example #3 – Customize Columns Under Pivot Table

Suppose we want to check the amount-wise distribution of accounts. We want to check what % of accounts are falling under what amount range. We can do this under a pivot table. So first, create a pivot table and then the columns as below.

Under Rows Field, select Amount (as a range). To add it as a range, select any cell from Pivot Table and right-click. Click on the Group section,

Pte 3.2

After that, make grouping as shown in the second image. Starting from 0 to 90000 with a difference of 5000.

  • Under Values Field, select Amount (as a count). Also, add Amount under the Values field as a % of the column. Add column Amount two times under Values; it will automatically select it as a count.
  • For storing Amount as % of Amount in each group, click on the second Amount and select Value Field Settings.
  • Under Value Field Settings pane, click on Show values As inside, which select option named % of Colum Total. It will change the field as % of the Amount for each Amount group. Moreover, you can also use a custom name for the column displayed in a pivot. Please see the name give Pct (Which makes sense for the Percentage column) and Count, which makes sense for the count of Amount.

Pte 3.3.1

The final output should look like below:

Pte 3.4

Let us see another example in the Pivot Table.

Example #4 – Data Analysis

Suppose we want to check on which day of the week gets more deposits in the account? Let’s see how we can go towards an answer to this question through pivot tables.

  • Create a pivot table with Weekday under Rows field and Sum of Amount under Values
  • If the Values field by default does not give Sum of Amount, make sure to change it through Summarize Values By under Value Field Settings (Change the type from Count to Sum, which will give the sum of Amount instead of count).

Your pivot should look like below:

Pte 4.1

Please note that I have updated the visual settings of column Sum of Amount using Cell Formatting. Please see the image below for the cell formatting reference.

Pte 4.2

Now we are going to use conditional formatting to add the data bars in this pivot for better visualization.

Though this pivot shows you that Thursday is the day on which more account deposits happen, the data bar will give you a clearer and more graphical representation of the same.

  • Select all fields except Grand total from your pivot
  • Click on Home
  • Go to Conditional Formatting dropdown > Data Bars. Under which, select a bar with a color of your choice and fill (either gradient or solid).

Pte 4.3

You should get the below output:

Pte 4.4

It gives a better idea in a single look, Right!. In this way, we can use some graphical analysis techniques as well under our pivot table with the help of Conditional Formatting. I hope this article is helpful. Let’s wrap things up with some of the points to be remembered.

Things to Remember

  • Though it is very flexible, Pivot Table has its limitations. You can’t make a change in the pivot table fields. You can’t add columns or rows under it and can’t add formula within the pivot table. If you wanted to make changes in a pivot table in a way not allowed normally, make a copy of your pivot table to some other sheet and then do. Select your Pivot table and hit Ctrl + C. or go to Home and select Copy under Clipboard.
  • If you update your source data, make sure you are Refreshing the pivot table to capture the latest updates made in your data. This is because pivot prevents automatic up-gradation once the source data has been updated.

Recommended Articles

This is a guide to the Pivot Table examples in Excel. Here we discuss some of the Different Types of Examples in Pivot Table with the excel template. You can also go through our other suggested articles to learn more –

  1. Pivot Table Count Unique
  2. Pivot Table Formula in Excel
  3. VBA Pivot Table
  4. Excel Delete Pivot Table
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 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 Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & 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 Login

Forgot Password?

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

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & 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 Examples Template

EDUCBA

Download Pivot Table Examples Template

EDUCBA

डाउनलोड Pivot Table Examples 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