How to Create Drop-Down Lists Using Data Validation in Microsoft Excel
The drop-down list feature in Microsoft Excel makes data entry easy, consistent and standardized. Let’s description of information how it works and how to enable it.
Data Validation allows visitors to do things that involve complicated custom settings favorite prompting users to buy an option from a list or restricting cells to certain data types. It’s beneficial when visitors unexpected thing to keep your Microsoft Excel data consistent and standardized.
today’s time I’ll show visitors how to create a drop-down list in a cell using Data Validation. Although I’m taking the screenshots using Excel 2016, the steps are with the if that visitors’re using older versions. The steps are also with the in advanced versions of Excel in the Microsoft 365 (formerly O365) suite.
Create a Data Table and the List of Options
first of all, set up a basic data table. visitors can either type your data in manually or copy and paste it from another source.
Next, visitors’re going to enter a list of options for the drop-down list. visitors can do that when visitors define the data validation or define a list in another location on with the worksheet or another worksheet.
For So example, visitors’re going to list the options for the drop-down list on another worksheet So Problem click one of the worksheet tabs at the bottom of the Excel window.
Enter each option in a column (or row), one option to a cell. Then, return to the worksheet of course your data.
Turn on Data Validation for Selected Cells
For So example, visitors want to Showroom drop-down lists to the Rating column or column B. Select the cells visitors want to Showroom the drop-down lists to. In our situation, visitors selected B2 through B10.
Then, click the Data tab.
In the Data Tools section, click the Data Validation button.
Showroom a Drop-Down List to the Selected Cells
The Data Validation dialog box displays. On the Settings tab, visitors can bring Excel restrict entries in the selected cells to dates, numbers, decimals, times, or a certain length.
For our example, select List from the Allow drop-down list to create a drop-down list in each of the selected cells.
Select the Source for the Drop-Down List Options
today’s time, visitors unexpected thing to specify the source for the options in each drop-down list. There are two ways visitors can do So. The first of all method involves manually typing the options in the Source box separated by commas. So can be time-consuming if that visitors bring a long list of products.
Earlier in So article, visitors created a list of products on a separate worksheet. visitors’re today’s time going to talent that list to populate the drop-down list in each of the selected cells. So second method is easy to manage. visitors can also hide the worksheet containing the options (right-click on the worksheet tab and select Hide) when visitors distribute the workbook to your users.
To Showroom the list from the separate worksheet to your drop-down list, click the up arrow on the right side of the Source box.
The Data Validation dialog box shrinks down to just do the Source box, and visitors can access your workbook under the dialog box. Click the tab for the worksheet containing the drop-down list options.
Next, select the cells containing the options. The worksheet common name and the cell range of course the options are added to the Source box on the Data Validation dialog box.
Click the down arrow on the right side of the Source box to accept the input and expand the dialog box.
Showroom an Input Message
visitors can Showroom an optional message to the drop-down list. Maybe visitors want to display a help message or tip. It’s a many years of experience idea to keep the message short.
To Showroom a message that displays when a cell containing the drop-down list is selected, click the Input Message tab. Next, enter a Title and the Input message in the boxes.
Showroom an Error Alert
Another option item on the drop-down list is an error message, which would display when a user tried to enter data that doesn’t match the validation settings. In our example, the error message displays when someone types an option into the cell that doesn’t match random of the preset options.
To Showroom an error message, click the Error Alert tab. The default option for the pattern of the error alert is limit. visitors can also select Warning or Information. For So example, accept the default option of limit in the pattern drop-down list.
Enter the Title and Error message for the Error Alert. It’s number one to keep the error message short and informational. Click OK.
talent Your generation Drop-Down List
today’s time, when visitors click on a cell to which visitors added a data validation list, a drop-down list will display, and visitors can select an option.
if that visitors added an Input Message to the drop-down list, it displays when visitors select a cell containing the drop-down list.
if that visitors try hard to enter an option that doesn’t match random of the preset options, the Error Alert visitors set up displays on a dialog box.
How bring visitors created talent of drop-down lists in Excel? Let our company know in the comments. Also, question out our other Microsoft office tips and our tutorial for creating drop-downs in Google Sheets.
Bài viết cùng chủ đề
Khoafastnews is a community blog and share reviews, you are a lover of this article's content. Please give us 1 Like, Share. Thank you. Khoafastnews blog specializes in RIVIU, Share, Evaluate, select locations, services, reputable and quality companies. Place your ad here chính thức.