drop down list in Excel

Q40. In Excel, how do I ensure that user can only enter a set of standard text in an input field (alternatively how can I set up a drop down list)?

For example, in collecting personal information for record keeping, how do I ensure that user can enter only one of the following 4 values against Marital Status - Single, Married, Separated and Divorced?

To do it in Excel, here is the answer:

a) Select the field for Marital Status input. Under "Data" tab, click Data Validation -> Data Validation.

excel drop down list

b) Select "List" for Allow field and enter "Single, Married, Separated, Divorced" in Source field so that input is restricted to one of these 4 values. Click OK.

A "down arrow" appears to the right side of the "Marital Status" field indicating that it has a drop down list.

excel excel drop down list

c) If user inputs a value that is not part of the drop down list, error message appears asking user to retry or cancel.

excel excel excel drop down list

 

You can find similar Excel Questions and Answer hereunder

1) I have several pictures that are misaligned - manually aligning them is very laborious. Is there an automatic alignment option?

2) How can I dynamically add series to an existing chart using VBA?

3) How can I enter an Engineering or Mathematical equation like Binomial Theorem Equation in Excel?

4) One of my formulas has returned an error - how can I trace the error to fix it?

5) I want a formula to concatenate multiple cell values separated by a line break.

6) How do I update my DropDown list whenever the sheet is activated?

7) Applying a countif formula only to visible cells in a filtered list in Excel

8) I have a table where inputs have to come from various users. In Excel, how can I highlight a row automatically that does not have input so that I can follow up with the concerned?

9) How can I quickly remove all blank cells in a data range?

10) I frequently use a Macro - is there a way to quickly access the Macro using a Keyboard Shortcut?

11) Can I apply formatting to a number concatenated with a Text?

12) How to change the color of gridlines in a worksheet in Excel

13) How can I list all files in a folder using VBA?

14) How do I get the rank of a number in a list of numbers?

15) How can I create dependent drop down lists?