Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
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.
Tutorial to Create an Excel Pivot Table (Table of Contents)
Pivot Table in Excel Tutorial
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.
- 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.
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.
- Select 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 the 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 of Pivot Table in Excel
Below are the examples of the Pivot table in excel.
Pivot Table in Excel Example #1 – Max of Science by Maths
The data selected for the PivotTable is the marks of Maths and Science subject 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.
Pivot Table in Excel 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 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 on 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.
- Excel 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.
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 downloadable excel sheet. You can learn more about excel functions from the following articles –