Showing % variance for data in excel or pivot table

Hi Wendy

Thanks for getting in touch. You can use the following formula to calculate the percentage variance between two values:

=(Later value - Older value) / Older value

I've attached a workbook with the formula in to demonstrate.

You can also do it with a PivotTable, but you need a Calculated Item. Build your PivotTable with Clients on the left, Years on the top and Values in the middle, and then click on one of the Year headings. Under the PivotTable Options ribbon click Formulas > Calculated Item.

Enter the Name of your Item (e.g. Variance) and then in the formula build the formula as per my example above (enter a bracket, double click 2011, enter a minus sign, double click 2010, a closing bracket, then a divide sign, then double click 2010. Click Add and OK to add it to the table. As before, this in the example.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector