Create a Dynamic CrossTab Range
This tutorial is going to teach you how to create a dynamic crosstab that pulls a vertical dataset into a crosstab with two dimensions you choose. To see how it is done watch the video below or jump straight to the summary step guide below. The associated file from the video is here.
7-Step Summary Guide
- Get your dataset in place and identify the two variables you want to get a cross tab for
- Draw up the two dimensions of your crosstab with one variable in rows and the other in columns
- Go back to your dataset and create a key for each record/row using the CONCATENATE function or ‘&’ operator (see video). This will be the KEY that your cross tab vectors will use to get their values
- If you are going to use VLOOKUP to pull back the associated values into your crosstab make sure the value you want to pull in is to the right of your KEY column
- Go to your cross tab and in the values area write a VLOOKUP formula. Be sure to lock the column of your column dimension and the row part of your row dimension, and you will need to fully lock the reference table argument in your formula (e.g. =VLOOKUP($M5&N$4,$H$1:$I$51,2,FALSE)). If you’re doing this you might want to also wrap an IFERROR function around the VLOOKUP formula (see video!)
- If your dataset has only unique KEY records (i.e. they are not duplicated in the KEY column you created) you are done… if you want to sum or count and there are duplicate instances of your key, use SUMIF or COUNTIF functions (or array functions if you know how–see this article for more on array formulas)
- To use a sumif, just replace your VLOOKUP formula with a SUMIF formula in the same form. My example has this one: =SUMIF($H$2:$H$51,$M5&N$4,$I$2:$I$51)
When to Use this Technique
This is a truly dynamic technique ubiquitous among Excel modelers that I have seen in my career. It is also useful in my expert view. If you are doing quick analysis always go straight for the pivot table to do this kind of thing. If you want to create something that updates dynamically without using a pivot table–and you know the extent of the dimension ranges in advance (by this, I mean you know all the Weeks and AcTypes, or whatever else you are examining, in advance) then use this type of thing.
These can be great for scorecards or other data tracking tools that auto update using Excel formulas.
Got great ideas for applying this? Let me know in the comments below. Like this application?–then subscribe to my newsletter!