"Microsoft Excel 2019 Inside Out", error at start of Chapter 17?

[rd18010]

I was going to submit an errata for the topic I asked out in my thread over in Power BI, but the Submit Errata page needs exact information I don't have, as I came across the book in a Google search, that had the Google Books page as one of the results.

So instead of that, I'll post the question in this thread. (The details are in the original thread in Power BI.)

Re-stated, in Chapter 17 "Mashing Up Data with Power Pivot", in the part giving the history of Power Pivot, the last bullet point has the incorrect sentence:

"With Excel 2019, the full benefits of Power Pivot are freely available to anyone using Windows versions of Excel [...]"

due to Power Pivot only being available in Excel Professional Plus 2019 and not in Excel Standard 2019, among the volume licensed products.

This error does not appear in the "Errata & Updates" page.

 

[MrExcel]

Thanks - I am checking with Microsoft about that particular SKU. However, I think they will argue that even if you don't have the Power Pivot tab, they've exposed everything from the Power Pivot tab in the regular UI.

 

[MrExcel]

I heard back from the Excel team in Redmond. They say that every Excel 2019 has Power Pivot, but due to a bug it is sometimes not activated.
The easiest way to activate it is to go to the Data tab and click on Manage Data Model. You will be prompted to enable the add-in, and after that the Power Pivot ribbon will appear.

 

[rd18010]

I spent last week following advice like that, and going back and forth with the IT dept at work, before posting here (actually the other thread) out of desperation. I'll explain with more detail here.

On Excel in my home Office 365 Personal, I can see how that button could be activated, if it wasn't already, as the COM Add-Ins has the "Microsoft Power Pivot for Excel" checkbox. And I can see the directory having the needed files at:
C:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in\

But, on Excel 2019 Standard, at work, the equivalent directory is not there, so the COM Add-Ins does not list "Microsoft Power Pivot for Excel" at all, so there is no checkbox to check. Hovering over the green icon that would "Go to the Power Pivot Window" if functional then following the "Tell me more" link and following "Start the Power Pivot add-in" has a link "Where is Power Pivot?" has a list of "Power Pivot can be found in the following Office products" with "Office Professional 2019" listed, but not "Standard".

And that volume licensing page seems to indicate that, too.

So what Microsoft is saying still seems confusing and conflicting to me.

What I'm asking is if you can personally verify that Excel Standard really does have Power Pivot available?

 

[rd18010]

The next troubleshooting steps in the Excel Power Pivot section of the help file (which duplicates whats on the Microsoft sites) is to check for the registry keys:

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\User Settings\
PowerPivotExcelAddin - key is present on home Office 365 Personal, but not at work in Excel 2019 Standard

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\
PowerPivotExcelClientAddIn.NativeEntry.1 - key is present on home Office 365 Personal, but not at work in Excel 2019 Standard

Widening the search, the most relevant explanation I can find is Power Pivot missing from COM Add-ins, still a Microsoft site.
What I would want to see is an Excel Standard 2019 equivalent of the third screenshot prefaced by:
"Select Excel- Add ins- PowerPivot and Run from My computer:"

Users of volume licensed Microsoft Office Standard 2019 likely don't have access to that screen, so can't simply look at it.

You can imagine busy IT departments around the world being asked about why there is no Power Pivot ribbon, or lack of functionality of the Manage Data Model icon, will simply look at the Help in Excel and point out that for 2019, only the Professional version is listed, and that is also what the volume licensing page at Microsoft says, so they go no further.

So, to correct this information problem, can someone post a screen shot the "Select Add or Remove features" section, for Excel 2019 Standard, to prove that PowerPivot really there?

 

[rd18010]

And speaking of confusion, how about the topic of Is Excel 2019 just Excel 2016 with a new name?

Maybe the internals of MSO 2019 didn't get the memo that it is not MSO 2016, and doesn't know to fully deploy the Power Pivot resources?

(Grasping at straws here.)

 

[rd18010]

Another web search found some more confusing information, and I thought of test to ask about.

I hope Bill can write an Excel Article to clear all this up.

One interesting answer on the Microsoft Community to the question of "activate power pivot add-in" in Office 365 by Rohn007 | MVP, on December 16, 2019 was:

"You don't have to do anything to activate PowerPivot in 365. Although some documentation still refers to PowerPivot as an addin, that is only for versions prior to 2016"

This is similar to what Bill may be saying.

OTOH, another site's article "How to enable the Power Pivot data analysis Add-in for Excel?" in the Excel 2019 / Office 365 section, where the writer states:

"Note: A far as i can tell, the Microsoft Data Analysis add-ins are now shipping as part of all Microsoft Office 365 subscriptions as well as Office 2019 Home, & Business and Home & student and Professional versions."

And has the screenshot:

Neither my home Office 365 Personal nor my work Office Standard 2019 have that marked item.

