Friday, August 24, 2007

Faster Pivot Tables with Lots of Data

Often you get huge sets of data to analyze, or "slice" as you say when you want to sound cool to the client. You duly put it into excel and pivot table it, only to have excel turn into a dog. There's a better way - put the data into Access.

Lots of Consultants are afraid of Microsoft Access, and for good reason - it's a piece of crap for lots of reasons that I may detail later. It's a pin hammer when you need to bash in 100K nails quickly into a pretty-looking house.

Never the less, it can help you in this case. First, get the data into Access:

  • Open Access, create a new database
  • File->Get External Data->Import (Alt+F,G,I)
  • Import the data into a table

Now, get ready for speedy pivot table manipulation:

  • Open Excel and create a new pivot table (Data->Pivot Table, or Alt+D,P)
  • Choose "External Data Source" then next, then "Get Data"
  • A file menu pops up. Click "MS Access Databse", then find your database.
  • Highlight the table where the data is, and click the ">" button to move all the fields over to the right side
  • Click next again and again and again until you're finished.
  • Voila! You're ready to slice and dice that data to your heart's content.

No comments:

Friday, August 24, 2007