Beyond Excel: VBA and Database Manipulation

Jul 12 2014   8:45PM GMT

Need to make your data more PivotTable friendly?

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Learn to convert cross tab data into tabular data to which we can apply PivotTables,
Filters, and Sorts.

A Common Problem
The bottom table at right is an example Cross tab table.  It looks great and is easy to add data to when small.  But even when small, try summingTerritory sales from it.

If we are good with formulas, we rate this a trivial task until we consider, “what happens when we add a month?”  Suddenly traditional formulas start to get a little messy. And what happens when we finish the year?  Will we extend the table and re-label all columns to include year and month?  Or will we create a new worksheet for the next year and start this table all over again?  Hmmm… what would a total formula across multiple worksheets look like then?

Again, those of us with formula skills know this is possible.  Good for us.  But why go through all that effort when PivotTables sum data without formulas? No matter how quick we are with formulas, we can create PivotTables over tabular data in a fraction of the time with superior results. PivotTables provide slicers, drilldown, PivotCharts and a whole lot more.

The benefits of well-structured data far outweigh the aesthetics of cross tab formats.  That does not mean we have to abandon aesthetics when aesthetics count because we can present tabular data in PivotTables easily.

The UnPivot add-in makes converting cross tab data to PivotTable friendly, Auto-Filter friendly, Dynamic Form friendly, formula friendly, database friendly tabular formats quick and effortless.  Plus, UnPivot provides a bonus feature that allows us to use cross tabs as input forms and append data entered into these forms to our master table so we can perform XL miracles over tabular data AND have the ease of entry associated with cross tab data..

Here’s how to get UnPivot.  And yes, it’s free and the code totally unprotected so those new to VBA can learn from it or modify it as needed. Download the PDF at:
Discuss this post or other BXL topics at:

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: