How to Create a Top 10 List in Excel

Excel is a handy program to use in many ways. One way you can use this program is to answer common business questions such as, “What customers provided the most revenue for my company this year?”, and “Who are my top ten clients?” Creating a “Top Ten” list is one handy feature you can use once you have created a simple pivot table (If Pivot Table Reports are new to you, see my article on creating a Simple Table from the RESOURCES section below.)

Instructions

Difficulty: Moderately Easy

Things You’ll Need:

• Computer
• MS Excel

1. Top Ten State report Begin with a pivot table that has a list (rows) of items that you want to rank. (See the picture for an example.)
   A “Top Ten” ranking is just a way of showing the highest ranked items only.
   The ranking might be the number of customers at the company, or the volume of sales per customer, or anything similar that you can think of. Other examples might be to show an executive summary of the top accounts for the year, or to help a sales team identify the top sales person from a large list.
   For this article, our example is to show the TOP TEN STATES BASED ON THE NUMBER OF CUSTOMERS.
   (Note: This is sample data only, the names and companies have been changed.)
2. Once you have the pivot table created you can add more advanced features We will begin with the report already created.
   The report shows the count of companies by state, in no particular order.
   Notice the headings in the report are the same as the column headings from the original data.
   To use this feature you need to access the item button, which in this example is the STATE column heading. (See the picture for this step.)
3. Advanced popup screen Double click the button to see the options popup, and choose Advanced.
4. For AUTO SORT OPTIONS click Descending (from high to low).
   Below AUTO SORT OPTIONS, for USING FIELD, choose COUNT OF COMPANY from the drop down list.
   For TOP TEN AUTO SHOW click ON.
   Click OK on this page and the next one.
   Your report should now only show the highest 10 items based on this count.
5. Final Report Now review the report.
   It is now sorted automatically and only the TOP TEN show on the list.
   Notice that the caption STATE displays in blue to indicate that it is filtered.
   You can double-click it any time to change the options as needed.
   Just remember to double click the item button to access the ADVANCED page.

Tips & Warnings

• See the Resources section for a demo of how to create a pivot table
• Practice creating a simple pivot table first before you work on these more advanced features.