Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,974 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Using the Slicer feature for dynamic PivotTable filtering
- Sharing workbooks via e-mail, the Excel Web App, and SharePoint
- Using Paste Preview for more effecient copying and pasting
- Inserting Sparklines to see patterns in data
- Taking advantage of enhancements to the Conditional Formatting feature
- Analyzing data from multiple sources using the PowerPivot for Excel add-in
- Maintaining file compatibility with older versions
Skill Level Appropriate for all
In this movie, I want to show you the new PowerPivot plug-in. Say that three times fast. PowerPivot was originally called Project Gemini, and you might have heard that name bantered about a little bit. This is a free plug-in from Microsoft, and you could get it at PowerPivot.com and what it does is it puts PivotTables kind of on steroids. You can query Excel sheets, databases, and many other sources all at the same time. And this generally falls under the category of business intelligence, and let's talk about some of the main advantages over regular PivotTables.
PowerPivot has the ability to handle millions and millions of rows of data. You may know that Excel 2007, and now 2010 will give you about a million and change rows. But using PowerPivot, you can query tens of millions of rows from a database that you are looking at. You could import data from many different types of databases. We will look at that as we go. You can import data from web sites, text files, just about anything that anybody can send you; as long as there is a structure to it, you can query the data. And what's also nice is that the data will refresh automatically.
You might know that when you're dealing with a regular PivotTable, you have to refresh the PivotTable manually every time there's a change to the underlying data. Well, you don't have to do that with PowerPivot. And not only that, but once you've done your analysis, you can publish your PowerPivot analysis to a SharePoint Portal, if you're using one. So let's take a look. So in Excel, let's start with a blank empty workbook, and I already have the PowerPivot plug-in installed, so you can see up here there is a PowerPivot tab.
So let's click the PowerPivot tab, and the first thing on the Ribbon bar is to launch the window, so click that. It brings it up. So what we want to do is start querying some data. So click over here where it says From Files and then From Excel, and the friendly connection name is Excel, here in this window, and click Browse and if you have access to the data files, go to the Chapter 5 folder and double-click 05_02 powerpivot sales volume.
So if you want to make sure that the connection with your data source is working properly, you can click Test Connection. It tells us that the connection is successful. By the way, we can see that Gemini is still up there in the Title bar, and click OK. Well, because our data source is on our own computer, it's really not a big surprise that the connection is okay. This is really when you're querying data that may be on a network source somewhere, and click Next. And this tells us what part of that data are we getting.
Now, if we want, click Preview & Filter and here this is showing us that Excel workbook, and we can sort, and we can filter any of these rows before we bring them in. I'm just going to leave it the way it is and click OK, and click Finish. And then it tells us that it's successful. That's great, so click Close. Now, you see in the PowerPivot window, we see the data, and down here in the lower-left corner we can see the tab that is showing us that is Volume by month. Well, let's bring something else in. So let's go back here From Files, From Excel, and again click Browse, and this time let's bring in the PowerPivots sales reps worksheet, and again if you want to Test Connections, you can do that.
If you click Advanced, this gives you all sorts of information about the data. Most of this you can ignore, especially when it's Excel. But I wanted you to see that it's there. I will click OK, then click Next, and here it shows us what it's bringing in. You can Preview & Filter this also, if you want. Same thing. I will just cancel out and click Finish. It tells us it's successful. So click Close. Now you see, down here in the lower-left, we have Volume by month tab and the Reps tab.
Well, I saw that you could query data from many different sources, so let's get some data from a database. And up here, the upper-left, click From Database. Then you see we have some common databases, SQL server access and so on, but I just want to show you this. Click Other Sources. And I will move the dialog box here. And this really shows you the power of PowerPivot: SQL Server, Oracle, Sybase, Informix. These are the types of databases that are used by huge enterprises, and you see all so many different places, all different types of databases that you can get.
Well Access is here, but I am going to cancel out, just to show you that if you do want an Access database, it's easier just to get it from the first level. You don't have to go all the way into that menu. Again, click Browse, and also in the Chapter 5 folder of the Exercise Files, we have this Access Database. You can see this accdb means it's Microsoft Access Database. And double-click, and again if you want, you can test the connection, go Next, and we are going to choose from the list of tables. We are not going to write our SQL query here.
There is really no need for this data. And it shows you the same thing. We can Preview & Filter, if we want. This is just a small table anyway. And click Finish. Successful, click Close, and now we have that code table. It's just the code of our products, and we could, at anytime, switch back and forth between these. So let's say, for example, in Volume by month, I only want to see the Northeast. So I can click this. I want to just turn these off. I am going to turn on Northeast. Click OK, so I can see this. Now when I go to Reps, again if I want, I can show just the Northeast, so you can see we can do this independently.
I am going to turn these back on, and I am going to clear that filter. I am going to go back here and clear that filter. Well, we want to do a little bit of manipulation with this data, and if you're familiar with databases at all, you might know that you have to tell your database application, Access in this case, how your data are related, and to do that we set up what are called relationships. So let's click the Table tab - we are still in the PowerPivot window - and over here click on Create Relationship.
So we want to choose the first table, and if Volume by month is there, that's great, choose that. And the Related Lookup table that we want to relate that to, let's choose Code Table. For Column, let's choose Size, and then the Related Column Lookup, let's choose Product name. So this is telling us which table and column is related to which lookup table and column. And click Create. Let's create one more relationship. Click that button. And this time let's say we will stay here with Volume by month, but now we want to relate that to the Sales Reps.
The Column, let's change that to Quantity. And we want this also related to Quantity, because what's happening is we have two columns called Quantity in two different tables, and that's how these data will relate to each other because you always have to have at least one thing in common between them. And click Create. Let's go back to the Home tab. Now that we have our data related, let's do something useful with it, and let's create a PivotTable. Now over here on this tab, we have PivotTable, and we have a whole bunch of choices.
Let's start simple, just with a single PivotTable. Click that, and we will put this one a New Worksheet, which is fine. Click OK. Now, this looks like a regular PivotTable, but look at this. This pane shows us all the data from all the sources that we queried. Now, a regular PivotTable isn't going to do that. You see up here it also tells us Gemini Task Pane. So let's go and fill out this PivotTable. So the first for the Report Filter, let's take Month, and bring that in here.
Let's open up Rep, and we will take Rep Name, and bring that into Row Labels. Let's take Region, and also bring that into Row Labels, and you'll see we are building this up. For Column Labels, let's take Product Name for Column Labels and for the sum of Values, let's take Quantity. I am going to take this Quantity here. And look at this. So this now shows us which reps are selling which items.
This is now just like a regular old PivotTable, and we can collapse and expand, and do all kinds of great stuff that you normally do with a PivotTable. Well, let's do one more. We will go back to the PowerPivot window, again click PivotTable, and this time - this is really cool - choose Four Charts, and again, we will put this on a new sheet. I need to zoom out, so I am just going to hold the Ctrl key and roll the wheel backwards a little bit. Let's select Chart 1, and we will start building this up.
So when we have that selected, we go in here. Let's open up Reps, and let's take Rep Name, and we will make that an Axis field. And let's take the first Quantity, and that will be the sum of Values, and look at that. Now, let's go click Chart 2, and for that, let's take Region. And we will make Region an Axis field, and we will take Quantity and make that sum of values.
So again, we have some more useful information. Let's select Chart 3, and for Chart 3, let's create a Report Filter for this. So let's take Region, and we will make that the Report Filter. For Axis Field, Let's take Rep Name. And for Sum of Values, we want size. So now we see what size our sales reps are selling. Now let's go here to Chart 4. And the Axis Field, let's choose Month.
For the Sum of Values, let's choose Quantity from the Reps table. There we go. Now at anytime we can go back to the PowerPivot window. If we want, we can change things. We could even close it, because keep in mind, this is a plug-in that's just putting the data into Excel. So I am actually going to click this, just to show you that we can continue manipulating these PivotTables, even though PowerPivot is closed. Also, in this chapter, we talked about Slicers. So let's put that to use here.
Let's click the first chart, and remember Slicers are things we insert. So go to the Insert tab, and over here click Slicer. We want to slice this by Rep Name. So choose Rep Name, click OK, and I am just going to move this over here. You see the pin gets out of our way. I just want to stretch this out. Let's do this also for this chart. So like this chart, go back to Insert, Slicer, and again let's slice this by Rep Name.
Click OK, and we can drag this right over here, and again stretch this out. So when we have Chart 1 selected - and if you want to you can just close that - now we can filter by any Sales Rep, and if we decide we no longer want to filter, of course we can turn the Filter off, and let's click on this chart over here, and again I'll close this. We can sort by any Sales Rep. And then if we want, we can turn that filtering off.
So this is just a simple example using a small number of data records. But if you need to handle very large data sets, not only will you find this helpful, but amazingly fast, especially if you normally have to go through the process of modeling data warehouses.