5. Create Dropdown List for Search
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
Create Dropdown Lists
1 Create list of salespeople
2 Organise alphabetically
3 Select Allow 'list' in dropdown
4 Select array of salespeople as chosen list
ALT + A, M: Remove duplicates
ALT + A, S, A: Sort alphabetically
ALT + A, V, V: Open data validation dialog box
ALT + E , S , F: Paste formulas
ALT + E , S , V: Paste values
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
F2: Jump into formula
F4: Anchor cells
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 a 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 drop down lists in Excel.
Let's start off by creating a list of our 15 sales reps. So I'll go to the Sales person 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 OK 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 drop down 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 write Alt + A V V to open up data validation.
I now select Allow List and the source will be the list I created earlier.
I'll then press Enter to complete and now I have a drop down list for all of my sales reps.
We can now perform calculations by referencing the selected rep in our formulas.
To calculate the number of customers, we’re going to use COUNT IF.
So I'll write “=countif”, we'll then select the range, which is going to be the sales person column in our data set, we'll select all with Ctrl + Shift + Down Arrow, and then comma to select the criteria.
And the criteria will simply be the selected rep.
Close the bracket, press Enter and we can now see that Hitchcock has 11 customers.
We can also calculate total sales and I'll do this using SUM IF.
So I'll write “=sumif”, the range again is going to be the sales person column, the criteria is going to be the selected rep, and the sum range is going to be revenue.
I’ll press Ctrl + Up Arrow to get to the top and then select the full range.
I'll then close the bracket and press Enter.
And this gives me total sales for Hitchcock of just above $295,000.
If we switch the rep name using the drop down list, you 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 reps performance.
From average sales size, to average monthly revenue, or even where they rank versus their peers.
In the next lesson, we’re going to learn how to use rankings to find the top 5 customers by revenue for each rep.
In the meantime as an exercise, try to create a drop down list that will 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.