Save a Workbook in PowerPivot

You can save your PowerPivot workbook to your computer or to a file share, or you can save the workbook to SharePoint to share the workbook and your analyses with others.

Understanding the PowerPivot Data Store

The data that you work on in the PowerPivot window is stored in an analytical database inside the Excel workbook. Data that you import and that you create by using calculations resides within memory until you save the workbook, at which point the PowerPivot data is written to disk together with the contents of the Excel workbook. The next time you open the workbook, Excel detects whether the workbook contains PowerPivot data or not, and opens the PowerPivot window if necessary. If you do not save the workbook to a format that supports the embedded PowerPivot data store, your work in the PowerPivot window can be lost.

Supported File Formats

The PowerPivot data store is supported when you save your workbook to one of the following Excel 2010 file formats:

  1. Excel Workbook (*.xlsx)

  2. Excel Binary Workbook (*.xlsb)

  3. Excel Macro-Enabled Workbook (*.xlsm)

If you or your administrator used Excel Options to customize the default format in which files are saved, you might not be able to save your PowerPivot data. The setting, Save files in this format, lets you save Excel workbooks to older versions of Excel, such as Excel 97-2003 Workbook (*.xls), or to other formats, such OpenDocument Spreadsheet (*.ods). However, these formats are incompatible with the PowerPivot data store, and as a result your data will not be available when you open the workbook the next time. To avoid losing your work, always use a format that is compatible with the PowerPivot data store. If you do not want to change your default format, whenever you save the workbook be sure to use the Save As option from the Excel File menu and choose one of the supported file formats.

Warning

Only Excel 2010 file formats are supported. You cannot save to an Excel 2003 file format, such as .XLS.

Saving to SharePoint

If you save your file to SharePoint, you can use any of the three Excel 2010 file formats that support PowerPivot data, and your reports and data will be visible in the PowerPivot Gallery.

For more information about working with PowerPivot files on SharePoint, see Share Data from PowerPivot.

Saving to Excel Services

If you publish your workbook to Excel Services, and the workbook contains Excel 2010 features that are not supported by Excel Services, you may see an error message warning you that the workbook contains unsupported features. This error does not refer to any PowerPivot functionality, but rather to other advanced features of Excel. Therefore, you can click OK in the message dialog box and continue to work with the PowerPivot PivotTables.

If you want to find out which features in Excel caused the error, re-open the workbook in Excel. When you do so, Excel displays a message indicating the features that will be unavailable when you view it in Excel Services. At this point you can either continue to publish to Excel Services, or make changes to your Excel workbook so that content is available in Excel Services as well.