The all-important value field is the best thing to create before you PivotTable becomes too extensive or complicated. This video shows how to set it up.
- [Instructor] Sometimes people put in a blank pivot table like this and they think, "Well, now what?" Well, let me show you the details of how to get going here. The first step you have to do is sort of imagine or envision the pile of information you want to divide up. And if I look at the data here, there's lots of things that could pile up. But the most obvious field I want to pile up is the amount field 'cause it has a bunch of money. So what do we do now? We make a value field out of this amount field.
And the way we do that is we go on over here to the Pivot Table Fields list. Now if this fields list isn't open for you yet, then go ahead and right click this area and choose Show Field List. Mine's already open so mine says hide. So you may need to do that if you don't see this fields list open over here. So to make that value field out of the amount field, I'm going to take this amount field and drag it down to values. Great! Here's a little pivot table here and it has one thing of value field, that initial pile to divide up.
Now if you're ever stumped and you're not sure which field to turn into a value field, then look for any kind of amount or quantity like dollars or if you have a column for duration, number of minutes, number of hours, that works too. If you don't have a field that has a quantity in it, then I'll show you what happens. I'm going to go ahead and remove this value field by clicking the arrow on the field, then choosing Remove Field. And I'll just add this type field to the values field instead.
What happens here is because I added the type field, type has a bunch of text values. It doesn't have quantities or numbers. So Excel does its best and instead of adding this stuff up, it counts the number of rows in the field. This is sometimes helpful actually if you need to know how many purchases were made, just the count of them, or how many orders were sold on a sales team for example. Let's remove this field. I'm going to go back down here and I could click this arrow and choose remove field like I did before, but I'm going to instead just drag it off to the side until it changes to an X, and then I'm going to let go.
And there we have that empty pivot table. That's fine. We'll go ahead and bring this amount field down to values to bring back that Sum of Amount value field. And one more thing I'd like to do here. Since this is money currency, I want to change the formatting to match this one. By default it doesn't do that. You just have to do one extra step. So I'm going to click the arrow. Go to Value Field Settings, Number Format, and then Currency. And then I'll just knock down the decimal places to zero.
Click okay, okay again, and now we have a nicely formatted value field for currency. So we made a value field, and really in my opinion, it's the best field to add to the pivot table first because it gives you that initial pile or total of stuff that you can later divide up with the other fields.
- Describe when you would use a PivotTable.
- List the things you need to do prior to creating a PivotTable.
- Explain the benefits of creating an Excel table before creating a PivotTable.
- Describe a conceptual representation of the value field.
- Cite the reason you should periodically refresh a PivotTable.