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:
| Jan-2010 | Feb-2010 | Mar-2010 | Apr-2010 | May-2010 | Jun-2010 | |
| A | 2 | 11 | 1 | 1 | 1 | 1 |
| B | 12 | 2 | 1 | 2 | 2 | |
| C | 12 | 1 | 2 | 1 | 21 | 2 |
| D | 1 | 2 | 1 | 12 | 2 | 1 |
| E | 1 | 21 | 2 | 12 | 1 | 2 |
and converts it to:
| Row | Column | Value |
| A | 1/1/2010 | 2 |
| A | 2/1/2010 | 11 |
| A | 3/1/2010 | 1 |
| A | 4/1/2010 | 1 |
| A | 5/1/2010 | 1 |
| A | 6/1/2010 | 1 |
| B | 1/1/2010 | |
| B | 2/1/2010 | 12 |
| B | 3/1/2010 | 2 |
| B | 4/1/2010 | 1 |
| B | 5/1/2010 | 2 |
| B | 6/1/2010 | 2 |
| C | 1/1/2010 | 12 |
| C | 2/1/2010 | 1 |
| C | 3/1/2010 | 2 |
| C | 4/1/2010 | 1 |
| C | 5/1/2010 | 21 |
| C | 6/1/2010 | 2 |
| D | 1/1/2010 | 1 |
| D | 2/1/2010 | 2 |
| D | 3/1/2010 | 1 |
| D | 4/1/2010 | 12 |
| D | 5/1/2010 | 2 |
| D | 6/1/2010 | 1 |
| E | 1/1/2010 | 1 |
| E | 2/1/2010 | 21 |
| E | 3/1/2010 | 2 |
| E | 4/1/2010 | 12 |
| E | 5/1/2010 | 1 |
| E | 6/1/2010 | 2 |
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 !!
Dick
Dick Moffat's Spreadsheet and BI Blog 
I..I..I..am..speechless!
I hope you’re not being sarcastic (??)
Not a chance, sorry it might’ve come across so.
Like you I am genuinely amazed by this. It could save so much time!
No – it didn’t come across that way but it could be interpretted that way :-).. Thanx for clearing that up.
It’s nice to know somebody else gets excited about s**t like this ….. and this one is BIG S**T in my books. The fact that at least two of us didn’t know about it I can assume most Excel Devs also don’t. That’s a REAL problem.
Maybe I’ll blog what I think are the most important unheralded features and “tricks” in Excel (??) and everyone else can chime in. But in the end MS has to get this kind of stuff out to the world somehow I’m afraid (cracked record sound here).
Dick
Sweet – very useful and unknown to me until now – thanks
Yes, that’s a great way to normalize data, and it’s been posted the Excel newsgroups since at least 2003. I recommend it in my pivot table books (2006 and 2007), as a quick way to prepare data for use in a pivot table, and John Walkenbach has a page that describes the steps:
http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/
I guess I should read more 🙂 …. Glad to hear it has been out there for a while.
Thanx
While I can see some reason to like this, I have to question how useful this would be in spreadsheets.
Are the rest of you RECEIVING a lot of data in crosstab form, whether from other people or other systems? If so, then you’d be doing a lot of these conversions, no? And if you’re doing A LOT OF THESE CONVERSIONS wouldn’t a macro be more efficient than this interactive technique? I’m just not seeing this as the biggest thing since the zipper fly.
But let’s say you convert a crosstab like this to a normalized table. Compare how you’d work with the crosstab vs the normalized table using ONLY formulas. Select the C – March2010 value:
=INDEX(crosstab,MATCH(“C”,INDEX(crosstab,0,1),0),
MATCH(DATE(2010,3,1),INDEX(crosstab,1,0),0))
vs
=INDEX(normtbl,MATCH(“C”&CHAR(127)&DATE(2010,3,1),
INDEX(normtbl,0,1)&CHAR(127)&INDEX(normtbl,0,2),0)
[the CHAR(127) isn’t strictly necessary here, but experience with composite keys in spreadsheets has led me to use explicit field separators in all cases]. The first is a normal formula. The second has to be an array formula.
If the SQL.REQUEST function were available (and better implemented), using the normalized table would be simpler.
OTOH, if you were using code or query objects against such normalized tables, it’d beg the question why use Excel at all? Because it’s a handy (if vastly overstuffed) grid control?
Actually yes – every week I get a new one.
My crusade is to move Excel to a new level by accepting that Relational or OLAP data are the way data MUST be stored. Then all of Excel’s capabilities can be put to use for the “slicing and dicing” of data using Pivot Tables, PowerPivot, CUBE functions, Tables, and all the other capabilities of the product.
If we don’t accept that fact then IMHO Excel will be marginalized and not get to take its rightful place in Corporate Information Systems going forward. It’s time for a paradym shift in the use of Excel.
This is not to say that what you’re saying isn’t true if you choose to go that design route. I’m just saying it’s time to redesign new (and old) spreadsheets.
Thanx for your thoughts as always Harlan.
Dick
Specifically with respect to crosstabs, my experience with Lotus 123 and (at least from Release 3 on) its @XINDEX function made working with crosstab data simpler than corresponding relational tables.
Consider: if you want all March data in an array,
INDEX(crosstab,0,MATCH(DATE(2010,3,1),
INDEX(crosstab,1,0),0))
or if you want all row B data,
INDEX(crosstab,MATCH(“B”,INDEX(crosstab,
0,1),0),0)
OTOH, if you need this kind of data in an array, it’s more difficult to produce arrays without unnecessary entries from normalized tables, at least by using only worksheet formulas.
If you think of crosstabs as INTERMEDIATE data structures, they’re not so bad. After all, OLAP may use data stored in relational tables, but its own INTERNAL base data structure is the hypercube, which is essentially an N-dimensional crosstab. If that data structure is useful for OLAP, why couldn’t it be useful in spreadsheets?
Back to the other point: if one converts A LOT of crosstabs, wouldn’t a macro make a lot more sense than ANY interactive approach?
Don’t get me wrong. I’d like to see more organization in spreadsheets. I’ve asked in the Excel team blog and newsgroups why not add Tablesheet as another type of sheet object, where Tablesheets would contain only a structured Table, and its contents could ONLY be accessed using structred referencing, never using range references. But I’d really, Really, REALLY like a BUILT-IN and ROBUST SQL.REQUEST function.
“Specifically with respect to crosstabs, my experience with Lotus 123 and (at least from Release 3 on)”
Yeah … there were a lot of cool things in Lotus especially from Rel 3 on. I started with 1A by the way 😦 ) . In fact I still have the 5 1/4 in. disk with the Grey top with Red text with a major Thumb-Print from taking it in and out of the drive.
But Excel added some awesome stuff in Match(), INDEX(),OFFSET(), etc. that more than makes up for that IMHO.
I am a BIG fan of using the cool functionality of Excel formulas. I even used an Array formula in a spreadsheet just last week to emulate a SUMIFS() in an 2003 file. I love that stuff.
I get your point that maybe the perfect world is a bit of both, where you bring in Relational Data into tables in Excel and then “Party” on that data with the Indexes() and MATCH()’s etc. to produce your results or reports. Good point. Again using the right tool for the right job.
Thanx
Dick
Pingback: Self Service Is Cheap? - Excel Do, Dynamic Does
Yep, that trick’s sweet. And fast…much faster than dicking about with arrays in VBA, I’ve found. And consequently you CAN automate it with VBA…provided your unwound data is no longer than the grid.
Or you can of course write a SQL query that unwinds with a whole bunch of UNION ALLs. Provided you don’t exceed the limit of 50 UNION ALLs that the Excel/Access brand of SQL suffers from.
(Or if you do, you can create a UNION of UNION ALLs as per comments at http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets-7.html in order to get around this.)
OMG. I can’t believe I didn’t know about this…That is one sweet trick. I bow to the excel gurus!
BTW …. This trick has been more than superseded by the “UnPivot” feature in Power Query .. It is AWESOME !!
Dick
Pingback: Excel Novice |Printing made easy, Excel Adept | Quickly transpose | The Excel Expert