What's new in Excel 2016 for Windows

New Excel training

The Office Training Center has 10 new Excel courses, created in partnership with LinkedIn Learning. Watch videos at your pace to learn about Excel 2016 including using formulas and analyzing chart data. Also learn how to get unlimited access to over 4,000 video courses from LinkedIn Learning. See all our Excel training.

New feedback command on the File menu

Have a comment or suggestion about Microsoft Office? We need your feedback to help us deliver great products. In Excel, you can suggest new features, tell us what you like or what's not working by clicking File > Feedback.

Updates for Get & Transform and the Power Query add-in

• Added support for query parameters: You can now add parameters to your queries in the Query Editor. Just click Home > Manage Parameters > New Parameter.
  

  
  

  

• Create new queries based on merge/append operations: You can now use merge and append operations to create new queries in the Query Editor. Click the Home tab. Then click the down arrow next to Merge Queries, and then click Merge Queries as New. To append as new, click the down arrow next to Append Queries, and then click Append Queries as New.

  

  

• Improved web connector with page preview: We've improved the web connector experience. Now you can see a live preview of a web page, and select the desired table(s) for importing. Select Data > New Query > From Other Sources > From Web. After you put in your URL and click OK, the Navigator will appear. On the right, click the Web View button. Now scroll through the page. Any table with a tab and border is ready to import. Select the table that you want, and then click either Load or Edit to work it further.

The Tell Me what you want to do box at the top of the Ribbon has been updated with improved search, content and recommendations to answer your questions better and fast. Now when you enter a search for a certain task, function or question you'll see a wide variety of options. If it's a quick task Excel will try to let you take care of it right from the Tell Me pane. If it's a more complex question, we'll show you help that we think best matches your needs. Try it out to see how fast you can find what you're looking for and get things done.

Updates for Get & Transform and the Power Query add-in

• Refresh the Query - Keep the Layouts: Formats and calculated columns for data imported using Get & Transform are now preserved after queries refresh.

• Enhanced SAP HANA connector—allow multi-select of values for variables and parameters: With this update, users can now select more than one value for a given variable or parameter when leveraging the SAP HANA connector. This can be achieved via the Navigator dialog.

• Enhanced OData connector—option to import Open Type columns from OData feeds: The OData connector now supports importing Open Type columns from OData feeds. Prior to this update, such columns were not supported in Power Query. This option is exposed under the Advanced Options section in the OData connector dialog.

  

• Enhanced Access DB connector—new Select Related Tables button to “” in the Navigator dialog: We improved the Access Database connector to enable users to easily select related tables by adding the Select Related Tables button into the Navigator dialog. The behavior when clicking this button is the same as for all other databases that already support this capability—it selects all tables that have a direct relationship to any of the already selected tables.

  

• Option to generate Step names in English within the Query Editor: When creating new Steps in the Query Editor based on transformations from the ribbon, the default Step Names will be based on the transformation name (i.e., SplitColumns, FilteredRows, etc.). These step names will be localized to the current installation language for Excel.

  With this update, we introduced a new option to allow users to change this default behavior so that auto-generated step names use the English name for that transformation. This allows users to contribute to a single Excel report using multiple localized desktop versions, but keep the Step names recognizable by all parties (i.e., all in English by default). The new configuration setting is available under Data > New Query > Global > Regional Settings for non-English installations.

  

• Description for Query Steps within the Query Editor: We also introduced a new Description field for Query Steps within the Query Editor, which gives the users an option to document and provide comments for their query transformations. The new Description field can be accessed from the right-click menu on any Query Step > Properties… command.

  

• Extract Week, Day or Month name from a Date/Time column from the Query Editor: With this update, the users can now extract the Week, Day or Month names from a given Date/Time column. We’ve made this available from the Query Editor ribbon—either via the Transform tab, to modify an existing column, or via the Add Column tab, to insert a new one.

  These two options can be found under Date > Day > Name of Day and Date > Month > Name of Month respectively. Note that the Day and Month names are localized according to the local setting for your current file (available under the Data tab > New Query > Query Options > Current Workbook > Regional Settings for your Excel workbook).

  

