Akib Bin Rashid
Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to calculate variance for our use. We can easily do it using the Pivot Table in Excel. In this article, I will show you how to calculate variance in Excel Pivot Table.
Download Practice Workbook
Download this workbook and practice while going through this article.
5 Simple Steps to Calculate Variance Using Pivot Table in Excel
This is the dataset that I am going to use. We have some products and their sales amount.
I will calculate the variance of sales amount for the years 2020 and 2021.
Step 1: Create a Pivot Table from Data Range
- Select the range B4:D14. Then go to the Insert tab >> select Pivot Table >> select From Table/Range.
- A new window will appear. Select New Worksheet to get a pivot table in a new worksheet. Then select OK.
Excel will create a pivot table.
Read More: How to Calculate Sample Variance in Excel (2 Effective Approaches)
Step 2: Drag the Fields to Required Areas
- In the PivotTable Fields, put Product in the Rows, Year in the Columns, and Sales Amount in the Values
Then the table will look like this.
Read More: How to Do Variance Analysis in Excel (With Quick Steps)
Step 3: Remove Grand Total for Rows
- Now go to the Design tab >> select Layout >> select Grand Total >> choose On For Columns Only.
Excel will remove the Grand Total for Rows.
Read More: How to Calculate Pooled Variance in Excel (with Easy Steps)
Step 4: Change Cell Format to Accounting
- Now select the range B5:D10. Go to the Home tab >> select the drop-down (see image) >> select More Number Formats.
- Format Cells box will appear. Select Accounting >> set Decimal Places as 0. >> Click OK.
Excel will change the format of the sales amounts.
Read More: Budget vs Actual Variance Formula in Excel (with Example)
Step 5: Calculate Variance as a Change in Percentage
- Now put the Sales Amount to the Values field
- Now select the drop-down shown in the image >> select Value Field Settings.
- Now, the Value Field Settings window will pop up. Set the Custom Name Variance >> select Show Values as >> choose % Difference From.
- Now, select the Base field as Year and the Base item as 2020. Click OK.
- Excel will calculate the variance.
- Now, select column C. Select Hide from the context bar to hide the column.
Your final output will be like this.
Read More: How to Calculate Variance Percentage in Excel (3 Easy Methods)
Things to Remember
- This method is basically about calculating the variance in percentage between the sales data of two separate years. However, it is worth mentioning that this variance is different from statistical variance.
Conclusion
In this article, I have demonstrated an effective method to calculate variance in Excel Pivot Table. I hope it helps everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
