Save content
Have you found this content useful? Use the button above to save it to your profile.
Businessman typing
iStock_xijian_businessman

Excel 2016: Initial impressions

by
28th Oct 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

Following a particularly extensive preview program, Office 2016 was officially released on 22nd September. Over the next few articles we will look in some depth at the more significant new features and enhancements in Excel but, for now, we will just provide an overview of what the new version may have to offer.

A Proper Interface

It might seem a small thing, but many will welcome the change back to ‘proper’ case for the ribbon tab headings, the aberration of all UPPER CASE headings in Excel 2013 having been mercifully short-lived. There are some more substantive and less cosmetic changes to the ribbon. Immediately to the right of the ribbon tabs themselves, Microsoft have added a ‘Tell me what you want to do…’ box. You can type a word or phrase into the box and a list of relevant commands will be displayed together with a link to Get Help on the phrase and another link to use the new Smart Lookup feature:

(Click to expand)

In the example above we have typed in ‘summarize’. It’s often preferable to just type the start of what you want, if we had typed in ‘summary’ rather than ‘summarize’, no commands would have been shown, but ‘summar’ displays the same set as ‘summarize’. The commands displayed can be accessed directly from the dropdown list. Clicking on the Smart Lookup option displays the new Insights pane with additional information about our word or phrase, much of it of dubious relevance in this case.

The ‘Tell me’ box is an excellent idea with just one small drawback: It’s probably eight years too late. If Office 2007 had incorporated this feature, it might well have significantly lessened the irritation caused by trying to locate much-loved options in the then new ribbon interface. After eight years, most people will now have a good idea of where to find things. However, it will be undeniably useful for those new to Office, or to anyone who has only just upgraded from the 2003 or an even earlier version.

The ribbon also now includes a Share button that displays the Share pane, making it easy to share your workbook online:

(Click to expand)

A new ‘Colourful’ Office Theme replaces ‘Light Gray’ and, as you can see in the screenshots above, ‘brands’ each application with its own particular colour.

New features

Forecast Sheet

The Forecast Sheet option, in the new Forecast group of the Data ribbon tab makes it very quick to add a sheet to a workbook that contains a set of forecast figures showing confidence intervals and a chart, based on an existing set of data that contains a set of consistent date/time data.

The following example is based on a PivotTable (it could equally well be an ordinary range or an Excel Table) containing a consecutive time series and associated figures. We can click any cell within our block of data and then click the Forecast Sheet option. This will display a preview of the chart that will appear on the new sheet together with a proposed Forecast End date and an Options button that can be expanded to show more detailed options for producing the Forecast Sheet: 

(Click to expand)

Once you have set the Options to the required values, clicking the Create button will add a new sheet to your workbook containing the forecast as an Excel Table, and a line chart based on that Table. The Forecast figures are based on two new forecast functions. The Forecast column itself uses the FORECAST.ETS() function, while the Confidence Intervals are based on FORECAST.ETS.CONFINT().

New Chart Types

Excel 2016 makes several new chart types available as we can see the in the Insert ribbon tab, Charts group or in the Change Chart Type dialog:

(Click to expand)

The Treemap and Sunburst charts can be used with hierarchical data. A Treemap can show up to two levels of values as differently sized boxes within an overall rectangle whereas a Sunburst is similar to a ‘doughnut’ chart and can work with several levels in the hierarchy.

The Histogram is used to show the frequency of occurrence of ranges of values in a set of data. The values are organised into bins or by category. The Histogram can be sorted by value and combined with a cumulative percentage line chart to create a Pareto chart.

The Box and Whisker chart shows the distribution of values by quartile and can also highlight mean values and outlier values.

Waterfall charts are often used with financial data to show how we get from an opening position to a closing position. For example, showing how the cash movements in an accounting year move us from our opening cash balance to our closing bank balance.

3D Map

If you currently use the Professional Plus edition of Office 2013 or Standalone Excel 2013, this ‘new’ feature might seem surprisingly familiar. It is the latest stage in development of what was formerly the optional, and restricted by edition, Add-In: Power Map. 3D Map can be found in its own ‘Tours’ group of the Insert ribbon and it allows you to turn data that includes an appropriate location field, or set of location fields, into an animated 3D Map, using a Bing Maps view as the background. Values are plotted on the Map using the location field or fields and can be animated over time using a date or time field. Tours can be created by ‘flying over’ the map in a variety of ways and can also be turned into an MP4 video file for distribution or as an alternative to watching Eastenders.

PivotTable and PowerPivot enhancements

There are several relatively minor, yet still worthwhile, changes to PivotTables and to the Power Pivot Add-in.

Measures are now called Measures again after being relegated to ‘Calculated Fields’ for a version and the ability to right click a measure to edit it directly from the PivotTable Field List has been restored. The Field List also sees the introduction of a Search box, so that, if you are working with a long list of fields, or lots of different tables, it’s much easier to located the field that you want to use.

There are a couple of date-related changes. When a date/time field is added to a PivotTable it will be automatically grouped, so a field containing individual dates will be grouped by month with a Quarter and a Year field also being created and shown as an outline:

(Click to expand)

Also, in Power Pivot, there is a new ‘Date Table’ command that can create a table of all potential dates for the data set with various pre-set columns such as MonthNumber, Month and DayOfWeek. A previous article on Time Intelligence DAX functions should help explain how useful this is. On the subject of DAX, there are some useful new functions as well as the ability to create your own variables as part of a DAX formula.

There are several other improvements. PivotTables and associated formulae now adjust more automatically to the renaming of tables or columns in the data model. PivotCharts include drill-down buttons to enable you to drill down and up directly in a PivotChart and some cosmetic improvements in the PowerPivot window make working in both data and diagram view easier.

Transformational

Perhaps the most significant change in Excel 2016 is one that doesn’t actually involve any new feature. The Power Query Add-in includes a comprehensive set of features for accessing and working with data from a wide range of sources. As an Add-in, it was originally restricted to certain editions of Excel before becoming generally available to all editions of Excel 2013 in a ‘Power Query Public’ edition. The inclusion of the Power Query tools as an integral part of Excel 2016 within the Get & Transform group of the Data ribbon tab not only extends how much of the data processing burden Excel can cope with, but also has the potential to change the way we work within Excel itself. To take a simple example, complex lookups involving large numbers of separate formulae could be replaced by joining two Excel tables within a Workbook Query.

Conclusion

In the coming months we will explore some of these new features and capabilities in more detail to see what they are capable of and how to get the best out of them in practice.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.