FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Pivot Table Examples

Updated on February 9, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Are Pivot Table Examples?

PivotTable is a table of stats that summarizes the data as sums, averages, and many other statistical measures. Let us assume that we got data of any real estate project with different fields like type of flats, block names, area of the individual apartments, and their other cost per various services, etc.

Key Takeaways

  • Pivot Table sums up data through statistical measures like sums and averages. 
  • It eases the analysis of large amounts of data, such as in real estate projects with various flat types, block names, individual apartment areas, and service costs.
  • To select a row or column, tap its letter or number.
  • In Excel, modifying the format creates a duplicate format. Custom number formats affect the cells’ appearance, not their values. On custom format removal, cells are present in the default format.

Examples Of Pivot Table In Excel

Below is the raw data for the PivotTable practice exercises.

Pivot Table Raw Data

Create a PivotTable using the above table.

PivotTable Example #1 – Performing Statistics Measures In PivotTable

SUM:

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.read more Example, we should perform the sum of the final product value to extract the value of different blocks got as below:

Drag the “Block Name” to “Rows” and “Sum of Final Product Value” to “Values” Fields.

Pivot Table Example 1 sum
Pivot Table sum Example 1-1

It shows that we have the “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 than the other blocks.

COUNT:

Let us count the number of flats for different blocks by changing the value field settings in Excel PivotTable to count as below:

Then, click on “Value Field Settings.”

Pivot Table Count Example 1-2

A dialog box appears. Select “Count” from “Summarize value field by.”

Pivot Table Count Example 1-3

Then the PivotTable looks like as given below:

Pivot Table Count Example 1-4

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 the “Rainbow” block and “Strawberry” block compared to other blocks. In total, we got 79 flats in this project.

AVERAGE: 

In the PivotTable 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.”

Pivot Table Average Example 1-5

Then, PivotTable changes from “Count” to “Average,” as given below:

Pivot Table Average Example 1-6

From the above PivotTable example, we can see that we got the average flat price in each block. The “Donald” block got the highest average flat price 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 PivotTable example.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Pivot Table Example #2 – How Can We Modify The PivotTables In Excel

We can change the layout of the Excel PivotTable per our requirement by dragging the fields into areas where we want to place them.

In this example of a PivotTable in Excel, let us see how it works.

First, we must drag the “Maintenance Deposit” into the “Values” area to check each block’s total maintenance price. You will get your table modified as below:

Pivot Table Example 2

This PivotTable shows both maintenance deposit and an average price for different blocks. As discussed earlier, we can change the statistical measure by changing the value field setting.

Pivot Table Example 2-1

We can also modify the above table further by adding the type of units that are available as block-wise as below:

Pivot Table Example 2-2

The above table shows that the type of flats in the “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 PivotTable

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.read more PivotTables to differentiate a few fields. In our PivotTable practice exercise example, let us create the “Strawberry” block, “Cheeku” block as “Group-1” and “Donald” block, “Mickey” block, and “Rainbow” block as “Group-2.”Let us see how we can create these groups:

As per our above PivotTable example, select “Cheeku” and “Strawberry” with the help of the “Ctrl” key. Then, right-click on the mouse to get the list. Next, go to the “Group” option and then click on it.

Pivot Table Example 3

Now, we can see the group has been created as below:

Pivot Table Example 3-1

Similarly, we can select the rest of the block and do the same. Then, click on the “Group” again. Now, “Group-2” will also be created as follows:

Pivot Table Example 3-2

We can also name the group by editing the group and giving the names per our requirements.

PivotTable Example #4 – Creating Multi-Levels In Excel Pivot Table

Creating multi-levels in PivotTable is easy by just dragging the fields to any specific area in a PivotTable.

But here, in the example of the PivotTable, we understand how we can also make great insight into this multilevel PivotTable.

First, we need to drag the “Block Name” into “ROWS” as we find some insight into the block of the project. Then, drag the “Final Product Value” to the “VALUES” area as we get the sum of all flats units value of a particular block in the table.

Drag the “Final Product Value” to the “VALUES” area to create a multi-level table.

Pivot Table Example 4

We can see the table with two columns with “Final Product Value” for different blocks below:

Pivot Table Example 4-1

Now, let us find out some insight from this data. To do so, we should change the value field set to any measures that need to be considered. Here, we will take a percentage to see the contribution of different blocks in terms of the product value as below:

Go to “Value Field Settings,” then select “Show values as.” In that, select “% of Column Total.”

Pivot Table Example 4-2

We can see the table with a percentage in “Sum of Final Product Value2” below:

Pivot Table Example 4-3

From the above data, we can understand that the “Cheeku” block had contributed 17.76%, “Donald” – 12.88%, “Micky” – 19.63%, and “Rainbow” – 28.23%, and “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 PivotTable, which provides great insight into the data.

PivotTable Example #5 – Creating Frequency In Excel PivotTable

Earlier in the example of the PivotTable, we had seen grouping the name of the blocks. Now, let us group the value to create a frequency that shows us the number of units that fall under a certain area category.

First, drag the area field into “ROWS” and again drag the same area field into the “VALUES” area. By default, the “VALUES” area gives the sum of the area, so we should change it to count because here, our goal is to find several units in a particular area category.

Pivot Table Example 5

Now, right-click on any cell of the row labels and the select group.

Pivot Table Example 5-1

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. In addition, we have to provide a number that will create a frequency.

Pivot Table Example 5-2

Let us provide the conditions as a starting point as 1000 and ending at 3400. Then, it should be split by 400.

Pivot Table Example 5-3

Now, we can see that the area category has been formed. There are 19 flats which got an area between 1,000-1,400 sq.ft, 5 flats with an area between 1,400-1,800 sq.ft., 41 flats with an area between 2,200-2,600 sq.ft., 2 flats with an area between 2,600-3,000 sq.ft. and 12 flats with an area between 3,000-3,400 sq.ft. So, here we can assume that we got the highest number of flats with 3 BHK configurations per area.

Pivot Table Example 5-4

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

Important Things To Note

  • Pivot Table helps us analyse large dataset used in various fields such as gaming, finance, auditing, business projects etc.
  • We can use statistical functions to create accurate Pivot Tables useful for the users.
  • To select the entire sheet, remember that we need to click on the box next to “A” or above “1.”

Frequently Asked Questions

1. What is the use of PivotTable in daily life?

Pivot Tables can quickly point out essential data in our records/data that were first unnoticed, such as identifying any invoices before or after the date.

2. Are pivot tables used in finance?

Yes. In finance, pivot tables are a critical tool for efficiency and accuracy in data analysis and financial reporting.

3. What are the 4 fields in PivotTable?

A Pivot Table consists of four areas where field names can be placed – Filters, Columns, Rows, and Values. These areas can help you to organize and analyze your data more effectively.

This article is a guide to PivotTable examples in Excel. Here, we provide practice exercises PivotTable in Excel with examples such as modifying PivotTable, statistical measures, and grouping fields. You may learn more about Excel from the following articles: –

Reader Interactions

Comments

  1. Akulino Okeny says

    Nice to join you people I wish I will perform better

Leave a Reply

Your email address will not be published. Required fields are marked *