Microsoft Office 2016: The Complete Guide (2015)

CREATING A DROP-DOWN LIST IN EXCEL 2016 FOR WINDOWS

Worksheets can be made more resourceful by offering drop-down lists. When you select an arrow on the worksheet then click an entry in the list.

On a new worksheet, type the entries that you want to be displayed on your drop-down list. These entries should however, be in a single column or row that is void of blank cells.

TIP: = It would be helpful if you take this time to arrange your data the way you would like it to be shown in your drop-down list.

Select every entry, right-click, and then select Define Name.

Type all your entries in the Name box, for instance ValidDepts after which you should select [OK]. Please ensure that names do not have spaces between them. This name will not be seen in your list. It is still imperative however, to label it so as to associate it to your drop-down list.

Select the cell in the worksheet where the list should be.

Select Data> Data Validation.

Just in case you are unable to select Data Validation, there are a couple of reasons why this might occur.

Drop-down lists cannot be added to tables that are linked to a SharePoint site. An alternative would therefore be to unlink or remove the table then try step 5 again.

Another reason can also be that the worksheet is protected or shared. Therefore, you would have to remove the protection or stop sharing the worksheet and repeat step 5.

On the Settings tab, in the Allow box, select List.

Inside the source box, type an equal sign (=), followed by the name given in step 3 without a space in between them.

Tick the In-cell dropdown box.

If you want to have the option to leave the cells empty, tick the Ignore blank box.

Select the Input Message tab.

If you would like a pop up message when the cell is selected, tick the [Show Input Message When a Cell is Selected] option box. Then type desired titles and messages in the box. Please be reminded though that the limit is 225 characters. If you don’t want a pop up message to show up, clear the check box.

Select the Error Alert tab.

Tick the [Show Error Alert Invalid Data Entered] box. Select an option from the style box then type a title and message. If you don’t want to have an option of a message to show up, clear the check box.

Can’t Decide Which Option to Choose in the Style Box.

Show a message that doesn’t stop people from inputting data that is not available in the drop-down list, select information or warning. In formation will be displayed with the icon and warning displayed with this icon.

If you want to completely prevent people from entering data that is not a drop-down list, select [Stop].

NOTE: if you don’t enter a title or text, the automatic title is Microsoft Excel and automatic text “The value you entered is not valid. A user has restricted values that can be entered into this cell.”

Working with Your Drop-down List

After generating your drop-down list, ensure that it functions the way you expect it to. For instance, you want to check if the cell is wide enough for all entries to be displayed.

If you want to avert users from seeing the list of entries for your drop-down list which may be another worksheet bear the option of hiding or protecting that worksheet in mind.

If you want to change the options in your drop-down list, see Add or Remove items from a drop-down list.