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 drop down list for a search panel. Sales data sets, such as this example, are often used repeatedly within companies to provide information on sales reps, 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 gonna 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. So I go to the salesperson column, I'll select all the entries, I'll copy and then I'll paste onto a new sheet just the values, with ALT+E+S+V.
I now want to remove all of the duplicates, so I'll press ALT+A to access the data tab and then M to access the remove duplicates command. I'll then click okay to complete. And this gives me the names of our 15 sales reps.
I'll now sort these alphabetically with ALT+A+S+A and this will be my dropdown list. I'll now return to my search panel.
I'll go to the rep name cell, which you can see that I've named selected rep and then I'll type ALT+A+V+V to open up data validation.
I now select allow list and the source will be the list I created earlier.
And I press enter to complete.
And now I have a dropdown list for all of my sales reps.
We can perform calculations by referencing the selected rep in our formulas. To calculate the number of customers, we're gonna use COUNTIF. So I'll write equals COUNTIF.
We'll then select the range, which is gonna be the salesperson column in our data set.
We'll select all with control+shift+down arrow and then comma to select the criteria. And the criteria will simply be the selected rep.
Close the brackets, press enter. And we can see that Hitchcock has 11 customers. We can also calculate total sales and I'll do this using SUMIF. So I'll write equals SUMIF, the range again is gonna be the salesperson column, the criteria is gonna be the selected rep, and the sum range is gonna be revenue.
I'll press control+up arrow to get to the top, then select the full range. I'll close the brackets and press enter. And this gives me total sales for Hitchcock of just above $295,000. If we switch the rep name using the dropdown 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 gonna learn how to use rankings 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. I'll leave the answer in the after file for you to check against your own.