• Merge Dates and Times into a Date/Time column from the Query Editor: Another new transformation this month allows users to combine a Date column and a Time column into a single Date/Time column. This can be achieved by selecting those two columns and clicking Combine Date and Time under Date or Time menus in the Transform or Add Columns tabs respectively.

  

• Extract Start/End of Hour from a Date/Time or Time column from the Query Editor: The last new data transformation this month allows users to easily extract the Start or End of an Hour based on a Date/Time or Time column. These two options can be found under the Time > Hour menu in the Transform and Add Column tabs.

  

• Refresh the query — keep the layouts: When you use Get & Transform to work with external data, you might want to make formatting changes or layout changes. You may even add a calculated column. Previously, these kinds of changes were not preserved when loading the data into Excel. But with this update, they are. This is because the Preserve column sort/filter/layout checkbox is now turned on by default. (To access this checkbox, click inside an external data table, and then click Design > Properties.)

• Command timeout available in the From Web connector: Now you can set web queries to time out, in case they take too long. Select Data > New Query > From Other Sources > From Web. Then click Advanced. At the bottom of the dialog, specify how many minutes should occur before the query times out.
  

  

• Support for fixed-width delimited files in the CSV/Text connector: If you have a text or CSV file that contains delimiters between columns, you can set the column widths to specific character positions.

  First, start the CSV or From Text connector: Data > New Query > From File > From CSV (or From Text). Then select a file that contains a delimiter between values. Under Delimiter, select --Fixed Width--. Finally, under --Fixed Width--, specify the character position for each column break.

  

Publish to Power BI

If you have a Power BI subscription, you can now publish files that are stored locally to Power BI. To get started, first save your file to your computer. Then click File > Publish > Publish to Power BI. After you upload, you can click the Go To Power BI button to see the file in your web browser.

No warnings when saving a CSV file

Remember this warning? "This file may contain features that are not compatible with CSV..." Well, you asked for it! We will no longer show this when saving a CSV file.

Updates for Get & Transform and the Power Query add-in

• New SAP HANA connector: In this update, we added a new connector to allow you to import data from SAP HANA databases. The new connector can be found under Data > New Query > From Database, or from the Query Editor window. See Connect to an SAP HANA database for more information.

  

• New SharePoint Folder connector: Available under Data > New Query > File, the new SharePoint Folder connector enables you to import data from multiple files within a SharePoint folder. This is similar to how the Folder connector, which enables you to import and combine multiple files from a filesystem folder.

  

• New Online Services connectors category: With this update, we added a new category that includes all available connectors for Online Services in Excel. The new category is available under Data > New Query > From Online Services, or from the Query Editor window.

  

• Improved DB2 connector: In this release, we improved the IBM DB2 connector to provide you the choice of using the Microsoft driver for IBM DB2, which is also automatically included in the Excel/Power Query installation. Within the IBM DB2 connector dialog, you can now select which driver to use under Advanced options.

  

• Improved Text/CSV connector, now exposing editable settings in the preview dialog: In this update, we improved the Text and CSV connectors so you can configure basic import settings from within the preview dialog. These import settings include: File Origin, Delimiter, and Detect Data Type (strategies include: Base on top 200 rows, Base on entire dataset, or No data type detection). Based on your choices, the preview in this dialog automatically updates. You can then decide whether to directly load the data, or edit first to apply additional data transformations.

  

• Improved relational database: In this update, we enhanced the relational database connectors. There is now an option that allows you to include Schema information as part of the Navigation hierarchy. This option is available under the Advanced option in all database connector dialogs, such as the SQL Server Database dialog below:

  

• Data Source Settings enhancements, including “Change Source” capability: You can now easily update the location of a data source for all queries connected to that data source within a single Excel workbook. (Before this update, you had to update the data source location for each of the queries. The new solution allows you to do this in a single step.) Within the Data Source Settings dialog, there is also new Current Workbook scope option, which allows you to limit the list of displayed data sources to only those that are used in the current file. From this view, you can modify credentials, privacy levels and other settings for that data source. Or you can use the Change Source option to modify the location for that data source.

  

