Top new capabilities in Excel 2016

Some time ago Microsoft realeased new Office 2016. As always we have opportunity to read about new functionalities from enormous number of sources.  I was intereseted in Excel mostly because most of the time in my work i try to analyze data using this tool and additonal tools associated with self-service Business Intelligence. I count on some improvements in this fanatstic tool – and i think Microsoft did a great job in newest release. In this article i would like to present my personal TOP 5 new features in Excel 2016 – let’s begin!

Field Search-box in Pivot table

This is it! I was waiting for this functionality a lot of time. Everyone knows that working with cobe with huge amount of columns via Excel is sometimes very hard – if we need to find dimension field and we don’t know where it is located then we need to expand dimension and scroll down – but now we can use new search box that is very simple in use:

Search box in Excel 2016 pivot table

Of course displayed objects are filtered on the fly so we can very easily find appropriate object that we want to use. There is only one question – Why it took so long to introduce this improvement to Excel?

It’s not all – there is very similiar functionality where we can type Excel functionality or setting and tooltip should show us everything in listbox – awesome!

result

Smart rename

I think it’s must important improvement in Excel 2016. What is it? I think you are experienced in power pivot renaming problems. For example if you change name of a column and there were some calculations associated with this column all of them were automatically invalid – after that you need to change name in every calculation manually – but no in Excel 2016. Let’s explain this functionality in more detail. As you can see i have column named OrderQuantity and measure sumOfOrderQUantity which is simple sum of OrderQuantity column.

result

Let’s change name of the column to Order_quantity – as you can imagine our formula was automatically changed to use new name of a column!

result

What if some pivot table or chart used this column? Smart rename functionality detects this fact and change reference too – great.

result

Multiselection in Slicers

Very small change but useful for users that don’t like to use keyboard during filtering report object. Of course in previous releases (Excel 2010 and 2013) we was able to achieve this functionality by holding ctrl and select members that we want – but now you can use small button on the slicer and select multiple members.

multiselect slicer

Time column grouping and drill-down capabilities on charts

These changes give us great savings in time when we create power pivot reports. Now Excel creates new derived columns based on date column. For example if we have date columns it creates Year, Quater, Month name and Month ordinal number columns. After that when we put date column on pivot table area – we have classic hierarchy with drill-down capabilities as you can see in the picture below:

result

As you can see there is OrderDate(Month) column and associated OrderDate(Month index) – power pivot use OrderDate(month Index) as a sorting column for OrderDate(Month) – very user friendly feature.

result

In this place there is another change with drill-down that we should talk about. In previous versions of Excel if we want to roll-up between different levels of hierarchy then we have to use context menu options . In new version we have nice-looking small buttons to change level. Very useful change is my opinion because most user that work with excel reports want to “see” on the screen all capabilities that they need.

result

New forecasting

This is a fact that Data Science and advanced business analytics is one of my favourites areas in Business Intelligece so i am very happy that there is new forecasting feature in Excel 2016. How it looks? Just look at the picture below – as simple as that! On click on the ribbon button and you have your forecast with upper and lower confidence bound:

 

forecast

beside, we receive nice chart with the same information:

forecast

The results look very impressive – point for Microsoft for confidence boundries that are very useful in such analysis. This feature is similiar to forecasting in Excel Data Mining Adding – but now we don’t need any connection to external software like Analysis Service – we’ve got this “out-of-the-box”. I think we need to cover in more detail and analyze alghoritms used in this piece of software.Of course it’s not all new features – i chose most interesting ones that i think is most useful for me. In the future i will write few words about new DAX functions and Power Pivot improvements – stay tuned!

Leave a Comment

Your email address will not be published. Required fields are marked *