Sign in or start a free trial to avail of this feature.
5. Create a Dropdown List for a Search Panel
We all love dropdown lists on the web so why not use them in Excel? In this lesson we create a dropdown list of our sales reps which automatically return their total annual sales
Search Panels (00:04)
A search panel is an interactive summary page in a spreadsheet that provides a range of information on a topic of interest. They allow people to obtain information from a spreadsheet without writing formulas or analyzing data themselves. Over the next few lessons, we’ll create a search panel providing information about the performance of salespeople.
Creating a Drop Down List (00:35)
We want to create a dropdown list of salespeople. To do this, we create a list of the salespeople in the dataset. First, we copy all the salespeople from the sales data to another sheet. We then remove duplicate entries using the shortcut Alt, A, M. Finally, we sort the list alphabetically using the shortcut Alt A, S, A.
Once we have a list of salespeople, we can create the dropdown. On the search panel, we select the cell where the dropdown will be located, then open the data validation window (Alt, A, V, V). We select the list of salespeople as the valid values. This creates a dropdown list for all of the salespeople.
Using the Dropdown List (01:53)
Once we’ve created the dropdown list, we can perform calculations by referring to the cell containing the dropdown in our formulas. The calculations update anytime we change the selected salesperson in the dropdown.
Here, we create a COUNTIF formula counting the number of customers for the selected salesperson. We use a SUMIF formula to calculate the total sales for the selected salesperson. By referencing the dropdown cell, we can calculate any metric we want about a salesperson’s performance.
In the previous lesson, we learned how to create pricing tiers using nested if statements. In this lesson, we'll learn how to create a dropdown list for a search panel.
Sales status sets such as this are often used repeatedly within companies to provide information on sales rep, monthly performance, and other metrics. In this scenario, it's always a good idea to create a search panel or summary page that allows you to quickly gather the information you need. It also allows first-time users of the data set to pull information without creating formulas or performing any analysis. Over the coming lessons, we're going to create a search panel that allows you to select a sales rep and view various details about their performance. We'll start in this lesson by learning how to create dropdown lists in Excel.
Let's start off by creating a list of our 15 sales reps. We'll go to the salesperson column select all the entries, copy, and then just paste the values onto a new sheet with alt, E, S, V.
We now want to remove all of the duplicates, so we'll press alt, A to access the data tab and then M to access the remove duplicates command. We'll then click okay to complete.
This gives us the names of our 15 sales reps.
We'll now sort these alphabetically with alt, A, S, A, and this will be our drop-down list. We'll now return to the search panel.
We'll go to the rep name cell which you can see we've named selected rep.
And then type alt, A, V, V to open up data validation.
We'll now select Allow List, and the source will be the list we created earlier.
We'll press enter to complete.
We now have a dropdown list for all of our sales reps.
We can perform calculations by referencing this elected rep in our formulas. To calculate the number of customers, we're going to use count if. So we'll write equals count if, then select the range which is going to be the salesperson column in our data set.
Select all with control, shift, down arrow, and then comma to select the criteria. The criteria will simply be the selected rep.
We'll close the parentheses and press enter. We can see that Hitchcock has 11 customers. We can also calculate total sales using sum is.
So we'll write equals sum if.
The range again is going to be the salesperson column.
The criteria is going to be the selected rep.
And the sum range is going to be revenue.
We'll press control, up arrow to get to the top, then select the full range.
We'll close the parentheses and press enter. This gives us total sales for Hitchcock of just above $295,000. If we switch the rep name using the drop-down list, we can see our numbers update accordingly.
By simply referencing the selected rep cell, we can now pretty much calculate any metric we want regarding a sales rep's performance from average sales size to average revenue, or even where they rank versus their peers. In the next lesson, we're going to learn how to use ranking to find the top five customers by revenue for each rep. In the meantime as an exercise, try to create a dropdown list that'll allow you to calculate the total sales for a selected state. As always, I'll leave the answer in the after file.