Mastering Data Analysis with Pivot Tables

Pivot tables. In this section, we're going to take a look at how to work with data using pivot tables. We've already created some pivot tables and we're going to recreate them so we can show you what the process is like of actually creating one.

So I have some cafes that serve drinks and those drinks come in several sizes and also several prices. So I want to be able to compare and analyze the different coffee drinks, their sizes and their prices. So to create a Pivot Table, you're going to click anywhere in the table.

Now before you actually create the Pivot Table, you need to make sure that your data is organized in a certain way. You need to make sure that there are no blank rows or blank columns in your data. Also, you should have titles for each of your columns and I do.

So at a minimum, you'll need structured data to create the Pivot Table. I'm going to go to Insert. From the Insert tab, I'll go to the first command Pivot Table on that ribbon and I'll click Pivot Table.

This opens up a dialog box where I will have two options. I can create a new Pivot Table on a new worksheet or create a new Pivot Table on an existing worksheet. I'm going to choose existing and I'm going to create the Pivot Table in the same worksheet that the data is located in.

I'll create it right over here and I'll click OK and now I have a Pivot Table ready to be created and filled in. If I take a look at this first Pivot Table, we are looking at the names of the cafes in a column, but these are actually row labels. So I'm going to select cafe and I'm going to move that into rows, pretty much the same as what I have here.

Now, what do I have for columns? Well, for columns, I have the sizes of the drinks. So I'm going to take size and I'm going to move that over into columns. Now, the only thing I'm missing is the information in the middle, which of course is the price.

So I'll take price and I'll move that into values. The kind of field that you should bring into values is a numeric field. You should bring in a field that can have math performed on that field.

There are some cases where you may want to bring in a text field to get a count, but otherwise bring in a value that is numerical that you can perform some kind of calculation on. I'm doing a pretty good job in terms of recreating the Pivot Table. So there are a couple of things that I want to do to make this look exactly like what I have here on the left.

I don't have grand totals. This may not be as important for this display. So I'll head over to the design tab and from the design tab, there is an option to control grand totals for this Pivot Table.

I'll click the dropdown here and I'll turn off grand totals for both rows and columns. I'll click off for rows and columns. And there we go.

I like the formatting that I have here. If I didn't like the formatting, I may need to right click on any of the numerical values and choose number format. Format cells will only format what you select, whereas number format will format the entire field.

I'll click number format and I have what I want here, which is currency with two decimal places. So nothing I need to change here, but if this was not in the format that you wanted, then you can go and make that change. Now I want to add some coloring.

I want to change the color format or the color scale to one of two possible colors. I'll go to the home tab. I'll choose conditional formatting and from conditional formatting, I can choose color scales where the highest value is green.

This would be advantageous to the person selling the coffee. For them, the highest price coffee is what's going to generate the most revenue for them. So they probably want that to be green.

Green for them means money and the highest price coffee that they need to sell to make the most of it. Now if you're a buyer, you might be interested in a lowest price coffee, in which case you'll choose color scale and you'll choose the second color scale. If you choose, I didn't select all the values, so I'm going to undo what I did and make sure to select all the values before I apply the color scale.

Now I'll go to color scales and I'll choose the second color scale. Okay, so now the lowest value is green. So it just depends on your perspective.

I'll keep it with the lowest priced green so you can see the contrast between the original and the version that I created. Now I want to use something called an index calculation. So what is index and why are we using it here? Well, it's mentioned here at the top.

The Pivot Table, first of all, is going to show data as index values for meaningful comparisons. Index values present the item's importance compared to other items in the same row or column. A value greater than one means the item has greater importance.

A value less than one means the item has less importance. Now if I wanted to create another Pivot Table, I could go through the same process that I went through to create the first Pivot Table, but I can also just copy this Pivot Table and then paste. Now I know I'm going to have the same information, but what I can do is change what's being calculated here for the values.

And so I can right click and choose value field settings. From value field settings, I want to choose show value as because show value as will give me the opportunity to select a different type of calculation. When I click show value as, it currently says no calculation.

I'm going to click the drop down and the option I'm looking for is called index. It's towards the bottom and there it is. I'm going to select it.

And now all I have to do is click OK. Now automatically you're going to see a lot of decimals, but if you choose number format, you can convert this to two decimal places. I'm going to choose number, which by default uses two decimal places.

Now all I have to do is click OK. And that looks much better. That looks exactly like what I have here, except I have a different color scheme.

That's because I used a different color scheme for my original Pivot Table. So what can we make of index values? Now this is not a perfect science, but let's take a look at our original tables. So here three dollars is the highest priced coffee.

