The drop-down feature in Excel it’s quite useful when doing data entry.
Using it, you can create a drop-down in a cell that will show you all the pre-populated options that you can choose from.
It saves time as you can simply choose from the list instead of entering it manually, and it also helps you avoid errors such as misspelled words.
One common drop-down list that is often needed is a Yes and No drop-down list. Once you create this Yes/No drop-down list, you can quickly choose to fill the value yes or no in a cell by simply selecting it from the drop-down.
In this tutorial, I will show you two simple ways to quickly create a Yes / No drop-down list in Excel.
This Tutorial Covers:
Create a Yes/No Drop Down List by Manually Entering the Values
The easiest way to create a Yes No drop-down list is to manually specify the values that you want will be shown in the dropdown in the data validation option.
Below I have a data set of names and column A and I want to create a Yes-No drop-down list in column B so that I can select whether the person has completed the training or not from the drop-down.
Below are the steps to do this:
- Select the cell or range of cells where you want to get this drop-down
- Click the ‘Data’ tab
- In the ‘Data Tools’ group, click on the ‘Data Validation’ icon
- In the Data Validation dialog box that opens up, within the ‘Settings’ tab, click on the ‘Allow’ drop-down menu
- Select the ‘List’ option
- In the ‘Source:’ field, enter the following:
Yes,No
- Click OK
The above steps would create a Yes-No dropdown list in the selected cells (you should see a small drop-down icon when the cell is selected).
Now to show these options in the cell and select Yes or No, click on the drop-down icon and then select the option you want.
You can also use the keyboard shortcut ALT + Down Arrow Key to show the drop-down in the selected cells (you need to hold the alt key and then press the down arrow key).
Once you have created the drop-down list in a cell, you won’t be able to enter anything other than Yes or No. If you try doing that, you would see an error box as shown below:
In this method, we have hard-coded the drop-down values, and in case you want to change the options that show up in the drop-down, you will have to go back to the Ddata Validation dialog box and make the changes there.
Pro Tip: You can use the following keyboard shortcut to open the data validation dialog box (use this after selecting the cells where you want the drop-down) – ALt + A + V + V (press these keys one after the other
Create a Yes/No Drop Down List by Using a Cell Range
In the above method, we hard-coded the Yes/No values in the data validation dialog box.
Another way of doing this is to enter the values that you wanted in the drop-down in a cell, and then create the drop-down by using these cells as the source for the drop-down values.
The benefit of doing this is that it makes your drop-down list dynamic, i.e., if you change the values in the cells that I used to create the drop-down, the values in the drop-down would also automatically change.
Below I have the same data set where I want to create the yes no dropdown list in column B, and I also have Yes and No entered in two cells as shown below (in D2 and D3):
Here are the steps to create a drop-down list using the values from cells:
- Select the cell or range of cells where you want to get this drop-down
- Click the Data tab
- Click on the ‘Data Validation’ icon (it’s in the ‘Data Tools’ group)
- In the Data Validation dialog box that opens up, within the ‘Settings’ tab, click on the Allows drop-down menu
- Select the List option
- In the Source field, click on the range selection icon
- Select the cells that have the Yes/No values. The reference to these cells would automatically be added to the ‘Source’ field
- Click Ok
The above steps would create the drop-down list in the selected cells using the values that we selected as the source.
While this gives us the same result as the previous method, the benefit of this method is that if you change the values in the source cells, drop-down values would automatically update.
Copy and Paste the Yes/No Drop-down Lists
Dropdown lists can be copied just like cell color or cell formatting.
So, if you already have a yes-no dropdown list in a cell and you want to create the same one in another cell or another range of cells, instead of doing it by following the steps I’ve shown in the previous methods, you can simply copy the cell that already has the drop-down list and paste it over the cells where you want it.
Editing the Yes/No Drop-Down Lists
In case you want to change the values in the drop-down or edit the existing values, you need to open the data validation dialog box, and make the changes there.
For example, if you have made a spelling error or you want to change the source of the cells from where the dropdown values are picked up, you will have to go back to the data validation dialog box to make these changes.
In this short tutorial, I showed you how to create a simple Yes/No drop-down list in Excel.
I hope you found this tutorial useful.
Other Excel tutorials you may also like:
- Drop Down Lists To Show Numbers Between Two Specified Numbers
- Show Symbols in Drop Down Lists in Excel
- Creating Multiple Drop-down Lists in Excel without Repetition
- Display Main and Subcategory in Drop Down List in Excel
- How to Make Multiple Selections in a Drop-Down List in Excel
- Creating a Searchable Drop-Down list in Excel – Step by Step Guide
- How to Create a Dependent Drop-Down List in Excel