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 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 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 🙂
If some pictures are hard to view, you can get the PDF of the ...
We have a dish where I come from (Slovenia), called Minestrone. ...
One might think that analyzing data with a Pivot table is hard, ...
One of the most annoying things a Pivot Table does on refresh is ...
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Never ceases to amaze me
Very nice explanation. Thank you 🙂
Thankyou. I’ve been getting very confused, as you would think that being called Pivot it would be something to do with pivot tables. But your explanation has cleared up the problem.
I ‘ve been exporting Excel with PowerPivot to PowerBI and wondering why only the data model came across, not the Pivot table. This is why!
I’m glad this helped clear things up for you ????
How i can use a Power-pivot in Excel-2010?
please guide me at on my e-mail
This is a site that has all the information you need.
https://support.office.com/en-us/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045
This is a great article! Wondering now if you have a good comprehensive resource for learning SQL – beginning level. I understand I need to know more than just SQL commands. Could you direct me to such site? – Thank you!
I would start @ the Microsoft Virtual Academy (https://mva.microsoft.com/search/SearchResults.aspx#!q=SQL&index=2&lang=1033)
or EdX (https://www.edx.org/course?search_query=sql).
Those are all free courses (on EdX you only pay if you want to get a certificate, but as a learning resource most courses are free)
Later you can go to Pluralsight… It’s not free, but the courses are great.
Thanks Gasper, I found your blog very useful and it helped me better under the difference between Pivot and PowerPivot.
You are welcome!