So compared to other values, it's going to be higher in the row and almost as high in the column, and that's why it's one point zero three. It has more importance, whereas the lowest priced coffee is one dollar. And you can see here it has the lowest value because that is point eight four.

And so in comparison to one eighty five and one dollar, which is practically the same here, it has less significance in the column. It definitely has less significance because two dollars and a dollar seventy eight are greater than one dollar. And so that's why it's point eighty four.

Now, it's not an exact science because you have to take into account both the column and the row. So it gives you some general sense of importance. OK, so that's our first example of working with a Pivot Table.

Let's create another Pivot Table using the information down below. Now here we're looking at categories of products that are related to camping products. We have locations that could be a stand in for store locations, and then we have units, the amount of items sold.

Now, we've already done the work here to figure out the units by the products and the locations. We have if I click in the Pivot Table, I can see, yes, product is rows and location is the column. And in the middle we have the comparison.

Now, the comparison is units. It was just rename comparison here, but it's actually the units. So I can tell because I can see the checkmark here for units and that has to be included if it's checked.

And it is, but it's called comparison. We have the index here. If I click here, pretty much a duplication of this, except we're looking at values related to index.

So let's not do the same work here. Let's create a Pivot Table on a brand new worksheet. I'm going to click in the data and I'm going to go to the insert tab, ribbon tab and click Pivot Table.

Now this time I am going to create a new worksheet. So I'm going to leave it at new worksheet and then all I'm going to do is click okay. And now I'm going to create a Pivot Table on a brand new sheet.

One of the first things you should do is probably rename the sheet. So you know how to find this sheet later. I'm going to call this pivot one.

Okay. So what information do I want to bring in here? I usually tell people when you're working with a Pivot Table, Excel goes a long way to telling you what you should do. Even if I don't tell you what you're supposed to do when you create a Pivot Table.

If you just look at the instructions, they really guide you really well here. They say to build a report, choose fields from the Pivot Table field list. Well, what if you don't know what the Pivot Table field list is? Well, they drew you a picture.

Oh, this thing over here looks like what's on the right. Hey, by the way, it's called Pivot Table fields. So this must be the list.

And apparently if you click some check boxes, it will generate a table of information over on the left. So for this, I think I'd like to focus on category and I'd like to focus on units. Now on the PC, if you click category and units, and if you need this as a numerical value, it'll automatically get added to values.

You could also right click on units and then you can choose from here to add it to values. So in addition to checking units, you can drag and drop it and put it into values, or you could right click and choose add to values and it adds it there. Now we're going to do several calculations.

Let me zoom into the spreadsheet. Sum of units is the base value. These are the values that I want to compare.

I want to do additional calculations based on these units. So it's completely fine for me to take units and bring it in more than once. I'll take units and bring it in a second time.

I'll take units and bring it in a third time. I'll take units. I'll bring it in a fourth time.

You can bring it, you can bring this in as many times as you need for all the different calculations you're going to work with. So the first column, I'll choose a new title. I'll just name this total units.

Maybe I'll auto fit the column by going between columns B and C, that line that separates them and double click. And now I want to take a look at the second column. What I'd like to see is the percentage of these categories.

I'm selling three categories of products. What is this value broken down as a percentage? So I want to go to value field settings. I'll simply right click here.

There are many ways to go to right value field settings. I could also click the button down here and go to value field settings. I can right click here and look for value field settings.

I could access it from the Pivot Table analyze tab by simply clicking field settings. I could also access it by double clicking on the title. It brings up value field settings.

But traditionally you may just want to right click and choose value field settings. Now the calculation I want is going to be under show value as. You have basic calculations that summarize data on show value as, but calculations will be found on show value as.

Currently it says no calculation. That's what I want to change. I want to change this to percentage of grand total.

If I want, I can change the title here and then it will reflect up here. So I'll do that while I'm here. Percentage of grand, I'm going to say GT just to keep it short, grand total.

I'll click okay and there is my percentage of grand total. If I want, I can go into value number format and I can decrease the decimals. Maybe I don't need those extra decimals.

I'll click okay. Very good. So that's the first calculation I want to show you.

There's another type of calculation that you can display called a running total. So I have my original units here. I want this to represent a running total.

So I'm going to right click and choose value field settings. I'm going to go to show value as and I'm going to choose a different type of calculation. I'll click the drop down here and I want to look for running total.

There is running total in. I'm going to select that and I can change the title or I'll do it later. Now, how do I, what do I want a running total for? I have several fields to pick from.

I want running total for category. It's already selected. So that's what I'm going to go with.

I'll click okay and there we go. What does this represent? Well, we start with 391 and what happens when you take 391 and you add 743? Well, you get 1134 because that is the total of 391 and 743. If you look at the status bar, you'll see the sum there and that's what you have here.

