How to Apply Conditional Formatting Based on Cells Above and Left in WPS Office Spreadsheets
Question details
The user needs to conditionally format a price matrix in WPS Office Spreadsheets so that any cell that is not lower than the cell directly above or directly to the left is highlighted, without creating a separate rule for every cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Validating a price matrix where values should decrease from top to bottom and from left to right.
- Observed behavior
- Cells that break the decreasing pattern need to be highlighted, but manually checking or creating individual cell rules is inefficient.
Open the WPS Spreadsheets workbook that contains the price matrix. Identify the first cell of the range you want to validate—typically B2—and decide whether the first row and first column should be included or handled separately.
Create a Single Formula-Based Conditional Formatting Rule
Use this method to highlight all cells in the matrix that are not lower than the cell above or the cell to the left with one rule.
WPS Spreadsheets evaluates the formula relative to the first cell in the selected range. The formula returns TRUE when a cell is greater than or equal to the cell above or the cell to the left, so formatting is applied only to cells that break the expected decreasing pattern.
In WPS Spreadsheets, select the range beginning at the first data cell that has both a cell above and a cell to the left. For example, if your matrix starts at A1, select B2 down to the bottom-right cell of the matrix.
On the Home tab, click Conditional Formatting, then choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, type =OR(B2>=B1,B2>=A2). Adjust B2, B1, and A2 to match the first cell in your selected range and its relative neighbors.
Click Format, choose a fill color or font style for cells that break the decreasing pattern, and confirm with OK. Then click OK to apply the rule.
Add Separate Rules for the First Row or First Column When Needed
Use this extra step if the first row or first column also must be validated against a specific direction, since the main matrix rule excludes them.
Apply Matrix Conditional Formatting in WPS Office Spreadsheets
Use WPS Office Spreadsheets to create a single formula-based conditional formatting rule that highlights cells in a price matrix when they are not lower than the cell above or the cell to the left. WPS Spreadsheets is free, lightweight, and fully compatible with Excel XLSX files, so you can open the same workbook and apply the rule without changing tools.
- 1. Open your workbook in WPS Spreadsheets: Download and open WPS Office, then open the Excel workbook containing the price matrix in WPS Spreadsheets.
- 2. Select the matrix range: Select the range starting from the second row and second column, for example B2:D10, so each cell has a cell above and to the left.
- 3. Create the formula-based rule: Click Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =OR(B2>=B1,B2>=A2) adjusted to your selected range.
- 4. Apply the highlight format: Click Format, choose a fill or font style, and confirm with OK to highlight cells that break the decreasing pattern.
Frequently Asked Questions
Does this formula work if my matrix starts at a different cell than A1?
Yes. Select from the first cell that has both an above and left neighbor, then adjust the formula references to that selected cell. For example, if your selection starts at C3, use =OR(C3>=C2,C3>=B3).
Why are the first row and first column not highlighted by the rule?
The formula compares each cell with the cell above and the cell to the left. The first row has no above cell and the first column has no left cell, so they are excluded from the main rule. Add separate rules if those cells also need validation.
Can I use a different highlight style for cells that break the decreasing pattern?
Yes. In the New Formatting Rule dialog, click Format and choose your preferred fill color, font color, or borders. The fill is applied to cells where the formula returns TRUE.
Do I need Excel 365 or will this work in WPS Spreadsheets?
This works in WPS Office Spreadsheets. The conditional formatting formula approach is supported, and the same workbook remains compatible with Microsoft Excel XLSX format.
