Too many times in Excel, you need to analyze and exploit data that someone gave you. Most of the times, that involves a pretty table with nice formatting that you can’t use for anything except it’s original purpose. Something like this, that could you easily reproduce in a pivot if you had the data formatted the right way to start with, rather than the other way around.
If like me, you’d rather get better analysis than formatting, and can’t really live without a pivot table, what you really want is this: a clean data table ready to be used in a pivot, i.e. one column per data type, no duplicates columns, no blanks, etc. Something like this:
You can do it in a couple easy steps once you’ve installed my unpivot add-in, Table 2 DB. Download it here: Table 2 DB:
- Select the first cell of your detail data and run the add-in from your add-ins ribbon.
- Validate the parameters on the first screen
- Validate the parameters on the second screen:
- Do a final cleanup on the data: adjust formatting for dates, add titles, etc.
- I’m ready for my pivot, Mr. DeMille.
Try it, you might like it.