We always use Microsoft Excel for storing records data to make it more accurate and quick to process. Excel allows you to store your data in worksheets. You can use excel formula to manage your data like you can use the formula to get a total of amount in different cells, get the average, subtract, multiply, sum and much more formulas used in Microsoft Excel. For more please read Top 10 Excel formulas that make your life easy.
Why need to create drop down in Excel
Sometimes you may need to use some words, amounts very rapidly in your data entry using Microsoft Excel. As in Microsoft Access, you can create a drop-down list using the lookup option in the table design. There you can add your options manually or you can link a table which contains those options. Connecting a table is always a good idea because you can easily edit those options from your options table.
How to Create Drop Down in Excel 2013
Fortunately, you can create such drop downs in Microsoft Excel. Once you get the idea that the option is already added to Excel, you will be able to implement it easily and efficiently. In this article, you will learn how to create drop down in Excel.
Follow this step by step guide to create drop down in Excel easily.
Create an Excel worksheet using the right-click context menu New>Microsoft Excel Worksheet or open Microsoft Excel from the program menu, create a new sheet and save it.
Type the options you want to use in the drop-down. Type all the options in a column for easy selection. It can be also in a row.
Now select an empty cell where you want to create drop down in Excel, as in the above image we have selected an empty cell E2. Open the DATA tab from the menu bar and click “Data Validations” as shown below.
The data validation window will open as shown below. On the settings tab, change the value of criteria under the allow option. Select “List” from the drop-down options.
List option is select, now uncheck the “Ignore blank” checkbox to ensure the data must be selected from the list and the cell will not leave empty.
Now, it’s time to associate the list data from where the list options are to be picked and shown to the data entry operator. Click on the source box, select the range of records in the worksheet and the source code will be automatically added to the source box. Click OK to save the validation.
You have create your first drop down in excel. You can select months using this drop down list.
How to hide the options data
If you want the options should not be visible, you can hide the options list in plan format that is visible in the first column of the worksheet. You can “Hide” that column contains the options source in the form of plain text. You can hide the column by right clicking and select “Hide”. If you delete the options column, the drop-down will no more work.
You can also use another way to hide the options and use it in the backend. Create a new sheet and create the options columns on the new sheet. Repeat the whole procedure of data validation and in the source, field adds the address of the cell with the reference to the sheet and click OK to save the settings.
Now you can hide the sheet named “Options Sheet” and unhide whenever you need to make changes in it. You can save all options data on this sheet and make it easier to have the fast and accurate entry in you excel worksheets.
Add Message and Title
You can add an input message. In the data validation window, select the “Input Message” tab and add the title and message to your drop down list. Check the radio button “Show input message ”. Add a message and click OK to save the settings.
When you select the cell, you will see a popup box showing the message you added.
Add Error Message.
You can also add an error message that will pop up whenever you enter invalid data in the selected cell. In the data validation windows, select the “Error message” tab and change options as you require. Select the style as stop, warning, or information. Enter a title for the message and error message that you want to display on invalid entry. Click OK button to save the settings.
If you enter invalid data in the cell, it will show the error message. its all set, you have completed the process of how to create drop down in excel successfully.
You have all set now. Enjoy the full of excel and share this post with your friends.