PowerPivot in Excel 2013 makes it easy to perform sophisticated modeling with the data in your Excel pivot tables. To open the PowerPivot for Excel window, you click the Manage button in the Data Model group on the PowerPivot tab shown or press Alt+BM.
If your workbook already contains a pivot table that uses a Data Model created with external data already imported in the worksheet when you select the Manage button, Excel opens a PowerPivot window similar to the one shown.
This window contains tabs at the bottom for all the data tables that you imported for use in the pivot table. You can then review, filter, and sort the records in the data in these tables by selecting their respective tabs followed by the appropriate AutoFilter or Sort command button.
If you open the PowerPivot window before importing the external data and creating your pivot table in the current Excel workbook, the PowerPivot window is empty of everything except the Ribbon with its three tabs: Home, Design, and Advanced. You can then use the Get External Data button on the Home tab to import the data tables that you make your Data Model.
The options attached to the PowerPivot Get External Data button’s drop-down menu are quite similar to those found on the Get External Data button on the Excel Data tab:
From Database to import data tables from a Microsoft SQL Server, Microsoft Access database, or from a database on a SQL Server Analysis cube to which you have access
From Data Service to import data tables from a database located on the Windows Azure Marketplace or available via an OData (Open Data) Feed to which you have access
From Other Sources to open the Table Import Wizard that enables you to import data tables from databases saved in a wide variety of popular database file formats, including Oracle, Teradata, Sybase, Informx, and IBM DB2, as well as data saved in flat files, such as another Excel workbook file or even a text file
Existing Connections to import the data tables specified by a data query that you’ve already set up with an existing connection to an external data source
After you select the source of your external data using one of the options available from PowerPivot window’s Get External Data button, Excel opens a Table Import Wizard with options appropriate for defining the database file or server (or both) that contains the tables you want imported.
Be aware that, when creating a connection to import data from most external sources (except for other Excel workbooks and text files), you’re required to provide both a recognized username and password.
If you don’t have a username and password but know you have access to the database containing the data you want to use in your new pivot table, import the tables and create the pivot table in the Excel window using the Get External Data button’s drop-down menu found on the Data tab of its Ribbon and then open the PowerPivot window to use its features in doing your advanced data modeling.
You cannot import data tables from the Windows Azure Marketplace or using an OData data feed using the Get External Data command button in the PowerPivot window if Microsoft.NET Full Framework 4.0 or higher is not already installed on the device running Excel 2013.
If you don’t want to or can’t install this very large library of software code describing network communications on your device, you must import the data for your pivot tables from these two sources in the Excel program window, using the appropriate options on its Get External Data button’s drop-down menu found on the Data tab of its Ribbon.