Abstract
Transcript
In this video, we'll look at how to rank data in a pivot table.
Here we have data that represents one month of sales for a camping store. You can see the store sells tents, backpacks, headlamps, and so on. In fact, after I summarize the data with a pivot table, I can get an exact list of items sold by adding the Item field to the Rows area.
And, when I add Amount as a value field, I'll get a breakdown of total sales by item. I'm going to rename this field "Sales," and set the number format to Currency.
Now, looking at the pivot table, we can see that tents are the top item by sales, followed by car racks, sleeping bags, backpacks, and finally headlamps.
Now, what if I want to add an actual rank value to the pivot table?
This is easy to do.
The first step is to add the field you want to rank in the Values area. In this case, I want to rank total sales, so I'll simply add the Amount field again, and change the name to "Rank."
Now I need to change the display to show rank.
This is done with Value Field settings under Show Values As. In the dropdown, near the bottom, you'll find an option to rank values from smallest to largest and largest to smallest. In this case, we want largest to smallest.
Now, whenever you rank with a pivot table, you need to set a base field. This will be the field that you are using to summarize data, which in this case is Item.
Now when I save these settings, we have a rank value in the pivot table.
Notice, the pivot table remains unsorted, but I can easily sort in descending order using the rank field.
To prove this is all working properly, I'll temporarily change a data value for headlamps to a large number.
Now when I refresh the pivot table, we see updated rank values.
I'll undo that change.
Keep in mind if you change the field used to summarize data, you'll break the rank settings. For example, if I replace item with state, the Rank now shows errors. To fix this problem, just update the base field setting. In this case, I need to change the base field to State.
Finally, notice there is no requirement to keep the original amount field in the pivot. If I remove this field, the ranking still works fine.
