Microsoft Excel 2013 features deeper integration of Power Pivot functionality. For example, you can now import and relate large amounts of data from multiple sources right in Excel by using a new built-in data model, without having to go to the Power Pivot window. Power Pivot provides an environment for more advanced data modeling and connects to the same data model. Read about everything else you can do in Power Pivot.
In this article
Basic data model functionality now built into Excel
In Excel 2010, you installed the Power Pivot for Excel 2010 add-in so that you could import and relate large amounts of data from multiple sources.
In Excel 2013, much of that functionality, including the infrastructure that supports it, is built directly into the Data Model (Data Model: A collection of tables and their relationships that reflects the real-world relationships between business functions and processes, for example, how Products relates to Inventory and Sales.) in Excel. Without installing a separate add-in, you can now:
- Import millions of rows from multiple data sources.
- Create relationships between data from different sources, and between multiple tables in a PivotTable.
- Create implicit calculated fields (previously called ‘measures’) – calculations created automatically when you add a numeric field to the Values drop zone of the Field List.
- Manage data connections.
Thus all Excel users can now build data models, which they can use as the basis for PivotTables, PivotCharts, and Power View reports. Excel automatically loads data into the in-memory analytics engine, which used to be available only with the Power Pivot add-in. The data model in Excel has other advantages:
- Processing data is fast.
- Data is highly compressed, creating a file of manageable size.
- Data is saved inside the Excel workbook, so it’s portable.
The Power Pivot add-in is available, too, and provides more advanced modeling capabilities for more experienced data modelers.
Power Pivot in Microsoft Excel 2013 for more advanced data modeling
The Power Pivot in Microsoft Excel 2013 add-in, which connects to the same data model, provides a richer modeling environment that allows more experienced users to enhance their models.
For example, with the Power Pivot in Microsoft Excel 2013 add-in you can:
- Filter data when importing. You can import data in both Excel and Power Pivot, but when importing data in Power Pivot, you can filter out unnecessary data to import just a subset.
- Rename tables and columns as you import data in Power Pivot.
- Manage the model and create relationships using drag and drop in the Diagram View.
- Apply formatting (to be used in Power View and PivotTable reports).
- Define your own calculated fields to use throughout a workbook.
- Define key performance indicators (KPIs) to use in PivotTables.
- Create user-defined hierarchies to use throughout a workbook.
- Define perspectives.
- Author your own calculations by writing advanced formulas that use the Data Analysis Expressions (DAX) expression language.
- Use other more advanced data and modeling operations.
These features are not available in Excel. To use these features, you need to enable the Power Pivot in Microsoft Excel 2013 add-in. The add-in is included in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013. See Start Power Pivot in Microsoft Excel 2013 for more information.
If you’ve used Power Pivot in the past, a few features available in earlier releases of Power Pivot aren’t available in Power Pivot in Microsoft Excel 2013. See the table in the “What's new in the Field List” section below for details. See Upgrade Power Pivot Data Models to Excel 2013 if you have existing Power Pivot workbooks that you plan to edit and use in Excel 2013.
What’s in the new Field List
Excel and Power Pivot now have a single, uniform Field List. Some features of the Field List have changed from the previous version of Power Pivot (v. 2, which shipped with SQL Server 2012) to this version. For example, you can now drag fields directly into the Values drop zone (to create implicit measures) and add fields to slicers directly from the Field List. Other features have moved and are available from other locations in the Power Pivot window. Others aren’t available in Excel 2013.
|Creating or formatting calculated fields in the Field List
You no longer create calculated fields by right-clicking a table in the Field List. You create, edit, and delete them in a PivotTable by clicking Calculated Fields in the Power Pivot ribbon in Excel.
You can also create calculated fields in the Calculation Area in the Power Pivot window (no change from the previous version).
|Support for implicit calculated fields
With Excel 2010 you needed the Power Pivot add-in to create implicit calculated fields—a calculated field you create just by dragging and dropping numeric columns to the Values area of the Power Pivot Field List.
With Excel 2013 you can create implicit calculated fields in the Field List for PivotTables you create in Power Pivot and Excel.
As with v.2 of the Power Pivot add-in, in Excel 2013 you define perspectives (perspective: A subset of the objects in a data model - tables, columns, calculated fields, and KPIs - that track different sets of data. Typically defined for a particular business scenario, they make it easier to navigate large datasets.) from Advanced tab.
In v. 2 of Power Pivot, after creating a perspective, you view that perspective of your model in Diagram View, or filter the Power Pivot Field List by that perspective. Filtering the Field List is no longer possible.
In Power Pivot in Microsoft Excel 2013, you choose the perspective to use in the Advanced tab, which lets you view a given perspective of your entire model in the Data View grid and in Diagram View in the Power Pivot window. This allows you to work more efficiently with a subset of tables in a large model.
|Creating key performance indicators (KPIs)
In the previous version of Power Pivot you could only create a KPI by selecting an existing calculated field in the Field List.
Creating KPIs is no longer tied to the Field List. You create, edit, and delete KPIs from the Power Pivot ribbon in Excel.
You also create KPIs in the Calculation Area of the Data View grid in the Power Pivot window (no change).
As in the Power Pivot add-in v. 2 you add descriptions for calculated fields, tables, and columns by right-clicking them in the Power Pivot window.
In Excel 2013, the descriptions are not visible as tooltips as you hover over the fields in the Field List, but they are visible in the Field List of a Power View sheet. After you add descriptions in Power Pivot, refresh the Power View sheet to see them.
|Searching metadata in the Field List
Search is not available in the Field List. Instead, we’ve added a metadata search option to the Power Pivot window. In both Data and Diagram Views click Find on the Home tab of the Power Pivot window to search for metadata.
Note that you’re not searching the data in individual cells, just the metadata of the table—for example, field names.
|Adding slicers to PivotTables
||Slicers are available by right-clicking any field in the Field List.
|Automatic slicer layout
||Lay out slicers in groups either horizontally or vertically by clicking the Slicer Alignment options in the Power Pivot ribbon in Excel.
|Automatic relationship detection
||Not available in Power Pivot in Microsoft Excel 2013.
Data categorization and suggestions from DataMarket
When you add tables to Power Pivot, Power Pivot can automatically categorize each column. For example, Power Pivot would categorize a column of city names as City, and a list of company names as Company. You can modify the automatic categorizations, categorize your own data, or turn the feature off altogether.
Categorizing your data has two main benefits. With your columns categorized:
- Microsoft Azure Marketplace can suggest data from the DataMarket that may be related to your data. For example, it might suggest census or weather data. The suggested data could be from a free data provider such as the United Nations, or from a company that charges for data. You choose whether to accept the suggestions. Get suggestions of related data from Microsoft Azure Marketplace
- Power View can provide visualizations that are appropriate for your data. For example, if your data has URL addresses, Power View can render the URLs as hyperlinks.
Support for more languages
Power Pivot is localized into all 40 Office languages. Power Pivot now supports right-to-left languages Arabic and Hebrew.
Support for larger file sizes in 64-bit Excel
Previous releases limited the size of a workbook to 2 gigabyte (GB) on disk and 4 GB in memory. This limitation ensured that Excel workbooks containing Power Pivot data would always fit under the maximum file upload size set by SharePoint.
Now that the data model is part of Excel, the maximum file size limitation is gone. If you’re using 64-bit Excel, workbook size is limited only by the availability of disk and memory resources on your computer.
Limits set by other platforms still apply. SharePoint Server 2013 still has a 2 GB maximum file upload size. Similarly, if you’re sharing a workbook in SharePoint Online or Office Online, you might be directed to open the workbook in Excel if the file is too big to open in a browser. More about file size limits in Data Model specification and limits.
Advanced Mode is the new default view
In Excel 2013, when you click Manage to go to the Power Pivot window, by default the window includes the Advanced tab.
- To hide the Advanced tab, click the File button to the left of the Home tab > Switch to Normal Mode.
Top of Page