Formatting Issues

How to Apply Excel Conditional Formatting Based on Another Sheet

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to format cells on one sheet based on the status of corresponding matching values located on a different sheet.

Product: Excel
Device & OS: not provided
Scenario: Applying dynamic cell formatting to a dataset by cross-referencing values and their associated statuses from a master sheet.
Observed behavior: The user is trying to match a value in Sheet 2 to Sheet 1, retrieve its status, and apply a specific color format based on that status, but their initial formula attempt failed.

Before you start

Ensure both sheets are within the same workbook and that your lookup columns contain exact matching values without hidden trailing spaces to prevent formula errors.

Solution 1Recommended

Use XLOOKUP Formula in Conditional Formatting

Utilize the XLOOKUP function to search for your target value in another sheet and apply formatting based on the returned status.

The XLOOKUP function is a powerful and flexible way to cross-reference data between sheets. When placed inside a Conditional Formatting rule, it will dynamically evaluate each cell's corresponding status and apply the correct format.

Select the target range

Open Sheet 2 and select the range of cells you want to apply the formatting to (for example, column G or specific cells within it).

Create a new formatting rule

Navigate to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.

Choose the formula option

In the dialog box, select 'Use a formula to determine which cells to format'.

Enter the XLOOKUP formula

Enter the formula: =XLOOKUP($G1,Sheet1!$B$1:$B$700,Sheet1!$A$1:$A$700,"")="Occupied" (Ensure you adjust the column letters and row numbers to match your actual data layout).

Set the formatting style

Click the 'Format' button, choose your desired fill color (e.g., green for Occupied), and click 'OK' to apply the rule.

Adding Multiple Conditions: You can repeat these exact steps to create additional rules for other statuses like 'Reserved' and 'Unknown' by simply changing the status word in the formula and picking a different color.

Solution 2

Use VLOOKUP or INDEX/MATCH Formulas

If you are using an older version of your spreadsheet software that does not support XLOOKUP, you can use VLOOKUP or INDEX/MATCH instead.

Apply Conditional Formatting Across Sheets with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, including XLOOKUP, VLOOKUP, and INDEX/MATCH, allowing you to seamlessly cross-reference and format data across multiple sheets.

1. Open your file in WPS: Launch WPS Office and open the workbook containing your datasets.
2. Select the data: Highlight the cells on your target sheet that need conditional formatting.
3. Add a formula rule: Go to the 'Home' tab, click 'Conditional Formatting' > 'New Rule', and choose 'Use a formula to format cells'.
4. Enter formula and format: Type your XLOOKUP or VLOOKUP formula, click 'Format' to choose your colors, and click 'OK'.

Fully compatible with Microsoft Excel formulas and conditional formatting rules.Easily cross-reference data across multiple sheets and workbooks without lag.Lightweight performance that runs smoothly even with massive datasets.Free to use with an intuitive, familiar spreadsheet interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting formula referencing another sheet not working?

This is usually caused by incorrect absolute and relative cell references. Ensure that the lookup range (e.g., Sheet1!$B$1:$B$700) is absolute (using $ signs), while the target cell (e.g., $G1) has a relative row number so it adapts properly as the rule is applied down the column.

Will the conditional formatting update automatically when data in Sheet 1 changes?

Yes, conditional formatting is dynamic. If you change a status in Sheet 1 (for example, from 'Available' to 'Occupied'), the color formatting in Sheet 2 will update automatically as long as your workbook calculation settings are set to Automatic.

Can I use conditional formatting across completely different workbooks?

Standard conditional formatting formulas only support referencing data within the same workbook. If you need to format based on another workbook, you must first pull that data into a hidden sheet within your current workbook using standard formulas, and then base your conditional formatting on that local sheet.

Document & File Issues