How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)

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.

How to Calculate Variance Using Pivot Table in Excel: with Easy Steps

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.

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.

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.

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.

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 menu to hide the column.

Your final output will be like this.

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.

Download Practice Workbook

Download this workbook and practice while going through this article.

Variance in Pivot Table.xlsx

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.

<< Go Back to Calculated Field in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!