PIVOT table is one of Excel’s most powerful feature. Using PIVOT table you can create your own extract from a large detailed data set. In this tutorial we will learn different behaviors of Pivot tables.
In our example we have the below table containing 5 columns as you can see.
Now select any cell of the table and click on the ‘Insert’ tab of the ribbon and then click on ‘Pivot Table’ as shown below.
Now in the Create Pivot table window select the source table/range on which you need to create the Pivot table. Here we selected our initial table.
You need to also specify the location where you want to place your Pivot table. It can be same or in a different worksheet. In our example we have selected the location G1 in same worksheet. Click OK.
The empty PIVOT table has been created in cell G1 as shown below. Along with the empty PIVOT table, in the same worksheet PivotTable Fields pane is also displayed, which is empty at this point. Note all five columns of our table are listed as fields, but unused:
Now to build the pivot table, drag fields into one the Columns, Rows, or Values area. The Filters area is used to apply global filters to the pivot table.
- Now first drag the Amount field to the VALUES area.
It calculates the sum of all Amount values in the entire table.
- Now Drag the Product field in ROWS area.
It breaks out the amount by Product. See there are multiple entries of product Banana, Egg & Tomato in source table. In PIVOT table the the sum of amount for each product has been calculated & displayed.
- Now to apply the currency on the amounts in PIVOT table select the column and right click. Click Number Format.
4. You can select the desired currency and the number of decimal places in the below Format Cells window. Click OK.
5. Below you can see the currency and other format changes have been applied to the Sum of Amount column of PIVOT table. The currency format will continue to be applied to Sum of Amount values, even when the pivot table is reconfigured, or new data is added.
6. To sort the PIVOT table on Sum of Amount column, right click on any Sum of Amount Value and go to Sort and select the desired sorting method (smallest to largest or largest to smallest)
7. Here we have sorted by smallest to largest and below is how it shows.
8. Now we have updated the amount in source table for products Mango as 50 and Egg as 30. In PIVOT table right click on any value and Refresh in order to bring in updates.
9. See below the Sum of Amount in PIVOT table has been updated for Products Mango and Egg. Please note, since it has been sorted from smallest to largest on Sum of Amount, even after the change of values it is still sorted.
10. Now we have added one more field in VALUES area. That is Unit. In PIVOT table, Sum of Unit column appeared.
11. Now let’s say, you have a requirement to display the Sum of Unit as “% of grand total”. You can do it by right click on any value in Sum of Unit and then ‘Show Values As’ and then choose from the available options there.
12. Here we have selected “% of grand total” and below is the resulted PIVOT table.
13. Now drag the Unit field out of the VALUES area and drag & drop the Amount field to the VALUES area for one more time. By this way a second column named ‘Sum of Amount2’ has been created in PIVOT table. And then we have chosen to show the values of 2nd Sum of Amount as “% of grand total” as follows.
14. Now if you simply drag and drop the date field to COLUMNS area, the PIVOT table will look like below.
15. Now Right click on any year in the header and Choose Group.
16. When the Group window appears, you can select the respective options like year, month, days and so on based on what you want to group your data. In our example we have selected only year and deselected the rest.
17. As you can see below, the ‘Sum of Amount’ and ‘Sum of Amount2′(% of grand total) both have been grouped by year. Since the date range in the source table is from 2/4/2019 to 10/3/2020 as shown in the above Grouping window, hence the date includes 2 years(2019 & 2020). So here the data has been grouped by 2 years only.
18. Now drag the Date field out of the COLUMNS area. Drag the ‘Sum of Amount2’ field out of the VALUES area. Drag and drop the CATEGORY field in COLUMNS area. Here comes the two-way pivot table that breaks down the amount by product and category as follows.
19. Now Swap Category and Product (i.e. drag Category to the ROWS area and Product to the COLUMNS area). Here comes another two-dimensional pivot table as follows.
Now we will learn how to build a pivot chart which is the visual representation of a pivot table in Excel. The Pivot charts can be built based on the two dimensional Pivot table like the above one we have.
- To insert a pivot chart, Click any cell inside the pivot table. On the Analyze tab, in the Tools group, click PivotChart.
- Then Insert Chart dialogue box will appear like below. Click OK.
- The PIVOT chart will appear as follows. Please note, any change you make to the pivot chart will immediately reflect in the pivot table and vice versa.
- Now you will be able to filter the Pivot chart based on category or product or both. Like we are filtering only for the Non Veg Category as follows.
- As you can see below, in Non Veg Category category we have got the chart for the products Egg and Chicken which only belonged to Non Veg Category. Please Note here as per the filter you are choosing in PIVOT chart, the same is being updated automatically in the connected PIVOT table as well.
- Remove Category filter and filter the Product for Carrot, Tomato, Mango, Egg & Banana. The Pivot chart will be displayed as follows.
- The type of PIVOT Chart can be changed by Selecting the chart and on the Design tab, in the Type group, click Change Chart Type.
- Here we have selected 3-D Stacked bar under Bar. Click OK.
- Here comes the PIVOT chart of type 3-D Stacked bar.