Most of my colleagues in office, often come up asking how to create a drop-down list in Excel.
Seemingly simple though it may be, with more and more functions being embedded in Excel, I even sometimes tend to forget which tools is under which section.
You may ask, why a drop-down!! To have uniform data, a standard form has to be created, which should in turn have standard answers, such as a day of the week, or a month. A drop-down box with pre-filled selections would be handy in such cases.
A rectangular group of cells (of any size or form) is called a range, in Excel.
Considering that a particular column is expected to have a list of pre-determined values, lets get along with creation of a drop-down in Excel.
Steps to create a drop-down
1) Select the cells that will be the contents of the drop-down list. If you wish to have more than one cell to display the same list, just select them all now instead of setting them individually.
2) Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)
3) Select the cell where you want the drop-down list.
4) On the Data tab, in the Data Tools group, click Data Validation
4) Select "List" from the Allow items.
5) Go to "Source" field and select the data of the Drop-down, by dragging. Make sure that the In-cell
dropdown check box is selected.
6) To specify whether the cell can be left blank, select or clear the Ignore blank check box.
7) You can set stricter rules/ restrictions while data selection by displaying an error message.
Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
8) Select one of the following options for the Style box:
To display an information message that does not prevent entry of invalid data, click Information.
To display a warning message that does not prevent entry of invalid data, click Warning.
To prevent entry of invalid data, click Stop.
Type the title and text for the message (up to 225 characters).
9) Finally, click OK
10) Drop-down list will be created at the cell.
Seemingly simple though it may be, with more and more functions being embedded in Excel, I even sometimes tend to forget which tools is under which section.
You may ask, why a drop-down!! To have uniform data, a standard form has to be created, which should in turn have standard answers, such as a day of the week, or a month. A drop-down box with pre-filled selections would be handy in such cases.
A rectangular group of cells (of any size or form) is called a range, in Excel.
Considering that a particular column is expected to have a list of pre-determined values, lets get along with creation of a drop-down in Excel.
Steps to create a drop-down
1) Select the cells that will be the contents of the drop-down list. If you wish to have more than one cell to display the same list, just select them all now instead of setting them individually.
2) Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)
3) Select the cell where you want the drop-down list.
4) On the Data tab, in the Data Tools group, click Data Validation
4) Select "List" from the Allow items.
5) Go to "Source" field and select the data of the Drop-down, by dragging. Make sure that the In-cell
dropdown check box is selected.
6) To specify whether the cell can be left blank, select or clear the Ignore blank check box.
7) You can set stricter rules/ restrictions while data selection by displaying an error message.
Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
8) Select one of the following options for the Style box:
To display an information message that does not prevent entry of invalid data, click Information.
To display a warning message that does not prevent entry of invalid data, click Warning.
To prevent entry of invalid data, click Stop.
Type the title and text for the message (up to 225 characters).
9) Finally, click OK
10) Drop-down list will be created at the cell.