• Advanced Filter Rows dialog mode within the Query Editor: We’re introducing a new mode in the Filter Rows dialog within the Query Editor. This new mode allows you to add multiple filter clauses within a single Filter Rows step. (Before this update, only one to two clauses were allowed.) It also allows you to combine filter clauses based on multiple columns. (Before, all clauses were applied to a single column.)

  

• Inline Input controls for Function invocation within the Query Editor: We improved the inline preview for functions within the Query Editor dialog. It now allows input values for function invocation. This new Enter Parameters section in the inline function preview is available for both “out of the box” functions (M Engine functions) as well as user-defined functions.

  

• Support for reordering Query Steps within the Query Editor by using drag and drop gestures: We now support reordering of Query Steps within the Query Editor by using drag and drop gestures. This can be done for each query via the Applied Steps section in the Query Editor.

  

• Date picker support for input Date values in Filter Rows and Conditional Columns dialogs: In this update, you can now leverage a Date picker control to provide input Date values in the Filter Rows and Conditional Columns dialogs.

  

• New context menu entry to create new queries from the Queries pane within the Query Editor: You can now add a new query in the Queries pane within the Query Editor. Right-click in the background of the pane or in a query group node. This allows a more intuitive experience for adding new queries when working in the Query Editor pane. Also, when you right-click a query group, the new query is added to the selected group.

  

Convert ink drawing to shapes

In an earlier update, you received a new Draw tab when on your touch-enabled device. That tab lets you select inking styles and start making ink annotations. However, with this update, you can convert those ink annotations to shapes. Just select them, and then select Convert to Shapes. That way, you get the freedom of freeform drawing with the uniformity and standardization of Office graphic shapes. To learn more about inking in Office, see Draw and annotate with ink in Office 2016.

Faster OLAP PivotTables!

If you work with connections to OLAP servers, your PivotTables are now faster. The June 2016 update contains query and cache improvements in this powerful feature’s performance. You could benefit from this work, whether you use PivotTables to answer one-off questions, or build complicated workbooks with dozens of PivotTables. It doesn’t matter if your PivotTables are connected to a tabular or multi-dimensional model, Any PivotTable connected to Microsoft SQL Server Analysis Services, 3rd party OLAP providers, or the Power Pivot Data Model will likely give you fresh data, faster. Additionally, now if you disable Subtotals and Grand Totals, PivotTables can be much faster when refreshing, expanding, collapsing, and drilling into your data. The bigger the PivotTable, the bigger the potential improvement. Specifically, we have made improvements in three major areas while querying OLAP servers:

• Improved query efficiency: Excel will now query for Subtotals and Grand Totals only if they’re required to render the PivotTable results. This means you wait less for the OLAP server to finish processing the query, and you wait less while waiting for the results to transfer over your network connection. You simply disable Subtotals and Grand Totals from the PivotTable Design tab just like you would normally. See: Show or hide subtotals and totals in a PivotTable.

• Reduced the number of queries: Excel is smarter when refreshing your data. Queries will now only refresh when they’ve actually changed and need to be refreshed.

• Smarter caches: When the PivotTable schema is retrieved, it is now shared across all of the PivotTables on that connection, further reducing the number of queries.

Updates for Get & Transform and the Power Query add-in

• Conditional Columns: With this update, we’re making it extremely easy for users to create new columns in their queries based on values from a different column. A typical use case for this includes creating a set of “buckets” or “categories” based on ranges from a continuous value column. For example, categorizing a person’s BMI into “Underweight,” “Normal,” “Overweight” or “Obese” based on well-known BMI value ranges, or defining categories such as “This Week,” “Last Week,” etc. based on a Date column. Previously, this could be achieved by creating Custom Columns and capturing the conditional logic with a set of “if-then-else” statements. These expressions can very soon become extremely complex to author and maintain as new conditions are added. Starting with this update, users can now define a set of rules and output values for the new column based on values in other columns within their tables. This can be achieved via the new Conditional Columns dialog, available in the Query Editor under the “Add Column” tab on the ribbon.

  

• Column type indicator in Query Editor preview column headers: With this update, we added column type indicators in column headers within the Query Editor preview. These new column type indicators allow you to quickly understand the types for each of the columns in their table, as well as change them to another type by clicking on these indicators or by using the previously available ribbon options (Data Type drop-down menu at the Home tab on the ribbon or Detect Type command under the Transform tab).

  

