Excel 2016 Updates

I was a bit surprised to see some Excel 2016 updates when I opened it up this morning. For reference, I am on an Office 365 early release program – so I might get these a bit before you do – but how cool is this? Some of the key ones that made me take note:

New Formulas

We’ve got some new formulas to add to our arsenal. I haven’t tried any of them yet, but the ones listed were:

CONCAT
TEXTJOIN
IFS
SWITCH

Chris Webb just posted a blog on the first two, IFS sounds useful, but SWITCH… Are you kidding me?

I LOVE that function in Power Pivot and am just itching for an excuse to use this one in a real world Excel project.

A New Chart Type

When Excel 2016 first came out, we saw some new chart types added to the product for the first time in… ages. Those included:

Treemap
Sunburst
Histogram
Waterfall

And now we got another for those of us on the subscription:

Funnel Charts

This is a pretty simple one, but here’s a sample mocked up in about 3 seconds:

A Power Query (Get & Transform) Update

I put this last, but to me this is the biggest deal of the whole bunch. The Power Query engine has been updated to version 2.29.4217.xxx. It’s hard to see what’s been added, as the update hasn’t been released for Excel 2010/2013 yet, nor has a detailed feature page…

Having said that, a feature that I asked for a while back has finally been implemented: Monospaced Fonts.

The importance of this is huge. Power Query has always been big on using a pretty font, which wasn’t monospaced. I.e. the characters weren’t the same width. This is a big problem if you are trying to split by number of characters, as they just don’t line up.

Now, there is still an issue… Power Query is still aggressively trimming spaces (something that started with version 2.28.xxx) as you can see below:

But, if you go to the Advanced tab and click the new Monospaced option, you get this beautiful view:

How much easier will that be for splitting columns based on width? Like 1000% easier, that’s how much!

Dear Power Query team

This is a fantastic feature, thank you. I’ve got two asks for you:

Can you get us the update for Excel 2010/2013 fairly soon? We need this there as well.
Can you please give me an option to set Monospaced as the default way to display my queries? This is not due to the overzealous trimming issue (which I do want to see fixed) but rather because this is the way I need to see my data come in every time.

Thanks!

More about Excel 2016 Updates

If you want to see Microsoft’s official page listing all the new features in this Office update, or if you’d like to get into their early release program, have a read here: https://support.office.com/en-us/article/What-s-New-and-Improved-in-Office-2016-for-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426?ui=en-US&rs=en-US&ad=US

10 thoughts on “Excel 2016 Updates”

Patrick M on February 5, 2016 at 12:37 pm said:

I see myself using TEXTJOIN *a lot*. It looks very similar to a UDF I'd been using.

SWITCH is potentially very useful, assuming it's basically the same function from Access...
Bill Szysz on February 7, 2016 at 2:33 am said:

Thanks for notificationt.
What a cool features !!!
Unfortunately, I'm still on Excel 2010 :-(.
Monospace in PQ is great !!
Pingback: Excel Roundup 20160208 « Contextures Blog
wanght on February 17, 2016 at 1:06 am said:

I encountered an error:when a column contains numbers in some rows and letters in other rows. I changed the column to text type. then I load the data to worksheet. there will always be errors which say "cannot convert xxx to numbers". xxx is the value contains letters. when do other operations, it generates error too.

please help,thank you
Ken Puls on February 17, 2016 at 8:44 am said:

Not sure exactly what to suggest here, as it really comes down to what you want to do with those numbers/letters. Converting them to text should let them show without issue. You could duplicate the column, change the new column to numbers, then replace errors with nothing. That would leave you with only numbers. But without knowing your overall goal here, it's kind of hard to lead you to a good solution.
wanght on February 23, 2016 at 5:02 am said:

@Ken Puls, Thanks for your reply. I've tested this problem by generating some data mannully, and I found everything went well. So strange.
Alex Jankowski on March 7, 2016 at 7:24 am said:

Ken, I found and loaded the update for Excel 2010 this morning. Can't find a detail function sheet, but monospace fonts work.
Ken Puls on March 7, 2016 at 2:56 pm said:

Hey Alex, yes, that makes sense. The Power Query updates are still being released for Excel 2010/2013 as well and... despite the releases being on different dates, the features are intended to keep in line with each other.
Alex Jankowski on March 11, 2016 at 10:09 am said:

Saw the update description for v 2.29.4217 today. One of the highlighted features is "Copy and paste queries between Power BI Desktop and Excel".
The really cool thing is that it works between Excel files (and for Excel 2010, not just 2016)!
Bernard Vukas on March 26, 2016 at 8:19 am said:

Hi Ken,

Thanks for the update! I wanted to let you know about a new Excel add-in that would enable these functions in all older versions of Excel; including versions that are not under an active Office 365 subscription:
http://bvukas.com/announcing-excel-formula-pack-add-in/

I hope you find it useful. I look forward to hearing your comments.

Thanks,
Bernard