{"id":2945,"date":"2015-07-09T10:06:04","date_gmt":"2015-07-09T10:06:04","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2945"},"modified":"2023-12-31T00:07:23","modified_gmt":"2023-12-31T00:07:23","slug":"whats-new-in-excel-2016","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/whats-new-in-excel-2016\/","title":{"rendered":"What’s New in Excel 2016?"},"content":{"rendered":"

 <\/p>\n

\"Whats<\/a><\/p>\n

Here’s what’s New in Excel\u00a02016<\/strong><\/h2>\n

With the launch of Office 2016 in the Autumn of 2015, you may be wondering\u00a0about the new features in 2016 and what’s changed. With Excel at the core of many businesses, it would help to have all of the new features explained. We have compared the different versions of Excel and come up with a quick overview of What’s New in Excel 2016.<\/p>\n

To learn more about that, please take a look at our Excel courses<\/a>.<\/p>\n

Changes from Excel 2013<\/strong><\/h3>\n

The main changes between\u00a0Excel 2016 and 2013 are associated with the Business Intelligence (BI) features. They can be located on the Insert and Data tabs and include several new chart types, a Power Maps tool and a Forecast Sheet feature. There is also a new Get & Transform section for creating and working with queries.<\/p>\n

The Tell Me box<\/strong><\/h3>\n

\u2018Tell Me what you want to do\u2019 on the Home ribbon is now available across Office 2016. Type what action you would like to take and you get some useful\u00a0tips to help you get started. ‘Tell Me’ remembers what you have asked and further actions to try.<\/p>\n


\nNew Chart Types<\/strong><\/h3>\n

On the Insert tab there are several new Chart Types available only in Excel 2016.<\/p>\n

Waterfall
\nBox & Whisker
\nTreemap
\nSunburst
\nHistogram –\u00a0Pareto
\n
\nWaterfall<\/em><\/p>\n

Being able to create a Waterfall chart with one click will amaze anyone who has attempted to create such a chart\u00a0in previous versions of Excel. It would have involved a lengthy work around using formulas, recolouring bars, white boxs and adjusting the scale.<\/p>\n

Recap:<\/strong> A Waterfall Chart, sometimes called a \u2018flying brick chart\u2019 is a way of visualizing a series of positive and negative data such as monthly cash flows. The bars appear to fly or float between the start and end columns giving the impression of a waterfall or a bridge.<\/p>\n

Suppose you have the following data and wish to display it in a Waterfall Chart:<\/p>\n

\"What's<\/a><\/p>\n

 <\/p>\n

With Excel 2016 you click in the data and choose Insert, <\/strong>Waterfall<\/strong> from the Waterfall and Stock Chart button.<\/p>\n

\"<\/a><\/p>\n

Some formatting is needed but most of the work is done.<\/p>\n

See How to create a waterfall chart with Excel 2010<\/a><\/p>\n

Treemap<\/em><\/p>\n

This is a brand new chart type and a great way to visualize hierarchies of data.<\/p>\n

Suppose you have PivotTable data based on car sales. A Treemap can\u2019t be created directly from a PivotTable so you will\u00a0have to copy as values to another location. Now click in the data and choose<\/p>\n

Insert, Hierarchy Chart, Treemap<\/strong><\/h3>\n

\"<\/a><\/p>\n

Sunburst<\/em><\/p>\n

Another way to display this type of data is with a Sunburst chart.<\/p>\n

\"<\/a><\/p>\n

Box & Whisker<\/em><\/p>\n

\"<\/a><\/p>\n

The Box & Whisker chart is used to show statistical information about a set of data. The line in the middle indicates the median value (middle value) while the bottom and top of the boxes represent the spread of the data from the first to the third quartile (25th<\/sup>percentile to the 75th<\/sup> percentile). The lines extending vertically (whiskers) shows the spread of data outside this range.<\/p>\n

Histogram<\/em><\/p>\n

From the same data you can now easily create a\u00a0Histogram chart.<\/p>\n

 <\/p>\n

\"<\/a><\/p>\n

Right clicking on the axis and choosing Format Axis<\/strong> allow you to change the number of bars (bins) or the bin width.<\/p>\n

Pareto Chart<\/em><\/p>\n

A Pareto chart displays a series of figures as a combination of a cumulative line chart and columns chart sorted in descending order.<\/p>\n

Pareto Sorts your Bars: Highest first. This highlights\u00a0which Bars have the biggest impact\/return. This will influence your decision on\u00a0where to assign your resources.<\/p>\n

\"<\/a><\/p>\n

To create a Pareto chart, click in the data and select<\/p>\n

Insert,<\/strong> Recommended Charts<\/strong>, Pareto<\/strong><\/p>\n

 <\/p>\n

Power Map<\/strong><\/h3>\n

Now available on the Insert tab is Power Map. Click inside some data containing locations and a map is inserted onto a separate sheet as a new scene. Click the Add field<\/strong> button and Add Category<\/strong> to represent the data graphically.<\/p>\n

The following graph can be created from data shown.<\/p>\n

\"<\/a><\/p>\n

Click Layer Options<\/strong> to control bar thickness and height.<\/p>\n

Power Map can also detect post codes. This UK map shows the location of training events based on post codes which have been hidden on this sheet.<\/p>\n

Tip<\/strong>: To hide cells in an Excel worksheet Select the range of cells Right Click, Format Cells Select Custom and type ;;;\u201d\u201d<\/strong><\/p>\n

\"<\/a><\/p>\n

The pie charts represent the number of events broken down by each course.<\/p>\n

The maps are initially inserted onto a new sheet but can be copied back to an Excel worksheet by selecting a copy image button.<\/p>\n

With Power Map you can even create a video to show a series of changes to a map over time.<\/p>\n

Forecast Sheet<\/strong><\/h3>\n

New to Excel 2016 on the Data tab is Forecast Sheet. This automatically creates formulas to make a forecast of your existing data.<\/p>\n

\"<\/a>\"<\/a><\/p>\n

 <\/p>\n

Suppose you have monthly sales pivot data for two years and wish to predict the next 12 months taking into account seasonal patterns if there are any. To create the sheet, click a cell in the table data and select:<\/p>\n

Data, Forecast Sheet<\/strong><\/p>\n

In the dialog box set the Forecast end date and click Create.<\/p>\n

Next you will see a new sheet with forecasted data and a graph including upper and lower levels of confidence for the forecast.<\/p>\n

\"<\/a><\/p>\n

 <\/p>\n

Time Grouping in PivotTables<\/strong><\/h3>\n

When creating PivotTables in Excel 2016, data containing date fields the dates will be grouped automatically.<\/p>\n

Opening up Excel Workbooks with the New Charts on an Older version<\/strong><\/h3>\n

What happens if you are using Excel 2013 or 2010 and open an Excel 2016 workbook containing the new chart types or maps?<\/p>\n

Rather than see the new chart you will see is a text box with a warning not to save your workbook into the older file format.<\/p>\n

\"<\/a><\/p>\n

You may need to PDF workbooks containing the new chart types or cut and paste them as pictures if you want people with previous versions\u00a0of Excel to view them.<\/p>\n

What’s new for Excel 2010 users?<\/strong><\/h3>\n

There are many people currently using Office 2010 and for those considering upgrading there will be even more new features to explore. Previously only available as Add-ins, Power Pivot, Power Query and Power View can now be accessed directly from the Excel 2016 ribbon.<\/p>\n

Those who make use of these Business Intelligence tools will be working in new ways with Excel Tables and Table connections. Others will discover Slicers can be used\u00a0with Tables as well as with\u00a0Pivot Tables and many will find useful and fun ways to use Data, Flash Fill.<\/p>\n

We wish you a happy Excel 2016 upgrade!\u00a0<\/p>\n

\n

For more information on Excel training courses London<\/a>, have a look at the full range of our courses.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

  Here’s what’s New in Excel\u00a02016 With the launch of Office 2016 in the Autumn of 2015, you may be wondering\u00a0about the new features in 2016 and what’s changed. With Excel at the core of many businesses, it would help to have all of the new features explained. We have compared the different versions of […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[23,4,6,9,13],"tags":[180,186,210,238,394,591],"class_list":["post-2945","post","type-post","status-publish","format-standard","hentry","category-data-visualisation","category-excel-training","category-hints-tips","category-microsoft-office","category-office-365-2","tag-excel-2016","tag-excel-chart-types","tag-excel-pareto-chart","tag-excel-waterfall-charts","tag-office-2016","tag-whats-new-in-office-2016"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2945","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2945"}],"version-history":[{"count":4,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2945\/revisions"}],"predecessor-version":[{"id":3581,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2945\/revisions\/3581"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2945"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2945"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2945"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}