Microsoft Excel 2013 offers lots of new business intelligence features and functionality, making it easier than ever to gather data and use the data to create powerful reports, scorecards, and dashboards. Excel Services in SharePoint Server enables you to share workbooks with others, view and explore information in a browser window, and refresh data.
Read this article to get a brief overview of business intelligence capabilities in Excel 2013 and which ones are supported in Excel Services. For more detailed information, TechNet Article: Business Intelligence in Excel and Excel Services.
Note The information in this article applies to Excel 2013 and SharePoint Server 2013 (on premises) only. If you’re looking for information about business intelligence capabilities in SharePoint Online or Office 365, see Business intelligence capabilities in Excel and Office 365.
In this article
Overview of business intelligence capabilities in Excel
Business intelligence is the set of tools and processes that are used to gather data and turn it into meaningful information that people can use to make better decisions. Using Excel 2013, you can perform a wide range of tasks to create and support a business intelligence solution.
The following table summarizes some business intelligence capabilities in Excel 2013.
Table: Business intelligence capabilities in Excel
| Task |
Description |
| Get data |
You can connect to a variety of data sources in Excel, such as SQL Server tables, SQL Server Analysis Services cubes, Windows Azure data, OData data, and more. You can import data into Excel, or you can use a data connection to create charts, tables, and reports using external data. |
| Sort, organize, and work with data |
You can use Flash Fill to format columns of data to display a particular way. You can also create data models to combine one or more tables of data into a single data source that you can use to create reports, including views that you create by using Power View. |
| Create reports, scorecards, and dashboards |
You can use Quick Analysis to select data and see recommended ways to visualize that data.
You can create a wide variety of reports that include tables, line charts, bar charts, radar charts, and so on.
You can also create scorecards that use conditional formatting and key performance indicators (KPIs) to show at a glance whether performance is on or off target for one or more metrics.
|
| Apply filters |
You can add slicers, timeline controls, and filters to worksheets to make it easier to focus on more specific information. |
| Explore and analyze data |
You can use Quick Explore to explore information in a PivotChart report or a PivotTable report that uses data stored in SQL Server Analysis Services. Quick Explore enables you to select a value and view additional information about that value in a new chart or table on the screen. You can also use Quick Explore to view higher or lower levels of information in a report, or to temporarily change the information that is displayed in a report. |
| Use advanced analytic capabilities |
If you are an advanced user, you can create Calculated Members and Calculated Measures for PivotChart reports or PivotTable reports that use Analysis Services data. Or, you can use PowerPivot for Excel to create Calculated Fields, which are custom columns that you can create for data models in Excel. |
For more information about new features in Excel, see What's new in Excel 2013.
Top of Page
Business intelligence capabilities supported in SharePoint Server 2013
Many of the business intelligence capabilities that are available in Excel 2013 are supported in Excel Services in SharePoint Server 2013 (on premises). This means that you can view and interact with workbooks in a browser window much like you would by using the Excel client. In general, the following capabilities are supported:
- Most external data connections. Depending on the data sources that are used and how SharePoint is configured, in most cases, workbooks that are published to SharePoint Server 2013 retain live data connections so that they are always up to date.
- Workbooks that contain data models. This requires Excel Services to be configured to support data model functionality.
- The ability to view, sort, and explore data in reports, scorecards, and dashboards.
- The ability to open the Field List and Field Well for PivotChart reports and PivotTable reports. This enables you to temporarily change what information is displayed in Excel Services reports.
- The ability to use existing filters, slicers, and timeline controls in workbooks.
- The ability to display a single item in a workbook in its own SharePoint Web Part. This includes a PivotChart report, a PivotTable report, or a range of data in Excel 2013.
You cannot create certain kinds of items, such as timeline controls, calculated members, and calculated measures, in a browser window. However, you can create items by using Excel, publish the workbook to SharePoint Server, and then you and others can view and use those items in a browser window.
Top of Page
Business intelligence capabilities supported in Excel Services and Excel Web App
If your organization is using SharePoint Server 2013 alongside Office Web Apps Server 2013 (on premises), then either Excel Services (SharePoint Server) or Excel Web App (Office Web Apps Server) is used to render workbooks in a browser window. This decision can affect which business intelligence capabilities are available to you when you are using a workbook in a browser window.
The following table summarizes some of the differences between business intelligence capabilities that are supported in an on-premises environment that includes Office Web Apps Server and SharePoint Server.
Table: Business intelligence capabilities supported in Office Web Apps Server and SharePoint Server
| Capability |
What’s supported when Excel Web App is used to render workbooks |
What’s supported when Excel Services is used to render workbooks |
| External data |
Most kinds of secure external data connections are not supported. For more information, see Working with external data in Excel Services. |
Most kinds of external data connections are supported, as is the ability to refresh data in a browser window. For more information, see Working with external data in Excel Services. |
| Data models |
You can view workbooks that contain data models, but in general, you cannot explore data in certain items, such as PivotChart reports, PivotTable reports, and timeline controls that use data models as their data sources. |
You can view and interact with items in workbooks that contain data models. |
| Flash Fill |
The ability to use Flash Fill when viewing or editing a workbook in a browser window is not supported. |
The ability to use Flash Fill when viewing a workbook in a browser window is not supported. |
| Quick Analysis |
The ability to use Quick Analysis when viewing or editing a workbook in a browser window is not supported. |
The ability to use Quick Analysis when viewing a workbook in a browser window is not supported. |
| Reports and scorecards |
The ability to view, sort, filter, and interact with reports and scorecards that were created by using Excel is supported in a browser window. This does not include views that were created by using Power View. |
The ability to view, sort, filter, and interact with reports and scorecards is supported in a browser window. |
| Power View |
Views that were created by using Power View are not supported in Excel Web App. |
Views that were created by using Power View are supported in Excel Services. |
| Slicers and timeline controls |
The ability to use existing slicers and timeline controls is supported. |
The ability to use existing slicers and timeline controls is supported. |
| Quick Explore |
The ability to use Quick Explore is supported in a browser window. |
The ability to use Quick Explore is supported in a browser window. |
| Calculated measures and calculated members |
Workbooks that contain calculated measures and calculated members are supported. |
Workbooks that contain calculated measures and calculated members are supported. |
| Calculated fields (created by using the PowerPivot add-in for Excel) |
Calculated fields are not supported in Excel Web App. |
The ability to use existing calculated fields is supported in Excel Services. |
Top of Page