So for a test, I downloaded the Excel2019InsideOutCompanionFiles.zip files and extracted 17-PowerPivot.xlsm, and tried the Manage Data Model button on both computers. On Excel 2019 Standard at work I nothing happened.
But on my home Office 365 Personal, the button resulted in this screen:

Would there a way to get here without using the Manage Data Model button at work?

And if so, would that mean that Power Pivot is really there, but not as obvious as on Office 365?

What I am trying to find out is if I should proceed to get training material for the Power BI tools that are in Excel, or will I get stuck at some point?

(E.g., books like Microsoft Excel 2019 Pivot Table Data Crunching, by you know who.)

 

[rd18010]

Another look around found a few more Excel Power BI pundit's sites, with some mentions of the all Excel SKUs plan.
It may be possible to register with those sites, and pose my questions there.

My SKU is "Microsoft Office Standard 2019" and the Build Number is "Version 1809 (Build10827.20150 Click-to-Run)."

Something like this drama played out on another site (“Hey, Who Moved My (PowerPivot 2013) Cheese?”), that spilled over to this site (in Power BI), for Excel 2013, in a thread titled Does Stand-alone Excel 2013 (Non-Commercial) have PowerPivot, with a Verified: Yes, Excel 2013 Standalone Now Includes Power Pivot. (For real, normal people have it.) thread to confirm it.

So far I haven't found the equivalent thread for Excel 2019.

 

[MrExcel]

Thanks for posting your SKU. I will pass that along to the Excel team to see what they say. Since this is at work, it is possible that your IT department has used Group Policy to block the add-in.

Let me try to explain the difference between what is built-in to Excel and what is still an add-in.
1. Starting in Excel 2013, the ability to link two data tables was built-in to Excel. It was only exposed through the box "Add This Data To the Data Model" that you can see when creating a pivot table. That sentence is intentionally boring, underselling the power that was unleashed if you clicked it. The hassle with this method, you would have to define relationships after building the pivot table.

2. In Excel 2016, they added the Relationships icon to the Data tab. This allows you to define relationships before creating the pivot table. Also in 2016, if you right-click the table name in the PivotTable Fields pane, a menu item appeared for Add Measure... This lets you build DAX formulas in your pivot table.

That means, as of 2016, you had these benefits available to you:

• Join data from multiple data sets in the same pivot table
• Define 1-to-Many relationships between tables
• Use DAX to add formulas to your pivot table

At that point, almost every important feature of Power Pivot was exposed in the Excel user interface. You still couldn't see the data in the data model. It would have been tough to Sort Month Name by Month Number. But, between the "Add This Data to the Date Model" box and the Relationships, and Add Measure, you had almost everything. If you paid a bit extra (Office 2016 Pro Plus or the E3 version of Office 365), they would give you the COM add-in that let you browse the data model.

In Excel 2019, they gave us all the Manage Data Model button which is supposed to load the Power Pivot Add-in. This lets you browse your data.

The question at work: why is Excel 2019 Standard not reacting when you click Manage Data Model? My theory is someone at work is using Group Policy to prevent add-ins.

Try this: File, Options, Add-ins, Manage COM Add-ins.

Is PowerPivot there? Is it checked? Can you check it? Does that solve the problem? Is there any sort of a message saying that Group Policy is preventing this add-in?

 

[rd18010]

By looking up how to look up my SKU for the Excel at work, I found that it was apparently very old, maybe even pre-dating that bug you mentioned earlier. I am just an end user, and had to delve into matters that are really for the IT dept.

At the Update history for Office 2016 C2R and Office 2019 page at Microsoft, there is a 400 page PDF to download, that has Version 1809 (Build 10827.20150) as being for Oct 9, 2018, and in that same PDF in the "Non-Security updates Excel: Non-Security updates, the bug you pointed out is listed:

• Fixed a bug where Power Pivot did not appear in some builds/versions.

So there was a bug, that got fixed, but I must still have version that doesn't yet have that fix.
The bug is not something I can work around just by settings.

What is confusing is the the build numbers my work Excel reports match the "Office 365 ProPlus" product, not the "Office 2019" numbers. Even though the account screen for Office Updates says "Updates are automatically downloaded and installed." Yet, they actually aren't. The registry key does indicate that the product is Standard 2019 on volume licensing.

I've got many pages printed out, to try to make sense out of, but as I stated earlier in the thread, there may be others out there that don't have control over the Excel they are using. There doesn't seem to be Microsoft help page that simply says if you can't get the Manage Data Model button to work in Excel 2019, your version may be too old. Most of the help pages were written for Excel 2016.

So, it is likely that the bulk of the users here have enough control of their Excels that they won't be behind in updates, and many likely would have the Pro versions, anyways.

Pages like Overview of Office 2019 (for IT Pros) and Update Office 2019 (for IT Pros) make it seem like Microsoft would like end users to have their Excels updated periodically. My guess is that if my work Excel were brought up to date, that Power Pivot, meaning that green Manage Data Model button, might actually work. But all I have is pages of confusing information from Microsoft, and conjecture.