Contents
Very soon, the next version of Excel 2016 is waiting for you. At the moment, a free Technical Preview version of Office 2016 is already available for everyone to review. Let’s see what’s new and delicious in Redmond.
General view
As you can see from the previous screenshot, the overall look of the interface hasn’t changed much. The background of the ribbon has turned green, and the ribbon itself has turned gray, which, in my opinion, is good – the active tab can be seen more clearly and the ribbon does not merge with the sheet, as it was in the past Excel. The names of the tabs said goodbye to CAPITAL – a trifle, but nice.
In settings File – Options you can, as before, change the color scheme of the interface, but the choice (as before) for some reason is completely miserable. In addition to green and pure white, a dark gray version is also offered:
… and jet black:
Not rich for a program that has a billion users around the world staring for 5-10 hours a day sometimes. There is still room for improvement in terms of design, that’s a fact. (Author’s note: am I the only one tired of this flat faceless flat-design everywhere and around?)
Assistant
A field appeared in the upper right corner of the screen Assistant. This is a kind of reincarnation of the famous Paperclip – a fast built-in search engine for all the functions and tools of Excel. In this field, you can start typing the name of a command or function, and Assistant promptly gives a list of tips that you can use:
Of course, it requires simple and correct formulations with official terminology (“sparklines”, not “microdiagrams”, etc.), but it’s a nice thing. Novice users in the situation “I remember that there is a function, but I don’t remember where” should like it.
I hope that in the future this thing will not just search in the help, but support voice input and understand -language morphology – then you can just tell Excel what you want to do: “Make a quarterly report by region and send it to your boss!”
New chart types
The last time Microsoft added new chart types to Excel was in 1997—almost 20 years ago! And finally, the ice has broken on this issue (not without friendly pendles to developers from members of the MVP community, I’ll tell you a secret). In Excel 2016, as many as 6 fundamentally new types of charts appeared immediately, most of which in older versions could only be built using special add-ins or dances with a tambourine. Now everything is done in two movements. So, meet:
Waterfall Chart
Other names: bridge (bridge), “steps”, waterfall diagram. A type of chart very often used in financial analysis (and not only) that clearly displays the dynamics of a parameter change over time (cash flow, investments) or the influence of various factors on the result (price factor analysis). Previously, to build such a diagram, you had to either shamanize or buy special add-ons.
Hierarchical (Treemap Chart)
A specific type of chart for visually displaying the distribution of a parameter by category in the form of a kind of rectangular “patchwork quilt”. Moreover, you can use a double level of nesting of categories (cities within the country). It is convenient to use for visualization, for example, profit by region or revenue by product category. In older versions, building such a chart was extremely difficult and usually required the installation of additional add-ons.
Sunburst Chart
An analogue of the previous type, but with a circular placement of data in sectors, and not in rectangles. In essence, something like a stacked pie or donut chart. To visualize the distribution, this is the very thing, and you are no longer limited to two levels of nesting, but can decompose them into three (category-product-sort) or more.
Pareto (Pareto Chart)
A classic diagram for visualizing the “80/20 law” or “Pareto law”, which many, I think, have at least heard of. In general terms, it is formulated as “20% of the effort gives 80% of the result.” When applied to a business, this is refined to “20% of products make 80% of revenue”, “20% of customers create 80% of problems”, etc. In such a diagram, the total revenue for each product is visually displayed as a histogram and, at the same time, the orange graph shows the accumulated share of revenue. Where the line crosses 80% (near Pineapple) and you can mentally draw a vertical line to separate key items (to the left of Pineapple) from unimportant ones (to the right of Pineapple). A mega-useful chart for ABC analysis and similar things.
Mustache Box (BoxPlot Chart)
Another name is “scatter plot” or Box-and-Whiskers Chart. A very common type of chart used in statistical evaluation that displays for a data set at once:
- arithmetic mean – cruciform notch
- median (50% quantile) – horizontal line on the box
- the lower (25%) and upper (75%) quantiles are the lower and upper boundaries of the box
- emissions – in the form of separate points
- maximum and minimum value – in the form of a mustache
Frequency histogram (Histogram Chart)
For the specified data set, displays the number of elements that fall within the specified ranges of values. The width of the intervals or their number can be set. A very useful diagram in frequency analysis, segmentation and the like. Previously, such a task was usually solved by grouping by numerical intervals in pivot tables or using the add-in Analysis Package.
Power Query
Data Import Add-in Power Query, previously shipped separately for Excel 2013, is now built-in by default. On the tab Data (Date) it is presented as a group Download & Convert:
Using the tools of this group, you can download tables to Excel from almost all existing main formats of databases, the Internet and other sources:
After loading, the received data can also be processed using Power Query, “bringing it to mind”:
- fix numbers-as-text and dates-as-text
- add calculated columns or remove unnecessary ones
- consolidate data from several tables into one automatically, etc.
In general, this is a very useful addition for those who periodically load large amounts of data from the outside world into Excel.
Pivot tables
Such a useful tool as pivot tables in this version received two small improvements. Firstly, in the panel with a list of fields, when building a summary, a tool for quickly finding the desired field appeared:
A very useful thing when there are dozens of columns in your table + you also added calculated fields from yourself.
Secondly, if the pivot table is filtered by a Slicer or Scale, and you double-click on a cell with data to “fall through” into the details, now the parameters selected on the slices and scales are taken into account (previously they were ignored, as if there were no slices , no scale at all).
Forecasting tools
Excel 2016 has received several new forecasting tools. First, in the category Statistical (Statistical) there are functions for calculating the forecast using the exponential smoothing method:
- FORECAST.ETS – gives a predicted value for a given date in the future using the seasonally adjusted exp.smoothing method
- FORECAST.ETS.DOVINTERVAL – Calculates a confidence interval for the forecast
- FORECAST.ETS.SEASONALITY – Detects seasonality in data and calculates its period
- FORECAST.ETS.STAT – Gives detailed statistics on the number series for the calculated forecast
- PREDICT.LINEST – Calculates a linear trend
A convenient tool for making forecasts on the fly has also appeared – the button Forecast sheet tab Data (Date):
If you select the source data (periods or dates and values) and click this button, then we will see the following window:
As you can see, you can easily set the necessary forecasting parameters in it and immediately see the result in a graphical representation – very convenient. If you press the button Create, then a new sheet will appear, where the forecast model will be automatically generated with the formulas:
Nice stuff. Previously, for example, at a forecasting training, we did this manually “from” and “to” – and it took a very decent amount of time.
Also in this version, several familiar mathematical and statistical functions have moved to the category Compatibility (Compatibility), because instead of them, their more perfect “descendants” appeared.
Final conclusions
The Technical Preview is not a release, and perhaps in the final version we will see some additional changes and improvements. But, apparently, nothing supernatural should be expected (someone will say that this is for the better, perhaps). Microsoft deliberately and methodically polishes the existing features and slowly adds new ones from version to version.
It’s good that, finally, new types of charts have appeared that everyone has been waiting for a long time, but there is still room for growth – project charts (Gantt, Timeline), scale charts (“thermometers”), etc. remained behind the scenes. I am also silent about the fact that sparklines could have been made for a long time not three types, but significantly more, as in the original.
It’s nice that useful add-ons (Power Query, Power Pivot) are built into the program by default, but then it would be possible to be generous even on Fuzzy Lookup with Power Map. Unfortunately, not yet.
And personally, I’m sorry that we won’t see, it seems, in the new version of Excel 2016, neither advanced tools for working with ranges (range comparison, for example), nor improvements in the Visual Basic programming environment (which has not been changed since 1997), nor new functions like VLOOKUP2 or Sum in words.
I hope to live until the moment when all this appears in Excel, but for now I will have to use the usual crutches.