Now, what if you take that 1134 and add 506 to it? Well, if you take the 1134 and add 506 to it, you get 1640 and ultimately that is the total that you have here. So you're getting a running total where you're taking each of the values and you're adding them up and getting a total. So I can change the title here.

I'll call this running total. Okay. That fits perfectly.

All right. Now I want to do a different kind of calculation. Now I see the difference in the values, but I would like to see the difference as a percentage.

I want to see the difference as a percentage from one value to the next. So I'm going to right click. Now I could go to value field settings, but here's a secret.

In the right click menu, there's an option to choose show value as and get your calculations right from here. I don't have to go to the dialog box to choose a calculation. So the calculation I'm looking for is percentage difference from, which is right here.

How would I like to see the percentage difference from? I would like to see it based on category, but based on the previous value. I want to know when I go from 391 to 743, what is the difference as a percentage? So I'll click okay. And this is where I can see the percentage.

To go from 391 to 793, that is a 90 percent difference. It's not exactly 100 percent, but it's 90 percent. To go from 743 to 506, that is a decrease of 31 percent.

So this is a good way to see if values are going up or down, but be able to see that in terms of percentage. All right, so that is percentage difference from. I now want to take a look at the, well, let me change the title.

I'm going to go over here. Cancel that. I'll type percent.

Percent. All right, and let's take a look at two more calculations. I'm going to go here.

I would like a ranking. I would like to see the ranking of the values here from largest to smallest. So I'm going to right click and go to show value as, and we do have an option called rank.

And you can either rank smallest to largest or largest to smallest. So I'm interested in the largest value. That would be number one, then the second largest, and the third largest.

So I'll click rank smallest to largest. I mean, largest to smallest. And I do want this for category, so I'll click okay.

And there is my ranking. And as you can see, number one for largest is 743. Number two for largest is 506.

And the smallest value in third place, we have 391. So these are all the different types of calculations you can create from the Pivot Table. The Pivot Table already has built in calculations, but you may be looking for a calculation that doesn't already exist.

So I'm going to show you how to create that. If there is a calculation that doesn't exist that you want to create, you can go to the Pivot Table analyze tab and go to fields, items, and sets. When you go to field items and sets, you can click calculated field.

When you click calculated field, the window for the calculated field comes up. And the first thing you want to do is give this a name. So I'm going to call this 10% difference or just 10%.

So maybe I want to evaluate these values and see what 10% of these values are. It's a simple calculation. Maybe for some reason, I need to see 10% of these values.

You'll know it's accurate because it's very easy to figure out the 10% of a value because you're just basically moving a decimal place. So I'm going to call this 10%. Now I'll enter the calculation.

The calculation is not zero, so I'll backspace on zero. I'll keep the equal sign because you always need that for a calculation. I'm going to either click the insert button to insert the field, or I can simply double click on units and it will put it right there.

And what I want to do is I want to multiply that by 0.10. Or you can as well just write 10 and use the percent sign. That will also work. But I want you to take a look at underneath units and also underneath units in the Pivot Table field list.

There's nothing underneath there, but that is about to change when I click add. So I'm going to click add and now I have that field added. All I have to do now within the dialog box is click OK.

And I have my calculation. So I'm going to change this to just 10%. So I'll click on the title.

I'll just call it 10%. Now I anticipate I'm going to get an error message. So this is something that happens.

You can't name a column with the same name as a field that you've created. If I click OK, I get this error message. Pivot Table field name already exists.

So here's a trick. If I just press the space bar, that is enough to make that a different name. And when I click OK, it lets me use it.

For anyone else looking at this, they might not even notice the extra space, but I'm able to use the name that I want. So that is 10% of the units. And like I said, it's just a difference of a decimal.

And so you can confirm that calculation works. One thing that you may want to do if people are not clear where 10% came from, they might look to the original table. For instance, up in Pivot Table Analyze, if you click the dropdown here and choose Change Data Source, you can see the source of the data that created the Pivot Table.

This is where people might be looking for that 10% column. I'm going to click Cancel. But 10% is in a field in the table.

So along with the Pivot Table, you might include a list of calculated formulas. I'm going to click in the Pivot Table. I'll go to Pivot Table Analyze.

And you'll go to the same place you went to create the calculation, which is Field Items and Sets. And then you'll choose List Formulas. This will take a look at the Pivot Table and create a description on a separate sheet of all the formulas that are being used currently or all the calculated fields that are being used in your Pivot Table.

So I'll click List Formulas. And here we go. We have a nice explanation of the calculation result of units multiplied by 10%.

So I'm going to rename this sheet as well so I don't get lost and I know where things are. And I'm going to call this Calc Sheet. OK.

