If you’re a big Excel user, then you may have found yourself in a situation where you wished you could have simply picked a value for a cell from a dropdown list of choices rather than having to copy and paste text between cells. For example, let’s say you have an Excel sheet for your own personal budget and every time you enter a transaction on a new row, you type in whether its income or an expense.
Well sometimes you can type it in incorrectly or you might have a long list of different types of expenses and incomes, so this can be a pain to maintain and remember. An easy solution in Excel is to create your own dropdown list of choices pulled from a particular column on the same sheet or another sheet. This can be very useful because now you can use one sheet to store all of your reference lists like a database and use them on any other sheet in the workbook.
So how do we do this? It’s actually not very difficult! Here’s how:
1. First, create a new workbook and on Sheet1 start off in row 1, column 1 (A1) and type in a few expense categories in the column. For example, here’s what I came up with:
2. Now that you got your reference list, go ahead and select the cells with the data in them. In our case, we’ll select the cells from A1 to A8. Left click on A1 and hold the mouse button down and drag until you get to A8.
3. Now in the box next to the formula textbox on the left (also called the Name box), type in a name for your list and press Enter. I typed in Expenses for our list. Doing this basically gives the cells from A1 to A8 a name so that the group of cells can now be referenced by a name rather than saying A1 through A8.
4. Now we can use our list to create a dropdown menu with those values. First select the cell where you want your dropdown list to be created. This can be on the same sheet or on a differnet sheet. I went ahead and selected cell A1 on Sheet2 for the example.
Next, go to the top menu and choose Data and then Validation. If you’re using a version of Excel with the ribbon interface, you’ll have to click on the Data tab.
On the Settings tab choose List under the Allow heading. Make sure In-cell dropdown is selected and then type in the name that we had just created earlier (Expenses in this case) with an = sign in the front. So it should be “=Expenses“.
Click OK and you’ll now see that the current cell you had selected has a little arrow pointing down next to it. Click on the arrow and you’ll see the list of items from that we have typed into Sheet1.
Now what if you need that list in 1000 rows? Well, it’s easy also. Just move your cursor to the bottom right corner of the A1 cell and then drag it down as far as you like. All of the cells will now have the dropdown list available.
That’s it! Remember, this can be used on any Excel sheet where you have some kind of lookup list that needs to be typed in over and over again. Using Data Validation, you can avoid human errors and increase efficiency.If you have any questions about using dropdown lists in Excel, post a comment and I’ll try to help. Enjoy!
