Save to SharePoint
You can save an Excel workbook that contains PowerPivot data to a SharePoint site that has been extended to support PowerPivot data access. After the file is published, SharePoint permissions and document properties will determine how others view and use your workbook.
This topic contains the following sections:
Before You Start
Check Authentication and Refresh Settings
Choose a Location for Your File
Save your File to SharePoint
Publish Using Excel Services Options
Upload a PowerPivot Workbook from SharePoint
Before You Start
Publishing PowerPivot workbooks to SharePoint requires that you have sufficient permissions, software, and knowledge of how to prepare your workbook for publication.
Server Software
To view the workbooks in a browser, the SharePoint server you are using must have PowerPivot for SharePoint and Excel Services. Both services are required for processing and rendering PowerPivot workbooks on SharePoint.
You can sometimes identify whether you have the correct software by reviewing the libraries and context menus on your site, but you might need to ask your SharePoint administrator whether the required server software is installed. If your site includes PowerPivot Gallery, PowerPivot for SharePoint is installed. Similarly, if you can open an Excel workbook in a browser window without having to download the file to your Excel desktop application, Excel Services is running on your SharePoint server.
If you plan to schedule automatic data refresh for your workbook, be sure to check with your server administrator to ensure that the data providers you used to import data are also available on the server.
Permissions
When you save a document to SharePoint, SharePoint permissions determine who can view, edit, or delete your work. The following table describes which SharePoint permissions grant specific rights to the workbooks you publish:
Permission |
Rights |
|---|---|
View |
Anyone with View permission can open the workbook by going to the URL address for the file. In addition, View permission will also allow someone to use your workbook as a read-only external data source for other Excel workbooks or reports. |
Contribute |
Contribute permission is more powerful because it conveys permission to add, edit, or delete documents in a SharePoint library. Contribute permission is required for saving a document to SharePoint. Anyone with Contribute permissions can download the file for modification in PowerPivot for Excel and then save it back to SharePoint. |
Full control |
Full control includes all of the permissions associated with Contribute, plus the ability to create or manage the list or library, and undo a check-out. |
Access to any SharePoint site or library is granted through permissions that a Site administrator or site owner specifies for groups or individual users.
Most people have either View or Contribute permissions. You can tell which permissions you have by looking at the Site Actions menu on your SharePoint site. If the only item on the menu is View All Site Content, you have View permissions and you cannot publish files to this SharePoint site. If you see a longer list of items, or if you see Library Tools in the ribbon, you have Contribute permissions.
Workbook Preparation
Before you publish an Excel workbook that contains PowerPivot data, you should follow this checklist to prepare your document:
Read about how to create a PowerPivot workbook that uses connection information that continues to work after the file is published. For more information, see Create a PowerPivot Workbook for SharePoint.
Understand the security considerations for publishing PowerPivot data in SharePoint. For more information, see Secure a PowerPivot Workbook on SharePoint.
Improve the visual presentation of a PowerPivot workbook on SharePoint by implementing these suggestions:
Give your Excel worksheets descriptive names. Worksheet names become page labels when the document is published to PowerPivot Gallery.
Turn off unused visual elements. In the Excel window, in the Show area of the View ribbon, turn off Gridlines, Formula Bar, and Headings.
Hide Excel worksheets that are empty or that are not relevant to the report. In the Excel window, right-click a sheet tab and select Hide Sheet.
Select cell A1 prior to saving and publishing the workbook. When a published file is opened in a browser window, focus is placed on the last active cell in the workbook. By placing the mouse cursor in cell A1, you ensure that the top left corner of the worksheet is positioned at the top left corner of the browser window.
When publishing to SharePoint, you can pick which objects in the workbook to show or hide. For more information, see Publish Using Excel Services Options in this topic.
Check Authentication and Refresh Settings
For best results, use the Excel default settings for authentication and data refresh.
Verify Windows authentication
By default, Excel specifies Windows Authentication so that the identity of the person viewing the workbook is used when accessing PowerPivot data. Keeping the default setting allows PowerPivot server software to capture accurate information about who is using PowerPivot workbooks, allowing you to better collaborate with the colleagues who are using your data. If you specify None or a Secure Store Service (SSS) ID, workbook usage data will be recorded for the Excel Services unattended user account (or for the SSS ID) rather than an actual person in your organization.
In Excel, on the Data ribbon, click Connections.
Click Properties.
Click Definition.
Click Authentication Settings.
Verify that Windows Authentication is selected, and then click OK.
Specify refresh control settings
Refresh control settings can affect how you preview a PowerPivot workbook in the PowerPivot Gallery library. For example, if your workbook uses the refresh on open property, and Excel Services is configured to issue a warning for data refresh operations, you will see a thumbnail image of Excel's data refresh warning instead of a document preview image.
In Excel, on the Data ribbon, click Connections.
Click Properties.
In Usage, in Refresh Control, select or clear the Refresh data when opening the file checkbox based on the following recommendations. Recommendations vary depending on how the PowerPivot data is accessed:
Data access |
Refresh control recommendation |
|---|---|
Embedded in the workbook (most common scenario) |
Clear the checkbox Refresh data when opening the file. Because all of the data is embedded, refreshing it each time you open the workbook is unnecessary. |
Stored as an external data source (for example, if you setup an external data connection to a PowerPivot workbook rather than import its data) |
Select the checkbox Refresh data when opening the file. Because the data is in a different location, you want refresh to occur to pick up any recent changes to the data. To allow preview image generation, ask your SharePoint administrator to customize the Warn on refresh setting in Excel Services. For more information, see Create a trusted location for PowerPivot sites on the web. |
Server-side data refresh operations (applies to PowerPivot workbooks that you save to SharePoint and schedule for PowerPivot data refresh). |
Select the checkbox Refresh data when opening the file. Because data refresh is scheduled for this workbook, PowerPivot will automatically enable the Refresh data when opening file property on this workbook to ensure that the PivotTable cache is replaced with newer data. If you clear the checkbox, it will be selected automatically when you save the workbook back to SharePoint. |
Choose a Location for Your File
Documents that you share on a site are typically kept in a document library. Shared Documents is the most common document library, but a SharePoint site that has PowerPivot for SharePoint will often have PowerPivot Gallery which offers preview, custom library views, and easy access to data refresh configuration schedules for the workbook. For more information about PowerPivot Gallery, see Use PowerPivot Workbooks on SharePoint.
You must choose a SharePoint server that has PowerPivot for SharePoint. If you publish to a server that does not have the PowerPivot server software, the data will not load when you open the document from a library.
You can choose Shared Documents, PowerPivot Gallery, or any other document library to store your workbook. To use these locations, you must know the Web URL address of the library. On a default SharePoint site, Web URL addresses for these libraries are as follows:
http://<server name>/Shared%20Documents
http://<server name>/PowerPivot%20Gallery
To find the Web URL address of any library, right-click a document in the library and choose Properties. The Address (URL) shows the SharePoint path to the document. Be sure to use your mouse to select the entire URL. Sometimes long URLs might be partially hidden from view.
Most likely, you will need to edit the URL before you can use it. Specifically, if the URL includes viewer and source properties, you should remove those properties to get a usable location that consists of server, site, and library names. For example, given the following URL, you can derive the actual URL path by keeping the portion in bold and deleting the rest: http://Contoso/Sales/_layouts/xlviewer.aspx?id=/PowerPivot%20Gallery/ContosoSales.xlsx&Source=http%3A%2F%2FContoso%2FPowerPivot%2520Gallery%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1. Based on the URL property, the actual path you should use for publishing purposes would be 'http://Contoso/Sales/PowerPivot%20Gallery/ContosoSales.xlsx' if you are publishing the ContosoSales.xlsx file, or just 'http://Contoso/Sales/PowerPivot%20Gallery' if you want just the site and library.
When choosing a location, pick a library that is available to the people with whom you want to share the workbook. You might need to request permissions on the library or the document to ensure workbook availability. For more information, see Secure a PowerPivot Workbook on SharePoint.
Save Your File to SharePoint
Save the file.
Click the File tab.
On the File menu, click Save & Send.
Click Save to SharePoint.
Select a site in either Recent Locations or Locations, or select Browse for a location to browse a site for the library you want to use.
Click Save As.
In the Save As dialog box, in File name, type a name for the file if you are using an existing location. Or, if you chose Browse for a location, enter all or part of a SharePoint site URL. If you enter a portion of the URL address, such as http://<server name>, click Save to open a connection to the server you specified and then browse the site for the library that you want to use. For examples of a SharePoint URL, see Choose a Location for Your File in this topic.
.gif "Screen shot of Excel Save As dialog box")
Click Save to publish the workbook to the library.
In a browser window, verify that the document appears in the library. Newly published documents will appear in the list. Library settings determine where the document appears (for example, sorted in ascending order by date, or alphabetically by name). You might need to refresh the browser window to view the most recent additions.
Publish Using Excel Services Options
Excel 2010 includes options for selecting specific worksheets or objects, including PivotTables and Slicers. Remember that the named objects you select only affects data visualization in the workbook. All the tables, rows, and columns in the underlying PowerPivot data source are included in the workbook when you save it to SharePoint. For more information, see Secure a PowerPivot Workbook on SharePoint.
On the File tab, click Save & Send, and then click Save to SharePoint.
Click Publish Options at the top right corner of the page.
In Publish Options, in the Show tab, select individual sheets or items in the workbook.
In the Parameters tab, select which parameters you want to publish. The Parameters tab in Publish Options lets you choose which Slicers appear in the published workbook.
Click OK.
Click Save As.
In the Save As dialog box, in File name, type a name for the file if you are using an existing location. Or, if you chose Browse for a location, enter all or part of a SharePoint URL address, such as http://<server name>, then click Save to open a connection to the server you specified.
Click Save.
Upload a PowerPivot Workbook from SharePoint
Another approach for adding a PowerPivot workbook is to upload it from a SharePoint site. Use this approach if you want to start from SharePoint.
In a SharePoint site, open a document library.
In Library Tools, click Documents.
In Upload Document, click Upload File.
Browse to the location of the saved .xlsx file and select it.
Click Save.
The file is uploaded to the SharePoint site and appears in the folder. You may need to click the refresh button in your browser before you can see it. It can now be opened in SharePoint. You can tell people where the file is by giving them the URL address for the file.