skip to Main Content

Post by Rob Collie

We Love Power Pivot in Excel 2016

Seriously, I Want to Hug the Computer and Every Software Engineer in Redmond

Tales from the Preview

I’m traditionally very slow to look at interim releases of software, but the Office 2016 public preview is out.  Everything listed below is now also available to you to look at as well.  Just go grab the preview and slap it on a “spare” computer.

***UPDATE Oct 2015:  The preview period is now closed, but this post will help you find a version of Office 2016 that includes Power Pivot.

Rundown of Improvements

The next release of Excel (2016) brings MAJOR improvements to our world.  Unlike 2013, which offered us little noticeable benefit over 2010 Power Pivot, I can’t wait for 2016 to become mainstream.  It’s a monstrous win.

Each of these improvements warrants its own in-depth blog post, but for now, let’s just run through the list of things that catch my eye…

Measure Icons and Search in the Field List!

Measure Icons and Field List Search are Back in Power Pivot / Excel 2016

We Had Both of these in 2010.  2013 Took Them Away.  2016 Puts them Back Smile

A bit cosmetic perhaps, but if you never used 2010 Power Pivot, you have NO IDEA how useful these are.  We welcome them back to our world with open arms.

Even Better:  Right Click and Edit Measures in Field List!

Once Again, We Can Edit Measures in the Field List in Excel 2016!

In the Field List, You Can Now Right Click and Edit a Measure!
(Again, a 2010 Convenience that was ripped from us in 2013)

I can pretty safely say that I will never use the “Manage Measures” dialog box ever again.  Introduced in 2013 to compensate us for the loss of right click edit, that thing made me grumpy every time I used it.  No more.  Or at least, once we’re all using 2016 regularly.

Speaking of Which…  “MEASURES” are Back!!!

Measures are Once Again Named "Measures" in 2016.

The Most Amazing Formula Feature In the World is back to Having a Decent Name
(“Calculated Fields” were/are the long-neglected, woefully underpowered feature of traditional pivots)

I’ve sent some EPIC email rants on this topic over the years.  Power Pivot measures are a life-changing capability.  LIFE-changing, not just game-changing.  They will improve your LIFE.

So why, in 2013, were they renamed “Calculated Fields????”  That’s the name of the calculation feature in traditional pivots that literally has about 0.00000001 % of the power of Measures.

The 2013 name change, in other words, would scare you off from discovering Measures, the greatest thing ever.  Bad idea.  It was done in the name of Consistency, and we all know that a foolish consistency is the hobgoblin of software projects.

Excel team, I salute you for reversing course.  Plus, we now gain the GOOD kind of consistency, where the same thing is given the same name in all environments.  The BI Designer, for instance, calls them Measures.  And this makes, you know, books and stuff easier to write too.  WIN!

More Readable Data View (in Power Pivot Window)

Power Pivot Data View is More Readable in 2016

Small Outlines Around Every Four Rows, Better Contrast, Etc. – Just Much Easier on the Eye

Arrows in Diagram View Point the Correct Direction!

Power Pivot Diagram View - Arrows Point the Correct Direction in 2016!

Relationship Arrows Now Indicate the Direction in Which Filters Flow!
(And the “1” and “*” Help Too)

This is another long-running rant, finally put to rest.  The number of times I’ve had to explain, “yeah, filters flow in the OPPOSITE of the direction of the relationship arrows…”  Boy I won’t miss that little snarky speech.

Calendar/Date Table Creation?

I haven’t even had a chance to try this out yet. But it’s good to see that they are making an attempt to help us do this without having to resort to external machinery.

Built-In Date/Calendar Table Creation in Power Pivot 2016

I Suspect this Still Won’t Be Adequate for Most Needs, But It’s a Start

Built-In Date/Calendar Table Creation in Power Pivot 2016

These Two New Calendar Functions Show More Promise, I Think
(But I’ve Still Yet to Try Them)

More Functions!

Here are the new DAX functions that, so far, excite me the most:

  1. MEDIANX and PERCENTILEX.INC / PERCENTILEX.EXC – median and percentile calculation has always been tricky in DAX, now we will have first-class functions for them.
  2. PRODUCTX – no more crazy logarithmic transformations required.  This will make forecasting scenarios a LOT easier.
  3. CONCATENATEX – I am disproportionately excited about this one.  If there are five things that tie for best performance, for instance, you can return a list of their names as a Measure in a pivot!
  4. EXCEPT and INTERSECT – every few months, I find myself wishing I could compare two lists and quickly return just the items that match or don’t match.  Usually I then want to perform a calculation on the resulting list.  These are going to be super helpful in those cases.
  5. ISSUBTOTAL – sometimes, HASONEVALUE and HASONEFILTER are subject to exceptional cases like “this parent only has one child,” and as a result, using them to detect whether you’re in a subtotal situation (in a measure) has been only about 99% reliable.  This hopefully takes us to 100%.

