Excel Drop Down List With Search Suggestions

Wednesday, November 16th 2022. | Sample Templates

Excel Drop Down List With Search Suggestions – I love using dropdown menus in Excel! They are very simple to create and are a great way to make your spreadsheet easier to use. In this article, I will first show you how to create a dropdown in-cell using data validation, and then I will show examples that demonstrate the awesome things you can do with dropdowns.

This approach is great for simple Yes/No options and other lists that appear only once in your spreadsheet.

Excel Drop Down List With Search Suggestions

Excel Drop Down List With Search Suggestions

The problem with this approach is that if you use this on multiple cells and later want to update the list, you will need to update all the cells that use the list, and there is a good chance you will lose one. A more elegant approach is to use a reference to a set, or even better – a set name.

How To Create A Drop Down List In Excel 2007: 10 Easy Steps

Instead of manually entering a list of items in the data validation dialog, you can reference a range of cells. For example, let’s say I have a separate worksheet with my list defined in cells A1:A3 as shown below. In this case, I called the set “myList”. You can later hide the worksheet containing your list to keep your workbook nice and clean, or to prevent the user from changing the list.

In the data validation dialog, instead of entering the list manually, you enter a reference to the named row in the Source field as shown below:

You could use a reference in the Source field like =Sheet2!$A$1:$A$3, but I usually prefer to name the sheet. Why? If you want to change the range, all you have to do is modify the specified name (via Formula > Name Manager) instead of finding and editing all the cells that use this data validation.

Note: When using a range name in a data checklist, the range name must be defined as a reference to a range of cells or be a formula such as OFFSET or DISIRECT or INDEX that returns a reference. If you’re going to get fancy and want to specify a name without a cell reference like =, the dropdown won’t work.

Using Searchable Dropdown List On Forms

Another bit of trivia: In older versions of Excel, using a range name was the only way for a dropdown to refer to a range in a different worksheet.

The font used in the dropdown cannot be changed, so it is always just a black sans serif font. This means that you cannot show different colors and fonts in the dropdown list. However, I think it’s great to use Unicode character symbols to do fun things with dropdowns, like star ratings ★ or checkboxes that use √, ✔, ☐, ☑ or ☒.

Important: One of the main reasons I like using drop-down-style checkboxes is compatibility and ease of use with Excel Online and the Excel mobile apps (checkbox fields don’t work in Excel Online or the mobile apps). Also, when using a touch device, I find the drop-down box easier and more fun to use than typing an “X.”

Excel Drop Down List With Search Suggestions

This example is from one of the To Do List templates. The Source field is just “☐, √” (without quotes).

Adding A Blank Option In A Data Validation Drop Down List In Excel

For star rating, you can use “★★★★★, ★★★★, ★★★, ★★, ★” in the Source field. This example comes from the Feature Comparison template.

A dropdown in the cell will ignore spaces if you manually enter text in the Source field (such as “, Yes, No”). So if you want an empty value as an option, use a reference to the array as shown in the examples below.

Usually you use absolute references like $C$76:$C$77 in the Source dropdown. However, there may be times when you want to scroll the Source to change when you copy and paste a cell. In the example above, the dropdowns use a relative reference to the Source field (there is no $ sign in the reference). This makes it easy to create other examples of checkboxes by copying the cells to the right.

Using a relative reference is important when creating dependent lists, which will be shown a little later in this article.

Excel Tips For Becoming A Spreadsheet Pro

When you copy and paste cells, data validation is also pasted, but you cannot copy and paste data validation using the Format Painter. If instead you want to copy and paste just the drop-down list (and not the formulas or formats), after copying the cell, use the Paste as function and select the Confirm option as shown.

I like to create templates that allow someone to edit lists, like food for a meal planner, or accounts for a ledger, or items for a purchase order for a price list.

If you want to allow a variable number of items in the source row, you can use a very large range like =$A$1:$1000, but the dropdown would have a crazy number of empty items. Instead, you can create a dynamic range that expands the list to the last value in the range.

Excel Drop Down List With Search Suggestions

You can see that although the list range $C$127:$C$133 has two empty cells, the dropdown only extends to row 131 (the last text value in the category column).

Clear All Under Data Validation

Go to Formulas > Name Management and create a range called Category List using this formula in the Reference field and replace the title and range cells of the list with the appropriate cell references.

See my article “Dynamic Named Ranges” for more information on the different formulas you can use. The formula used in the example above works well for lists containing only text values.

A dependent drop-down list is a list that changes based on the value of another cell and can also contain its own drop-down list. The dropdown source can be a formula, and is the key to making a dependent list. As I mentioned earlier, the formula must return a reference, so there are only a few types of formulas in the dropdowns. Personally, I prefer to use SELECT or DIRECT.

The example below is based on an account register, where the idea is to select categories for each transaction. The Type column contains a drop-down list that references cells C179:D179 (labeled “Expenses” and “Revenues”). We want the Category column dependency to use a list of expenses if the type is “Expenses” and a list of income categories if the type is “Income”.

How To Create A Drop Down List In Google Sheets

The formula uses a relative reference to the type cell and an absolute reference to the type_values, price range, and income range as follows:

Alternatively, we could create a dynamically named array named ExpenseRow and RevenueRow, and use the following formula for the source:

You can also use named ranges in the SELECT formula, so I’m not sure if one method is better than the other. Some may argue that SELECT is better because INDIRECT is a volatile function, but I don’t think that’s important for dropdowns.

Excel Drop Down List With Search Suggestions

See the Food Cost Book template for a practical example of how dependent lists can be used in a spreadsheet.

Ways To Remove Suggestions On Chrome

If the previous examples aren’t cool enough for you, my article “Dynamic Dropdown Lists” explains how to create dropdown lists that change based on user input, dates, check numbers, etc.

If the drop-down list is really long, it may be difficult to find the product you are looking for. Google Sheets offers a great solution, although not (yet) perfect. In Sheets, you can start typing in a cell and the dropdown list will automatically filter based on what you typed…as long as it’s the beginning of one or more words in the list.

For example, suppose your list contains the names Abe Lincoln, George Washington, Harry Truman, and J. Edgar Hoover. As soon as you type “h,” the list shortens to Harry Truman and J. Edgar Hoover, but Sheets doesn’t recognize the h in Washington.

Excel: With a rather complicated trick, you can create a Searchable dropdown menu in Excel. Watch this youtube video.

How To Get Google Sheets Formula Suggestions

In my article Add cool features to your Excel task list, there are a few more examples, such as using conditional formatting along with a drop-down box to select a priority value from a to-do or to-do list.

Example 1: This task list allows you to select a high, medium, or low value in the Priority column. There is also this example on the To Do List template page.

Example 2: This task list template uses the conditional formatting icon set for the Priority column and a drop-down list to select a value between 1-4.

Excel Drop Down List With Search Suggestions

More examples: Drop-down lists are a common feature in many of my templates, including Meal Planners, Money Managers, and many financial calculators. You can download the templates to see how dropdown lists are created.

Create A Drop Down List In Excel To Restrict Data

The Customer List Template page describes how to copy the customer list worksheet into a spreadsheet, create a list of customer names, and add lookup formulas to display the selected customer information.

The first version of this article was published on 04/07/2009 at https:///ExcelTips/drop-down-list.html, but the original article has been updated.

Excel quote template with drop down list, drop down with search, dynamic drop down list excel, excel drop down list, excel spreadsheet with drop down list, excel drop down search, excel search drop down list, conditional drop down list excel, excel insert drop down list, search suggest drop down list, drop down list with search, excel create drop down