Data Analysis With Pivot Table Part – 2/2
(This is the second part of Data Analysis With Pivot Table Part – 1/2 post)
You can also customize your pivot tables with custom calculated fields and conditional formatting. In this post, I am going to show you how to do enchance your data analysis with pivot table.
As mentioned above, you are not limited to the fields that you are given with pivot table. You can add custom fields and columns to your pivot table.
Lets add another column that shows monthly sales revenue if unit prices were 20% cheaper. To do this, you need to click any cell within pivot table and open Calculated Field window like shown below.
Set your screen like below and click on Add button.
A calculated column will be added to your pivot table for each month. You can change your column headers just as any cell and make it look fancier.
Similar to the example above, you can divide, multiple, subtract, etc. columns with eachother as calculated fields.
Now lets see how did our customers do for each month. We are now going to visually indicate whether monthly revenue of each customer is increased or decreased in comparison with previous month.
Click on Total field in Pivot Table Field List and drag it in to Values field for a second time. Yes you can do it 🙂
Now select this secondary columns (like shown below).
Make below selection and set options as month and previous.
Now your secondary columns will show monthly % change in revenue. Change column headers as Sum of Total to Monthly_T and Sum of Total2 to Change
Select all Change columns and apply conditional formatting. Select Icon Sets / Directional 3 Arrows (Colored). While all secondary columns are still selected enter Conditional Formatting / Manage Rules. Select your conditional formatting rule and click edit button. Set options like shown in the picture below:
This setting will make your negative percentages shown as red (downward) arrow and your positive percentages as green (upward) arrow. Using same value for both boxes will eliminate use of yellow arrow. Why use 55 as value? I don’t know, I found it with trail and error.
Now final form of your pivot table: