How To Extract Data From Dropdown In Excel

The Ultimate Guide to Dropdown Lists in Excel

This mail will show you everything at that place is to know nearly dropdown lists in Microsoft Excel.

If you are creating an Excel spreadsheet for other users to input information, then dropdown lists are very useful to control what information they are entering.

This way you can ensure that they will not enter incorrect data which will produce errors in your spreadsheet when calculations are fabricated based on the user input.

Dropdown lists should exist familiar as you will ofttimes find them on the web or while working in other applications.

They heighten the user experience equally they make choice selection piece of cake and assistance to standardize data entry.

This post is going to cover everything most dropdown lists in Microsoft Excel.

Are yous ready for the ultimate resource guide to dropdown lists in Microsoft Excel? Get your copy of the example workbook and follow along!

Instance Dataset

All the examples in this post will utilize the above standard set of data within Excel.

How to Create a Dropdown List

There are several means to populate listing items when y'all create a dropdown listing within your spreadsheet.

Utilise Comma Separated List of Values for List Items

The first method is the almost basic where all items are entered in the Data Validation menu equally a comma-separated listing.

Go to the Information tab and click on the Data Validation push in the Data Tools group.

This will open up the Data Validation menu. Go to the Settings tab and select List from the Allow dropdown.
In the Source input box, enter your delimited list using commas equally the delimiter between items.
Click OK push button to create your dropdown list.

Important: Keep the In-jail cell dropdown option checked as this is what volition create the dropdown.

Your selected cell volition now have a dropdown pointer to the right of it. Click the pointer, and your list will at present show as separate items based on the comma delimiters that you lot entered.

Annotation: If you use a comma and space to delimit your list items, Excel will remove the leading space from each particular in your dropdown.

The advantage is that the listing can exist created in a very straightforward manner. All y'all need to do is to type the list in, or even to paste it in from elsewhere.

The disadvantage is that it is hardcoded and is not dynamic. There is no way to modify the list based on data entered in the spreadsheet.

Any changes to list items need to be done in the Data Validation menu. If yous desire to use the same listing elsewhere in the spreadsheet, then you either need to re-create and paste the listing or set up the list from scratch.

Utilize a Range Reference for Listing Items

This is the well-nigh obvious pick for your listing items as the Data Validation menu has a button to select a range from the grid.

From the Data Validation menu click on the Select button institute on the right side of the Source input field. This will allow you to select the required range from the filigree.

Use a Named Range for Listing Items

Another way to enter listing items in your dropdown is by entering them in a named range, and so referencing the named range in the Data Validation menu.

Follow these steps to create a named range.

Select the range of cells to use for range name for the listing of information that y'all want to employ. This must exist a unmarried column range.

Go to the Formula tab and click on the Ascertain Proper name command in the Defined Names group of the ribbon. You can edit the range name afterwards past clicking on Proper name Manager in the same group.

This volition open up the New Proper name menu. Enter a proper noun for the range in the Proper name field. This is how you will refer to the range when creating a dropdown listing.
The cells you selected should be listed in the Refers to field, and so check this is correct and update it if needed.
Printing the OK button.

This will create the name and when you lot select the range, you will see the name displayed in the Proper noun Box.

You can too employ the Name Box to skip the Ascertain Name menu and quickly create a named range. But select the range of cells to name and and so blazon the proper name into the Proper name Box and press Enter.

At present you tin use the named range to create your dropdown list.

Select the cell for your dropdown list.
Become to the Data tab in the ribbon.
Click on the Data Validation button in the Data Tools group.

This volition open up the Information Validation card on the Settings tab. In the Allow dropdown, select Listing fromt the options.
In the Source input box, enter the name of your named range for the list source. Precede it with an equal sign (=). You can also utilise the Up Arrow selector icon to select the range from the sheet. When you select the full named range, Excel will display the proper noun as your selection.
Press the OK button.

Your selected cell volition now have a dropdown arrow to the correct of information technology and will show all the items inside your named range.

The advantage is that y'all tin can use this range name as a unmarried source for many data validation lists. You can easily edit values in the named range, and that will reflect in all the dropdown lists that utilise that range.

Also, if the range proper name is moved to some other location within the spreadsheet, it will still act equally a valid source for all the dropdowns that utilise information technology every bit the source for list items.

The disadvantage is that you will need to fix up the range name first of all, but if you have many dropdowns within the spreadsheet using this aforementioned source, then information technology is a very small overhead.

Use a Table for Listing Items

Y'all can also apply an Excel table as the source for your dropdown list.

Check out his post to find out everything virtually Excel tables if you lot haven't seen them before.

Tables are great considering it's like shooting fish in a barrel to add new data to the table. But type in the row directly below the tabular array and it volition absorb the new data into the table.

New entries in the table will then appear in whatsoever dropdown lists with the tabular array as a list item source.

Follow these steps to catechumen your range into a table.

Select your information for the tabular array including the header row.

Go to the Insert tab and click on the Table button in the Tables group of the ribbon.

This volition open upward the Create Tabular array bill of fare with the range selected. Make certain the My table has headers option is checked if your range had a cavalcade heading included. Printing the OK push after you've checked enverything is correct.

Select the table go the Tabular array Design tab and give your new table a proper name. Type over the generic Table1 name with the new name and press Enter.

Now you will be able to create a dropdown listing based on this table.

Select the cell for your dropdown list and click on the Data Validation button in Information tab.

Select Listing in the Allow field.

          = INDIRECT ( "Cars[Model]" )

Enter the above formula into the Source box. This assumes that your table is called Cars, and that Model is a cavalcade header in that table.
Press the OK button.

Your selected cell will now take a dropdown list based on the Model cavalcade from your tabular array.

Using a divers tabular array has huge advantages over the previous methods described.

Yous can use a source that has multiple columns, and you can easily select which column you desire to apply by changing the header name inside the source formula.

If yous require carve up dropdowns for both columns in the tabular array, all you need to practice is re-create and paste the prison cell with the validation into some other location, and modify the column proper name in the source formula.

This is easier than creating a split unmarried-column range name for each column of the data!

It is also easier for you to follow if you take several dropdowns all being driven off of i table. The tabular array is as well dynamic and can be hands changed or updated with new data that will automatically flow into the dropdown list.

Note: If you lot alter the table name, you will need to update the formula used in the Data Validation Source input to reflect the new proper name. This is because the proper noun is reference by a hard coded text string.

Use a Dynamic Array Reference for Dropdown List Items

This is the most flexible method for calculation list items in a dropdown list.

Start by adding a tabular array containing your dropdown listing items. In an side by side cell, insert a formula that references the entire column from the tabular array.

          =Cars[Model]

In this example, the in a higher place formula has been entered in jail cell D3. You can see this creates an assortment which is the exact same as the tabular array cavalcade which information technology references.

If y'all add, edit or delete any items in the table, the array will update accordingly to match.

Yous can then reference this dynamic array inside the Data Validation carte du jour as =$D$three# equally the Source input. The hashtag means it volition reference the entire assortment.

When yous add, edit, or delete items in the table, items in the array volition update. Considering the dropdown list references the assortment, it will also update with the same changes.

How to Add Items to an Existing DropDown Listing

One time you accept created dropdown lists you will probably need to make changes, such as calculation, editing, or deleting items in the list.

In the example of cars that are used here, new models are oft added, or older models are retired and might need to be removed.

Editing Dropdown List Items from a Comma Separated List

Y'all can change the items in a dropdown listing with the post-obit steps.

Select the cell which contains the dropdown list to edit.
Get to the Information tab.
Click on the Data Validation button in the Data Tools group of the ribbon.
This will upwards the Data Validation carte and y'all can add, remove, or edit the list items in the Source input field.
Press the OK button.

Note: This will only update the items in the selected dropdown list. Other like dropdown lists in the workbook will remain unchanged.

Editing Dropdown List Items from a Range Reference

Coming soon.

Editing Dropdown List Items from a Named Range

The data in the single column named range can be changed easily and will reflect through to any dropdown that uses that named range.

Adding a new item is a flake more involved as you will demand to extend the named range. You tin practice this from the Proper name Manager.

Go to the Formulas tab.
Click on the Name Manager button in the Divers Names section.

Select the named range to update.
Update the range reference in the Refers to field to add more cells to the range.
Click on the checkmark to the left of the Refers to field.
Press the Close button.

The Named range will now include the cells which you lot added into the range reference and you tin can enter the new list items at that place.

Notation: Unfortunately, typing new items at the lesser of your named range won't automatically extend the named range. You need to update the range reference manually to ensure new items are included in your dropdown lists.

Editing Dropdown List Items from a Table

Considering tables are dynamic, it is far easier to add, edit, or delete list items.

When you add a new detail at the lesser of your table, the table volition automatically expand to include the new row. This means your new item will announced in the dropdown listing.

Editing or deleting an item is merely as easy!

Type over any detail in the table to edit information technology.

Correct-click on an item and select Delete ➜ Tabular array Rows. This will delete the entire row in the table and remove the item from the dropdown list.

Editing Dropdown List Items from a Dynamic Array

This is the verbal aforementioned procedure every bit editing a dropdown list from a table.

Changes y'all brand to the tabular array will propagate to the dynamic array which drives the dropdown list.

How to Remove a Dropdown List

If you no longer require a item dropdown listing within your spreadsheet, it is very hands removed. Follow these steps to remove a dropdown list.

Select the cell with the dropdown list to remove.
Go to the Data tab.
Click on Data Validation in the Data Tools grouping.
Printing the Articulate All push in the Data Validation menu.
Press the OK push.

This will merely remove the dropdown list from the selected cell and not any other copies of the dropdown listing.

Creating a Dynamic Dropdown List with Offset

Coming soon.

Copy and Paste a Dropdown List

Yous may want to employ your dropdown listing elsewhere in the workbook and this can easily be done past copying and pasting the prison cell to a new location.

You tin use the Paste Special control to paste in but the data validation in the jail cell.

Apply Ctrl + C to copy the cell which contains the dropdown list.

Select the jail cell that you want to re-create the dropdown to and so right-click on the cell, and cull Paste Special from the options.

You can also employ the Ctrl + Alt + Five keyboard shortcut to open the Paste Special carte du jour.

The Paste Special carte du jour will appear and yous can select the Validation pick and click on OK.

You will now have an verbal copy of the dropdown list in your new cell and it volition utilise the same source for its list items.

Create a Dropdown List from Another Sheet

If you desire to re-create and paste or cut and paste a dropdown list into a new sheet, yous might meet a problem when the list items were created using a range reference.

If the range reference was originally created within the same sheet, and then it won't contain a reference to the sheet proper name.

          ='My Canvass'!$B$iii:$B$12

You will need to update the range reference with the canvass name like the to a higher place instance.

Search a Dropdown List in Excel Online

The online version of Excel has a handy feature that allows you to search the dropdown listing by merely typing in a few characters.

This will narrow downward the available listing of options to choose from in the dropdown. This is extremely useful when dealing with a long listing of items!

For example, using the list of car models, you tin can blazon Ac into the prison cell and that will display all entries in the list beginning with Air conditioning. In this case, it volition display Accord and Accent.

In the instance of a very long list, this could pull out several entries outset with F.

As y'all enter more letters, the number of entries in the search list will subtract to list items with a partial lucifer. You can then click on a value in the search listing dropdown to select it.

Case Sensitive Dropdown List Items

Y'all can make your dropdown list instance sensitive by entering your list options equally a comma separated text string.

If a user enters a value that does not correspond to an item in the list in both value and example, then an error message will appear.

This volition ensure that the final value in the validation cell matches the case of the listing items.

Note: Whatsoever other source used for your listing items will allow whatever variation of case to be entered into the cell.

Remove Duplicates from Listing Items

When you select data for listing items, you may observe that there are duplicates within that data.

Duplicates that are in the source data for the list items volition testify up in the listing and there is no option in the Information Validation bill of fare to remove them.

If yous include them in the dropdown list, this can cause confusion for the user when they multiple choices which are the same. It's best to remove any duplicate values from the list items.

How you remove the duplicate values will depend on whether your version of Excel has dynamic arrays.

Using a Non-Dynamic Assortment

Coming before long.

Using a Dynamic Array

When you lot accept dynamic arrays, getting a listing of unique items is easy. Yous tin can apply the UNIQUE role to render the items with the duplicates removed.

          = UNIQUE ( Cars[Make] )

Yous tin can use the above formula which references a table named Cars that contains a column named Make. This column contains a few repeated items.

Detect the UNIQUE part returns all the items from the table but does not repeat whatever item of them.

Now y'all tin reference this dynamic array as the listing of items for the dropdown list.

Sort List Items in a Dropdown List

The Data Validation menu gives no pick to sort the list items into alphabetical lodge.

Sorting the list items will aid make finding an detail in a long list much easier.

Fortunately, sorting list items can be done outside of the Data Validation menu and is a fairly piece of cake implementation.

Using a Non-Dynamic Array

Coming soon.

Using a Dynamic Assortment

With dymic arrays, sorting is besides quite like shooting fish in a barrel. Y'all can use the SORT function to sort your list items for the drop down list.

          = SORT ( Cars[Model] )

The above formula can exist used to sort a column in alphabetical order and the results tin can then be references in your dropdown list source input.

Edit All Dropdown Lists

When your spreadsheet has many verbal copies of the aforementioned dropdown list, you lot may need to update them all when adding, editing, or deleting list items.

This is especially true when you are using driblet-down lists with comma-separated listing items.

Thankfully, in that location is an easy choice to update all your dropdown lists at the same time.

Follow these steps to update all your dropdown lists that use the same settings.

Select one of the dropdown lists to edit.
Get to the Data tab.
Select the Data Validation command in the Data Tools department.
Brand any changes to the Source list.
Bank check the Apply these changes to all other cells with the same settings option.
Printing the OK push button.

When you check this choice in the Information Validation menu, you volition come across all dropdowns with the aforementioned settings will get selected in your sheet. When you printing the OK button, the changes are made to all these cells.

Note: This will simply affect dropdown lists in the current sheet! If you have dropdowns using the aforementioned settings merely located in other sheets, then you volition need to update those sheets separately.

Error Alerts for Dropdown Lists

The best thing about drop-down lists is they force users to input information correctly.

If a user tries to skip selection from the dropdown list and instead enter their own information, Excel volition bear witness a warning and entry volition exist prevented.

Information Validation for lists gives you the flexibility to alter the default error alert message and also to alter the icon used in the error message.

You lot can customize the error message in the Error Alert tab of the Data Validation carte du jour.

Make sure the Testify error alert afterwards invalid data is entered option is checked. Information technology should be enabled by default.
Select the Style of alert.
- Stop volition prevent the user from entering any value not in the list.
- Alert will alarm the user the detail is not in the list, but will let them decide if they however want to enter the value or not.
- Information volition but alarm the user the particular is not in the list simply will go along the value entered.
Add together a Title for the alert.
Add an Error message for the alert.

Printing the OK push once you've adjusted the error alert settings to your liking.

Now when a user tries to enter a value into the cell which is not in the list, a pop-up alert will evidence with your custom message. The above example shows a Warning alert which gives the user the Aye or No option to continue with the entry.

Input Letters for Dropdown Lists

You can create an input message for your dropdown list. This will appear when the user selects the cell containing the dropdown listing.

Click on the Input Message tab in the Data Validation pop-up window, and enter a title (optional) and a message for the user to see.

You can create an input bulletin from the Input Message tab of the Data Validation carte du jour.

Make sure you bank check the option to Show input bulletin when cell is selected. This will let the pop-upward to display when the cell is selected.
Add a Championship for the pop upwards message.
Add the Input bulletin to be displayed in the pop up.

Press the OK push button to salve the popular-up message on the dropdown list.

Now when y'all select the cell with the dropdown listing, a pop-up will show with your custom bulletin. This is a great mode to add together any required instructions for the spreadsheet user as information technology doesn't even require the use of a dropdown.

Allow Entries Non in the Dropdown Listing Items

You lot may have a situation where y'all are using a dropdown in a cell, but you lot want to allow the user to enter values outside of the dropdown list.

This can be done from the Error Alert tab in the Information Validation carte du jour.

Uncheck the Show error alert afterwards invalid information is entered pick.

This volition requite the user the option to use the dropdown list to select a value, but will not require information technology.

If a user does non selection from the list it volition suppress the error bulletin and allow any value to be entered in the prison cell.

Create a Dependent Dropdown List

Utilize car make and model data where car model dropdown choices depend on car make option.

Solution without Dynamic Arrays

Coming soon.

Solution with Dynamic Arrays

Coming presently.

Create a Dropdown List with VBA

          Sub CreateDropdown()     With Pick.Validation         .Delete         .Add Blazon:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _         xlBetween, Formula1:="Yep, No, Perhaps"         .IgnoreBlank = Truthful         .InCellDropdown = True         .InputTitle = ""         .ErrorTitle = ""         .InputMessage = ""         .ErrorMessage = ""         .ShowInput = True         .ShowError = True     Finish With End Sub

You can use VBA to create a dropdown listing.

The above VBA code can be used to create a bones dropdown from a comma separated listing of items.

