How to Highlight Non-Symmetric Matrix Values with Conditional Formatting in WPS Office Spreadsheets
Question details
The user needs to highlight cells in a matrix that do not match their corresponding mirrored cell across the diagonal, using conditional formatting.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A calculation matrix is expected to be symmetric across its diagonal, but some mirrored pairs may differ and must be identified quickly.
- Observed behavior
- Cells that differ from their diagonal counterpart are not visually flagged, making asymmetry hard to spot.
Confirm that the matrix is a square range and note its top-left cell reference, such as E8. The rule must use the same top-left cell in both the COLUMN and ROW offset calculations.
Use a formula-based conditional formatting rule to compare mirrored matrix cells
Best for live highlighting: WPS Office Spreadsheets automatically flags any cell whose value differs from its counterpart across the diagonal.
This rule uses INDEX with COLUMN and ROW to calculate the mirrored cell position. When the selected cell differs from that mirrored cell, the rule formats the cell.
Open your workbook in WPS Office Spreadsheets and select the square range to check, for example E8:H11.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, enter =E8<>INDEX($E$8:$H$11,COLUMN(E8)-COLUMN($E$8)+1,ROW(E8)-ROW($E$8)+1), and adjust E8 and $E$8:$H$11 to match your matrix range and top-left cell.
Click Format, choose a fill color or font style, and then select OK twice to apply the rule.
Troubleshoot the rule with a helper TRUE/FALSE matrix
Use this when you want to see exactly which mirrored pairs are evaluated as different before or after creating the conditional format.
Apply symmetric matrix highlighting with Conditional Formatting in WPS Office Spreadsheets
Use WPS Office Spreadsheets to open your Excel workbook, apply a formula-based Conditional Formatting rule, and highlight any matrix cell that differs from its mirrored counterpart across the diagonal.
- 1. Download and install WPS Office: Go to the WPS Office official website, download the free installer for your Windows, macOS, Android, or iOS device, and complete the installation.
- 2. Open your Excel workbook: Launch WPS Office Spreadsheets, click Menu > Open, and select the workbook containing the matrix you need to check.
- 3. Select the matrix range: Highlight the square range, such as E8:H11, and note the top-left cell reference before creating the rule.
- 4. Apply the formula-based rule: On the Home tab, click Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =E8<>INDEX($E$8:$H$11,COLUMN(E8)-COLUMN($E$8)+1,ROW(E8)-ROW($E$8)+1), choose a fill, and click OK.
- 5. Review the highlighted cells: Inspect the highlighted cells and adjust the formula or rule if the matrix range starts at a different cell.
Frequently Asked Questions
Why does the formula also highlight cells that appear equal?
If you enter only =INDEX(...) without the <> comparison, Conditional Formatting treats any non-zero mirror value as TRUE. Use =E8<>INDEX($E$8:$H$11,COLUMN(E8)-COLUMN($E$8)+1,ROW(E8)-ROW($E$8)+1) so only different values are formatted.
Can I apply this conditional formatting rule to a matrix that does not start at E8?
Yes. Replace $E$8:$H$11 with your actual matrix range and replace E8 with the top-left cell of the selected range in both the COLUMN and ROW parts of the formula.
Does this method work for text values or only numbers?
It works for numbers, text, dates, and most cell values because the comparison checks whether the current cell value is not equal to its mirrored cell.
How do I edit or remove the rule after it is applied?
Select the matrix range, go to Home > Conditional Formatting > Manage Rules, choose the rule, and then select Edit Rule or Delete Rule as needed.
