How to Configure Excel Services in SharePoint

Excel Services in SharePoint Server 2013 is enabled by creating an Excel Services Application service application in Central Administration. This article walks you through the steps of deploying Excel Services in your SharePoint Server 2013 farm.

Excel Services in SharePoint Server 2013 is a shared service that you can use to publish Excel 2013 workbooks on a SharePoint Server. The published workbooks can be managed and secured according to your organizational needs and shared among SharePoint Server 2013 users, who can render the workbooks in a browser. Excel Services was introduced in Office SharePoint Server 2007 and is available only in the Enterprise edition of SharePoint Server 2013. Excel Services is used primarily for business intelligence scenarios. Excel workbooks can be connected to external data sources, reports created, and then the workbook can be published to a SharePoint document library. When a user opens the workbook from the document library, it is rendered in the browser using Excel Services. The external data connection is maintained and the data is refreshed if necessary. This allows broad sharing of reports throughout an organization.

Excel Services consists of Excel Calculation Services, the Excel Web Access Web Part, and Excel Web Services for programmatic access. It supports sharing, securing, managing, and using Excel 2013 workbooks in a browser by providing the following:

• Global settings for managing workbooks, that include settings for security, load balancing, session management, memory utilization, workbook caches, and external data connections.
• Trusted file locations (that allow you to define which document libraries are trusted by Excel Services) together with session management, workbook size, calculation behavior, and external data settings of workbooks stored in those locations.

While users can interact with Excel workbooks in a browser through Excel Services, the workbooks cannot be edited in the browser by using Excel Services. Programmatic options are available. Looking at several specific scenarios can help you understand how best to take advantage of Excel Services:

• Sharing workbooks: Users can save Excel 2013 workbooks to a SharePoint Server document library to provide other users browser-based access to the server-calculated version of the workbook. When the workbook is accessed, Excel Services loads the workbook, refreshes the external data if it is necessary, calculates it if that is necessary, and sends the resulting output view back through the browser. A user can interact with Excel-based data by sorting, filtering, expanding, or collapsing PivotTables, and by passing in parameters. This provides the ability to perform analysis on published workbooks. A user does not have to have Excel 2013 installed to view the workbook. Users will always view the latest version of a workbook, and they can interact with it in a browser. Security permissions can be set to limit what access is provided for each user.
• Building business intelligence (BI) dashboards: Browser-based dashboards can be created using Excel and Excel Services together with the Excel Web Access Web Part. PerformancePoint Services can also use Excel Services workbooks as a data source.
• Reuse of logic encapsulated in Excel workbooks: in custom applications, besides a browser-based interface with the server, Excel Services provides a Web-service–based interface so that a published workbook can be accessed programmatically by any application that uses Web services. The Web service applications can change values, calculate the workbook, and retrieve some or the entire updated workbook by using that interface dependoning on what security permissions is set for the published workbook.
• Report Building: one of the most useful features of Excel Services is report building. By publishing data-connected workbooks to a SharePoint document library and making them available through Excel Services, you can make reports that you have created in Excel available to others in your organization. Instead of multiple users having separate copies of the workbooks on their computers, the workbooks can be created and changed by a trusted author in a central location that is trusted by Excel Services. The correct version of the worksheet is easier to find, share, and use from Excel, SharePoint Server, and other applications.

Start the Excel Calculation Services service

1. On the Central Administration home page, in the System Settings section, click "Manage services on server."
   
   
    
2. To select the server where you want to start the service, above the Service list, click the Server drop-down list, and then click "Change Server" and choose the appropriate server.
3. In the Service list, click "Start" next to "Excel Calculation Services".
   
   
    
4. After the Excel Calculation Services service has been started, the next step is to create an Excel Services service application.

Create an Excel Services service application

1. On the Central Administration home page, under Application Management, click "Manage service applications".
   
   
    
2. On the Manage Service Applications page, click "New", and then click "Excel Services Application".
   
   
    
3. In the Name section, type a name for the service application in the text box.
4. Select the "Create new application pool" option and type a name for the application pool in the text box.
5. Select the "Configurable" option, and from the drop-down list, select the account that you created to run the application pool.
   
   
    
6. Click "OK".
7. On the SharePoint Central Administration website home page, in the Application Management section, click "Manage service applications".
8. On the Manage service applications page, click the Excel Services service application that you want to configure.
   
   
    
9. On the Manage Excel Services Application page, click "Trusted File Locations".
   
   
    
10. Click on "Add Trusted File Location"
    
    
     
