Pivot Table and It’s Importance
Pivot table helps the user in preparing summary reports from large sums of data. In the coming paragraph, you will understand not only how to use this function but also its importance in adding values to your skills. It also reduces the chances of making errors and at the same time adding dashboards and charts to your reports will be at your finger tips. In the coming paragraphs, you learn “How to use Pivot Table Function in Excel 2016”. There might be few changes from the earlier version but basics are the same. The pivot table will make you proficient in preparing reports from different angles from the same data in few clicks.
Important Point to Note
There should be no blank row or column in the table for which you want to create Pivot table.
How to Use Pivot Table
We have our monthly sales report data as you can see in the image. There are four columns in the table. From the below data, with the help of pivot table we can prepare the following reports:
- Month wise Expense Report
- Project Wise expense Report for each month
- Expense wise Report for each project
- We can add different charts to our data for visualization
- Click somewhere in the above table, Select the table by using the keyboard shortcut (CTRL+A). you can select the table in the normal way as well.
- Go the Insert Tab, click on the “Recommended Pivot Table”. Keyboard shortcut for this ALT+N+SP or ALT+N+V.
- The below screen will open, the only thing you have to do here is to select the OK button.
- The next screen will be like the one below, you will find it to the right side of your work and to the left of your work sheet will be the data table. Drag the fields to the respective areas. Amounts field will always be in the values area. Drag Projects in the Row area, Expenses to the Column area and place the Months below the Projects in the row area.
- Your first report is ready, the report gives you information about project wise expenses. How much expense incurred in each expense category under each project. Total for Project A and Project B are listed in the Grand Total Column while the total for the expenses is listed in the Grand Total Row. The Grand Total can be turned on/off from the design tab.
Rearrangement of Data to Prepare report from different Perspective
Now just drag the month field and place it in the column area and Expenses field below the project in Row field. your new report should look like the one below:
In this report, project wise expense total is presented in rows and month wise total for each project in the column. This is due to the fact that we have placed expense field in the row and month field in the column.
Pivot Table can be updated by right clicking on it and select the refresh button.
Use of Filter Field in Pivot Table
If you are dealing with a large number of Projects, it will be a great idea to use the filter field. Drag the project to the filter field. Filter field creates a drop down list for the projects. You can choose the project from the drop list and instantly report will be updated. The same trick can be used for customers ledgers, vendors ledgers, employees advances and accounts ledgers depending upon your requirements. The basics are always the same.
You can also prepare separate reports for each project on separate sheets with the assistance of valuable trick. Click anywhere in the pivot table, it will activate the Pivot table Tools tan, click on the analyze, select the Option and click on the show report filter pages. In the next screen click, OK and separate reports for each project on separate sheets will be ready.
How to add calculated field in Pivot Table
Calculated field means a field that refers to other fields in the pivot table. Assume that 20% profit is added to the expenses incurred on the Projects to arrive at invoice figure to be sent to the customer. Click inside the pivot table, go the analyze tab. Click on the calculated field under the Fields, Items, and sets.
In the below screen, you need to add your name of the calculated field and formula. In our case formula will be =Expense Amount *20%. Double click on the field to which you want to link your formula. Click OK and your calculated field is now part of the pivot table.
Now we have expense total and profit. Let’s insert another calculated field. Named it as Invoice Amount. The formula for this will be Profit+Expense Amount. Again you need to double click on the Fields you want to link to this formula.
Look at the right side of the sheet and you will notice two new fields are added in the pivot table.
How to use the Design Tab
From design tab, you can change the report layout and report style. You have to be in the pivot table in order to activate the Pivot Table tools.
How to Insert Charts in Pivot Table
Charts can be added in the pivot table in more than one way. You can add charts from analyze tab and then click on the Pivot Chart. Select the chart that is useful in the given circumstance. Charts can also be added from the insert tab. Alternatively, use the keyboard ALT+N+C.
Pivot charts are a bit different than normal charts due to the fact that it creates drop down list for Axis. In the chart area, you can select the month or months and also you can select from the Project list as well. Select the first three months and Project A from Projects list. Click OK. We will see the impact on the next screen. When you click on the chart, + sign appear to the right corner of the chart. Clicking on it will open the chart elements. Do whatever is needful based on your requirements. From the Format tab, you can setup the chart shape height and width. From the shape styles, you can select the shape you like to add.
Not only our selection has its effect on the chart but on the pivot table as well. Having command over the pivot table and pivot chart will help you in mastering the dashboards preparation.
If it’s still not clear how to use it, you can watch the short video below.