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+)
- Power Bi (35+)
Examples of Pivot Table in Excel
A Pivot table is a table of stats which summarizes the data as sums, averages and many other statistical measures. Let’s assume that we got data of any real estate project with different fields like type of the flats, block names, area of the individual flats and their different cost as per different services etc.
Below is the Raw data for Pivot table practice exercises
Create a Pivot Table using the above Table.
Pivot Table Example #1 – Performing Statistics Measures in Pivot Table
In the Excel Pivot Table Example, we should perform the sum of final product value in order to extract the value of different blocks got as below:
Drag the Block Name to Rows and Final Product value to Values Fields.
This is showing that we have got Cheeku block with 293 Million, Donald block with 212 Million, Mickey block with 324 Million, Rainbow block with 466 Million and Strawberry with 355 Million. This table shows us that Rainbow block got higher value among the other blocks.
Let us count the number of flats for different blocks by changing the value field settings in Excel Pivot Table to count as below:
Click on Value Field settings.
A dialog box appears. Select “Count” from Summarize value by.
Then the Pivot table looks like as given below:
From the above tables, we can see how to change the field settings to count. Here we can note that we got the highest number of flats in Rainbow block and Strawberry block compared to other blocks. And in total, we got 79 flats in this project.
In Pivot Table Practice Exercise Example, we will find out the average price of the flat in each block by changing the value field settings from count to average.
Then a Pivot table changes from Count to Average as given below:
From the above Pivot table example, we can see that we got the average flat price in each block and Donald block got the highest average price of flat among other blocks.
Similarly, we can perform other statistical measures like Max, Min, Product, Standard deviation etc by changing the value field settings in this Example of Pivot table.
Pivot Table Example #2 – How can we Modify the Pivot Tables in Excel
We can actually change the layout of the Excel Pivot table as per our requirement by dragging the fields into areas that we want to place it.
In this example of a pivot table in excel, Let’s see how it actually works:
In order to check the total price of maintenance of each block we just need to drag the maintenance field into the Value area and you will get your table modified as below:
Now, this Pivot table shows both maintenance deposit and an average of price for different blocks and we can change the statistical measure by changing the value field setting as discussed earlier.
We can also modify the above table further by adding the type of units that are available as per block-wise as below:
The above table shows that the type of flats in Cheeku block are A1, A2a, B1, B2a, C1a, C1b, D1c, D2b, D3b, D4a, D4b, D4c. Similarly, we get for other blocks too.
Pivot Table Example #3 – Grouping the Fields in the Excel Pivot Table
We can also create a group in Excel pivot tables in order to differentiate a few fields. In our Pivot Table practice exercise example, let’s create Strawberry block & Cheeku block as Group-1 and Donald block, Mickey block & Rainbow block as Group-2. Let’s see how we can create these groups:
As per our above pivot table example, select Cheeku and Strawberry with the help of Ctrl. Then give a right click on your mouse to get the list. Go to the group option and then click on it.
Now we can see the group has been created as below:
Similarly, we can select the rest of the block and do the same process and click on the group again. Now, Group-2 will also be created as follows:
We can also name the group by editing the group and giving the names as per our requirement.
Pivot Table Example #4 – Creating Multi levels in Excel Pivot Table
Creating multi-levels in Pivot Table is pretty easy by just dragging the fields to any specific area in a pivot table.
But here in the example of the pivot table, we understand how we can also make great insight into this multi-level pivot table.
First of all, we need to drag the block name into ROWS as we are finding out some insight on the block of the project. Then drag your final product value to VALUE area as we get the sum of all flats units value of a particular block in the table.
In order to create a multi-level table, drag final product value again to VALUE area.
We can see the table with two columns with Final Product value for different blocks as below:
Now let’s find out some insight from this data. To do so we should change the value field set to any of the measures that need to look at. Here will take a percentage to see the contribution of different blocks in terms of product value as below:
Go to value field settings then, select “Show Values as” in that select “% of Column Total”.
We can see the table with a percentage in Final Product value2 as below:
From the above data, we can understand that Cheeku block had contributed 17.76%, Donald – 12.88%, Micky – 19.63%, Rainbow – 28.23%, Strawberry – 21.51% of Total value of the project. A rainbow block is the highest contributor among all other blocks.
Similarly, we can perform different multi-level operations using a pivot table which provides great insight from the data.
Pivot Table Example #5 – Creating Frequency in Excel Pivot Table
Earlier in the example of pivot table, we had seen grouping the name of the blocks and now let us group the value in order to create a frequency that shows us the number of units that fall under certain area category.
First, drag the area field into ROW and again drag the same area field into VALUE area. By default, the VALUE area gives the sum of the area so we should change it to count because here our goal is to find a number of units in particular area category.
Now right click on any cell of the row labels and the select group.
Then grouping window will pop out where we have to give the starting number for the frequency to get started and ending number to end it. In addition to this, we have to give a number which will create a frequency.
Let’s provide the conditions as a starting point as 1000 and ending at 3400. It should split by 400.
Now we can see that the area category has been formed. There are 19 flats which got an area between 1000-1400 sqft, 5 flats with an area between 1400-1800 sqft, 41 flats with an area between 2200-2600 sqft, 2 flats with an area between 2600-3000 sqft & 12 flats with an area between 3000-3400 sqft. Here we can assume that we got the highest number of flats with 3 BHK configuration as per our area.
You can download this Pivot Table Examples Excel Template here – Pivot Table Examples Excel Template
This has been a guide to Pivot Table Examples in excel. Here we provide practice exercises Pivot Table in excel with examples such as Modifying Pivot Table, Statistical Measures, Grouping Fields. You may learn more about excel from the following articles –