Content area
Full Text
Walk into most car dealerships, and you'll see a whiteboard on the wall showing how many cars each sales rep sold that month. Using the tools in this article, you can create an eye-catching leaderboard in Excel that shows the top three sales reps.
Calculating Totals for Each Rep
In Figure 1, columns A:C are the original data source. This might be a sales file that's downloaded daily into Excel, or it might be an external query that downloads information from your sales system every few minutes. Convert that range to a table by selecting one cell in the range and pressing Ctrl+T. The table feature, introduced in Excel 2007, allows subsequent formulas to expand as the table expands. You can accept the defaults for the table. In this example, I have typed a new name, Sales, as the Table Name on the Table Tools Design tab of the ribbon.
Column F contains a list of the sales reps. You'll have to remember to manually add or delete people as sales reps join the company. Column G uses the SUMIF function to get the total sales for each sales rep. The formula in cell G2 is: =SUMIF(Sales[Rep],F2,Sales[Amount]).
Sorting Using Formulas
The table in F1:G8 contains an alphabetical list of sales reps. You want to extract the three sales reps with...