I think we're almost done with the Pivot Table section. Let's do one more thing. Let's take a look at creating a pivot chart.

So I want to create a chart using the information I have for index up here. Now, once I'm in the Pivot Table, it's very simple to create a pivot chart. I'll go to Pivot Table Analyze.

And when I go over to the Tools group, there's Pivot Chart. When I click that, I get the option of choosing from several types of charts. Clustered Column Chart is actually what I want.

The other place you can go to create a chart, which is a natural place to go to because normally when you create charts, you go to Insert. Well, guess what? Microsoft knew that you most likely would be clicking this to insert a chart. So they added the option to create a pivot chart from here as well.

If I click that, I'll get the same dialog box. I'll choose Clustered Column. And I just created my chart.

Now that I've created my chart, I may decide that I want to have more space for the chart. So I want to move this over to its sheet. If I right-click on the chart, I will have the option to move the chart.

I don't have to do it from the right-click menu. I'll also have the option on the Design tab. And there's the option to move chart right here as well.

I'm going to move it to a new sheet. So I'm going to choose New Sheet. And here, I'm going to give this a new name.

I'll just call it Index Chart. I'll click OK. This chart is going to get moved to its own worksheet.

Now that sheet is already named and now I have my information. When you're in a chart, please be aware there are two new tabs that display when you're in a chart. And this is where you'll make most of your customizations.

I decided that maybe I would like to change the chart. I can see large, small, and medium. That relates to the sizes of the coffee for each of the coffee shops.

But maybe I don't want to see three bars. So I can go to Change Chart Type. And I can choose a different type of chart.

I'll choose a stack chart. I'll select this. I get a preview right here when I click OK.

Now I see my values here. I don't see any labels. I could add labels individually or I could choose a style.

On the PC, if I hover my mouse over any of the styles, I might see one that I like. This one actually displays the numbers. If I would like to make the numbers bigger because I think they're a little too small, I can select the number.

I'll hold on to the Shift key and select this number. Let's see, maybe Control. Yeah, so now I'm going to select this.

And I'm going to go over to the Home tab and I'll increase the font size. I'll go to 12. Then I'll do the same thing here.

Change this to 12. And go here and change this to 12. I like the colors that are here already, so I'm not going to change that.

Now I can clearly see the values inside of the bars. I'll go to Pivot Table, Chart Analyze. I'm sorry, Design.

I can move my mouse and take a look and see what other types of styles I might prefer. This is a dark style. If I click the drop down here, I have a couple of more options to choose from.

All right, I'm not going to change anything there. I also have Quick Layout. Quick Layout also changes the look of your chart.

But I'm going to leave this the way that it is. And there's my pivot chart. What else do I like to do? I'll do one more thing.

I'll go over to the existing chart that's in this workbook. So there's a tab called Pivot Chart. And this is just another way of looking at your information.

This time, we're looking at the products for the other table. We have the locations and then we have the units. So I want to be able to filter my information based on perhaps category or even location.

Now I could click the drop down here and choose a location like Chambers. When I click OK, it'll filter the information that I see in the chart. Now I'm only looking at Chambers.

I go to Front Street. I'll click OK. I'll select everything.

I get more colors. I could go to Product and choose a specific product, but this could be cumbersome and tiresome. What I'm going to do is add a slicer.

So as long as your chart is created from the Pivot Table, you can go to Pivot Table, Analyze and choose Insert Slicer. Now the two things I wanted to filter for were product and location. I could actually filter for category.

I think that's going to be a better way to filter the information because category will contain many products. So I'm going to go for that. I'm not going to use location.

I'll click OK. And now I have buttons. These buttons allow me to filter the information I see here on the left.

If I'm looking for a particular product, I can just click on it. And now I only see information for that specific product. I could also bring in location and do that as well.

If I wanted to select multiple products, I'll hold on to the Control key and I can select multiple products that are not next to each other. If I want to select products that are next to each other, I can simply click and drag. I'm going to clear this by clicking the Clear Funnel button.

I can also filter the information by category. Categories will contain multiple products. So I can say show me all the backpacks.

One click is all it takes. And now these represent the backpacks. How about sleeping bags? These are all the sleeping bags.

And then I'll click on tent. And I'll see the categories, tent and all the products that are contained within it. So that is working with PivotTables.

We covered a lot. You can create PivotTables either in the worksheet or outside of the worksheet in a brand new sheet. You can choose different calculations to display in your Pivot Table.

You can create charts based off your Pivot Table and you can filter those charts using slicers. So many things you could do with PivotTables. That covers that section.

In the next section, we're going to take a look at how you can create a mini version of a chart using something called Sparklines. All right. So thank you for watching.