Um…  DAX Variables!!!

DAX Variables in Power Pivot 2016!

This Subtotal Calc Column Used to Require the Use of the EARLIER Function
(But now we can do it with variables in DAX)

Variables DEFINITELY deserve their own post.  MULTIPLE posts, actually.  But for now, just know that we’ve got this exciting new capability coming to us.

In most cases, you won’t need EARLIER anymore.  Wahoo!  (Although I have now become comfortable with EARLIER, so this comes a bit late for me).

And just as good, IMO, is that in measures, we won’t constantly be re-calculating, for every cell in the pivot, something that returns the same value every time (like for instance, the first date in the entire calendar table).  So there should be some speed gains here too.

Formula Fixup on Rename!

No picture for this, but if you rename a column, all formulas that reference that column will be auto-fixed to use the new name.

Same thing if you rename a measure or a table.

ONE EXCEPTION:  if you rename a measure in the Excel window, using the Measure editor that hangs off of pivots, that “slips past the goalie” and does NOT trigger auto-fix.  I wonder if the Excel team is aware of this loophole.

Color-coded formula editor

Color Coded DAX Formula Editor in Power Pivot 2016!

Last but not least:  color-based indication of keywords and variables is a nice touch

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 53 Comments
  1. I have been using the 2016 Preview for months and the many new features are great.

    One feature that is not great, is that my model will sometimes vanish without warning, never to return (without setting it back up or moving it from another workbook). It actually is gone from the file when I inspect the archive contents. This happens across various models, and despite being in contact with Microsoft for months on it, I haven’t been able to figure out a way to reliably reproduce the issue–other than to be up against a deadline and have a lot of work done since the last backup.

    If I had it to do over again, I would not use this for my real work; and I really hope Microsoft can get the bug ironed out by the time the final version is released. The instability pretty much eliminates the efficiency benefits of Power Pivot.

    1. After playing around with professional 2016 for the past few days. I think macros could be the problem. When I have macros in a personal workbook, then the model just vanishes without warning. When there are no macros, it works great. Microsoft needs to fix this.

      1. Anyone knows a fix for this? I worked more than 4 hours yesterday on Excel 2016 Powerpivot; then, all at a sudden, after coding at VBA editor, my internal data model disappeared. Any idea?

          1. Hi Rob do you know if there is a fix to the “losing data model”? I am new to power pivot (thx to you =] ) but I´m downgrading to 2013, because I lost my models or the corruped files all the time. It is impossible to work.

  2. Awesome improvements across the board. I still wish that the diagram view would end the relationship arrows at the field used in the relationships though. Seems like it should be such an easy fix, and would be so incredibly helpful in debugging problems.

  3. Is there a target date for release-to-manufacturing (hopefully we will not have to wait until 2016).

    Also, will there be an earlier release for Office 365 users?

      1. Arrow direction will just make things more teachable for new users. I agree that for existing users, this doesn’t really help. And since it’s different, we have to learn new habits. So for me personally, no, this is not a good thing. It’s just through my lens as teacher that I see this as a positive.

  4. Rob, do you know whether power pivot will be included in all SKUs of Excel 2016 standalone or will power pivot only be included in EXCEL 2016 Professional PLUS? (As was the case with EXCEL 2013 until subsequently changed).

    1. I have heard nothing on that front and honestly haven’t even asked. I’m so frustrated by the whole 2013 Pro Plus fiasco that I’m subconsciously keeping my distance I guess.

      I wouldn’t be at all surprised if those decisions have not yet been made for 2016. These things tend to happen at the last minute, just like they did in 2013.

      1. As of now, PowerPivot is not showing up in the preview for Home Premium, but is in Professional. That said, you can load data in Home Premium into the Data Model… so the engine is there, you just can’t get access to that data model to do anything fun.

  5. I have tried to find information on backward compatibility of Excel 2016 Power Pivot models but have not been successful in finding any details. Does anyone know if you’ll be able to open 2016 models workbooks in 2013?

    1. Yes, you can open 2016 models in 2013. If you use any of the new DAX functions, you might not be able to navigate in the data model or certain measures will generate an error. But the workbook opens in any case and you should be able to modify it if necessary.

  6. I’m drooling over CONCATENATEX. So many possibilities, especially with ERP data. I also really like the name change back to “Measures”. It makes the perceived value of our work much higher, like taking a “town car” instead of a “taxi”.

  7. Wow, such great news, here ! Direct access and editing of MEASURES, new functions, Calendar…

    Do you know if the preview would work on a virtual computer (I don’t see why not but who knows…) ?

    1. I have the preview running on a couple vm’s and as expected no problems (Other than those associated with a beta product which has nothing to do with virtualization.)

  8. Sounds like great news! Can you have 2013 and 2016 on the same PC? and are there version issues that would prevent opening a 2016 data model with 2013?

    1. Both on the same pc? Officially no. You have to uninstall one to install the other. Marco Russo answers your second question in the comments above.

  9. Given that Excel 2013 can open a 2016 model, can we still restore an Excel 2016 model to a SQL Server 2014 SSAS Tabular instance as long as we avoid the new DAX functions not available in SSAS 2014?

  10. What happened to the right click on measure to edit mentioned above? Cant see anything different to 2013 but agree its tremendously irritating going to measures button. Is there some setting to allow the right click function?

    1. Mike, you can right click in the FIELD LIST itself, but NOT on the measures in the Values dropzone area.

      Which, I agree, is only half a fix. I emailed my buddies at MS just last night actually, with a slide deck of usability improvements I think we need in 2016. This is one of them, big time, and should be easy for them to do.

  11. I can not get Power Pivot on my new 64 bit machine with Office 2016:
    I get “Setup is missing prerequisites: This add-in requires Excel 2010.”

    PLEASE HELP, I MISS MY PP!!!!

    1. Johan,

      In Excel 2016, Power Pivot is no longer a downloadable add-in. If you have a version able to access the Power Pivot window (such as Pro-Plus or Excel stand-alone), you may need to just go to the COM Add-Ins dialog and enable Power Pivot.

      1. Thomas,

        You’ve lost me. Like Johan, I quickly discovered the lack of Power Pivot either built-in or as an add-in for 2016. The previous generation Power Pivot add-ins won’t install. Having upgraded to Office 2016, I’m told I cannot downgrade back to 2013. Fortunately, I have other machines with 2013 and can do my Power Pivot work there.

        There’s no sign of Power Pivot com add-ins to enable, and I have yet to find a link or an install for getting Power Pivot on any of the ribbons of Excel 2016.

        Can somebody who actually has Power Pivot running in Excel 2016, please provide a step-by-step.

        1. So, you don’t see the Power Pivot Add-in available if you do this?

          1. Go to “File -> Options -> Add-ins”
          2. Change the “Excel Add-ins” drop down to “COM Add-ins”
          3. Click Go…

          1. Answer found.

            I have multiple computers. The one I was using was loaded with apps from the home version of Office 365 and it had Excel home version, which doesn’t have the full set of power tools.

            I uninstalled, went to one of my E4 Office 365 sites (which has pro-plus apps) and installed the Pro version of Excel 2016. Aha! There it all is!

            Lots of “Excel 2016 Power Pivot” links fail to mention it’s only available in Pro.

            I’m once again a happy camper.

    2. You have to have the professional version of office (or the standalone version of excel) for it to work. Home and Business don’t have PowerPivot

  12. My favorite I think will be “Formula Fixup on Rename.” I imagined from PowerPivot day one that it can be very time consuming to rename measures that other measures use. Managers and clients often demand “truth in labeling” and “descriptive column names” instead of just the answer or data dictionary with descriptions for every measure or column. The data dictionary DAX Studio tool seems promising too, perhaps using DAX Studio and 2016, all older 2013 powerpivot models can be cleaned up. I hope the rename feature can go many levels deep.

    In pivot past, I would just pretty up the final report (overwrite the column names in the pivot table / use getpivot on a report sheet) and keep a separate list of Excel field names, explain that no Excel field names could never be changed under fear of breaking every filtered pivot table in the sheet.

    The naming conventions you and your guest posters suggest here are somewhat genre-specific (sales, etc.), I’m curious with rename whether you have ever asked the user base how different genres/industries have had to keep their column/measure names straight up to today?

  13. Will there be a way to write comments into the code, i.e., documentation? For example, you have a 10-line formula and would like to make notes for yourself or your team so time isn’t wasted decoding the formula.

  14. Will they quit having weird default colors in Excel graphs? For example why is the default color for “legend fill” dark blue?

    Will they quit having the default PPT table with no borders and all the fields shaded different blues, with white text? The first thing I have to do is change it to white, add black borders, and make the typeface black. Then I can modify and use color coding for my purposes, not Micro$oft’s purposes.

  15. It happens to me that I built a data model with Office365 version 2013, and a partner cannot update it using Excel 2016. He can read the data but cannot update it.
    It comes to me the idea that if upgrade to 2016 will lose my data model, since I will not be able to update it.
    It only occurs in some tables from Power Query, the tables straight from SQL data base work perfectly in both versions.
    Any idea?
    Thanks a lot.

  16. Anyone know where the PowerPivot field list is in 2016? It does not seem to appear in the PowerPivot ribbon with 2016.

  17. Just a general question regarding PowerPivot – do you recommend using 64bit Excel in order to take advantage of more RAM for large datasets? Are there any problems to be aware of with 64 bit Office 2016?

  18. it seems if we update to Excel 2016 we lose the automatic update of PowerQuery, unless we use office 365, so I am holding to my Excel 2013 pro 🙂

Leave a Comment or Question