Copying and Pasting Drop-Down Lists in Excel Using a relative reference is important when creating dependent lists which will be shown a little later in this article. This makes it easy to create other checkbox examples by just copying the cells to the right. In the example above, the drop-downs use a relative reference in the Source field (no $ signs in the reference). However, there may be times when you want the drop-down Source to change when you copy and paste the cell. Usually, you will use absolute references like $C$76:$C$77 for the Source in your drop-down list. So, if you want a blank value as an option, use a reference to a range as in the examples below. Including a Blank Value and Using Relative ReferencesĪn in-cell drop down will ignore blanks if you enter text manually into the Source field (like " ,Yes,No"). This example comes from the Feature Comparison template. The Source field is just "☐,√" (without the quotes).Įxample 2: Choose a Star Rating using a Drop Down Menuįor a star rating, you can use "★★★★★,★★★★,★★★,★★,★" in the Source field. This example comes from one of my Task List templates. Example 1: Using a Drop Down List to create a Checkbox field Also, when using a touch screen device, I think the drop-down checkbox is easier and more fun to use than entering an "X". Important: One of the main reasons I like to use checkbox-style drop-down lists is for compatibility and ease-of-use with Excel Online and the mobile Excel apps (Form Field checkboxes don't work in Excel Online or mobile apps). What I think is awesome, though, is using Unicode Character Symbols to do fun things with drop-down lists, such as star-ratings using ★ or checkboxes using the characters √, ✔, ☐, ☑ or ☒. This means that you can't show different colors and fonts within the drop-down list. The font used in the drop-down list cannot be changed, so it is always just a black sans serif font. Check Boxes and Star Ratings with Excel Drop-Down Lists If you're thinking of getting fancy and want to define a name without a cell reference such as =, the drop-down list won't work.Īnother bit of trivia: In old versions of Excel, using a named range was the only way for a drop-down list to reference a range on a different worksheet. Note: When using a named range for a data validation list, the named range must be defined as a reference to a range of cells, or it must be a formula like OFFSET or INDIRECT or INDEX that returns a reference. Why? If you want to change the range, you only need to edit the defined name (via Formulas > Name Manager) rather than finding and editing all cells that use that particular data validation. You could use a reference for the Source field like =Sheet2!$A$1:$A$3, but I usually prefer to name the list. In the data validation dialog box, instead of entering the list manually, you enter a reference to the named range in the Source field as shown below:
You can later hide the worksheet containing your list to keep your workbook looking nice and clean or to prevent a user from changing the list.
In this case, I've named the range "myList". For example, let's say I have a separate worksheet with my list defined in cells A1:A3 as shown below. Instead of manually entering the list of items in the data validation dialog box, you can reference a range of cells. The more elegant approach is to use a reference to a range, or even better than that - a named range.
HOW TO MAKE A DROP DOWN LIST IN EXCEL SEARCHABLE UPDATE
The problem with this approach is that if you use this in a lot of cells and later want to update the list, you have to update all cells that use the list and there is a good chance you'll miss one. This approach is great for simple Yes/No options and other lists that appear only once in your spreadsheet. Entering the Source of a Drop Down List as a Comma-Delimited List
0 kommentar(er)
