How to make a drop down list in excel: Quick Guide

Did you ever look at a disorganized spreadsheet and saw spelling errors and unevenly recorded data? The best way out of this frequent issue is to learn how to create a drop down list in excel. The use of drop-down lists helps to restrict the information that can be typed in a particular cell by the user to ensure accuracy and reduces the time and effort of typing data. Trusting to data validation tools will fully change your workflow, be it managing inventory, creating a team roster, or organizing a smooth digital marketing campaign on a modern platform like glossywise. Limiting options on input prevents users, so that they cannot type anything, but instead they can choose between a pre-approved list. This helps minimize human error, greatly simplifies the analysis of data, and provides your spreadsheets with a look of professionalism and a polished look. You have come to the right place in case you would learn this important spreadsheet technique.

The initial phase in designing a drop-down list is to decide what you want to have in it. These items are strongly advised to be typed in a clean column in any part of your workbook. You can put this list on the same worksheet on which you want the drop-down menu to be inserted, or you can always be organized by putting the list on an entirely different tab. As an example, in cells A1, A2 and A3, you could type Not Started, In Progress and Completed. Pointing out this list and turning it into an official Excel Table by pressing Ctl+T is an excellent pro-tip. By doing so, you can be sure that, when you finally add anything to the bottom of your list, your drop-down menu will be automatically updated without you having to make any manual changes.

After you have had your source data ready, you must then make a decision as to where you want the drop-down list to reside. You can highlight the desired cell or range of cells by clicking and dragging to highlight the desired area where you would like the clickable menu to be displayed. This might be just a single cell to enter a brief status, or a column in itself when you are creating a big data entry form. It is important to take the time to appropriately choose your target area so that you do not accidentally impose data validation rules to the wrong areas of your workbook. As with a beautiful publication, such as glossywise, it is important to be very careful in selecting what goes in a highly functional spreadsheet.

Your cells of interest identified; now, right on the ribbon at the top of your excel window, go and click on the tab that is labeled Data. Find the group of Data Tools and press on the button Data validation. This will bring about a dialog box that has three tabs namely Settings, Input Message and Error Alert. You will find a drop-down menu of allow at the settings tab. Click and choose List under the options without any problems. This instructs excel that you would like to limit the cell input to a certain list of specific items. Make sure that you have the In-cell dropdown checkbox checked, this is what makes the physical arrow that you can click on next to the cells you choose.

Once you have chosen List, you need to inform Excel where to get the choice of your menu. Click in the box of “Source” in the Data Validation window. There are two options that you have. When your list is quite short, you may just simply type the items into the box, separated by a comma e.g. Yes,No,Maybe. Nevertheless, it is preferable and more vibrant to simply visit the cells that you have prepared in the initial step and then click and drag. The cell references will be displayed in the Source box, and typically look like this: =A1:A3. After making your choice of source data, you can click OK. Once you have clicked on the cells you will instantly see a little downward-pointing arrow appear next to them. You have just made your list! congratulations!

In addition to the simple configuration, Excel enables you to adjust the user experience. Going back to Data Validation dialog box, one can investigate the tab of the Input Message. In this case, you may enter a short command, which will appear as a tool tip when the drop-down cell is clicked on. This proves to be of great assistance in instructing other people who may not be conversant with the spreadsheet. Moreover, there is the “Error Alert” tab, where you can configure the message warning that will be shown in case someone attempts to go round the list and enters the invalid data. You can either opt to ensure they are indeed prevented to continue, or just demonstrate a warning but still the custom text.

Occasionally, your project requires modification, and you do not require data validation in a certain cell any more. It is as easy to remove a drop-down list as to create it. Mark the cells with the menus which you would like to delete. Go back to the “Data” tab and click “Data Validation.” There is a button in the bottom left-hand side of the dialog box which is called Clear All. A single click on this removes any validation rules on the cells selected and restores them to their default state (without any restrictions). Remember to click OK to save your changes.

How to create a drop down list in excel: FAQs.

Q: I want to make a drop-down list out of information on a different sheet.

A: Yes, absolutely. To set up the Data Validation, you can just go to another tab of your workbook and highlight the desired cells when you click in the Source box. All Excel will do is to create the right sheet reference formula on your behalf.

Q: Why is my drop-down arrow not showing?

A: The drop-down arrow is only visible when you actively click on the cell which has the list. Should the cell not show the arrow, you can again click on the cell and in the Data Validation box, make sure to tick the box of In-cell dropdown.

Q: What can I do to make my drop-down list automatically update?

A: The best method to make a dynamic drop-down list is to make your source list an Excel Table (Ctrl+T). The addition of new items to the bottom of the table will automatically appear in your drop-down menu when you are referencing a Table in your Data Validation source.

Q: Is it possible to copy drop-down list to other cells? A: Yes. The drop-down list is just an item in the cell, which can be copied (Ctrl+C) and pasted (Ctrl+V) to other cells. Or, drag the list along the column with the fill handle on the bottom right corner of the cell.

Q: Is it possible to have a drop-down list within a drop-down list? A: You can not insert a list within a list but you can make so-called dependent drop-down lists with the INDIRECT function. This is a sophisticated method whereby the choices in a second drop-down menu can vary according to the choice in the first menu.