In the year 2000, I began to use Microsoft Query to pull data from corporate client’s database tables, using their raw data to create PivotTable reports. Each table required a different query, each linked by using VLOOKUP formulas. And of course the data had to be restricted to Excel’s 65,536 rows.
Soon I was having clients write queries to get around using VLOOKUP formulas and started pulling data into Microsoft Access to get around the row limitations. But to get data into Microsoft Excel, the primary interface was Microsoft Query. I don’t know the history of this program but it served me well, even though it’s old, clunky, and not supported very well.
PowerPivot is a program that works with Excel 2010 to do some amazing things.
- Import hundreds of millions of rows of data
- Import data from multiple sources
- Build relationships from imported data
- Visualize the data with PivotTables and PivotCharts
If any of this sounds remotely interesting you can check it out for yourself on the PowerPivot website. Better yet, check out the video page where you can see an Overview (there’s three of them, the first one is enough to get the idea), check out how to download and install the PowerPivot program, and see the Importing Data video series.
Here’s a screen shot from the first video in that series.
And if that isn’t enough, you can even check out a demo.
Pretty awesome stuff.