11. In the Address column, click the trusted file location that you want to configure.
12. Configure the settings as described in the following table:
     

    Option	Description
    Address	The location of the Excel documents that you want Excel Services to trust.
    Location Type	If the document library is stored in the SharePoint Foundation 2013 content database, select "Microsoft SharePoint Foundation". If the document library is stored in a network file share, select "UNC". If the document library is stored in a Web folder address, select "HTTP".
    Trust Children	Select "Children trusted" if you want to trust all child libraries or directories.
    Description	Text description of the file location you specified.
    Session Timeout	Value in seconds that an Excel Calculation Services session can stay open and inactive before it is shut down, as measured from the end of each open request. The default is 450 seconds.
    Short Session Timeout	Value in seconds that an Excel Services session stays open and inactive, before any user interaction, before it is shut down. This is measured from the end of the original open request. The default is 450 seconds.
    New Workbook Session Timeout	Value in seconds that an Excel Calculation Services session for a new workbook stays open and inactive before it is shut down, as measured from the end of each request. The default value is 1,800 seconds (30 minutes).
    Maximum Request Duration	Value in seconds for the maximum duration of a single request in a session. The default is 300 seconds.
    Maximum Chart Render Duration	Value in seconds for the maximum time that is spent rendering any single chart. The default is 3 seconds.
    Maximum Workbook Size	Value in megabytes (MB) for the maximum size of workbooks that Excel Calculation Services can open. The default size is 10 megabytes.
    Maximum Chart or Image Size	Value in megabytes (MB) for the maximum size of charts or images that Excel Calculation Services can open. The default size is 1 megabyte.
    Volatile Function Cache Lifetime	Value in seconds that a computed value for a volatile function is cached for automatic recalculations. The default is 300 seconds.
    Workbook Calculation Mode	Select "File" to perform calculations as specified in the file. Select "Manual" if you want recalculation to occur only when a Calculate request is received. Select "Automatic" if you want any change to a value to cause the recalculation of all other values that depend on that value. Also, volatile functions are called if their time-out has expired. Select "Automatic except data tables" if you want any change to a value to cause the recalculations of all other values dependent on that value (the values cannot be in a data table.) Also, volatile functions are called if their time-out has expired.
    Allow External Data	Select "None" to disable all external data connections for the trusted file location. Select "Trusted data connection libraries only" to only enable using connections to data sources that are stored in a trusted data connection library. The server will ignore settings embedded in the worksheet. Select "Trusted data connection libraries and embedded" to enable connections that are embedded in the workbook file or connections that are stored in a trusted data connection library. If you do not have to have tight control or restrictions on the data connections that are used by workbooks on the server, consider selecting this option.
    Warn on Refresh	Select the "Refresh warning enabled" check box to display a warning before refreshing external data for files in this location. When you select this option, you make sure that external data is not automatically refreshed without user interaction.
    Display Granular External Data Errors	Select the "Granular External Data Errors" check box to display specific error messages when external data failures occur for files in this location. Displaying specific error messages can help troubleshoot data connectivity issues if they occur.
    Stop When Refresh on Open Fails	Select the "Stopping open enabled" check box to prevent users from viewing files that are configured to refresh on open, if the refresh fails. This prevents users from seeing cached information in the workbook. This option is only effective if the user does not have Open Item permissions on the workbook. (A user with Open Item permissions on the workbook can open the workbook in Excel and thus has access to any cached information.)
    External Cache Lifetime (Automatic Refresh)	In the "Automatic refresh (periodic / on-open)" box, type a value in seconds for the maximum time that the system can use external data query results for automatically refreshed external query results. The default is 300 seconds.
    External Cache Lifetime (Manual Refresh)	In the "Manual refresh" box, type a value in seconds for the maximum time that the system can use external data query results for automatically refreshed external query results. To prevent data refresh after the first query, type v"-1". The default is 300 seconds.
    Maximum Concurrent Queries Per Session	Type a value for the maximum number of queries that can run at the same time during a single session. The default is 5 queries.
    Allow External Data Using REST	Select the "Data refresh from REST enabled" check box to all requests from the REST API to refresh external data connections. Note that this setting has no effect if Allow External Data is set to "None". Note too, that this setting has no effect if "Warn on Refresh" is enabled.
    Allow User-Defined Functions	Select "User-defined functions allowed" if you want to allow user-defined functions in Excel Calculation Services for workbooks from this location.

    
    

13. Click "OK".
14. On the site click on "Edit page".
    
    
     
15. Click on "Insert" -> "Web part" and select "Excel Web Access web part".
    
    
     
16. Open this web part in Edit mode and provide the document library locations.
17. Provide the document library location and click on the rectangle as shown below:
    
    
     
18. You will be populated with document library details.
19. Select the document you want to add to Excel Web Access.
20. Click "Insert".
    
    
     
21. Once successfully inserted you will see the Excel file rendering in the Excel Web Access, as in: