Top Tens in Pivot Tables

Don't miss out on how easy it is to get a top ten list in Excel. If you want to find the top 10, or top number of any numerical values in an Excel table a pivot table provides a simple way to do this.

In this example we have a spreadsheet listing batting statistics for every major league baseball player from 1871 through 2015. To create a pivot table go to the Insert tab on the ribbon and click on PivotTable. In the dialog box that opens, select the table range, and click okay.

Next in the PivotTable Fields box select the fields you want to display. Here we want to show the highest career home run totals, so we're going to check off playerID. This field should then automatically display in the Rows selection box, and a column will be generated in the Pivot Table in which each player's name is listed once.

Next check off HR, or whatever stat you want to display in the Pivot Table. It should be added to the Values selection box, and the setting will default to adding up the sum of entries in the field. The Pivot Table will show the sum of the values in the Value selection box for every unique string entered in the field put in the Rows selection box. In this example each player's career home runs are added up.

Click on the filter for the first column in the Pivot Table, and select Value Filters . . . Top 10.

You can then select a top list of however many items you want to display.

The Pivot Table will condense to just show the top entries. You can sort it in order by going back to the filter and select More Sort Options, and choosing the value.

If you want to instead get the highest single entries in the selected value field, you can then in the value selection box, click the drop down arrow, go to Value Field Settings, and switch to Summarize Values by . . .Max:

. . . or Summarize Values by Average: