How to Add a Drop-Down List to a Cell in Excel

It thumps typing successful nan aforesaid options 200 different times manually.

Drop-down lists are very useful information introduction devices we spot conscionable astir everywhere, and you tin adhd civilization drop-down lists to your ain Excel worksheets. It’s easy and we’ll show you how.

What is an Excel Drop-Down List?

A drop-down database successful Excel is precisely for illustration immoderate different drop-down database you're acquainted pinch — conscionable click nan arrow past prime 1 of a fewer pre-determined values. Drop-down lists make it easier and much businesslike to participate information into your spreadsheets.

You tin adhd drop-down lists to cells successful Excel containing options specified arsenic "Yes" and "No", "Male" and "Female," aliases immoderate different civilization database of options, including numbers aliases typical characters.

How to Add a Drop-Down List to Excel

Drop-down lists are created utilizing nan Data Validation characteristic successful Excel — not peculiarly intuitive. We’re going to create a drop-down database pinch a action of property ranges to show you an example. We created 3 branded columns: Name, Age, and Sex, and filled successful 2 illustration names. You tin do arsenic galore aliases arsenic fewer arsenic you like.

To begin, participate nan database of property ranges into sequential cells down a file aliases crossed a row. We entered our property ranges into cells A7 done A11 connected nan aforesaid worksheet, arsenic shown below. You tin besides adhd your database of options to a different worksheet successful nan aforesaid workbook.

Now, we’re going to sanction our scope of cells to make it easier to adhd them to nan drop-down list. To do this, prime each nan cells containing nan drop-down database items and past participate a sanction for nan compartment scope into nan Name container supra nan grid. We named our compartment scope Age.

Now, prime nan compartment into which you want to adhd a drop-down database and click nan “Data” tab.

Open nan Data Tools conception of nan Data tab, past click nan “Data Validation” button

The Data Validation dialog container displays. On nan Settings tab, prime “List” from nan Allow drop-down database (see, drop-down lists are everywhere!).

Now, we’re going to usage nan sanction we assigned to nan scope of cells containing nan options for our drop-down list. Enter =Age successful nan “Source” container (if you named your compartment scope thing else, switch “Age” pinch that name). Make judge nan “In-cell dropdown” container is checked.

The “Ignore blank” cheque container is checked by default. This intends that nan personification tin prime nan compartment and past deselect nan compartment without selecting an item. If you want to require nan personification to prime an action from nan drop-down list, uncheck nan Ignore blank cheque box.

You tin adhd a popup connection that displays erstwhile nan compartment containing nan drop-down database is selected. To do this, click nan “Input Message” tab connected nan Data Validation dialog box. Make judge nan “Show input connection erstwhile nan compartment is selected” container is checked. Enter a Title and an Input connection and past click nan “OK” button.

When nan compartment containing nan drop-down database is selected, you’ll spot a down arrow fastener to nan correct of nan cell. If you added an input message, it displays beneath nan cell. The down arrow fastener only displays erstwhile nan compartment is selected.

Click nan down arrow fastener to driblet down nan database of options and prime one.

Of course, we person an full file we want to return property values. There are a fewer ways to make that happen. The first — and easiest — is conscionable to prime each of nan cells you want to person a drop-down paper before you spell to Data Validation and really adhd nan drop-down menu. The simplest measurement to prime each compartment successful a file is to click nan file header. If you person a compartment you're utilizing for a label, conscionable Ctrl+Click it to deselect it, truthful nan formatting doesn't get applied there.

Then conscionable use nan formatting conscionable for illustration we showed you previously.

The different script is erstwhile you've already applied nan drop-down paper formatting to 1 cell, but don't want to person to repetition nan process of creating a drop-down menu. In this case, conscionable click and prime nan compartment that already has nan drop-down menu. Note nan mini quadrate successful nan bottom-right area of nan cell. Left-Click and resistance that area down to each of nan cells you want to person a drop-down menu, past merchandise your mouse. The cells you dragged to will person inherited nan drop-down paper from nan original cell. It'll look for illustration this:

This instrumentality fundamentally applies to any formatting you use to a compartment successful Excel, not conscionable drop-down menus.

If you determine you want to region nan drop-down database from nan cell, unfastened nan Data Validation dialog container arsenic described earlier and click nan “Clear All” button, which is disposable nary matter which tab is selected connected nan dialog box.

The options connected nan Data Validation dialog container are reset to their defaults. Click “OK” to region nan drop-down database and reconstruct nan compartment to its default format.

If location was an action selected erstwhile you removed nan drop-down list, nan compartment is populated pinch nan worth of that option.

Follow this process to adhd drop-down lists to different cells arsenic needed. You tin usage nan aforesaid database for aggregate drop-down lists. If you person galore drop-down lists you request to adhd connected a worksheet, you whitethorn want to put nan lists of options connected different worksheet successful nan aforesaid workbook. You tin hide nan worksheet containing your lists of options to forestall them from being changed.