How to Create an Excel Drop Down List From Another Tab
When creating an Excel drop down list, the most time-consuming part of the process may be copy and pasting each name into the list one at a time. Fortunately, there’s an easier way to speed things up: by creating your drop down list directly in the worksheet you need it to be in instead of in another sheet altogether. To see how, just follow these instructions and you’ll have your Excel drop down list created in no time.
Find the cell with your data
Let’s say you have a column in the first tab of your workbook that needs to be displayed as a drop down list in the second tab. To do this, you need to highlight the cells with data on the second tab. Now, click Data > Create a drop-down list. Once you’ve done that, click and drag your cursor over all of the cells with data on your first tab. They will now be highlighted in blue.
Go to the next blank row
To create a drop down list in Excel, start by opening the spreadsheet in which you would like to create the list. In this tutorial, we will be using Sheet2 of our spreadsheet. To insert a drop down list, click on the Data tab and then Data Validation. On the right-hand side, scroll down until you find List. Click on this option. For data validation type, select List. Under source, input what values are available for selection.
Insert a new column
- Add a new column in the same row as the tab you want to get the data from.
- Enter in =VLOOKUP(A2,Sheet1!$A:$B,3,FALSE) and place this at the end of your current column.
- Select cell A2 (the one with the formula) and drag it down until it fills up all of your columns.
Paste in your list
Here’s how to create a drop down list in Excel:
- Go to the tab with your list and click on the cells you want included in the list.
- Select Data > Filter > Sort and Filter > Filter.
- Select Create new temporary filter.
- In New filter name, type in what you would like your first column of data to be titled.
Select the cell where you want your drop-down to appear
Select the cell where you want your drop-down list to appear.
On the ribbon, click the Data tab.
- In the Data Tools group, choose Data Validation.
- Choose List from the Allow field drop-down menu and enter a comma after it if you have more than one item on your list.
- In the Source field, type or paste in a reference to another worksheet tab containing your drop-down list items.
Go up one step
To create a drop-down list on the cell in another tab, highlight the name of the list and click the Insert button. Now highlight the column where you want that drop-down to appear and select Data Validation. For example, if you want a drop-down list for State, then highlight State and select Data Validation. Click in the field next to Allow and type: =List.
Click on Conditional Formatting -> Data Bars
To do this, we will first select the cell in which we want the drop down list to show up. After clicking on that cell, we are going to click on Conditional Formatting and then Data Bars. For the color, we are going to choose a lighter color for our conditional formatting so it doesn’t overwhelm our other cells. For example, blue is one of our lighter colors.
Select Blue and Over 100% in Data Bar Fill Color (for positive values), Red, Blinking, Above 50% in Data Bar Fill Color (for negative values). This will mean that when your users hover over any value above 100%, their cursor will turn into a cross, signaling they can expand the data bar. When users hover over values below 50%, their cursor will turn into a caret, signaling they can collapse the data bar. You may also want this for negative numbers so users know there are more negative entries than positive ones.
Step 1. Select Blue and Over 100% in Data Bar Fill Color (for positive values), Red, Blinking, Above 50% in Data Bar Fill Color (for negative values). This will mean that when your users hover over any value above 100%, their cursor will turn into a cross, signaling they can expand the data bar. When users hover over values below 50%, their cursor will turn into a caret, signaling they can collapse the data bar.
Optional – add three more criteria and/or change colors
- Click the tab you want in your drop down list.
- Select Data from the menu bar.
- Select Data Validation from the submenu that pops up.
- In the first box under Allow, select List and then click OK.
- Type in one of your items in the Source box and then type in a comma (,) after it.