Create a Dropdown List with Office Scripts

          function principal(workbook: ExcelScript.Workbook) { 	let selectedSheet = workbook.getActiveWorksheet(); 	selectedSheet.getRange("B2").getDataValidation().setRule({ list: { inCellDropDown: true, source: "Yes, No, Maybe" }}); 	selectedSheet.getRange("B2").getDataValidation().setPrompt({ showPrompt: true, bulletin: "", title: "" }); 	selectedSheet.getRange("B2").getDataValidation().setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.stop, bulletin: "", championship: "" }); 	selectedSheet.getRange("B2").getDataValidation().setIgnoreBlanks(true); }

Y'all can use Office Scripts to create a dropdown list.

The above TypeScript lawmaking tin be used to create dropdown list based on a comma separated fix of list items.

Keyboard Shortcuts for Using Dropdown Lists

In that location are useful keyboard shortcuts that you can apply in conjunction with a Information Validation dropdown list.

Alt + Down Arrow will actuate the dropdown list and is the aforementioned equally clicking the downward arrow on the control.
Up and Down Arrow keys will let you to navigate and to move up and down the dropdown list during pick.
Enter will cull the particular value that is highlighted in the dropdown listing.
Alt + A, V, Five will take you direct to the Information Validation bill of fare.

You can customize the QAT by calculation your favorite commands and then they are easily attainable at all times.

This will allow you to create your own keyboard shortcuts considering every command you add to the QAT volition become its ain keyboard shortcut based on its position.

For example, the second control in your QAT can be used by pressing Alt + two on your keyboard.

This means y'all tin add the Data Validation command to the QAT and access it with a customized keyboard shortcut.

Get to the Information tab and right-click on the Data Validation command. Select Add together to Quick Access Toolbar from the bill of fare and the command will exist added to your QAT.

In this example, the data validation command is the third detail in the QAT then you can press Alt + three to admission it with the keyboard.

When you press the Alt central, the hotkey labels will bear witness you lot what key to press next in order to admission the commands.

Create a Dropdown List from Data Above the Current Cell

A useful feature in Excel is the ability to create a dropdown list from the data direct above the current cell.

Select the cell direct below a cavalcade of data values.
Correct-click on the cell and select Selection From Drop-down List.

A dropdown will exist instantly created in that jail cell based on the values to a higher place. The prissy thing well-nigh this feature is it volition only show a list of unique values and they will be sorted in alphabetical order.

The downside is that the dropdown list is not permanent, and the user has to correct-click the cell once again each fourth dimension they desire to employ it.

Find All Dropdown Lists in a Sheet with Go To Special

Data Validation dropdown lists are hard to find within an Excel workbook. They remain invisible until the jail cell is selected, and the selector key appears to the correct of the cell.

In that location is a fashion of highlighting all data validation cells on a spreadsheet.

Select a cell which contains the dropdown list you want to detect.

Go to the Home tab.
Select Find & Select from the Editing section.
Select Go To Special to open up the Go To Special menu.

You can as well printing F5 and the Go To window will open up, so you lot can press the Special button to open the Go To Special carte.

Select the Data Validation option.
Select Aforementioned from the Data Validation options.
Press the OK button.

This will select all the cells in the sheet with the exact same data validation. This means it volition differentiate betwixt lists with slightly different list items!

Annotation: The All option volition find and select other types of data validations in the sail and not just lists.

Dropdown List Template Tutorial

Dropdowns are and then important and and then widely used in Excel, that there is a dedicated tutotial template for dropdown lists which can be accessed from the File carte du jour.

Go to the File tab and you click on the Drop-down tutorial template and then click on the Create push.

This template will take you on a guided and interactive bout of dropdown lists.

You tin too download the template here.

Conclusions

Very frequently the wrong input tin can lead to errors in your spreadsheets.

Data Validation dropdown lists are very useful, for guiding or restricting a user as to what input they tin use in certain cells to help avoid errors.

In that location are many means of constructing dropdown lists, including from a comma-separated list, a range, a named range, a tabular array, or a dynamic array.

A simple dropdown list is normally all that is required in nearly cases, but advanced setups such equally dependent lists tin can be accomplished with a bit of try.

Advanced options are also available with your dropdown lists such equally input letters and mistake alerts.

These all brand dropdowns a versatile y'all need to outset using in your spreadsheet solutions.

Are you using dropdown lists in your Excel workbooks? Do you have whatsoever special dropdown list tips I missed? Allow me know in the comments below!

About the Writer

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a diverseness of industries, including insurance, advertizing tech, and about recently Power Platform consulting. He is a keen trouble solver and has a passion for using technology to brand businesses more efficient.