Formatting Issues

How to Change Excel Cell Color from a Dropdown Without Showing Text

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to apply a specific background fill color to a cell based on a data-validation dropdown selection, while simultaneously hiding the selected text from being displayed in that cell.

Product: Excel
Device & OS: not provided
Scenario: Formatting a spreadsheet where cells need to display distinct color codes corresponding to hidden dropdown values (like location names) without cluttering the visual output with text.
Observed behavior: The goal is achieved by combining conditional formatting to apply the color rules and a specific custom number format to render the cell's text invisible while retaining its underlying value.

Before you start

Ensure you have decided on the list of items (e.g., locations like London and Brighton) you want in your dropdown menu before setting up the formatting rules.

Solution 1Recommended

Use Conditional Formatting and Custom Number Formatting

Combine data validation for the dropdown, conditional formatting for the colors, and a custom number format (;;;) to hide the text.

To achieve this, you need a three-step approach: first, creating the dropdown menu; second, telling Excel which colors to apply based on the selected word using conditional formatting; and third, making the text itself invisible without deleting the underlying data.

Create the Dropdown List

Select the target cells. Go to the Data tab, click on Data Validation, choose 'List' from the Allow dropdown, and enter your items (e.g., London, Brighton) separated by commas.

Apply Conditional Formatting Rules

Keep the target cells selected. Go to the Home tab, click Conditional Formatting, and select 'New Rule'. Choose 'Use a formula to determine which cells to format'. Enter a formula using a relative reference, such as =A1="London" (assuming A1 is the first cell in your range). Click Format, select the Fill tab, pick a color (e.g., green), and click OK. Repeat this process to add rules for other dropdown items.

Hide the Cell Text

With the cells still selected, right-click and choose 'Format Cells'. Go to the Number tab, select 'Custom' in the Category list, and type ;;; (three semicolons) in the Type box. Click OK. The cell will now change color based on the dropdown, but the text will remain hidden.

Using Relative References: It is crucial to use relative references (like =A1 instead of =$A$1) in your conditional formatting formula. This ensures that when the rule is applied to a larger range, each cell evaluates its own dropdown value rather than all cells looking at the first cell.

Advanced Formatting Made Easy

Easily Color Code Dropdowns and Hide Text in WPS Office

WPS Spreadsheet offers powerful, intuitive tools for data validation and conditional formatting. You can seamlessly set up color-coded dropdowns and apply custom number formats to hide text, just as you would in Excel.

1. Set up Data Validation: Open your worksheet in WPS Spreadsheet, select your range, go to the Data tab, and click Validation to create your dropdown list.
2. Add Conditional Formatting: Navigate to the Home tab, click Conditional Formatting, and add new formula rules (e.g., =A1="London") to apply specific fill colors.
3. Apply Custom Format: Right-click the formatted cells, select Format Cells, navigate to Custom under the Number tab, and enter ;;; to hide the dropdown text.

100% compatibility with Microsoft Excel (.xlsx, .xls) files and formatting rules.User-friendly interface for managing complex conditional formatting rules.Fully supports custom number formats, including the ';;;' trick to hide text.Lightweight, fast, and free alternative for all your spreadsheet tasks.

Frequently Asked Questions

Does the ;;; custom format delete the data in the cell?

No, the ;;; custom number format only hides the display of the text or numbers in the cell. The actual value remains intact, functions properly with the dropdown, and continues to trigger the conditional formatting rules.

Why isn't my conditional formatting applying to the entire column correctly?

Ensure you are using relative references in your formula (e.g., =A1="London" instead of =$A$1="London") and that the 'Applies to' range in the Conditional Formatting Rules Manager covers the entire target area. Absolute references will force every cell to base its color on the first cell's value.

Can I edit the cell content if the text is hidden?

Yes. Even though the text is invisible on the spreadsheet grid, you can still view and modify the actual value in the Formula Bar at the top of the screen whenever you select the cell.

How do I make the text visible again?

To reveal the text, select the cells, right-click, choose 'Format Cells', go to the Number tab, and change the category from 'Custom' back to 'General' (or another appropriate format). The text will instantly reappear.

Document & File Issues