Create drop-down lists in Excel by validating data

If you’re a big Excel user, you may have found yourself in a situation where you simply wished you had chosen a value for a cell from a drop-down list of options instead of having to copy and paste text. between cells. For example, suppose you have an Excel spreadsheet for your own personal budget, and each time you enter a transaction in a new row, type whether it’s revenue or expense.

Sometimes, you may write it incorrectly or you may have a long list of different types of expenses and income, so this can be a pain to keep and remember. An easy solution to Excel is to create your own drop-down list of options extracted from a specific column on the same sheet or another sheet. This can be very useful because you can now use a sheet to store all your reference lists as a database and use them on any other sheet in the book.

So how do we do that? It’s not really very difficult! The following explains:

  • 1. First, create a new workbook and move on Full 1 Start in row 1, column 1 (A1) and type some expense categories in the column. For example, this is what I thought:
  • 2. Now that you have the list of references, go ahead and select the cells with the data. In our case, we will select cells A1 to A8. Left-click A1 and hold down the mouse button and drag to A8.
  • 3. Now, in the box next to the formula text box on the left (also called the Name box), type a name for the list and press Enter. I wrote Expenses for our list. By doing this, cells A1 to A8 are basically given a name, so you can now reference the group of cells with a name instead of saying A1 to A8.
  • 4. We can now use our list to create a drop-down menu with these values. First select the cell where you want the drop-down list to be created. It can be on the same sheet or on a differnet sheet. I went ahead and selected cell A1 of Sheet2 as an example.

Then go to the top menu and choose Data and then Validation. If you are using a version of Excel with the ribbon interface, you will need to click Data card.

A la Configuration tab chooses List under the Allow cap. make sure Drop-down menu inside the cell is selected, then enter the name we just created above (Expenses in this case) with a = sign on the front. That’s how it should be “= Expenses“.

Click on OK and you will now see that the current cell you selected has a small arrow pointing to its side. Click on the arrow and you will see the list of items we wrote in Sheet1.

Now what if you need this list in 1000 rows? Well, it’s easy too. Simply move the cursor to the lower right of cell A1 and drag it down as far as you want. All cells will now have the drop-down list available.

This is! Remember that it can be used on any Excel sheet where you have some kind of search list that needs to be typed over and over again. With data validation, you can avoid human error and increase efficiency. If you have any questions about using drop-down lists in Excel, please post a comment and I’ll try to help. Enjoy it!

Leave a Reply