Excel: Five Reasons to Use PowerPivot

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: What is PowerPivot?

Strategy: PowerPivot is an amazing add-in for Excel. It is from Microsoft, but not from the Excel team. PowerPivot is from the SQL Server Analysis Services team. It is the greatest thing to hit Excel in 20 years.

Here are some reasons why you might consider using PowerPivot:

  1. Handle incredibly large data sets. I've seen 100 million rows, stored in the Excel workbook, without a problem.
  2. Import data from anywhere. Mash up data from Excel with data from Access, with data from Oracle.
  3. Create a pivot table from multiple worksheets without using VLOOKUP.
  4. New time-intelligence functions, including functions to handle fiscal years.
  5. New DAX functions for use in the grid and to replace calculated fields in the pivot table itself.