Google Sheets Dropdown Formatting: Colors and Rules

To format a dropdown in Google Sheets, edit its data-validation rule and assign colors to its options; to format other cells or an entire row according to the selected option, use Format > Conditional formatting. The first method colors the dropdown choice itself, while the second applies ordinary cell formatting based on the value.

What dropdown formatting controls

A dropdown is a data-validation control: it limits or guides what people can enter in a cell. In Google Sheets, you can give its choices colors and decide how the control appears. These settings are separate from standard formatting such as font, borders, and number formats. That distinction matters because changing a cell’s fill color manually does not assign a color to a dropdown option, and assigning a color to an option does not automatically color neighboring cells.

For example, a project tracker might have a Status dropdown with choices such as Not started, In progress, and Complete. You can color those choices gray, yellow, and green so the status is easy to scan. If you also want the full project row to turn green when its status is Complete, add a conditional-formatting rule for the row. Using both approaches makes the selected status clear without confusing the dropdown’s own appearance with the formatting of the surrounding data.

Create or edit a dropdown

In a browser, select the cell or range where the dropdown should appear. You can create one through Insert > Dropdown, or open the data-validation controls through Data > Data validation. If the selection already has a rule, edit that rule in the data-validation side panel. The exact panel wording may change as Google updates Sheets, but the rule will show the range, criteria, and dropdown choices.

Choose Dropdown for a list you will type into the rule, or choose Dropdown (from a range) to use values already stored in cells.
Add or edit each choice. Use the color control beside an option to choose its color.
Choose what happens if someone enters a value that is not allowed: reject it or show a warning, where that setting is available in the rule.
Save the rule. If you selected multiple cells, the dropdown applies across that selected range.

A range-based dropdown is useful when the same list is used in many places or may change over time. For instance, maintain department names in a separate Lists sheet and use that range as the source. If a list has to grow, make sure the source range includes the new entries; a dropdown cannot offer values that sit outside its configured source. A directly entered list is convenient for a short set of choices that is unlikely to change.

Set option colors and display style

In the rule editor, assign a color to each choice using the color selector beside that choice. Color is associated with the dropdown option, so a cell displaying that choice can show the corresponding colored chip. Choose colors with enough contrast to keep the label readable, and avoid relying on color alone to communicate important information. A text label such as Overdue is clearer and more accessible than a red marker without an explanation.

Google Sheets also offers display styles for dropdowns. Depending on the rule editor, look under Advanced options for Display style. Chip presents the selection as a colored pill; Arrow shows a dropdown indicator in the cell; Plain text displays the selected value without the chip treatment. The chip is prominent and useful for trackers, while plain text can fit better in compact tables or sheets where a minimal appearance is preferred. The style changes presentation, not the allowed values.

Changing the display style does not convert the dropdown into a different kind of data. The selected label remains the cell value, which can be used in formulas, searches, filters, and conditional-formatting rules. If a cell looks like a colored chip but you need the same color on a printout or on adjacent cells, use conditional formatting rather than assuming the chip color is a normal cell fill.

Format cells or rows based on a selection

To format cells based on their dropdown value, select the target range and choose Format > Conditional formatting. In the sidebar, set Apply to range, then choose a condition such as Text is exactly and enter the dropdown label. Pick the text color or fill color you want. Add another rule for each value that needs a different appearance. The condition must match the dropdown’s actual text, including spaces and punctuation.

For an entire row, apply the rule to the full table range and use a custom formula that checks the status column. For example, if the table occupies A2:F100 and status is in column C, set Apply to range to A2:F100, select Custom formula is, and enter =$C2="Complete". Choose a fill style and select Done. The dollar sign fixes the checked column at C, while the row number remains relative so each row checks its own status. If the status is actually in D, change the formula’s column reference to D.

To create several row colors, add separate custom-formula rules for the other labels, such as In progress or Not started. Keep the formula’s starting row aligned with the first row in Apply to range. If the range begins at row 3, use row 3 in the formula. This alignment is a frequent source of rules that appear one row off. Conditional formatting changes appearance when values change; it does not change the dropdown choices or prevent invalid input.

Choose between a dropdown chip and conditional formatting

Use option colors when you want the selected label itself to stand out. Use conditional formatting when the appearance should extend beyond the dropdown cell, depend on a more complex condition, or format cells that do not contain the dropdown. They can work together: a colored status chip can identify the value, while a subtle row fill helps readers follow the record across several columns.

