I’ve got this pivot table, and for column headers I’ve got different weeks, like 821/11-8/27/11. Each week the pivot table gets updated and a new column gets added, and I’m wondering if there’s an easy way to get a variance column between each of them showing the variance between any two weeks. Since I’m not trying to find the variance between two different fields, I haven’t had any luck using the Pivot Table formulas—each week is not a separate field in the data set that is used for the pivot. There’s just a field called ‘week’, and the values of that field form the column headers in the table. Is there any way to do this easily inside the pivot table?
I’ve used pivot tables quite a bit and I don’t believe there is a way to do what you want as part of the pivot table. (However, I have learned from experience not to say that it’s impossible.)
My approach to this would be (and in fact has been) to set up something outside of the pivot table to calculate the variances.
I have had good luck getting questions answered here
Free registration, and they allow attachments. A spreadsheet is worth a thousand words. (I post there as 6StringJazzer.)
It’s not clear to me what you’re trying to do. How about a sample of what the non-pivoted data looks like and what you’re trying to accomplish?
Concur.
Concur with bells on.
Bookmarked. 
The pivot table has a name in the first column, and then columns b-c-d etc. are the ‘week’ column from the data. Each different value in that ‘week’ field is a column header, so from left to right we see:
NAME WEEK1 | WEEK 2 | WEEK 3 |
FAKE NAME 4 | 32 | 9 |
what I need is to add another column between week1 and week 2 that shows the varaince between the 4 and the 32, but I need it to happen for each week.
Are the names pretty static?
If so then I’d say you could copy then down below your pivot table, then put in the formula B2-B1 in the first cell, copy it across and down. You’d either have to keep copying the formula over every week to get the new week or just copy it out 52 times and have it read zero until it fills in.
That’s what I can think of off the top of my head, not having tried it I can’t swear I’m not missing something here.
I just emailed you an example of what I suggested.
You are showing us what the pivot table looks like. If the underlying data is:
Name, numeric_data_like_sales, date_that_represents_week
You can put in the field settings to show “difference from”, with date as the “base field” and previous as the “base item”
Put your data field in the pivot table as a row twice, define it once to just sum and once to calculate the difference of the sum.
If your underlying data is close, you probably can put in a proxy value (a column that calculates a date from the week range for example).
I appreciate that, but unfortunately that won’t work. The Boss requesting this wants the variances inside the actual pivot table.
Well, you can never insert additional columns into a pivot table that’s already produced. You have to create the variance figures outside the pivot table. I agree with tim-n-va – show what your original data look like, not the pivoted result. The solution may be to not use a pivot table.