WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Pivot Table in Excel

Pivot Table in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Pivot Table in Excel Tutorial

Pivot table in excel is a very useful feature of Excel which is used to make dashboards and reports based on a source of data, pivot table changes as the data in the source changes, it is available in the insert tab in the tables section or we can press keyboard shortcut ALT + D + P to insert a pivot table, we can change the contents of pivot table as it only changes when the data in source changes.

The pivot table is one of the data processing techniques used to extract useful information out of a large dataset. The purpose of the Pivot Table chart is to summarize data and to find unique values in a field. Hence it is used to build a set of unique values.

pivot-table-in-excel

How to Create a Pivot Table in Excel?

We can create a pivot table in excel using the following two methods.

Method #1

  • Click any cell from the data worksheet.
  • Goto Insert tab -> Tables group -> Pivot Table.

pivot table method 1.1

  • Create a PivotTable window that will appear, as shown below.

excel pivot table method 1.2

  • Select a table or range. Excel will prepopulate the data range.

excel pivot table method 1.3

  • Choose the location of the PivotTable report. It could be the existing worksheet or a new one. E.g., D1 is chosen ‘Existing Worksheet.’

excel pivot table method 1.4

  • Selecting ‘New Worksheet’ will insert a pivot table on the new worksheet other than the existing data worksheet.

excel pivot table method 1.6

  • One can choose to analyze multiple tables by clicking a checkbox that says – ‘Add this data to the Data Model.’

excel pivot table method 1.5

  • Clicking on ‘OK’ will insert the pivot table in the worksheet, depending on the location selected. Here we have selected New Worksheet.

excel pivot table method 1.7

The above Figure shows that the PivotTable named PivotTable1 has been placed on the new empty worksheet. On the right, there is a column named ‘PivotTable Fields, ’ which shows a list of the fields, i.e., columns to be added to the PivotTable report. We can see that since the PivotTable was created on the column range A, B, named ‘Math’ and ‘Science,’ the same has been populated in the Fields list.

Method #2

  • Select the data range that needs to be given as an input to the PivotTable.

excel pivot table method 2.1

  • Goto Insert -> Recommended PivotTables.

excel pivot table method 2.2

  • This option gives the recommended ways of creating PivotTables.
  • One can select and choose anyone amongst the given recommendations.
  • As shown in the screenshot below, there are two recommendations given by Excel. One of the ‘Sum of Maths by Science’ and the other is ‘Sum of Science by Maths.’

excel pivot table method 2.3

  • Clicking on any of the options opens a bigger picture of the same in the right panel wherein one can review the actual PivotTable and the values.

excel pivot table method 2.4

  • Here the options are ‘Group By,’ i.e., Add Science column marks group by Maths column marks in ascending order OR Add Maths column marks group by Science column marks in ascending order.
  • The window also offers a way to create a ‘Blank’ PivotTable. This can be done by clicking on the button ‘Blank PivotTable’ situated on the left bottom of the ‘Recommended PivotTables’ window. This would be like the PivotTable created using Method-1 mentioned in the previous section.

excel pivot table method 2.5

Examples

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

Example #1 – Max of Science by Maths

The data selected for the PivotTable is the marks of Maths and Science subjects denoted by Col. A and Col. B, respectively.

excel pivot table method 2.1

Generate the PivotTable report about the maximum number present in the Science Marks column group by Maths marks column values. To do that, follow the below steps.

  • Create a Pivot Table using the above-given steps and drag math to Rows Field and science to Values Field.

excel pivot table example 1.4

  • Right-click on the Pivot Table and select Value Field Settings.

excel pivot table example 1.1

  • Value Field Settings window will open. Select Max option from the Summarize value by.

excel pivot table example 1.2

  • As seen in the figure below, the Value Field is selected as ‘Max,’ which, as a result, returns the Maximum number present in the Science Marks ( B ) column.

excel pivot table example 1.3

Example #2 – Average. of Maths Column.

The data selected for the PivotTable is the marks of Maths, i.e., Col. A.

EX 2.1

Generate the PivotTable report about the average number present in the Maths Marks column. To do that, follow the below steps.

  • Create a Pivot table and drag maths in row Field.

EX 2.4

EX 2.5

  • Right-click on the Pivot Table and select Field Settings.

EX 2.2

  • The field Settings window will open. Select the Custom radio button under that choose Average function.

EX 2.3

  • Then drag Math in the value field.

EX 2.6

EX 2.7

  • Right-click on the Pivot Table and select Value Field Settings.

EX 2.8

  • Value Field Settings window will open. Select Average option from the Summarize value by.

EX 2.9

  • The Value Field is selected as ‘Average,’ which, as a result, returns the average, i.e., 77.4 as a result in the PivotTable Report.

EX 2.10

Similarly, other numeric operations can also be performed on the dataset. The dataset can also be filtered to fit the ranges per requirement.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Pros of the Pivot Table

  • Each column bar represents one value. So, the Pivot Table chart is useful where the entities to be measured are discrete such as Marks.
  • PivotTable reports are used to sort, distinguish, analyze, and present the data per requirements.
  • MS Excel Pivot Table Tools offer a variety of custom operations and filters to apply to the dataset.

Cons of Pivot Table

  • A Pivot Table may not be a good choice if the dataset is too large.

Things to Remember

  • The pivot table is used to extract important information from a large dataset.
  • Pivot table gets created in a new worksheet if ‘New Worksheet’ is selected while creating the Pivot Table.
  • ‘Recommended PivotTables’ option gives readymade alternatives to create a PivotTable report. The options are recommended by Excel utility based on the type of data selected from the worksheet.
  • The default Value Field Operation MS Excel performs on the selected data range is the Sum.
  • MS Excel offers a variety of custom functions to summarize the value field by, i.e., Sum, Count, Average, Max, Min, Product, etc.
  • MS Excel offers a variety of filters to be applied to the dataset, such as Less Than, Greater Than, Less Than Equal To, Greater Than Equal To, Between, Not Between, Equal To, Not Equal To, etc.

Recommended Articles

This has been a guide to Pivot Table in Excel Tutorial. Here we discuss how to create a pivot table in excel using two methods along with examples and a downloadable template. You can learn more about excel functions from the following articles –

  • Pivot Table From Multiple Sheets
  • Examples of Pivot Table
  • Insert Pivot Table Slicer
  • Filter Data using Pivot Table
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download Pivot Table Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More