How to Change Excel Cell Colors and Hide Values with Conditional Formatting
Question details
The user wants to assign specific background colors to cells based on numeric scores (e.g., 1 to 10) and hide the displayed numbers while keeping the underlying values active for calculations.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a visual scoring rubric or heat map where colors represent scores, but the numeric text should not be visible to the viewer.
- Observed behavior
- The goal state is to have cells automatically color-coded based on input values, with the numbers completely hidden from the grid view but still fully functional in SUM or percentage formulas.
Before applying these formatting rules, ensure that your target cells contain purely numeric values (not text) so that mathematical functions and conditional formatting formulas can evaluate them correctly.
Use Conditional Formatting and the ;;; Custom Format
This method allows you to set specific cell colors based on the numerical value and completely hide the text while keeping it available for calculations.
Applying a custom number format of three semicolons is the most robust way to hide cell contents in Excel while preserving the data for formulas.
Highlight the range of cells where the scores will be entered (for example, C3:G10).
Go to the Home tab on the ribbon and click on Conditional Formatting, then select New Rule.
Choose 'Use a formula to determine which cells to format'. Enter a formula like =C3=1 (ensure you use a relative reference based on the top-left cell of your selection).
Click the Format button, go to the Fill tab, choose your desired color for this specific score, and click OK. Repeat this entire process to create rules for other scores (e.g., =C3=2, =C3=3).
To hide the numbers, select the same range again and press Ctrl + 1 to open the Format Cells dialog. Go to the Number tab and select Custom.
Type ;;; into the Type field and click OK. The numbers will disappear visually but can still be calculated using formulas like =SUM(C3:G3).
Match Font Color with Cell Fill Color
A quick alternative is setting the font color to match the background fill color directly within the conditional formatting rules.
Easily Format and Hide Cell Values in WPS Spreadsheet
WPS Spreadsheet provides powerful and intuitive Conditional Formatting tools, allowing you to quickly create custom visual rules and apply advanced number formatting. You can easily color-code scores and hide cell values without losing data integrity.
- 1. Open the workbook: Launch WPS Spreadsheet and select the range of cells you want to apply the formatting to.
- 2. Access Conditional Formatting: Navigate to the Home tab, click on Conditional Formatting, and select New Rule.
- 3. Define visual rules: Set up your formula-based rules (e.g., =A1=1) and assign specific background fill colors for each condition.
- 4. Hide cell contents: Right-click the selected cells, choose Format Cells, navigate to Custom, and input ;;; to hide the display text while keeping values active for formulas.
Frequently Asked Questions
Why are my hidden numbers still visible in the formula bar?
Custom formatting with ;;; only hides the value in the spreadsheet grid. The actual data remains intact and will always be visible in the formula bar when the cell is selected. To hide it from the formula bar completely, you must check the 'Hidden' property in Format Cells (under the Protection tab) and then protect the worksheet.
Can I apply conditional formatting to a whole column instead of a specific range?
Yes. When setting up the rule, you can select the entire column header (e.g., Column C). Alternatively, you can open Conditional Formatting > Manage Rules and change the 'Applies to' range to =$C:$C.
Will the ;;; custom format affect my SUM or AVERAGE calculations?
No. The ;;; custom format strictly changes how the data is visually presented on the screen. The underlying numeric value remains fully active, allowing SUM, AVERAGE, and percentage formulas to function normally.
How do I remove the conditional formatting if I make a mistake?
Select the affected cells, go to the Home tab, click Conditional Formatting, hover over 'Clear Rules', and choose either 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet'.
