What's new in Excel 2016?

Excel 2016 is a spreadsheet software that is included in the MS Office 2016 suite. It is a new version that was released in 2016. It is not that much different from Excel 2013, but it has some significant changes and interesting features in formulas that are certainly useful.

A big thing that got introduced in Excel 2016 is forecasting, a feature that predicts the future value. That's a new feature that was introduced with a lot of formulas around it and new charts that are real time savers.

We are going to go through all of that and see what it has to offer.

New Features

Excel has received a number of new features that were missing in previous versions. These are going to be quite useful, and they are going to come in quite handy while doing your work.

Tell me what you want to do?

This box appears on the menu ribbon next to the "View" section. It's a box where you can search for a function or a feature, and then, based on the terms you used, it will direct you to the correct tool that you couldn't find.

It's a really useful shortcut, especially if you are a beginner. It will definitely help you a lot. It would've been even better if it was around way before, since it would've eased a lot of work that has been done.

Forecasting

In Excel 2016, you can now create your own forecasts. It is used to predict a future value using a linear representation. It's good for beginners since it automates a lot of processes, like detecting the seasonality cycle.

To create a forecast, you have to:

  1.  Have two data series that correspond to each other,
  2. Select both data series,
  3. Then go to the Data tab, and select Forecast Sheet in the Forecast section,
  4. Then pick a line chart or a column chart that is going to visually represent the forecast,
  5. Click then create,
  6. And you will end up with a new worksheet that has a table of historical and predicted values along with a chart that shows that data.

It will not be affected if you change the data that was used in creating the graph, they are not linked together and it will not be shown on the chart.

With this feature came five new formulas that are linked to it:

  • FORECAST.ETS - This function returns a future value that is based on historical values,
  • FORECAST.ETS.CONFINT - This function returns a confidence interval for a future value,
  • FORECAST.ETS.SEASONALITY - This function returns the length of the repetitive seasonality pattern,
  • FORECAST.ETS.STAT - This function returns a statistical value for a future value,
  • FORECAST.LINEAR - This function returns the future y-value that is based on existing values.

PivotTable Search Field

PivotTables now have a search bar where you can just type in what you are looking for and you will get everything you want.

This is great for having huge amounts of data.

New Charts

Since 1997, there have been no new Excel charts, but in the 2016 version of Excel we were introduced to six new chart types.

Excel 2016 Types of charts

Histogram charts show frequency data; they show data as a range of values. A prime example of data it shows is the cost of a product versus the cost of another product. They can be found by expanding the "Charts" section that is situated on the "Insert" tab, and on the "All Charts" there is "Sunburst", which you would want to select, but in earlier versions you could only create it by using the frequency function.

The Waterfall chart is a chart that provides a great way to see the impact of various data points as a running total. It's good for measuring your overtime growth and analyzing financial data. They can be found by expanding the "Charts" section that is situated on the "Insert" tab, and on the "All Charts" there is "Waterfall" which you would want to select.

Pareto charts sort these frequencies, and they add a cumulative percentage line to show you a trend through the data. Data values are represented in descending order while the line chart represents the progression of the cumulative percentage line. They can be found by expanding the "Charts" section that is situated on the "Insert" tab, and on the "Recommended Charts" there is "Pareto" which you would want to select.

The Box & Whisker Chart is a great way to show how the data is sorted into quartiles. The vertical lines that boxes have are called whiskers. They show variability outside the upper and lower quartiles. Outliers are points that are not inside those lines. They can be found by expanding the "Charts" section that is situated on the "Insert" tab, and on the "All Charts" there is "Box & Whisker" which you would want to select.

The Treemap chart represents hierarchical data, enabling the recognition of patterns. Data is represented with rectangles that are sized according to the value or quantity. They are a great chart if you want to visualize data within categories as compared to other categories. They can be found by expanding the "Charts" section that is situated on the "Insert" tab, and on the "All Charts" there is a "Treemap" which you would want to select.

And finally, the Sunburst charts. They represent hierarchical data that spreads from the center in the form of concentric circles. It shows how the outer rings are connected with the inner rings. They can be used for breaking down and analyzing the sales of a company.

They can be found by expanding the "Charts" section that is situated on the "Insert" tab, and on the "All Charts" there is "Sunburst" which you would want to select.

Conclusion

Excel 2016 focused more on the charts and on the visual perspective. Those charts were done at the exact right moment. All those features are practical and not that hard to learn. That makes this version worthwhile.