Use of Pivot Table — Microsoft Excel
In this article, we will walk you through the fantastic journey of learning the uses of pivot table in excel and along with that we also understanding the fundamentals of using pivot table, specifically for financial projects.
Uses of Pivot Table
Now we are ready to create a Pivot Table. We have learnt about Pivot Table in this section, Getting Ready With Advance Excel. Now we will use it to create dashboards. First, we will create various Pivot Tables and Pivot Charts. Worksheet name is Raw Data from where the Data is coming. Insert a new sheet in the Workbook, rename it to Sales Revenue and then insert a Pivot Table for Sales
Revenue. Insert a Pivot Table in Raw Data and then select any cell and click on insert tab,
Pivot Table, From Table, Range, Existing Worksheet, Select Sales Revenue, Cell A1.
As we can see in the above figure, we are ready with Pivot Table formalities. Click on OK.
Now, we will select the fields in Pivot Tables for sales revenue month wise.
Select Month in Rows and Total Value Sold in Value.
As we can see in the above figure, we have selected fields for Row and Value.
But remember that, When we select month for Column/Row Fields Month, it is automatically converted into Months (Month), Quarters (Month) and Years (Month).
So we will unselect Months (Month) and Quarters (Month) and keep the Years (Month) selected. The Pivot Table will look as shown in the figure below.
As we can see in the above figure, the Pivot Table for Month-wise Sales is ready.
Rename the Pivot Table as Month wise Sales from the Pivot Table Analyze tab.
As we can see in the above figure, We have renamed the Pivot Table Name to Month wise Sales.
The next step is to insert a Pivot Chart, for which we will go to the Dashboard Sheet later. Now, select the Monthly Sales Revenue Pivot Table and insert a Pivot Chart.
In the above figure, is showing selected Column Chart for Pivot Chart for Monthly Sales
Revenue.
Now, we will clean our chart for a lucrative look.
Select Pivot Chart and add a new tab, name it PivotChart Analyze. Hide all Field Buttons from Show/Hide: select Hide All.
As we can see in the above figure, we selected Hide All in Field Buttons. The Chart will look as shown in the figure below and we will delete all items circled in red from the Chart.
As we can see in the above figure, our chart looks better, but we will delete all unwanted items circled in red for a much better look of the chart.
We will remove Gridlines as well from the chart by Selecting Chart and unselecting
Gridlines, as shown in the figure below.
As we can see in the above figure, we have removed all items circled in red and all Gridlines from the Pivot Chart.
As we can see in the above figure, we have removed all items circled in red and all Gridlines from the Pivot Chart.
As we can see in the above figure, we have our final chart for Month wise Sales.
Now, rename the Pivot Chart to Month wise Sales.
We will copy this chart to our Dashboard later.
Now, we will create Pivot Tables and Pivot Charts with the same process for Store wise Sales Revenue, Product wise Sales Revenue and Salesperson wise Sales Revenue. The Store wise Sales Revenue Pivot Table.
As we can see in the above figure, we have selected Store in row field and Total Sold Value in Value field, and we have renamed the Pivot Table to Store wise. Now, to create a Pivot Chart for the same, we will remove all fields and items that
are not required from the Chart.
As we can see in the above figure, we have a Pivot Chart for Store wise Sales. However, it does not look informative, so we must add some figures.
Select Chart and click on the Design Tab and Quick Layout then, select whatever
you want.
As we can see in the above figure, we have added layout 4 where we have store no. and its total during the period. Now we want to add percentage as well, so select any number in the Chart and then select percentage in Label Option from right hand side format data label as shown in the figure below.
Rename the Chart as Store wise Sales.
As we can see in the above figure, we have added percentage as well in the Pie Chart.
As we can see in the above figure, we have created a Pie Chart for Store wise Sales. Next, we will create a Pivot Table for Product wise Sales. We will not create a Pivot Chart for Product wise Sales because we will show only the Table for Product wise Sales.
As we can see in the above figure, we have Product wise Sales.
Rename the Pivot Table as Product wise Sales.
We will copy this pivot table to the Dashboard.
Next, we will create a Pivot Table and a Pivot Chart for Salesperson wise Sales.
As we can see in the above figure, we have Salesperson wise Sales.
Now, we will create a Pivot Chart with the previous Pivot Table and do the needful to get a better looking Chart.
As we can see in the above figure, we have Salesperson wise Sales Pivot Chart.
Now we are ready to make an amazing dashboard.
Copy the Month wise Sales Pivot Chart, the Store wise Sales Pivot Chart, the Product wise Sales Pivot Table and the Salesperson wise Sales Pivot Chart in a New Sheet
that is, the Dashboard.
Remove Gridlines from the Dashboard Sheet. Paste these one by one in the Dashboard Sheet, as shown in the figure below.
As we can see in the above figure, we have created a layout of the Dashboard.
Rename the Pivot Table of Product wise Sales in the Dashboard Table.
As we can see in the above figure, Pivot Table has been renamed in Dashboard Sheet of Product wise Sales.
Conclusion
Finally, we will understanding the fundamentals of pivot table and along with that we also discussing it’s uses specifically for financial projects and how it help us to figure out our important business decisions.
