Using a Pivot Table Top Values Filter
This tutorial shows you how to use pivot tables for filtering the top items from a field in your pivot table. Watch the video below to see walkthrough in Excel (skip to 1:30 to go straight to the technique). Alternatively jump down to the summary steps below for a guide to the technique. Get the file from the video here—and for my intro to pivot tables go here.
5-Step Summary for Using Top Value Filters
- Get familiar with your dataset and create your pivot table
- Choose a field with discrete items (e.g. not numeric data) for which you want to get the top 10 (or any other number) of items based on another value field in your table (e.g. athletes, by 100m trials time)
- With the field in your pivot table canvas, right-click on an item in the field (e.g. one particular meal) then choose ‘Filter’–>’Top 10′ then choose the number (x) of items to show as well as the value field you want to rank by (e.g. trial time) NOTE: you can also choose the bottom x values, or the top/bottom x percent of all values here!
- Click ‘OK’ and your table will list only the top x items
- You can do this for clustered fields also—for example, list the top 3 selling products for each quarter—or in my example, the top 3 calorie-meals in each week!
Using Pivot Table Top Values and Calorie Tracking
There are times where I have had pivot tables listing 100 employees by a productivity ratio for a given period and I only wanted to see the top 10 and worst 10 to make the information more useful. This technique works well for getting at that kind of information quickly. As for calorie tracking—this has also been an illuminating experience for me. It is insane how easily you can overconsume on trifling things like salted peanuts (they pack a whopping ca. 600 calories/100g), or ramen noodles (480 calories per packet). Merely tracking and analysing that data has (as you can see in the video) helped me control that activity—thus illustrating the power of measurement and data analysis 😉