So in the book I’m forever writing, I have an example of how you can use the Data Model to effectively do VLOOKUPS. Take the example where we’ve got a transactional list of sales that has a product ID but no ‘friendly’ name or price, and another table that maps Product ID to price and friendly name.
We can use the Data Model to set up a relationship between these two tables…
…so that we can display friendly product names from the Price table against aggregated sales quantities from the Sales Data:
Sweet. No VLOOKUPs necessary. So…given that we’ve got quantity, and prices, how much money did we make? Let’s find out. First, lets’ drag that Price field into the Values area of the PivotTable and see what it gives us:
Well, that obviously isn’t what we’re after…it gives us the price of every item, not total revenue. Maybe we need to create a calculated field to multiply Qty against Price:
Oh wait…we can’t. Unless the average user has a premium version of Excel that includes the glorious thing that is PowerPivot (not to mention the wherewithal to not be daunted by a completely different, programmer-centric User Interface) then they can’t do this rather basic datamodel-ish thing.
So here we are…15.64% of the way through this century, and yet your average user still doesn’t have the ability to mash together relational tables in the world’s most widely used business productivity tool. (Or mash together flat files from different sheets or workbooks, for that matter.) Unless of course they go back to last century and do it like this:
I just don’t get this. What’s the point of exposing non-premium users to something like the Data Model if it doesn’t let them do diddly squat? Without calculated fields, surely it’s a big red herring that unnecessarily confuses users? (And okay, so if the Data Model on its own isn’t the hammer for this particular nut, what is?)
I thought I was missing something here, so asked someone smarter than me about these matters (Sam). Sam tells me:
From 2013 onward the Data Model and the DAX Engine are separate
The Data Model is only to create Relationships (Joins) – available to all versions of Excel. The DAX engine if for the Calculations – the Pro Plus and Stand Alone versions.
The only extra function on which you can aggregate data in the Data Model based Pivot Table is Distinct Count at the expense of Numerical Count and Product of a normal pivot. A Data model based pivot table disables both Calculated fields and Items as well as the ability to Group. So the short answer is No – you need DAX alternately use SQL to create the Joins
Don’t get me wrong…Powerthis and Powerthat is all very exciting. But Microsoft: How about showing the tiniest bit of love for the common man (and woman) who live in the common version. Those folk shouldn’t have to upgrade to some fully-featured business intelligence flagship product merely so they can mash data from within Excel together, surely? (And yes, they might be able to use PowerQuery for free to join their relational data together, but leaving aside the fact that they’d probably end up with a flat file in Excel after importing the data from PQ, it’s just not good enough that they would have to go learn another programmer-centric tool just to do something that should be childs-play in Excel itself. )
Just to be clear, I’m not one of those that think that PowerPivot should be bundled into all versions of Excel just because we want it to be. Microsoft have spent a lot of money on PowerPivot, and I don’t expect them to have a sudden attack of benevolence merely because it suits users. Theirs is a money-making venture. And their shareholders expect them to do the best job possible at that. But here’s the thing: It’s nearly 2016, and MS still have not given users the ability to do some pretty basic stuff natively in Excel, such as mash together data in separate sheets or workbooks and serve it up piping hot as a PivotTable. Don’t they know that in the real world, people have to mash together data from different sheets all the time? You get a sheet emailed to you from division A, a sheet from division B, and a sheet from division C. You need to mash it up to get an overview. A lot.
I’m not asking for Tableau here. I’m just asking for a core product that also moves with the times. Or at the very least, keeps up with last century.
Lest MS think this only applies to private individuals: many if not most of us corporate Excel users are users of the Excel core product only, and due to contracts with outsourced IT support “partners”, we are *locked out* from installing anything not on the list of basic products.
Good luck explaining to the managers who control the team budget why we should pay for extra, and fight the contractors to get it installed. People who understand how to get the most modeling and analysis value for the smallest payroll cost, don’t get to be managers in charge of team budgets in my experience, paradoxically.
If we’re going have to jump through the hoops of the corporate bureaucracy to purchase and install Excel enhancements, we may as well jump through those hoops to get Tableau or something else.
Yeah, good points Derek. In fact, in a recent job I had, I wasn’t allowed to install PowerPivot for Excel 2010 even though it was free, because the steering committee didn’t want to let users save even more data in their Excel files.
But again, the fact that you have to fire up something else other than Excel to join data sets that live in Excel is just crazy. Sure, you can use VBA or SQL. But I just cannot fathom why this kind of basic stuff isn’t native, and as simple to use as a VLOOKUP.
You can download the FREE Power BI desktop app at:
https://www.microsoft.com/en-us/download/details.aspx?id=45331
Yes, I can download yet another tool to do something that intermediate users should be able to do in Excel right out of the box.
Let me put it another way: it’s a workaround, with a very high barrier to adoption to the average user, or even to the advanced user in a locked-down environment. Even if it’s free, it’s a free workaround that interrupts my workflow in Excel.
Thought experiment: Imagine if MS took VLOOKUP out of Excel and made it available as part of a free add-in with an entirely new UI. Would that be as frictionless to users as having VLOOKUP in Excel, available to the average user? Nope. It would make no sense whatsoever to have that functionality decoupled from the app in which you want to use it.
Now, there’s always got to be a point at which MS decides “This bit goes in Excel, That bit goes in some add-in”. But my argument is that they are just too busy improving add-ins, instead of making some basic improvements to the core. I come across lots of average users who need to mash together data from different files. Nothing fancy, sometimes just the equivalent of a UNION ALL because the different templates they’re mashing together are exactly the same. Until I come along and give them a bit of VBA, they can’t do this cleanly in Excel. So they stuff a spreadsheet with a coulple of hundred thousand VLOOKUPS, or do a whole heap of manual cutting and pasting every month. And it beggars belief to me that in 2015 this really simple requirement isn’t satisfied by Excel from within Excel.
I hadn’t even heard of the Data Model.
The worst thing about this is that it is near impossible to work out what is or is not included in a particular version of Office. Searching the Office help for Power Pivot gives no hint that it isn’t actually included in most versions of Office, even those that are described as being the “full” package.
Even if we look at the full list of business plans:
https://products.office.com/en-us/business/compare-more-office-365-for-business-plans
there is not a mention or PowerPivot. Presumably it is included under “Self-service business intelligence”, which apparently you don’t need if you go for the “Business Premium” option, with 300 or fewer users.
Regarding getting the result we want, can’t we just add an external column with a formula (price x sum of quantity)?
Finally one positive thing to come out of this is that whilst investigating what Jordan Goldmeier was going to do with the Kryptonite, I discovered that he has recently published a post on Developing a JSON Excel Add-In With VB.NET (by Bjoern Stiel), which is just what I need at the moment.
http://optionexplicitvba.com/2014/05/09/developing-a-json-excel-add-in-with-vb-net/
Doug: Thanks for the link to Bjoern’s article. I almost took umbrage at his line VBA has become a bit middle-aged. So have I, but I still run plenty fast!
Yes, in my very trivial example, you can add an external column with a formula. But then of course you’ve got a formula pointed at a PivotTable, so you better drag that formula down a whole bunch of extra rows to capture the case of the PivotTable growing. An untidy workaround. Let’s use a more realistic example: Say I have 20 templates from different business divisions to amalgamate, and that I’m an intermediate user, comfortable up to the level of VLOOKUPs and PivotTables, but that’s about it. How do I bring all these templates together? Manually? Pathetic, in this day and age.
How hard would it be for Microsoft to add something new to Excel? Why is there such a proliferation of Powerthings that address limitations of Excel, and yet there’s not much going on to acutally fix some pretty basic limitations of Excel? Again, I’m not suggesting they give these intermediate user types Powersomething for free. I’m just suggesting that it would be a good look if you could use Excel to bring together data from Excel within Excel.
Speaking of PowerPivot, if I was MS then as a way to spread the cheer of PowerPivot, I would let all users of any flavour use it, within Excel’s row limitations. Then once you get over 1M rows, display a message like “Sorry, that size data set is restricted to the full version. Click here to upgrade…. That would also help with my examples in some cases. I say in some cases because I still think MS need to put some common-man-friendly data mashup functionality into Pivots, and PowerPivot would likely be too scary for many users who just want some basic mashing.
Jeff – I agree totally with your main points. It’s even worse for people like me using Excel for engineering and science applications, which seem to be totally off the MS radar.
I came across this article which has some good info:
http://sqlblog.com/blogs/marco_russo/archive/2013/02/18/the-right-version-of-excel-2013-for-using-powerpivot-powerpivot-excel.aspx
Also a link to a post by Ken Puls explaining how you can get a volume licence for about $30.
Even so, I think for someone like me with a mega-business consisting of myself a cat and 3 hens the stand-alone version might be the way to go.
Not much help for people who have to work their way around penny-wise managers, I know.
I work on my own as an independent contractor.
I went through the entire exercise of trying to purchase Excel 2013 with powerpivot. I bought a new pc with Office already installed. Excel 2013; no powerpivot.
Did some investigation and thought I needed a standalone version. I went back and got the physical disk with Office 2013; still no powerpivot.
Finally found this article: http://datapigtechnologies.com/blog/index.php/understanding-office-365-plans/
Turns out I need the Enterprise version of Excel, which of course I do not have or I pay a monthly fee to get Office 365 which has it.
If someone knows another way around this, I would surely appreciate it, but it’s hard to believe that it would be so stinking difficult to get something that was a free add-on for Excel 2010.
And if it is so difficult, who will want to use it?
Maury – according to the link I posted Power Pivot was not included in the original stand alone Excel 2013, but it is now (and now has a space), since August 2013.
http://sqlblog.com/blogs/marco_russo/archive/2013/02/18/the-right-version-of-excel-2013-for-using-powerpivot-powerpivot-excel.aspx
Worth checking out anyway.
@Murray
The best option for Small business / Individual users is Office 365 Pro Plus (12$ a month)
The 2nd best option is Office 2013 Professional Plus (The advantage is you get the ISO, the only problem is Power Map is available as an unsupported add in)
What that’s about Kryptonite?
Access is one of the tools I use when I DEVELOP a Database app utilizing the access database.
However, end users don’t need Access when they USE this app.
I suppose that’s why Access is not part of any plan of Office 365 Business but is only available in Office 365 ProPlus and E3.
Likewise:
Power Pivot is one of the tools I use when I DEVELOP an Excel app utilizing the Data Model.
End users don’t need to have access to Power Pivot when they USE this app.
I suppose that’s why Power Pivot is not part of any plan of Office 365 Business but is only available in Office 365 ProPlus and E3.
Btw, in Excel 2016 there is much less Power this and Power that. It’s just Data Analysis tools.
http://blog.crossjoin.co.uk/2015/09/01/excel-2016-bi-branding-changes/
It is crazy and very confusing. I’m climbing the learning curve on all of this and wasting many valuable hours on understanding word salad and trying to figure out how and where to get PowerPivot.
FYI, here is the official list of exactly where PowerPivot is and is not included:
https://support.office.com/en-us/article/Where-is-Power-Pivot-aa64e217-4b6e-410b-8337-20b87e1c2a4b
I still had to deal with MS via chat trying to understand what I can do. I have Office 365 Home. Turns out I cannot *also* install the standalone version of Excel 2016 to get PowerPivot. So I’m going to need to subscribe to Office 365 ProPlus, then delete Office 365 Home from my computers, unsubscribe from that (I expect to get a pro-rated refund minus a month) and then install ProPlus. All to get PowerPivot. It really is absurd. Why can’t there just be a paid upgrade?