• Reorder Queries and Query Groups inside Query Editor via drag and drop gestures: You can now easily reorder queries and query groups within the Queries pane inside Query Editor by selecting one or multiple objects and dragging and dropping them into the desired destination. This can be used to reorder items within a given group (or top level) and also to move objects into a query group. Adding drag and drop gestures to the Queries pane inside Query Editor greatly improves the user experience for reorganizing queries and query groups, which was previously only possible via Context Menu options.

  

• Query Management menu in Query Editor: With this update, we’re exposing a new “Manage” drop-down menu for a query within the Query Editor Home tab on the ribbon. This menu exposes common management operations for a query, such as Delete, Duplicate and Reference.

  

Updates for Get & Transform and the Power Query add-in

• Remove blanks via Column Filter menu: With this update, we have added a new data filtering option that will remove all rows where the value for the current column is null or empty. It can be accessed via the Column Filter menu drop-down.

  

• Convert Duration values to Years: This new transformation option can be found under the Transform or Add Column tabs. Within the Duration drop-down menu there is a new “Total Years” entry that allows you to calculate total years based on a Duration type column. The logic applied is to divide the total number of days by 365.

  

• Keep Duplicates: This new transformation allows users to keep only the rows with duplicated values on the select column(s). Before this update, only “Remove Duplicates” was available. This new option can be found on the ribbon under Home > Remove Duplicates split button and then select the Keep Duplicates command.

  

• Hints for “sample input values” in the “Change Type with Locale” dialog: Power Query allows users to change the type of a column taking into account the Locale or regional settings/formats in which the data is being represented. This can be done by setting the Locale value for the entire workbook (under Data > New Query > Query Options > Current Workbook > Data Load) and also can be customized for each specific “Change Type” step in the queries. Within the “Change Type with Locale” dialog (accessible by right-clicking on a column header in the Query Editor preview then selecting Change Type > Using Locale…), users can now get a few “sample values” for the expected input format when selecting a specific data type and locale.

  

• Support for whitespace and line feeds in the Query Editor preview: With this update, we have added support for visualizing whitespaces in data cells within the Query Editor preview. This includes any whitespace characters, including line feeds. You can toggle between “Show whitespace” (default behavior) and not showing it (old behavior) from the View tab on the Query Editor ribbon.

  

• Ability to disable previews from the Navigator window: With this update, we have added an option to disable previews in the Navigator dialog. This allows users to reduce the number of calls being made to the data source in order to retrieve these previews.

  

• Technical name support in the Navigator window: Some data sources support the notion of a “technical name” for objects within the source. These “technical names” are meant to provide a more meaningful name for the end user connecting to the data source than the “physical name” for the object. In this update, we added a new option to the Navigator dialog to allow users to switch between “physical name” (previous behavior) and “technical name” (new behavior).

• Rename queries directly from the Queries pane in the Query Editor: With this update, it is now possible to rename queries directly from the Queries pane inside the Query Editor. To rename a query from this pane, simply select and right-click the query and select Rename, or double-click the query name, or select it and then press F2.

  

• Support for Command Timeout in the UX: With this update, users are now able to specify a Command Timeout value (in minutes) when connecting to database sources. This is a significant experience improvement since, before this update, this customization was only possible via custom formula authoring. This new Command Timeout option can be found under the “Advanced options” section in data source dialogs.

  

