# Pivot Table problem in Excel **URL:** \ **Category:** Factual Questions\ **Created:** [October 5, 2011, 2:45pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686 "2011-10-05T14:45:48Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 5, 2011, 2:45pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/1 "2011-10-05T14:45:48Z")
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? ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [October 5, 2011, 3:24pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/2 "2011-10-05T15:24:52Z")
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 > **[Excel Help Forum](https://www.excelforum.com/)** > > Excel Help and Support from Excel Experts( MVPs). Excel2003,Excel 2007, Excel 2010 What's New in Excel and Excel Tips and Tutorials. Free registration, and they allow attachments. A spreadsheet is worth a thousand words. (I post there as 6StringJazzer.) ---
**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\ **Post date:** [October 5, 2011, 3:37pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/3 "2011-10-05T15:37:13Z")
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? ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [October 5, 2011, 3:56pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/4 "2011-10-05T15:56:03Z")
> [@CookingWithGas](#): > > I don’t believe there is a way to do what you want as part of the pivot table. Concur. > [@](#): > > (However, I have learned from experience not to say that it’s impossible.) Concur with bells on. > [@](#): > > [http://www.excelforum.com/](http://www.excelforum.com/) Bookmarked. 🙂 ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 5, 2011, 4:09pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/5 "2011-10-05T16:09:25Z")
> [@nivlac](#): > > 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? 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. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [October 5, 2011, 4:28pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/6 "2011-10-05T16:28:56Z")
> [@batsto](#): > > 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. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [October 5, 2011, 4:36pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/7 "2011-10-05T16:36:55Z")
I just emailed you an example of what I suggested. ---
**Author:** ![tim-n-va](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tim-n-va/32/3001_2.png) [@tim-n-va](https://boards.straightdope.com/u/tim-n-va)\ **Post date:** [October 5, 2011, 4:44pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/8 "2011-10-05T16:44:02Z")
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). ---
**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\ **Post date:** [October 5, 2011, 5:09pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/9 "2011-10-05T17:09:03Z")
> [@CookingWithGas](#): > > I just emailed you an example of what I suggested. I appreciate that, but unfortunately that won’t work. The Boss requesting this wants the variances inside the actual pivot table. ---
**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\ **Post date:** [October 5, 2011, 7:52pm UTC](https://boards.straightdope.com/t/pivot-table-problem-in-excel/598686/10 "2011-10-05T19:52:56Z")
> [@batsto](#): > > 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.