This question or rather comparison never seizes to amaze me, since it’s just like comparing Eric Clapton to a Ford Mustang. It just can’t be done since they are two totally different things altogether. And just like you can cruise around in your Ford Mustang (you lucky bastard) while listening to Eric Clapton, you can do a Pivot Table in Excel that is based on Data provided by or better yet prepared by Power Pivot. Let’s elaborate.

Pivot Table

Pivot Table is an Excel functionality that’s been around since 1993 or to put it differently since Excel 5. It is by far the easiest and most efficient way for a non SQL user to do a BI analysis. It is so efficient that its core hasn’t really changed that much since its early days. But lately the limitations of a regular pivot table (i.e. a Pivot table based on data in Excel) are becoming very clear. Excel itself is actually a big culprit since its own limitations of 1048576 Rows means you cannot analyze more than 1048576 rows of data. This may seem like a lot, but when you talk about Databases, it’s miniscule. And furthermore, doing your analysis on two data sources at the same time is virtually impossible. Therefor functions like Vlookup have gathered their following but in a big workbook (more than 300,000 rows of data) those can bring your work in Excel to a crawl. And this is where PowerPivot comes in…

PowerPivot

PowerPivot is an Excel AddIn that was first introduced in Excel 2010, and gives you a chance to import, merge and prepare data from more data sources at once. Removes virtually all limitations on a number of rows you can analyze. Many tables from many different sources (SQL, Azure, Oracle, Excel, Access,…) can be imported into PowerPivot and then you can relate all this data to one another. This means you can build a Data Model containing multiple data sets from multiple different sources and by connecting them gaining the ability to analyze them all in one Pivot Table. Yes, a plain old Pivot Table! It does offer a bit more functionalities because of a data source (the comparison of the two pivot tables is very well described here), but all in all you can only analyze this data through a Pivot Table in Excel.

So to summarize, PowerPivot is an Excel AddIn that enables you to prepare your data, merge your data from multiple sources and work with Big Data (hundreds of millions rows of data). But when it’s ready, this data will be analyzed by a Pivot Table in Excel.

Hope this clears matters and helps you understand the Clapton – Mustang analogy and takes you one step closer to eternal happiness 🙂