Where Have You Been All MY Life?August 25, 2010
I just recieved Bill Jelen’s (MrExcel) book “PowerPivot for the Data Analyst: Microsoft Excel 2010” and am enjoying reading it. I really like the way Bill writes and his honesty is refreshing (to say the least). Thanx Bill.
I have only read half of the book (I always read books like this cover to cover even if in the end I find out I knew everything that was there – but in this case of course I don’t ).. but I have found one of the most important Excel tricks I have ever seen in there - and one that will be critical in getting Excel Devs to grasp a key database concept that many haven’t grasped yet.
On Page 91 there is a section called “Unwinding a Lookup Table”. In this section Bill refers to a “trick” he learned from Mike Alexander – not the NASCAR Driver but this guy: http://datapigtechnologies.com/
(is he really known as “The Data Pig” ? Cool !!)
For ever it seems I have been converting spreadsheet Crosstabs back to proper Relational Table format the hard way either by hand or by VBA “quick and dirty” code. This is not only a pain, but is critical to converting Classic Spreadsheets into a format that can take advantage of Pivot Tables or to move to a Relational Store like SQL Server or Access.
The technique Mike “discovered,” and that Bill loves as well, basically takes a Crosstab like this:
and converts it to:
in seconds – literally !!!
I don’t particularly want to steal their thunder here, but it involves using a secret Hot-Key combination in Excel 2010 (or any previous version back to probably Excel 5), “Alt+D+P”, that brings up the old Pivot Table and Chart Wizard (not available officially in Excel 2010 for some reason). From there you select “Multiple Consolidation Ranges” then choose “I Will Create Page Fields” (for some reason ??). Then you simple select the source range, add it to the list and Go !! Then you click on the “Grand Total of Grand Totals in the bottom right of thre resulting table and “Presto !”, there’s you nice little table!
Bill immediately got this when he saw it and he sees the importance of the technique enough to give it a section in his PowerPivot book. I on the other hand want to “YELL THIS TO THE SKIES!!”….. This will allow millions of crappy spreadsheets (sorry about that ) to properly designed Data-Centric ones that can then use Pivot Tables and PowerPivot to get the real value of all the Data features in Excel.
Buy Bill’s book !! Go to Mike’s Website !! Get this technique down and your productivity will sky-rocket and your business opportunities will too.
Well done Bill and Mike !!