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.
Thomas
You are fantastic.
thank you
Ouahh, Thomas! So many usefull things on your page!! Thanks!
Great site! Thanks!
You Rock! Thanks!
Yup! This was awesome!
Okay, I’m the dumb one; I’ve installed the add-in, but it’s not in my add-ins ribbon. How do I run it?
Thanks a lot… it’s very helpful for production planner as well:-)
This is an awesome add-in, thank you so much!
This was a great help to me– thanks.
[…] I have built an add-in that will let you do that, and that makes it easy to adapt to different situations. Check it out here: https://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/ […]
This is fantastic! I have used it to create a TB from a QuickBooks P&L by class report, which has enabled me to automate production of month end accounts. Really super – thank so much!
You are AWESOME! Thank you!
awesome! thanks
Excellent. Thanks a lot for making this available
I would believe it’s fantastic: it is exactly wht I am looking for.
But how may I get this add-in?
The link is here: Table2DB
Just downloaded it and tried it – looks terrific. Thanks a lot!
Hello, I don’t see the download. Where do I find it?
Hi Jaslyn,
The download is after the second screenshot. Here is the link: https://tduhameau.files.wordpress.com/2012/09/table2db.xla
Thank u. The tool is amazing.
Thank you so much for this. This is a LIFESAVER!!!!!!
This is a real time saver, thanks!
Thank you, This worked Super!
Seriously you are awesome for writing that!
Great stuff! Thx a lot!
You will go to heaven!