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 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 TableExcel Pivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. Example, we should perform the sum of the 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 the Rainbow block got a 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 the 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 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 ExcelCreate A Group In ExcelThe “Group” is an Excel tool which groups two or more rows or columns. With grouping, the user has an option to minimize and maximize the grouped data. 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 the 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 the final product value again to the 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 the 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 the 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 a certain area category.
First, drag the area field into ROW and again drag the same area field into the 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 a particular area category.
Now right-click on any cell of the row labels and the select group.
Then the grouping window will pop out where we have to give the starting number for the frequency to get started and the 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 –