If you are a large Excel user, you may be in a situation where you just wanted to select a value for a cell from a drop-down list of options, rather than copy and paste text between cells. For example, suppose you have an Excel spreadsheet for your personal budget, and every time you enter a transaction on a new row, you enter its income or expense.
Well, sometimes you can enter it incorrectly, or you can have a long list of different types of expenses and income, so this can be a problem to maintain and remember. An easy solution in Excel is to create your own dropdown list of choices taken from a specific column in the same or a different sheet. This can be very useful because you can now use one sheet to store all of your bibliography, such as a database, and use them on any other sheet in the book.
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 at row 1, column 1 (A1) and enter multiple expense categories in the column. For example, this is what I came up with:
2. Now that you have a list of links, select the cells with data in them. In our case, we will select cells A1 through A8. Left click on A1 and hold down the mouse button and drag until you get to A8.
3. Now, in the box next to the formula text box on the left (also called the Name box), enter a name for your list and press Enter. I typed “Expenses” for our list. This basically gives cells A1 through A8 a name, so that a group of cells can now be referenced by name instead of A1 through A8.
4. Now we can use our list to create a dropdown menu with these values. First select the cell where you want to create the dropdown list. It can be on one sheet or on another sheet. I went ahead and selected cell A1 on Sheet 2. as an example.
Then go to the top menu and select “Data” and then “Validation”. If you are using the ribbon version of Excel, you will need to go to the Data tab.
In the Settings tab, select List under the Allow heading. Make sure the In Cell drop-down menu is selected, and then enter the name we just created earlier (in this case “Expensesâ€) with an “=†sign in front. So it should be “= Expenses”.
Click OK and you will see that there is a small arrow pointing down next to the current cell you have selected. Click on the arrow and you will see the list of items that we entered in Sheet1.
What if you want this 1000-line list? Well, that’s easy too. Just move your cursor to the bottom right corner of cell A1, and then drag it down as far as you want. A drop-down list will now be available for all cells.
This is it! Remember, this can be used on any Excel sheet where you have some sort of lookup list that you have to enter over and over again. By using data validation, you can avoid human error and improve efficiency. If you have any questions about using dropdowns in Excel, please leave a comment and I’ll try to help. Enjoy!
–