How to Format Excel Hyperlink Cells Based on a Hidden Worksheet
Question details
The user needs to make hyperlink cells on a main worksheet change color automatically according to matching values stored on corresponding hidden worksheets by using a formula-based conditional formatting rule.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a main worksheet with hyperlink cells and one or more hidden worksheets that store matching values. The goal is to make the hyperlink cell color update automatically when the hidden value changes.
- Observed behavior
- The hyperlink cells currently keep the default hyperlink color and do not automatically reflect the values stored on the hidden worksheets.
Open the workbook in WPS Office Spreadsheets and confirm that the hidden worksheets containing the values exist. Make a note of the exact hidden worksheet names and cell references, or define names for the values so the conditional formatting formula is easier to maintain.
Create a Formula-Based Conditional Formatting Rule for Hyperlink Cells
Use this solution when the hyperlink cells on the main worksheet should automatically change color based on values stored on hidden worksheets.
In WPS Office Spreadsheets, conditional formatting rules can reference cells on hidden worksheets. The rule overrides the default hyperlink color when the formula returns TRUE.
Open the main worksheet and select the hyperlink cells you want to format, for example B2:B20.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
Select Use a formula to determine which cells to format.
In the Format values where this formula is true box, type a reference to the hidden worksheet, such as =HiddenSheet!A2="Urgent". Adjust the sheet name and cell reference to match your workbook.
Click Format, open the Font tab, choose the color you want for the matching hyperlink cells, and confirm with OK.
In the Conditional Formatting Rules Manager, verify that the rule applies only to the selected hyperlink cells, then click OK.

Use a Defined Name for the Hidden Worksheet Value
Use this approach when the hidden sheet reference is long or you want a clearer conditional formatting formula.
Apply Hidden-Worksheet Conditional Formatting in WPS Office Spreadsheets
Use the Conditional Formatting feature in WPS Office Spreadsheets to make hyperlink cells change color based on hidden worksheet values without changing the hyperlink address.
- 1. Select the hyperlink cells: In WPS Office Spreadsheets, open your workbook and select the hyperlink cells that need the color rule.
- 2. Open Conditional Formatting: Click the Home tab, then click Conditional Formatting and choose New Rule.
- 3. Create a formula rule: Choose Use a formula to determine which cells to format.
- 4. Reference the hidden sheet: Enter a formula such as =HiddenSheet!A2="Urgent" and click Format to set the font color.
- 5. Apply and save: Click OK to apply the rule, then save the workbook in .xlsx format to keep Excel compatibility.

Frequently Asked Questions
Can WPS Office conditional formatting reference a cell on a hidden worksheet?
Yes. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format, and type a direct reference such as =HiddenSheet!A1="Urgent". Hidden sheets can be referenced just like visible sheets.
Why is my hyperlink cell not changing color after I apply the rule?
Check that the formula returns TRUE for the selected cell, that the rule range covers the hyperlink cells, and that you changed the Font color under Format. Also check rule order in Conditional Formatting > Manage Rules if multiple rules are applied.
Can I use different colors for different hidden worksheet values?
Yes. Create one rule per condition and set the appropriate font color. In Manage Rules, arrange the rules so that the most specific condition is listed first or enable Stop If True as needed.
Will the workbook still work in Microsoft Excel?
Yes. WPS Office Spreadsheets saves conditional formatting rules in Excel-compatible .xlsx and .xls formats, so the formatting remains functional when opened in Microsoft Excel.
Do I have to edit the hyperlink itself to change its color?
No. Conditional formatting changes the cell appearance only. The hyperlink address remains intact, and you can remove or edit the rule later without affecting the hyperlink.




