Locked lesson.
About this lesson
Unfortunately Excel is not just Excel anymore. What you get depends on which "SKU" you have installed. In this module we will look at how to ensure you have the correct SKU as well as the other components you may need to download to build best in class solutions.
Exercise files
There are no related exercise files for this lesson.
Quick reference
Acquiring Power Pivot and Power Query
An overview of where to get Power Pivot and Power Query.
When to use
When you are unsure if your version of Excel has these add-ins already installed.
Instructions
The Power Pivot User Interface is included only in the following Office 2013/2016 versions/SKUs:
- Office Professional Plus
- Office ProPlus (Subscription)
- Excel (Standalone Product)
To enable the Power Pivot tab in Excel:
- Excel 2016 & higher:
- Go to File --> Options --> (Advanced) --> Data
- Check the box to “Enable Data Analysis add-ins: Power Pivot, Power Viewer and 3D Maps”
- Excel 2013:
- Go to File --> Options --> Add-ins --> Manage COM Add-ins --> Go…
- Check box next to “Microsoft Office Power Pivot for Excel 2013”
Power Query is a free download from Microsoft for Excel 2010 and 2013. It comes already included in Excel 2016 and higher.
You will find Power Query:
- Excel 2016 & higher: Data Tab in the Get & Transform group
- Excel 2010/2013: Power Query Tab
Hints & tips
- The Power Pivot UI is NOT included with any other Excel 2013 or 2016 version (SKUs containing Business, Premium, Enterprise, Personal, or Home)
- Microsoft released monthly updates for Power Query
- 00:05 In this course, we're gonna make use of two components to build modern business
- 00:09 intelligence solutions using best practices.
- 00:11 The first one is Power Pivot, the second one is Power Query,
- 00:14 let's make sure first that you've got Power Pivot.
- 00:18 So, the nice thing with Power Pivot is it's actually built into Excel 2013 and
- 00:23 higher at least, it sort of is.
- 00:25 The reality is you have to be using the correct skew or
- 00:29 stock keeping unit of Microsoft Office.
- 00:32 That means that in order to get the Power Pivot tab,
- 00:35 you must be using Office Professional Plus or an Office ProPlus subscription,
- 00:40 or alternately, a stand-alone version of Excel.
- 00:43 What this means is that no matter how you bought your software,
- 00:48 if it doesn't contain Professional Plus or ProPlus.
- 00:51 Or if you haven't bought it and installed it over top as just the Excel product,
- 00:56 you may not have access to Power Pivot.
- 00:58 Things like premium SKU do not have Power Pivot in them and this is something that
- 01:03 can be a little bit frustrating, so no other SKU has this interface.
- 01:07 Now the key thing is that it's just about the interface to build new models.
- 01:11 You can have somebody on Excel standard, they can refresh a Power Pivot model,
- 01:15 they just can't get into the editing interface in order to actually do things.
- 01:20 So you wanna make sure you have Professional Plus or ProPlus on your
- 01:24 Office side and you can find that in the file account in order to get that.
- 01:28 Or alternately install Excel over top of your existing Office installation, and
- 01:32 that will actually give you Power Pivot.
- 01:35 So how do you know that you have it, where do you find it?
- 01:38 Well, once you've got it activated,
- 01:40 it should actually give you a power pivot tab.
- 01:42 If it doesn't, in Excel 2016, you gonna go to File> Options> Advanced and data.
- 01:49 Now you may or may not, depending on how current
- 01:52 your SKU is need to go to Advanced, it may just be File > Options > Data.
- 01:57 And you're gonna check the box next to Enable Data Analysis add-ins, Power Pivot,
- 02:01 Power View and 3D Maps, okay?
- 02:03 There's a one check box for that, so you just check that.
- 02:06 In Excel 2013, you would go to File> Options> Add ins.
- 02:11 Down in the bottom, there's gonna be a little drop down box called
- 02:14 Manage COM Add-ins, and then you choose Go on the bottom right hand corner.
- 02:19 Inside that, it'll pop up a little window of all of the COM Add-ins that you have
- 02:23 available and installed on your system, and
- 02:25 you'll check the box next to Microsoft Office Power Pivot for Excel 2013.
- 02:29 It's a little bit of a hunt to try and find those,
- 02:31 once you've done that through either route, Power Pivot should show up.
- 02:35 If it doesn't, you're gonna need to talk to your IT department for support.
- 02:41 Now what about this other tool, Power Query?
- 02:43 Well, Power Query is a tool that we use to reshape data to make modeling easier.
- 02:48 It's a really important component, and the best practice to actually run through
- 02:52 Power Query, in order to build your Power Pivot models.
- 02:54 And that's why this course actually only supports Excel 2013 and
- 02:58 higher, because it's not quite as rich to work with in Excel 2010.
- 03:02 Having said that, you can get this tool in Excel 2010 and 2013.
- 03:06 It's a free download from Microsoft and you'll need it if you're on Excel 2013 in
- 03:11 order to actually complete working through this particular course.
- 03:14 You can get that here or by doing a Google search for download Power Query for Excel.
- 03:22 That term will get you right to this page and you can download and install it.
- 03:25 In Excel 2016, you know what, you're fortunate enough Power Query is built
- 03:29 right in so you didn't have to worry about that.
- 03:31 Now the connectors you see on screen may vary from what I have,
- 03:34 that's because of the SKU that you would have purchased.
- 03:37 But at the end of the day,
- 03:38 Power Query again will allow you to refresh anything that is built.
- 03:41 Everything you'll see in this course,
- 03:43 you're able to do with any SKU of Power Query, so that won't be a problem.
- 03:46 So if you're in Excel 2016 It's built right in, if you're in Excel 2010 or 2013,
- 03:51 you'd have to go and actually download Power Query manually and
- 03:54 install it with your Office.
- 03:55 So once I have Power Query installed, where do I find it?
- 03:58 Well, it's actually moved around as Microsoft is rolling it into the product.
- 04:03 In Excel 2016,
- 04:04 you're gonna find this on the Data tab on the Get and Transform Group.
- 04:09 The view that you're looking at on screen right here, is the very latest view where
- 04:13 Microsoft has actually gone and harmonized the ribbon so
- 04:16 you can see that there's a button on the very left-hand side that says, Get Data.
- 04:21 If you're in Excel 2016 and you don't see that, you wanna look about six or
- 04:25 seven buttons to the right, and you'll see one that's called New Query.
- 04:29 So, older versions of Excel 2016 that haven't been updated,
- 04:33 you'll find your new queries under New Query on the Data tab.
- 04:37 Newer versions of Excel 2016 will have it on the Get Data tab.
- 04:41 Whatever I call out to go to a new query that's where it's gonna be is on
- 04:45 the Data tab under one of those two buttons.
- 04:47 In Excel 2013, it's a little bit different again,
- 04:50 you actually have an entire Power Query tab so all of the commands will be there.
- 04:54 And on the Power Query tab you would see from file from database from online
- 04:58 services it won't be buried under a menu so that can be a little bit confusing.
- 05:02 Now the last thing about Power Query that’s worth knowing is that it actually
- 05:06 gets updated every single month.
- 05:07 There’s new features that come out, they add more robustness to it,
- 05:10 they fix a few bugs that happen in there every now and then, so
- 05:14 you wanna keep your Power Query up to date as much as you can.
- 05:18 In a Subscription Versions of Excel 2016, this happens for you automatically so
- 05:22 as long as you see that subscription product on the right you're good to go
- 05:26 these things are just gonna come in on you on a regular basis.
- 05:29 Non-subscription versions, well unfortunately it's not quite so
- 05:32 good there you only get bug fixes no new features.
- 05:35 In fact, the new feature set, if you've installed under a volume licensing build,
- 05:40 is not updated since October, 2015, which is a real, real shame.
- 05:44 You want to be on subscription if at all possible, the product is much more stable,
- 05:48 and I'll talk about that near the end of the course as well.
- 05:51 In Excel 2013, because it's not a subscription product in quite the same way
- 05:55 here and Power Query's a separate add-in, you actually have to go down and
- 05:59 manually download it and install it.
- 06:01 Now you don't need to do it every month,
- 06:02 I recommend if you've got an IT department that actually helps you with these things.
- 06:06 You only bug them about once every three months to try to get some new features
- 06:08 there, cuz you don't wanna wear out your welcome.
- 06:10 But at the end of the day, it is a manual process there, Excel 2016,
- 06:14 automatic if you're on subscription, Excel 2103, it's a manual download.
Lesson notes are only available for subscribers.