What?s new for business analytics in Excel 2016
Earlier this week we explained how Excel?the world?s most widely used and familiar data analysis tool?works with the new Power BI and Power BI Desktop to provide business analysts with a powerful portfolio of analytic tools unique in the industry. Today we unbox Excel 2016?s new and improved business analytics features, so you can take full advantage of each capability as it applies to your unique business needs. Excel 2016 improvements have been designed with the analyst?s journey in mind, from gathering to consuming data:
Faster and easier ways to get data
Before analysis can begin, you must be able to bring in the data relevant to the business question you are trying to answer. Excel 2016 now comes with built-in functionality that brings ease and speed to getting and transforming your data?allowing you to find and bring all the data you need into one place. These new capabilities, previously only available as a separate add-in called Power Query, can be found natively within Excel 2016. Access them from the ?Get & Transform? section on the Data tab.
Use these capabilities to connect to data from a wide range of sources?like tables in public websites, corporate data in databases and cubes, cloud-based sources like Azure, unstructured data like Hadoop and services like Salesforce?then bring the data into your workbook?s data model or display them as tables within a worksheet.
The Query Editor, which opens when creating a new query or opening an existing one under Get & Transform, provides intuitive data shaping and transformation options, enabling you to prepare a data set with only the information you want to see and in the way you want to see it. Each step you take to transform the data can be repeated and rearranged to help you work faster and more efficiently. When you refresh the data connections, every step you took to transform the data is preserved. And once you have the information prepared, you can share your queries easily with others.
Additional features available under the Get & Transform section include accessing recent sources you?ve used in previous queries and options to combine queries together. With a premium version* of Excel 2016 and a Power BI license, you can also use search to discover corporate data available to you across your company that is shared in a corporate catalog or even share your own queries with others.
Integrating the Power Query technology also provides benefits for programmability. With these features integrated into native Excel, you can use VBA and the Object Model to program a related query and transform actions. And if you need more than the predefined actions provided, you can use the powerful query language (M) to create additional actions and capabilities.
Streamlined data analysis
Excel is known for its flexible and powerful analysis experiences, through the familiar PivotTable authoring environment. With Excel 2010 and Excel 2013, this experience was significantly enhanced with the introduction of Power Pivot and the Data Model, bringing the ability to easily build sophisticated models across your data, augment them with measures and KPIs, and then calculate over millions of rows with high speed. In Excel 2016, we focused on making these experiences more discoverable, consistent and streamlined, so that you can focus less on managing your data and more on uncovering the insights that matter:
- Automatic relationship detection discovers and creates relationships among the tables used for your workbook?s data model, so you don?t have to. Excel 2016 knows when your analysis requires two or more tables to be linked together and notifies you. With one click, it does the work to build the relationships, so you can take advantage of them immediately.
- Creating, editing and deleting custom measures can now be done directly from the PivotTable fields list, saving you a lot of time when you need to add additional calculations for your analysis.
- Automatic time grouping helps you to use your time-related fields in your PivotTable more powerfully, by auto-detecting and grouping them on your behalf. Once grouped together, simply drag the group to your PivotTable in one action and immediately begin your analysis across the different levels of time with drill-down capabilities.
- PivotChart drill-down buttons allow you to drill in and out across groupings of time and other hierarchical structures within your data.
- Search in the PivotTable field list helps you get to the fields that are important to you across your entire data set.
- Smart rename gives you the ability to rename tables and columns in your workbook?s data model. With each change, Excel 2016 automatically updates any related tables and calculations across your workbook, including all worksheets and DAX formulas.
- Multiple usability improvements have also been made. For example, delayed updating allows you to perform multiple changes in Power Pivot without the need to wait until each is propagated across the workbook. The changes will be propagated at one time, once the Power Pivot window is closed.
New forecasting capabilities
We also revamped the forecasting capabilities provided in Excel 2016. In previous versions, only linear forecasting had been available. In Excel 2016, the FORECAST function has been extended to allow forecasting based on Exponential Smoothing (i.e. FORECAST.ETS() ?). This functionality is also available as a new one-click forecasting button. On the Data tab, click the Forecast Sheet button to quickly create a forecast visualization of your data series. From the wizard, you can also find options to adjust common forecast parameters, like seasonality, which is automatically detected by default and confidence intervals.
New and modern charts
Effective visualizations are critical to effective analysis as well as compelling storytelling. In Excel 2016, we have provided a new set of modern charts with the consistent look and feel of the familiar charting experience that Excel provides natively, to expand the ways to visualize your data.
These new charts?with the same rich formatting options that you are familiar with?help you cut through the tedious work it used to take to create some of the most commonly used business data visualizations, like the Sunburst and Waterfall charts below:
As part of an ongoing effort to provide more value in Excel?s visual offerings, you can expect ongoing updates through Office 365 to deliver even more new charts and visualization improvements.
We have also made our popular 3-D geospatial visualization tool, Power Map, accessible to all Excel 2016 users by delivering its features right out of the box. This innovative set of storytelling capabilities has been renamed to 3-D Maps and can be found along with other visualization tools on the Insert tab.
With the new Power BI Desktop offering, which is built with the same free-form reporting experience of Power View, you can have a dedicated environment for visual data exploration and report authoring. You can expect more investments in this type of reporting experience through this new tool, like new interactive visualizations. If you have Power View reports in Excel and want to continue working on them in Power BI Desktop, simply import your Excel workbook. For those who elect to continue to use Power View in Excel, the feature is fully supported in Excel 2016, and the insert Power View button can be enabled with a few steps.
Publishing and sharing in Power BI
A report is not complete without being able to share it with the right people. Once you?re finished preparing your data analysis, you can share it with your workgroup or clients through Power BI with just one button. Once published to Power BI, use your data models to quickly construct interactive reports and dashboards. With Excel Online support built into Power BI service, you can also display your fully formatted Excel worksheets as well.
Taking advantage of Excel Online
With rich features in Excel Online, your peers and clients are able to view and interact with your Excel data analysis and reports from anywhere. All you need to do is share your workbook through Power BI, SharePoint or OneDrive. Recently, we?ve included new features to make it even easier to consume and interact with your workbook from the web, including Search in Filters and the ability to change the settings of a value field in a PivotTable. With Office 365, we are providing more features and improvements to Excel Online on a regular basis.
Try the new features out for yourself!
With Excel 2016, we have provided enhancements at every phase of your analytic journey. Try each new feature and improvement out now by downloading the Office 2016 Preview for Windows and then share your ideas for improvements in Excel 2016.
- Premium features for Excel 2016 (like corporate data sources and query sharing) can be found in Office 365 ProPlus and other business plans, as well as Office 2016 Professional and Excel 2016 Standalone?more details coming up.