• Set to disable Privacy Level prompts at machine level (including Registry Key): Power Query allows users to combine data from multiple data sources into a single workbook. When dealing with multiple data sources, it is possible for users to define queries that require sending data from one data source to another data source. To prevent accidental disclosure of private or enterprise data, Power Query provides a feature called “Privacy Levels.” This feature allows users to specify the Privacy Level (Public, Organizational or Private) for each data source that they connect to when trying to combine data from multiple sources. Based on the selected Privacy Level, Power Query will ensure that data from a Private source is not sent to any other sources and that data from an Organizational source is only sent to sources within the organization. In some cases, this privacy enforcement can get in the way for users who want to define some data combinations that bypass this Privacy feature. Another potential effect of Privacy Levels is that, when combining data from multiple sources that are not supposed to send data between them due to their Privacy Levels, Power Query will compensate and download the data locally to perform the data combination in a secure way. This could result in a performance degradation due to the fact that data would have to be cached locally from all sources implied and combined in-memory. For those cases, users have the ability to ignore Privacy Levels as a “current workbook” setting. However, given that this option was only available per file and per user, it would require a user to enable this option for each workbook they would like to leverage. In addition, ignoring this privacy protection needs to be approved by each user of the workbook, so someone opening a .XLSX file from a different user in their computer would have to either provide Privacy Levels for the data sources involved on the report or manually disable this feature in the Options dialog (under Query Options > Current Workbook > Privacy section).

  In this release, we’re introducing a new setting to allow users and enterprises to pick one of the following behaviors:

  • Always combine data according to your Privacy Level settings for each source: This new option allows a user to “enforce” that Privacy Levels are taken into account for every .XLSX file on their machine, regardless of whether the “Current Workbook” Privacy Level setting is enabled or disabled.

  • Combine data according to each file’s Privacy Level settings: This is the default behavior and matches the Power Query behavior in previous releases.

  • Always ignore Privacy Level settings: This new option allows a user to always bypass Privacy Level settings for every .XLSX file in their machine, regardless of the Current Workbook setting.

  These options are available to the user under the Query Options dialog:

  

Updates for Get & Transform and the Power Query add-in

• ODBC Connector - Support for selecting User/System DSNs: A common request from customers using the ODBC Connector was being able to select from a list of available DSNs on their machine when specifying the connection details in the Import from ODBC wizard in Power Query in Excel. With this update, we added a new drop-down menu so users can select an existing DSN from the list of available User/System DSNs on their machine. In addition to selecting an existing DSN, users can specify additional Connection String details or select “None” in the DSN drop-down to specify a full Connection String independently from any existing DSNs (equivalent to the behavior prior to this update).

  

• CSV Connector - Ability to specify Column Delimiter in the Source dialog: We improved the Source dialog for the CSV Connector so that users can customize the Column Delimiter option. Before this update, the Column Delimiter option could only be modified via editing the underlying M formula for the Source step.

  

Updates for Get & Transform and the Power Query add-in

• Filter by not earliest/latest date: We added a new Date/Time columns filter option in the Query Editor to allow users to filter by dates that are not the earliest or latest dates within the current column.

  

• Filter by “is in previous N minutes/hours/seconds": Another new Date/Time filter provides the ability to filter out values that are within the previous N minutes/hours/seconds. This can be accessed from the In the Previous… option under the Filter menu for Date/Time columns.

  

  You can then define the filtering options by specifying the value and the desired filtering scope from the drop-down. Note the new hours, minutes and seconds options added in this update.

  

• Copy and paste queries between Power BI Desktop and Excel: We know that users often work with multiple tools in their daily activities and use Excel alongside Power BI Desktop for dashboarding and sharing scenarios. In a previous update, the ability to copy and paste queries between different Excel workbooks was enabled. This update makes it seamless for users to copy and paste their queries between Excel and Power BI Desktop. Users can now right-click a query or a query group in the Queries task pane in Excel workbook to copy those queries. They can then paste them into the Queries task pane in Query Editor for Power BI Desktop. Copying the queries from the Power BI Desktop into Excel is done in a similar way.

• Support for Special Characters in Split Column: Previously, we added support for using Special Characters in the Replace Values dialog to find and replace values. In this update, we enabled the same functionality for Split Column by Delimiter operations.

  

• Refresh previews in Merge Queries dialog: It is now possible to refresh the table previews within the Merge Queries dialog so users can select which columns to match using the latest available data.

  

• Monospaced font for Query Editor Preview: You can now customize the Query Editor Preview to display content using a monospaced font. This customization option is found under the View tab.

  

• Improved function invocation experience: It is now possible to invoke functions directly from within the Navigator dialog. This allows you to invoke and preview results of function invocation against a data source (such as SQL Server). You can now specify the function parameters directly from the Navigator window:

  

  And then preview the results in place:

  

