Now, whenever you rank with a pivot table, you need to set a base field. In this case, we want largest to smallest. In the dropdown, near the bottom, you'll find options to rank values smallest to largest and largest to smallest. This is done in with Value Field settings under Show Values as. Now I need to change the display to show rank. In this case, I want to rank total sales, so I'll simply add the Amount field again. The first step is to add the field you want to rank to the Values area. Now, what if I want to add an actual rank value to the pivot table?
To make it a little easier to see this, I can sort by Sales in descending order. Now, looking at the pivot table, we can easily see that Tents are the top item by sales, followed by car racks, sleeping bags, backpacks, and finally headlamps. I'm going to rename this column 'Sales', and set the number format to Currency. In fact, after I summarize the data in a pivot table, I can get an exact list of items sold by adding the Item field to the Rows area.Īnd, when I add Amount as a value field, I'll get a breakdown of total sales by item. You can see the store sells tents, backpacks, headlamps, and so one. Here we have data that represents one month of sales for a camping store. In this video, we'll look at how to rank data in a pivot table.
