The biggest reason why the question comes up, I think, is the longstanding symbiosis between Access and Excel – Access as data source, Excel as analysis tool .  Remember, Excel used to be limited to about 64,000 rows of data.  And many Excel pros learned to import large data sets into Access rather than Excel, manipulate and prep the data in Access, and THEN import from Access into Excel for PivotTables, charting, etc.

With Excel expanding from 64k to 1M rows in 2007, that tradition has already begun to fade, to an extent.  But even for data sets that fit into Excel, there is still a good reason that drives Excel pros into Access:  VLOOKUP is slow.

VLOOKUP = Excel Acting Like a Database, and Driving Folks to Access

Even for large data sets, arithmetic calculations in Excel can be blindingly fast.  After all, Excel is designed for that.  But VLOOKUP, and its more advanced cousin INDEX/MATCH, is not arithmetic.  It’s a search – “go find me a value that looks like X, and when you find it, return value Y from the same row.”

Even when you’re dealing with row counts merely in the thousands, that can get slow in Excel.  Because “search and retrieve” is what databases are designed for.  And Excel is not a database.  When it comes to finding values, Excel isn’t terribly more efficient than Word.  (A risky thing for me to say, I expect to be corrected in three…  two…  )

But since many data sets inherently “arrive” as multiple separate tables, you can’t avoid trying to splice them together, and that means VLOOKUP, or using a real database product.  I know the SQL snobs will say that Access doesn’t qualify, but Access IS a real db.

An Understandable Question

With that in mind, it’s easy to see why longtime Excel pros see their first PowerPivot demo, and come away asking this question.  At a high level, this is what they see:

The two biggest reasons that drove Excel pros into Access in the past are in fact alleviated with PowerPivot.  No 64K row limit in PowerPivot.  No 1M row limit either.  I commonly demo a 300M PowerPivot row workbook on my laptop!  You can load a lot more data into PowerPivot than you can into Access.

And VLOOKUP isn’t something you even need anymore in PowerPivot.  Got multiple tables?  Fine!  Leave them as separate tables, link them via relationships, and you are done.  That’s not even just a convenience – leaving them as separate tables is actually even just better, for many reasons.

All of that “go find me a match in another table” stuff is taken care of by PowerPivot.  Lightning fast in fact.

But whoa there, I put an asterisk on that statement above.  For good reason.

PowerPivot Goes Better with Databases!

Or more accurately, you will get even more out of PowerPivot if you have the cooperation of a database professional.

Why is that?  I can (and will) write many posts on that.  For now let’s keep it simple and just point out two reasons:  1) Databases are inherently a very good place to do data “shaping,” which is not something you can do at all in PowerPivot.   and 2) Databases are great places to perform complex row-wise and cross-row business calcs.  They centralize those calcs for re-use, often take the db pro 5 mins to do versus much longer for you, and result in faster and more compact workbooks than if you use calc columns.

I’m not saying you need a db pro to get amazing things out of PowerPivot.  But there’s another level even beyond amazing, and it opens up when you cooperate with a db pro.

Final Note:  Be Thankful PowerPivot Wasn’t Built By Office

I say this because it would have been questioned to death.  It’s hard to imagine, but as a product like PowerPivot is taking shape at Microsoft, no one is really sure how to describe it yet, or even what it’s going to turn out to truly be.  A hundred people in the Office org would have had the same question – are we cannibalizing the Access business, and there would have been as much time spent answering that as designing the actual product.

Now, in hindsight, no one in Office is worried about that.  Access always had a much bigger mission than carrying around data for Excel.  And Access’s current mission has evolved quite a bit from what it was even a few years ago.

But those inevitable nagging questions early on would have saddled PowerPivot with a number of “thou shalt not cross this line” concessions.  Concessions which ultimately were not needed, and that would have hurt the product.

Gives new meaning to the term “Office Politics.”