• Option to set credentials at the server level: You are now given the chance to set the scope for their credentials when prompted for Database credentials. This is available as a radio button option at the bottom of the Credentials dialog.

  

• Add prefix/suffix to a Text column: It is now possible to add a prefix/suffix to an existing Text column via Query Editor—either by using this option under Transform (modify column in place) or under Add Column to create a new column (available under Text Column > Format).

  

Updates to Power Pivot in Excel 2016

• Save relationship diagram view as picture: Excel 2016 users can now save the data model diagram view as a high resolution image file that can then be used for sharing, printing or analyzing the data model. To create the image file, in the Power Pivot add-in, click File and then select Save View as Picture.

  

• Enhanced Edit Relationship dialog creates faster and more accurate data relationships: Excel 2016 Power Pivot users can now manually add or edit a table relationship while exploring a sample of the data—up to five rows of data in a selected table. This helps create faster and more accurate relationships without the need to go back and forth to the data view every time you wish to create or edit a table relationship.

  

• Table selection using keyboard navigation: In the Edit Relationship dialog, type the first letter of a table name to move the first column name starting with the selected letter.

• Column selection using column navigation: In the Edit Relationship dialog, type the first letter of a column name to move the first column starting with the selected letter. Retype the same letter moves to the next column starting with the selected letter.

• Auto column suggestion for same column name in both tables: After selecting the first table and column, on the selection of the second table, if a column with the same name exists, it is auto-selected (works both ways).

• Fixes that improve your overall modeling user experience: 1) The Power Pivot data model is no longer lost when working with hidden workbooks; 2) You can now upgrade an Excel 2010 workbook with a data model to Excel 2016; and 3) You can add a calculated column in Power Pivot, unless it contains a formula.

For a full list in this customer update, visit Power Pivot and the Analysis Services Engine in Excel 2016 fixes.

For all Excel 2016 users, get the latest customer update by installing the following updates. Note, there are 32- and 64-bit versions of each, so be sure to download the right version:

• KB3114374- Office 2016 Excel update from December 8, 2015.

• KB3114297 - Office 2016 Power Pivot in Excel from December 8, 2015.

Funnel Charts

Funnel charts show values across multiple stages in a process. Typically, the values decrease gradually, allowing the bars to resemble a funnel. Read more about funnel charts here.

Improved autocomplete

Excel autocomplete isn't as picky as it was before. For example, let's say you want to use the NETWORKDAYS function, but you can't remember how it is spelled. If you just type =DAYS, the autocomplete menu will bring back all of the functions that contain "DAYS," including, NETWORKDAYS. (Before, you had to spell the function name exactly.)

TEXTJOIN

This function combines text from multiple ranges, and each item is separated by a delimiter that you specify. Learn more about TEXTJOIN here.

CONCAT

This new function is like CONCATENATE, but better. First of all: it's shorter and easier to type. But it also supports range references in addition to cell references. Learn more here.

IFS

Tired of typing complicated, nested IF functions? The IFS function is the solution. With this function, conditions are tested in the order that you specify. If passed, the result is returned. You can also specify an else "catch all" if none of the conditions are met. Learn about IFS here.

SWITCH

This function evaluates an expression against a list of values in order, and returns the first matching result. If no results match, the "else" is returned. Details and examples are spelled out here.

MAXIFS

This function returns the largest number in a range, that meets a single or multiple criteria. More information available here.

MINIFS

This function is similar to MAXIFS, but it returns the smallest number in a range, that meets a single or multiple criteria. Read more about the MINIFS function.

Black Theme

The highest-contrast Office theme yet has arrived. To change your Office theme, go to File > Account, and then click the drop down menu next to Office Theme. The theme you choose will be applied across all your Office apps. To learn more about Office themes, see Change the look and feel of Office 2016 for Windows with Office themes.

Unless otherwise specified, both Office 365 subscribers and Office 2016 one-time purchasers get the following new features.

Six new chart types

Visualizations are critical to effective data analysis as well as compelling storytelling. In Excel 2016, we've added six new charts—with the same rich formatting options that you are familiar with—to help you create some of the most commonly used data visualizations of financial or hierarchal information or for revealing statistical properties in your data.

