Back in August, I wrote up a Wish List for Excel 2015. There is a new version of Excel out, and it is still the year 2015… who could have known they would call it Excel 2016!? 
Anyway, let’s look at some of the winners and losers…
Winners:
- Built in Calendar support: As long as you have at least 1 date column, you now get an option to magically create a date table! Excel will guess a reasonable date range, or let you manually set the range. They even throw in a few typical columns (eg, Month, Day of Week, YYYY-MM). I think this will work great for a ton of people, and my demos certainly got easier. Love.
- Excel 2010 UI: There are still a few subtle things that were nicer in 2010, but in general… we are back to a good state. The field list now has a search box, and the ability to edit a measure… so let’s chalk this up in the W column!
- Improved VBA support: I totally wrote a piece of VBA to insert a measure today. I almost cried, I was so happy. I haven’t played with this SUPER hard to see how performance is, how updates work, etc… but I’m thrilled this functionality exists now.
- Improved Completion: Oh. Em. Gee. This truly makes 2016 an “upgrade”. It’s amazing. I want to marry it.
Half Winners:
- Better Refreshing and control of refreshing: Some time back, Kasper pushed through a change where as long as you stay on the power pivot window… your reports would not refresh. Clever and easy idea, that really helped me. However, I just can’t call this “good”, as I just added a measure [One] := 1 and promptly waited for 40 seconds to get control back. Whatever epic party they are having needs to be trimmed down.
-
Better (Bulk) Copy/Paste for measures: Well, truthfully it is zero % better. However, I am giving half-credit because… maybe VBA could help. I haven’t tried it, but I assume I could loop through measures, moving them to another table.
-
Power Pivot For All: I dunno if this really deserves half credit, but I am in a generous mood. At least in 2016, I can tell you with a straight face there is an Office suite you can buy, that includes Power Pivot… Office Professional! This is actually way, way better than in 2013. The Office 365 story is more confusing, and clearly… we are not talking Power Pivot For All, but… but it is better!
Losers:
- Better support for connection changes: There isn’t a great answer here. If you start CSV, you are lost and scared and alone … when you want to switch to SQL. The answer is kinda “just always use Power Query”, but then… that doesn’t work with Sharepoint or import to Tabular, so… ya. No credit.
Unknown:
- Easier/better drilldown support: Beats me. It is probably the same. I never really understood it before, so it is hard for me to evaluate if it is any better now… but I doubt it. Hey, maybe it was great in both versions. <shrug>.
- Better Error Handling: No idea. Will we still see totally random and mostly ignorable errors? Time will tell.
Conclusion:
Only 1 clear loser, and … let’s be clear and fair here. 2013 was worse than 2010. But 2016? Is it better than 2010! It’s like… 2010, but with amazing DAX completion, VBA, a built in Calendar, and a bunch of new toys (new DAX functions).
I’m happy!
And if for some reason you are still reading – apologies for lack of action here lately. I have been stupid busy. I think that is letting up, so I can start being more awesome
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
On the drilldown front, check this out:
http://blog.crossjoin.co.uk/2015/10/05/drillthrough-on-multiselect-now-works-in-excel-2016-and-ssas-2016/
Hi,
could you write the way to add a new mesure with vba in a simple way ?