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 is predicting the future value. That's a new feature that got 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.
Excel has received a number of new features that were missing in the past versions, these are going to be quite useful and they are going to come 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 a function or a feature, and then based on the terms you used it will direct you to a 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.
In Excel 2016 you can now create your own forecasts, it is used to predict a future value using linear representation. It's good for beginners since it automates a lot of processes like detecting the seasonality cycle.
To create forecast you have to:
- Have two data series that correspond to each other,
- Select both data series,
- Then go to the Data tab, and select Forecast Sheet in the Forecast section,
- Then pick a line chart or a column chart that is going to visually represent the forecast,
- Click then create,
- 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 what you are looking for and you will get everything you want.
This is great for having huge amounts of data.
Since 1997 there have been no new Excel charts, but in the 2016 version Excel we got introduced to six new chart types.
Histogram charts show frequency data, it shows data as a range of values. A prime example of data it shows is the cost of a product against 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've created it only 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 for 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 a 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 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 using 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 "Treemap" which you would want to select.
And finally, Sunburst charts. They represent hierarchical data that spreads from the center in the form of concentric circles. It shows how outer rings are connected with it's 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.
Excel 2016 focused more on the charts and on the visual perspective. Those charts were done at the right exact moment. All those features are practical and not that hard to learn, all that makes this version worth it.