To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Kelly W. wins an autographed copy of PowerPivot for the Data Analyst from CFO and MrExcel for submitting this week’s question: “I am utilizing Pivot Tables to analyze some large amounts of data on purchases. Each row of my pivot table is the ID # of a product so I can clearly see what products are purchased the most frequently and for the most dollars. I also have two columns for ‘Max of Unit Price’ and ‘Min of Unit Price.’
I would like to add a third column ‘Price Range’ that is simply the difference of the two columns already in the Pivot Table. It appears the calculated field option will not work. Every row ends up with a value of $0. I have searched the Internet to find a solution. Unfortunately, while I have found others with the same question I have yet to find a resolution. Any help would be greatly appreciated!”
I love pivot tables, but the calculated field feature leaves a lot to be desired. In the situation described by Kelly, the calculation engine goes to each row in the database, calculates MIN(Price) for that row and MAX(Price) for that row and calculates a difference. Of course, this is useless, since there is only one price on each row in the original database, and that calculation works out to zero for every row. You can show the Sum, Min, Max of a bunch of zeroes and the answer will still be zero.
PowerPivot to the Rescue
With the release of Office 2010, the SQL Server Analysis Services team at Microsoft released an amazing free tool for Excel called PowerPivot. Anyone with Excel 2010 can download the Excel client version of PowerPivot from www.PowerPivot.com. As I wrote in PowerPivot for the Excel Data Analyst there are the “Big Six” Reasons (not to be confused with the old Big Six accounting firms) why PowerPivot pivot tables run circles around regular pivot tables:
1. Create pivot tables from 100 million rows of data.
2. Mash up data from Sheet1 and Sheet2 without using VLOOKUP.
3. Import data from anywhere; text files, Oracle, Sybase, Teradata, SQL Server, Atom.
4. Create asymmetric pivot tables (think last year actuals vs. next year budget)
5. DAX measures run circles around calculated pivot fields.
6. New time intelligence functions handle fiscal years, parallel periods and more.