Compare Cells on Two Excel Worksheets with Conditional Formatting in WPS Office
Question details
The user needs to highlight cells whose values differ between a current WPS Office Excel worksheet and an older worksheet by creating a conditional formatting rule.
- Product
- WPS Office Spreadsheets
- Device & OS
- Windows or macOS
- Scenario
- The user has two versions of a worksheet—an older sheet and a current sheet—and wants to visually flag cells that differ between them.
- Observed behavior
- Cells currently do not show which values differ across the two worksheets; the user needs a conditional formatting formula to highlight them.
Open both worksheets in WPS Office Spreadsheets and confirm the sheet names exactly match the names you will reference in the formula. Select the range to format and note the top-left cell reference before creating the rule.
Use a Conditional Formatting Formula with <> to Compare Two Worksheets
Use this method when you have an older worksheet and a current worksheet and want WPS Office Spreadsheets to highlight values that differ, using the same formula-based conditional formatting rules supported in Excel.
WPS Office Spreadsheets uses the same worksheet formula syntax as Excel. In a conditional formatting formula, <> is the not-equal comparison operator, and the cell reference must be the top-left cell of the Applies To range so the reference can adjust relative to the other selected cells.
Open the current worksheet in WPS Office Spreadsheets and select the range you want to highlight, for example B4:D20. Note the top-left cell reference, such as B4.
Go to the Home tab, click Conditional Formatting, and choose New Rule. In the rule type list, select Use a formula to determine which cells to format.
In the formula box, type ='CurrentSheet'!B4<>'OldSheet'!B4, replacing the sheet names and B4 with your current worksheet name, older worksheet name, and top-left cell. Do not use the ≠ symbol.
Click Format, choose a fill color or font formatting, and click OK.
Click OK again to apply the rule. WPS Office Spreadsheets adjusts the relative references across the selected range and highlights cells whose values differ.
Highlight Differences Across Two Worksheets in WPS Office Spreadsheets
WPS Office Spreadsheets can complete this comparison directly with a formula-based conditional formatting rule. It supports the same worksheet references and <> operator as Excel, so you can highlight every value that differs between the older and current worksheet.
- 1. Open both worksheets: In WPS Office Spreadsheets, open the workbook that contains the current worksheet and the older worksheet, or open both separate workbooks.
- 2. Select the current range: Click the current worksheet, then select the range you want to highlight, such as B4:D20.
- 3. Create a formula rule: Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter ='CurrentSheet'!B4<>'OldSheet'!B4.
- 4. Choose formatting and apply: Click Format, pick a highlight color, and click OK to apply the rule to the selected range.
- 5. Confirm the result: Check that WPS Office Spreadsheets adjusts the relative references and highlights cells whose values differ.
Frequently Asked Questions
Why does the conditional formatting rule only highlight cell B4?
You probably used absolute references such as $B$4. Select the entire Applies To range first, then enter the formula with a relative top-left reference like B4. WPS Office Spreadsheets adjusts the reference across the rest of the selected range.
Can I compare two worksheets in different workbooks?
Yes. In the conditional formatting formula, include the workbook name in square brackets, for example =[Older.xlsx]Sheet1!B4<>Sheet1!B4. Keep the referenced workbook open so WPS Office Spreadsheets can evaluate the cells.
Is the ≠ symbol valid in WPS Office conditional formatting?
No. Use <> for not equal in worksheet formulas. The ≠ symbol is not recognized as a comparison operator in WPS Office or Excel.
How do I highlight an entire row when one cell in the row differs?
Select the full row range, such as A4:E20, and base the formula on the key cell using a relative column reference, for example ='Current'!$B4<>'Old'!$B4. This keeps the row comparison tied to column B while formatting the entire selected row.
