Important This feature isn’t available in Office on a Windows RT PC. Want to see what version of Office you're using?
Microsoft Excel 2013 features deeper integration of PowerPivot 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 PowerPivot window. PowerPivot provides an environment for more advanced data modeling and connects to the same data model. Read about everything else you can do in PowerPivot.
In this article
Basic data model functionality now built into Excel
In Excel 2010, you installed the PowerPivot 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 xVelocity in-memory analytics engine (formerly called VertiPaq), which used to be available only with the PowerPivot 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 PowerPivot add-in is available, too, and provides more advanced modeling capabilities for more experienced data modelers.
PowerPivot in Excel for more advanced data modeling
The PowerPivot in Excel 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 PowerPivot in Excel add-in you can:
- Filter data when importing. You can import data in both Excel and PowerPivot, but when importing data in PowerPivot, you can filter out unnecessary data to import just a subset.
- Rename tables and columns as you import data in PowerPivot.
- 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 PowerPivot for Excel 2013 add-in. The add-in is included in Microsoft Office Professional Plus. See Start PowerPivot in Excel 2013 for more information.
If you’ve used PowerPivot in the past, a few features available in earlier releases of PowerPivot aren’t available in PowerPivot in Microsoft Excel 2013. See the table in the What's new in the Field List section below for details. See Upgrade PowerPivot Data Models to Excel 2013 if you have existing PowerPivot workbooks that you plan to edit and use in Excel 2013.
What’s in the new Field List
Excel and PowerPivot now have a single, uniform Field List. Some features of the Field List have changed from the previous version of PowerPivot (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 PowerPivot 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 PowerPivot ribbon in Excel.
You can also create calculated fields in the Calculation Area in the PowerPivot window (no change from the previous version).
|Support for implicit calculated fields
With Excel 2010 you needed the PowerPivot 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 PowerPivot Field List.
With Excel 2013 you can create implicit calculated fields in the Field List for PivotTables you create in PowerPivot and Excel.
As with v.2 of the PowerPivot 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 PowerPivot, after creating a perspective, you view that perspective of your model in Diagram View, or filter the PowerPivot Field List by that perspective. Filtering the Field List is no longer possible.
In PowerPivot 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 PowerPivot 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 PowerPivot 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 PowerPivot ribbon in Excel.
You also create KPIs in the Calculation Area of the Data View grid in the PowerPivot window (no change).
As in the PowerPivot add-in v. 2 you add descriptions for calculated fields, tables, and columns by right-clicking them in the PowerPivot 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 PowerPivot, 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 PowerPivot window. In both Data and Diagram Views click Find on the Home tab of the PowerPivot 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 PowerPivot ribbon in Excel.
|Automatic relationship detection
||Not available in PowerPivot in Microsoft Excel 2013.
Data categorization and suggestions from DataMarket
When you add tables to PowerPivot, PowerPivot can automatically categorize each column. For example, PowerPivot 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:
- Windows 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.
- 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
PowerPivot is localized into all 40 Office languages. PowerPivot 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 PowerPivot 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 Web Apps, 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 PowerPivot 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