PowerPivot is an add-in for Microsoft Excel that enables you to import millions of rows of data from virtually any source including databases, data feeds, Reporting Services, and text files into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, easily create interact with views of data from data models based on PowerPivot workbooks published in a PowerPivot Gallery, or tabular models deployed to SQL Server Analysis Services (SSAS) instances. PowerPivot enables users to present and share insights with others in their organization through interactive presentations without requiring IT assistance.
During the course of this tutorial, we will use PowerPivot to:
- Import data from multiple sources.
- Create linked data.
- Create relationships between data from different sources.
- Rename columns, and create calculated columns.
- Create hierarchies.
- Create PivotTables and PivotCharts.
- Add Slicers.
- Create a measure and KPI.
- Create perspectives.
- Save the resulting Excel spreadsheet.
Start the Power Pivot in Microsoft Excel add-inPower Pivot in Microsoft Excel 2013 is an add-in you can use to perform powerful data analysis in Excel. The add-in is built into certain versions of Excel, but by default, it's not enabled. Here’s how you enable Power Pivot before using it for the first time.
- Go to File > Options > Add-Ins.
- In the Manage box, click COM Add-ins > Go.
- Check the Microsoft Office Power Pivot in Microsoft Excel 2013 box, and then click OK
Open the Power Pivot window
- Click Power Pivot.This is the tab where you work with Power Pivot.
- Click Manage.
- The Power Pivot window will open.
- The first step to getting data from a relational database is to create a connection. On the Home tab in Get External Data grouping.
- The From Database drop-down menu allows you to connect to SQL Server, Access, Analysis Services, or from another Power Pivot model.
- If you click on the From Other Sources button, you can see all the various data sources available to connect to.
If you need to connect a data sources not listed, you can install a driver from the database provider to connect to it. Also you can use the generic ODBC (Open Database Connectivity) driver to connect to it.