Rekha viewed ‘Learn Excel from MrExcel‘ Episode #655 where I used Group Field in a Pivot Table to create a year-over-year report. But Rekha needs current month from last year, current month from this year, variance, then YTD through current month from last year, YTD this year, variance. Urgently. In Excel 2010.
While this would be a perfect report for Power Pivot, Rekha hasn’t come up the Power Pivot learning curve yet. And we need the report urgently.
I am calling this an impossible pivot table because once you group daily dates to months and years, you are not allowed to add a calculated item inside the pivot table to show a variance. Many ideas flashed through my mind: Run the data through PowerPivot and used Named Sets. Make two pivot tables side by side, one showing monthly and one showing YTD. Abandon pivot tables and use SUMIFS instead. I ended up using the GetPivotData technique, although there are probably dozens of ways to approach this impossible problem.
…This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill’s book!
And if you’re looking to upgrade to Excel 2013, the check out:
“Microsoft 2013 InDepth” – by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013.
“The Learn Excel from MrExcel Podcast Series”
Visit us: MrExcel.com for all of your Microsoft Excel Needs!