Power Pivot: Powerful data analysis and data modeling in Excel

Are you struggling with importing massive amounts of data from multiple sources to create the kind of powerful reports you need? Do you need to extend your power view reports? Do you want to become a Power Excel user that does your own self service BI?

Well, PowerPivot is the tool you need, that extends Excel’s data model.

What is PowerPivot?

PowerPivot is a free extension for Excel 2010 and 2013. PowerPivot was introduced in Excel 2010 as an add-in that you could download, that gave you the ability to create a data model. Now in Excel 2013 you can create a simple data model with two or more tables with relationships, pivot tables, and power view reports.

So PowerPivot, gives you extended capabilities. You can create calculated columns, calculated fields (also known as measures), and hierarchies that group similar fields. With PowerPivot you are getting a lot more “power” with the ability to aggregate your data.

Who uses PowerPivot?

Usually Power Excel users are using PowerPivot, but even beginner pivot table creators can benefit from using Power Pivot with Excel.

What are some of the benefits of using Power Pivot

• Extend your pivot table, power view reports, and other service reports.
• If you have 2 or more tables and you need to relate that data in a specific way, PowerPivot can help you do that.
• PowerPivot lets you handle massive amounts of data in Excel.
• Add any formula to a pivot and it will adjust
• With PowerPivot you can turn workbooks into self maintaining web applications.

3 Examples Of How PowerPivot Extends Excel:

1. Unlimited rows of data: If you open Excel and scroll all the way to the bottom, you will see over 1 millions available rows for data. Once you open up PowerPivot, that extends your ability to have unlimited rows for data. This is true, but there are limitations depending on the version on Microsoft Excel you are running. The amount of rows is also based on whether you are publishing your spreadsheet to SharePoint 2010.
2. Combine data from multiple sources: For most non profit organizations and enterprise businesses, this is one of the most powerful features in PowerPivot for Excel. Excel has always been able to handle multiple sources of data, like SQL Server, XML, Microsoft Access, and also web based data. The problem starts when you try to create relationships between the data sources. PowerPivot on the other hand was built to handle these different sources of data by adding relationships between them and having them run seamlessly.
3. Advanced formulas in pivots: For many years there have been features in Excel that let you add formulas (calculated fields) to your pivots. With PowerPivot for Excel, you now have full control over whatever field you want. The field in question doesn’t even have to be in use on the current pivot. You can do running sums, moving averages, formulas that change based on filter and formulas that do different things at different levels of the pivot.

Additional PowerPivot Resources:

• Download PowerPivot
• Starting PowerPivot add-in
• Webinar: Understanding PowerPivot in Excel 2013