CODEWITHSUNDEEP
excel
dazzathedrummer
dazzathedrummer

Reputation: 531

How can I create a % variance column in Excel 2010 pivot table?

I have a Pivot table containing sales data.

In the Rows I have sales country, in the Columns I have sales date (year) and I have the sum of the invoiced net values in the data section.

Across the top I have two columns - '2012' and '2013' - next to these excel, by default adds a grand total column adding the two years together.

Is there anyway that, instead of a grand total, I can show the percentage variance between 2012 and 2013?

Upvotes: 1

Views: 19512

Answers (2)

Nickstoy
Nickstoy

Reputation: 194

pnuts' answer is accurate.

If you are looking for a way to have this calculated automatically in the PivotTable, you can use calculated fields.

In order for this option to work, you will likely have to change the way your data is structured.

I'm assuming right now you have a data column with Sales Date. You will need to add one column per year, and assign a value to it in your data. You can then get rid of your invoice column.

Click anywhere inside your pivot table, in the ribbon go to Fields Items & Sets, Calculated field.

In the formula, type in what you need (you can use the fields list to add them in). I did =('2013'-'2012')/'2012'

You can then of course change the formatting to be in %

See screenshot for old vs proposed.

enter image description here

Upvotes: 3

pnuts
pnuts

Reputation: 59485

Excel 2010 may be different but if Excel 2007 is better than nothing:

  1. Create your PT in the normal way (Country for Row Labels, Year for Column Labels, Sum of Invoiced for Σ Values).
  2. If present, remove the Grand Totals for rows (Select the PT, Pivot Table Options…, Totals & Filters, uncheck Show grand totals for rows). The Grand Totals for columns may be kept, but the value there in a column to be added would not be meaningful.
  3. Select a column header (say 2013) and in Options > Tools – Formulas select Calculated Item…
  4. In Name: select something suitable (perhaps ΔYonY) and for Formula: enter =('2013' -'2012' )/'2012'.
  5. Set font colour of bottom right-hand cell to match fill.

SO19491492 example

Upvotes: 3

Related Questions