Pivot Table in Excel

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.

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.

• Create a PivotTable window will appear as shown below.

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

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

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

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

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

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.

• Goto Insert -> Recommended PivotTables.

• 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 if ‘Sum of Maths by Science’ and the other is ‘Sum of Science by Maths’.

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

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

Examples

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.

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.

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

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

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

Example #2 – Average. of Maths Column.

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

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.

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

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

• Then drag Math in the value field.

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

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

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

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

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