Colored option: Best for a concise status, priority, or category label displayed in the dropdown cell.
Conditional formatting: Best for coloring a whole row, highlighting related values, or applying a rule based on multiple cells.
Manual fill or text formatting: Best when the appearance is fixed and should not change when the value changes.

Keep the visual system consistent. If red means urgent in one part of a workbook and complete in another, readers have to relearn the meaning. Include the words as well as the colors, and consider how the sheet looks when printed or viewed by someone using a different display. Too many bright fills can make a busy tracker harder, not easier, to read.

Common formatting problems and fixes

The option has no visible color. Reopen the data-validation rule and check that a color is assigned to the exact option. Also check whether the display style is Plain text, which does not show the chip treatment in the same way as Chip.
The whole row does not change. Check Apply to range, the custom formula’s column, and its starting row. A rule applied only to C2:C100 cannot color A:F, and a formula that checks the wrong column will not match the status.
A rule works for one value but not another. Confirm the rule uses the same spelling, spacing, and punctuation as the dropdown label. Repeated spaces or a slightly different label can prevent an exact-text condition from matching.
The dropdown allows unexpected entries. Check whether the rule is set to reject invalid input or merely show a warning. A warning signals a mismatch but may still allow the entry.
Formatting seems to disappear. Look at the conditional-formatting sidebar for overlapping rules and their order. A later or conflicting rule can affect what is visible. Also verify that the cells still have the validation rule; copying and pasting cells can bring different validation or formatting with the copied content.
The color does not appear in another app. Dropdown chip styling is a Google Sheets presentation feature. When exporting, copying, or opening a file elsewhere, check the result rather than assuming every application will reproduce the same chip appearance.

Dropdowns from a range and maintenance

When a dropdown is sourced from a range, the list values and their colors have different jobs. The source range supplies the available text; the dropdown rule controls validation and option presentation. Keep the source list tidy, with one choice per cell and no accidental blank rows within the intended list. If you rename a source value, review formulas, conditional-formatting rules, and other references that expect the old label.

For a shared workbook, put the source list in a clearly labeled area or a dedicated sheet and document who maintains it. Protecting the list cells can help prevent accidental edits, but protection should be configured with the right collaborators and permissions. Test the dropdown after changing its source range, and test conditional rules with each expected choice. This small review catches mismatched labels before a tracker is widely used.

How Google Sheets differs from Excel

These instructions and menu paths are for Google Sheets in a web browser. Excel also supports data-validation lists and conditional formatting, but its menus, rule editor, and dropdown presentation are different. Excel’s Data Validation feature does not provide the same native colored dropdown-chip experience as Google Sheets. In Excel, conditional formatting can color the cell based on its value, but that is not the same as assigning a color to each choice inside a Sheets dropdown.

If a workbook moves between Sheets and Excel, verify both the validation list and the visual result in the destination program. A list may remain usable while a chip style or a particular conditional-formatting rule is represented differently. For a cross-platform file, use descriptive text labels and straightforward conditional formatting, and avoid making color the only way to identify a status.

Frequently Asked Questions

Can I color each choice in a Google Sheets dropdown?
Yes. Edit the dropdown rule through Insert > Dropdown or Data > Data validation, then assign a color to each option in the rule editor. Choose a display style that shows the colored chip if you want the color visible in the cell.

Can a dropdown choice color the entire row?
Yes. Use Format > Conditional formatting on the full row range and add a Custom formula is rule that checks the dropdown column. Anchor the column with a dollar sign and use the correct first row in the formula, such as =$C2="Complete" for a range starting at row 2.

Can I change the dropdown color without changing its value?
Yes. Edit the option’s color in the data-validation rule. The color is a visual setting; changing it does not rename the choice or alter the cell’s selected text.

Why does my conditional-formatting formula not work?
Check that the rule applies to the intended cells, the formula checks the correct column, and the starting row matches the top row of the applied range. Also confirm that the text in the condition is exactly the value selected from the dropdown.

Will dropdown colors look the same in Excel?
Not necessarily. Google Sheets chip styling is specific to Sheets, and another spreadsheet application may not display it the same way. Check the file after exporting or opening it elsewhere, especially if the color communicates an important status.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.