The Best Way To Add A Drop-Down List In Excel
Using a drop-down list in Excel is a quick and easy way to choose data that has already been set up. You can keep time by not having to type these numbers into a given spreadsheet by hand. Drop-down lists are great for many different things, like filling out a form. Here’s how to use Microsoft Excel to make a drop-down list.
Use some cells to make a drop-down list
Using a range, which pulls data from other cells, is the most common way to make a drop-down list in Excel with more than one choice.
⦁ Step 1: Pick a column where you want to put the information that will appear in the drop-down list. This can be made in a similar spreadsheet as the drop-down list or done in a different spreadsheet (add a new spreadsheet at the bottom). If you choose the second option, your main spreadsheet will look more organized, professional, and less cluttered.
⦁ Step 2: Type all the information into the column, giving each piece of information its cell.
⦁ Step 3: Click the cell where the drop-down list should show up.
⦁ Step 4: Click the Information tab at the top and click the Data Validation button.
⦁ Step 5: Choose List from the Allow menu. At the edge of the Source field, there will be an arrow. Click on that arrow.
⦁ Step 6: Once you click on that arrow, you’ll be taken back to the main view of your spreadsheet. You can go to the column where you put the information. Now, choose the range of cells you want to work with by dragging the cursor from the first cell to where your last cell is.
⦁ Step 7: Click the arrow button in the small window that pops up under “Data Validation.” All of the cells you chose in the steps above will be in the Source bar. Select OK.
You now have a drop-down list menu in the cell you selected in step 3.
Create a drop-down menu by manually entering data into the Source box
Excel has more than one way to make a drop-down list. This method is the easiest and works best for people who are just starting with Microsoft Excel spreadsheets and lists that don’t need to be changed often.
⦁ Step 1: Choose the column cell where you want to add the drop-down list.
⦁ Step 2: In the top row of menus, click the Data button and click the Data Validation button.
⦁ Step 3: In the following window that pops up, click the Allow menu. Now, choose List.
⦁ Step 4: Type exactly what you want to be in the drop-down list in the Source field. Ensure you utilize a comma after every word or number.
⦁ Step 5: Hit “OK.”
The cell you chose in Step 2 will now have a drop-down list with all the information you entered in Step 5.
Displaying a message upon selection of a drop-down list cell
After making your drop-down List, you can make it easier to use by adding an Input Message.
⦁ Step 1: Choose the cell that has the drop-down list. Then click the button that says Data Validation.
⦁ Step 2: Choose “Input Message” from the menu. Type a relevant title and the text you want to show when the drop-down List clicks in the Input message box. Only 225 characters can be used for the text you add here.
Displaying a warning message
In the same method, you can add a message that explains what the drop-down List is for, and you can also show an error message when text or data is entered that is not in the list.
Step 1: Choose the cell where your drop-down menu was made. Click the Error Alert tab in the Data Validation window, and then check the Show mistake alert after invalid information is entered in the box. Put in your title and message. If the title or text fields are left blank, the title will automatically be set to Microsoft Excel, and the message will say, “The value you entered is not valid. There are limits on what a user can put into this cell.”
Step 2: Pick a style from the list, such as a Stop (X) sign. Select OK.
Choose either Information or Warning from the Style menu if you want a message to appear that doesn’t stop people from entering invalid data that isn’t in the drop-down list. Both choices have their look.
Keeping your drop-down menu safe
You can lock those cells so that no one can look at or change the drop-down List’s data source.
Step 1: Go to the pillar where your drop-down list information is written. Now, choose the cells you want to lock so that no one else can change them. Click the Home tab after choosing the area you want to lock. Click the little arrow at the bottom correct of the Alignment box. This will open the window for formatting cells. Click the Locked box on the Protection tab and then click OK.
Step 2: Click the tab that says Review. Choose to protect the sheet or the whole book. Create any changes you desire and click OK when you’re done.