Click Insert Hierarchy Chart on the Insert tab to use the Treemap or Sunburst chart, click Insert Waterfall or Stock Chart for Waterfall, or click Insert Statistical Chart for Histogram, Pareto, or Box and Whisker.

Or click Recommended Charts >All Charts to see all the new charts.

Get and transform (Query)

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. Access them from the Get & Transform group on the Data tab.

One click forecasting

In previous versions of Excel, only linear forecasting had been available. In Excel 2016, the FORECAST function has been extended to allow forecasting based on Exponential Smoothing (such as, 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.

3D Maps

Our popular 3D geospatial visualization tool, Power Map, has been renamed and is now available to all Excel 2016 customers and is built into Excel. This innovative set of storytelling capabilities has been renamed 3D Maps and can be found along with other visualization tools by clicking 3D Map on the Insert tab.

Calendar Insights Financial templates

Take advantage of the new My Cashflow template, and the Stock Analysis template. These templates track what you earn, how much you spend, and where your spending occurs. Plus, quickly analyze and compare the performance of selected stocks over time.

Template

View your calendar as a dashboard and drill into the data. You'll get a better handle on how you spend your time, and identify ways to get more out of your days.

PivotTable enhancements

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. Here are some of the enhancements we made in Excel 2016, 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 (year, quarter, month) 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 zoom 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.

Multi-select Slicer

Now you can select multiple items in an Excel Slicer on a touch device. This is a change from prior versions of Excel where only one item in a Slicer could be selected at a time using touch input. You can enter Slicer multi-select mode by using the new button located in the Slicer’s label.

Publish and share your analysis with 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.

Quick Shape Formatting

This feature increases the number of default shape styles by introducing new “preset” styles in Excel.

Insert pictures with the correct orientation

With automatic image rotation, once you insert an image into Excel, it automatically rotates the picture to match the camera’s orientation. You can manually rotate the image to any position after insertion. Note that this only affects newly inserted images and does not apply to pictures in existing documents.

Do things quickly with Tell Me

You'll notice a text box on the ribbon in Excel 2016 that says Tell me what you want to do. This is a text field where you can enter words and phrases related to what you want to do next and quickly get to features you want to use or actions you want to perform. You can also choose to get help related to what you're looking for, or perform a Smart Lookup on the term you entered.

Insights into what you're working on

The Insights pane, powered by Bing, offers more than just definitions. When you select a word or phrase, right-click it, and choose Smart Lookup, the insights pane will open with definitions, Wiki articles, and top related searches from the web. You can also get to Smart Lookup any time by going to Review > Smart Lookup and entering a query there.

Ink Equations

Including math equations has gotten much easier. Now, you can go to Insert > Equation > Ink Equation, any time you want to include a complex math equation in your workbook. If you have a touch device, you can use your finger or a touch stylus to write math equations by hand, and Excel will convert it to text. (If you don't have a touch device, you can use a mouse to write, too). You can also erase and select and correct what you've written as you go.

Simpler sharing

Choose Share on the ribbon to share your spreadsheet with others on SharePoint, OneDrive, or OneDrive for Business.

These changes bring together two key aspects of collaboration: who has access to a given document and who is currently working with you on the document. Now you can view both pieces of information in one place from the Share dialog box.

Improved version history

Now you can go to File >History to see a complete list of changes that have been made to your workbook and access earlier versions.

Note: This feature is only supported for files stored on OneDrive for Business or SharePoint.

New Themes

There are now three Office Themes that you can apply : Colorful, Dark Gray, and White. To access these themes, go to File>Options> General, and then click the drop down menu next to Office Theme.

Data Loss Protection (DLP) in Excel

Data Loss Protection (DLP) is a high-value enterprise feature that is well loved in Outlook. We are introducing DLP in Excel to enable real time scan of content based on a set of predefined policies for the most common sensitive data types (e.g., credit card number, social security number, and US bank account number). This capability will also enable the synchronization of DLP policies from Office 365 in Excel, Word, and PowerPoint, and provide organizations with unified policies across content stored in Exchange, SharePoint and OneDrive for Business.