Category Archives: Excel Training

What’s New in Excel 2016?


Whats new in Excel 2016

Here’s what’s New in Excel 2016

With the launch of Office 2016 in the Autumn of 2015, you may be wondering about 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.

Changes from Excel 2013

The main changes between Excel 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.

The Tell Me box

‘Tell Me what you want to do’ on the Home ribbon is now available across Office 2016. Type what action you would like to take and you get some useful tips to help you get started. ‘Tell Me’ remembers what you have asked and further actions to try.

New Chart Types

On the Insert tab there are several new Chart Types available only in Excel 2016.

Box & Whisker
Histogram – Pareto


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

Recap: A Waterfall Chart, sometimes called a ‘flying brick chart’ 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.

Suppose you have the following data and wish to display it in a Waterfall Chart:

What's New in Excel 2016? - Waterfall


With Excel 2016 you click in the data and choose Insert, Waterfall from the Waterfall and Stock Chart button.

 What's new in Excel 2016 - Waterfall2

Some formatting is needed but most of the work is done.

See How to create a waterfall chart with Excel 2010


This is a brand new chart type and a great way to visualize hierarchies of data.

Suppose you have PivotTable data based on car sales. A Treemap can’t be created directly from a PivotTable so you will have to copy as values to another location. Now click in the data and choose

Insert, Hierarchy Chart, Treemap

 What's new in Excel 2016 - Treemap3


Another way to display this type of data is with a Sunburst chart.

 What's new in Excel 2016 - Sunburst

Box & Whisker

 What's new in Excel 2016 - Box Whisker

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 (25thpercentile to the 75th percentile). The lines extending vertically (whiskers) shows the spread of data outside this range.


From the same data you can now easily create a Histogram chart.


 What's new in Excel 2016 - Histogram

Right clicking on the axis and choosing Format Axis allow you to change the number of bars (bins) or the bin width.

Pareto Chart

A Pareto chart displays a series of figures as a combination of a cumulative line chart and columns chart sorted in descending order.

Pareto Sorts your Bars: Highest first. This highlights which Bars have the biggest impact/return. This will influence your decision on where to assign your resources.

 What's new in Excel 2016 - Pareto

To create a Pareto chart, click in the data and select

Insert, Recommended Charts, Pareto


Power Map

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 button and Add Category to represent the data graphically.

The following graph can be created from data shown.

 What's new in Excel 2016 - Map1

Click Layer Options to control bar thickness and height.

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.

Tip: To hide cells in an Excel worksheet Select the range of cells Right Click, Format Cells Select Custom and type ;;;””

 What's new in Excel 2016 - Map2

The pie charts represent the number of events broken down by each course.

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

With Power Map you can even create a video to show a series of changes to a map over time.

Forecast Sheet

New to Excel 2016 on the Data tab is Forecast Sheet. This automatically creates formulas to make a forecast of your existing data.

 What's new in Excel 2016 - Forecast1 What's new in Excel 2016 - Forecast2


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:

Data, Forecast Sheet

In the dialog box set the Forecast end date and click Create.

Next you will see a new sheet with forecasted data and a graph including upper and lower levels of confidence for the forecast.

 What's new in Excel 2016 - Forecast4


Time Grouping in PivotTables

When creating PivotTables in Excel 2016, data containing date fields the dates will be grouped automatically.

Opening up Excel Workbooks with the New Charts on an Older version

What happens if you are using Excel 2013 or 2010 and open an Excel 2016 workbook containing the new chart types or maps?

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.

 What's new in Excel 2016 - Save 2013

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 of Excel to view them.

What’s new for Excel 2010 users?

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.

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 with Tables as well as with Pivot Tables and many will find useful and fun ways to use Data, Flash Fill.

We wish you a happy Excel 2016 upgrade!

How to Build an Automatic Gantt Chart in Excel

An easy to update Gantt chart in Excel

Building a Gantt chart in Excel is pretty easy. But what if you needed to update any of the tasks? This would usually mean lot’s of time consuming manual editing of cells.  But there is an easier way….

Step in Excel and the ever flexible conditional formatting function! Yes by deploying some neat conditional formatting tricks you can produce a presentable usable Gantt chart in Excel.

Recap: A Gantt chart is made up of task bars, one for each of the tasks required to complete the project in hand. The task bars typically have start and finish dates and therefore have different lengths on duration.

By using Excel’s Conditional Formatting feature we can create our project task bars for our Gantt chart. We will be able to different colour task bars conveniently managed by conditional formatting. An example is shown below on how cell colours can be changed; sales figures green if they exceed a target figure of 1200.


Let’s create a Gantt chart in Excel

By using conditional formatting we can easily create the following.

Gantt Chart in Excel

Creating the conditional formatting rule

Step 1. Start by selecting the range of cells where bars are to be displayed. In the above example range F2:T11.

Step 2. Select the Home tab then choose Conditional Formatting.

Step 3. Pick New Rule then Choose a formula to determine which cells to format.
In the empty box box (format values if formula is true) enter the formula:


This formula returns True if a task Start Date is before or equal to the chart date (entered in row 1) AND the Finish Date is after or equal to the chart date.

AND means both conditions within the brackets must apply. The $ sign before a letter means keep that column fixed when applying the rule to the range of cells. Similarly, the $ before a number means fix that row when applying the rule.

Step 4. Now click the Format button and choose a fill colour then OK.

In the example all cells that obey the rule are coloured blue.

Adding more rules

Additional rules can be added for example if certain tasks are designated critical by typing CT in the C column then colour the bars pink.

Project Chart

To do this create a new rule as before but include a third condition where the type is CT.


Click Format and choose pink as the fill colour.


For consistency you can choose specific fill colour by selecting Fill, More Colours, Custom then entering 255,153,255 for Red, Green and Blue.

Adding a white top and bottom border as part of the Formatting rule can help to separate the bars when touching.

To make changes to colours or to formulas you must highlight the exact range containing the conditional formatting then select Conditional Formatting, Manage Rules. To help do this quickly select Home, Find & Select, Conditional  Formatting.

Conditional formats can be copied using the Format Painter.

Additional Resources

Present your data in a Gantt chart in Excel

Pro tip: Create a Gantt chart in Excel