OmniDocConditional Formatting From a Dropdown or List
Learn how to color Excel cells based on a dropdown selection or a list of allowed values, with formula rules you can check and fix.
If you use a dropdown to track status, approvals, or priority, you probably want the matching rows to stand out. A "Blocked" task should look different from a "Done" task, and you should not have to scan the column to find them. Conditional formatting from a list solves this: the color changes automatically when someone picks a value from the dropdown.
This guide is for anyone who keeps a spreadsheet with a dropdown column, such as a project tracker, expense log, or inventory sheet. You will set up the dropdown, write a rule that reads the selected value, and check that every choice colors the way you expect. The method uses standard spreadsheet features, so it works in any desktop spreadsheet program that supports data validation and formula-based conditional formatting.
Many people search for this as "excel conditional formatting from list" or "excel conditional formatting from dropdown." The core idea is the same in both cases: the rule checks the text in the dropdown cell, and the formatting follows from that check.
Can you color cells from a dropdown? The short answer
Yes. Create the dropdown with data validation, then add a conditional formatting rule that uses a formula comparing the dropdown cell to one allowed value. Pick the approach that matches how your sheet is laid out.
| Your situation | What to do | Why |
|---|---|---|
| One dropdown cell controls one cell's color | Use a "cell value equals" style rule with the exact list text | Simple and easy to audit |
| A status column should color the whole row | Use a formula rule such as =$C2="Blocked" and apply it to the full row | Keeps the row readable at a glance |
| The list has many values, each with its own color | Create one rule per value, and keep the values in the list identical to the rule text | Each rule is short and easy to change |
| The list lives on another sheet | Name the list range, then point the dropdown source at that name | Some versions reject direct cross-sheet references |
| The sheet will be translated into another language | Update the dropdown values and rule text together after translation | A mismatch silently breaks the highlighting |
Before you start
Check these items first. Each one prevents a common reason the color does not appear.
- Your list values. Write down every allowed value exactly as you want it to appear, such as "Open," "In review," "Blocked," and "Done." The rules compare text, so "Done" and "Done " (with a trailing space) are different values.
- Where the list lives. Decide whether the list sits on the same sheet or on a separate sheet. A list on the same sheet is simpler to maintain, and a named range makes a cross-sheet list easier to reference.
- The row range. Decide which cells should change color. If the dropdown is in column C and the whole row should highlight, select A2 through the last column of your data, not only column C.
- Whether the data already contains typed values. Existing text that does not match the list will not trigger a rule. Clean up those entries or replace them with dropdown choices.
- A backup copy. Rules are easy to change, but a copy of the file lets you compare before and after if a formula goes wrong.
Creating the dropdown and the colored rule, step by step
- Enter your list values in a range. Put the allowed values in a column, such as F2 through F5, on the same sheet or a dedicated list sheet. This range becomes the source for the dropdown, so typing the values here keeps them in one place.
- Add the dropdown to the target column. Select the cells that should hold the dropdown, such as C2 through C100. Open the data validation settings, choose the list option, and point the source at your range. Users can now pick a value instead of typing one.
- Select the full data range before adding the rule. Select A2 through the last column of your table, for example A2:E100. Selecting the whole row first lets you write one formula that covers every row. Starting from the first data row matters, because a formula written for row 2 shifts correctly only when it is anchored to the first row.
- Open the conditional formatting rules. Find the conditional formatting option in your spreadsheet program's formatting controls, then choose the option to create a new rule. Pick the option that lets you enter a formula, since it gives you more control over the text match.
- Enter a formula that compares the dropdown cell. For a dropdown in column C, with the first data row at row 2, enter a formula such as =$C2="Blocked". The dollar sign before C locks the column so every row checks its own column C value. The row number without a dollar sign moves down as the rule applies to each row.
- Set the format for that rule. Choose a fill color or font color that stands out, such as a light red for "Blocked." Keep colors consistent across the sheet so readers learn what each one means.
- Repeat for each status you want to highlight. Add one rule per value, changing only the text in the formula and the color. Order matters less here, because each rule checks a different value, but avoid overlapping rules that set the same property to different colors.
- Test each dropdown choice. Pick each value from the dropdown and confirm that the expected color appears. If a choice does nothing, the list text and the rule text probably differ, which is covered in the next section.
Check the result
Once the rules are in place, test the sheet with real choices, not only one example. Use the table below to find and fix problems.
| Check | How to see it | If it's wrong |
|---|---|---|
| Every dropdown choice colors the right row | Select each value in one row and watch the color | Compare the rule text with the list value character by character |
| The color appears across the whole row | Look at columns outside the dropdown | Confirm the rule range starts at the first data row and includes every column you want colored |
| Rules are not competing | Open the rule list and read each rule's formula and range | Remove duplicate rules or change their order so the intended color wins |
| Typed values are not left behind | Filter the dropdown column for entries not in the list | Replace free-text entries with valid dropdown values |
| The sheet still works after edits | Add a new row, copy a row, and insert a row | Re-check that the rule range expands, or extend it manually |
If a rule works in one row but not the next, the likeliest cause is a missing dollar sign in the formula. Without it, the reference drifts across rows in the wrong direction. If the color never appears, check for extra spaces and different capitalization in the list. Text comparisons in these rules are not case-sensitive, but spaces still matter.
Working with translated spreadsheets
Some workbooks have dropdown labels in one language, and the team needs them in another. You can upload an Excel file (.xlsx) to OmniDoc on omnidoc.top for translation, and you can choose bilingual output or translation-only output. After the file comes back, open it and check two things: that the dropdown list shows the translated values, and that each conditional formatting rule still matches a value in the list.
The translated list and the rule formulas must use the same text. If the dropdown now says "Bloqueado" but the rule still checks for "Blocked," the color will stop working. Update the formulas to match the translated values, then run the same choice-by-choice test described above. Check the layout and any formulas in the translated file too, since you should confirm that everything you rely on survived the conversion.
❓ FAQ
1. Can I color a cell based on a dropdown in a different sheet?
Yes, but the setup depends on your version. Put the list on a separate sheet, give that range a name, and use the name as the dropdown source. Then write your formulas against the cell with the dropdown, which can stay on the main sheet.
2. Why does my conditional formatting not change when I pick a value?
A common cause is a mismatch between the list text and the rule text, including extra spaces. Compare them exactly. The other frequent cause is a rule range that does not include the cells you selected.
3. Can one rule color different values with different colors?
Not in a single rule. Each rule applies one format when its condition is true, so use one rule per value. Keep the number of rules manageable by highlighting only the statuses that need attention.
4. Will the colors still work if I copy the sheet?
Usually yes, but copying can change which rule range applies, especially if you copy only part of the table. Check the rule manager after copying, and confirm that the formulas still point to the dropdown column.
5. What if the list is long, with twenty or more values?
Color only the values that need to stand out, such as "Blocked" or "Overdue," and leave the rest uncolored. Twenty separate colors make the sheet harder to read. A short list of highlighted states is easier to scan.
📝 Summary
Conditional formatting from a dropdown or list works by comparing the selected value to a fixed text value in a formula. Set up the dropdown first, write one rule per status with exact text matches, and test each choice. If you translate the workbook, update the rule text to match the new list values so the colors keep working.
