How to Apply Conditional Formatting for Dates Compared with Another Worksheet in Excel
Question details
The user needs to highlight dates in a specific Excel range if they are earlier than a reference date located on a different worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing dates across different worksheets using conditional formatting to dynamically highlight specific date cells without modifying the source data.
- Observed behavior
- The user must apply a formula rule that uses relative references for the target range and an absolute reference for the comparison date on another sheet.
Ensure that both the worksheet containing your target dates and the worksheet containing the comparison date are in the same workbook. Verify that your date cells are properly formatted as Date values, not plain text.
Use a Formula to Apply Conditional Formatting Across Worksheets
Use a custom formula within the Conditional Formatting rule to compare your active cell against an absolute reference on another sheet.
When applying conditional formatting across different sheets, you must lock the comparison cell reference (absolute reference) so that it doesn't shift when Excel evaluates the formatting for each cell in your target range.
Select the cell range you want to format (e.g., B2:E24). Ensure that the top-left cell (B2) is the active cell in your selection.
Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule...' from the dropdown menu.
In the New Formatting Rule dialog box, click on 'Use a formula to determine which cells to format'.
In the formula bar, enter =AND(B2<>"",B2<'Read Me'!$C$5). This formula checks that the cell is not blank and is earlier than the date in cell C5 on the 'Read Me' sheet.
Click the 'Format...' button, activate the 'Fill' tab, choose your desired highlight color, and click 'OK' twice to apply the rule.
Highlight Data Instantly with WPS Spreadsheet
You can easily apply cross-sheet conditional formatting and advanced date comparisons using WPS Spreadsheet. It offers a fully compatible, intuitive interface for managing complex data rules effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet, open your workbook, and highlight the date range you wish to format.
- 2. Navigate to Conditional Formatting: Go to the Home tab, click the Conditional Formatting icon, and select 'New Rule'.
- 3. Select Formula Rule: Choose the option 'Use a formula to determine which cells to format' from the rule types.
- 4. Apply Formula and Format: Enter your cross-sheet comparison formula, click Format to set your fill color, and hit OK.
Frequently Asked Questions
Why is my conditional formatting applying to the wrong cells?
This usually happens if the active cell when creating the rule doesn't match the relative reference used in your formula. Always ensure the cell referenced in the formula (e.g., B2) is the top-left active cell of your highlighted range.
Can I compare dates across different workbooks instead of just worksheets?
Conditional formatting formulas cannot reference cells in entirely different, closed workbooks. The comparison cell must be within the same workbook, although it can be located on any worksheet within it.
What does the AND function do in the conditional formatting formula?
The AND function allows you to test multiple conditions simultaneously. In =AND(B2<>"",B2<'Read Me'!$C$5), it ensures that the cell is not blank and that the date is earlier than the reference date, preventing blank cells from being incorrectly highlighted.
How do I change the highlight color later?
Go to Home > Conditional Formatting > Manage Rules. Select the worksheet where the rule is applied, click on your rule, select 'Edit Rule', then click 'Format' to choose a new fill color or font style.
