Forum Discussion

S_Wood80's avatar
S_Wood80
Copper Contributor
Jun 22, 2023

Conditional formatting based on cell above AND left

I regularly create price matrices where the prices in each column should reduce row by row (Top down) and likewise each row should also reduce in price column by column (L to R).
i.e. Highest price is always top left cell and lowest is bottom right cell.

Basically every cell should be a lower value than the cells immediately left and above itself
Is there away to highlight a cell that falls outside the expected behaviour without creating rules for individual cells?

Thanks

excel
office 365
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 23, 2023

    S_Wood80 

    You can use conditional formatting in Excel 365 to highlight cells that fall outside the expected behavior without creating rules for individual cells. You can achieve this by using a custom formula that compares the values of the current cell with the values of the cell above and the cell to the left.

    Here is how you can set up the conditional formatting rule:

    1. Select the range of cells where you want to apply the conditional formatting. For example, if you want to apply it to the entire worksheet, click on the cell at the top-left corner (A1) and then press Ctrl+Shift+End to select all cells.
    2. Go to the "Home" tab in the Excel ribbon, click on "Conditional Formatting," and then select "New Rule."
    3. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
    4. In the "Format values where this formula is true" field, enter the following formula:

    =AND(A1<>""; A1<>"<"; A1>INDEX($A$1:A1, ROW()-1); A1>INDEX($A1:A$1, , COLUMN()-1))

    Note: Replace A1 with the top-left cell of the selected range.

    1. Click on the "Format" button to specify the formatting style you want to apply to the cells that meet the condition. For example, you can choose a background color or font color to highlight the cells.
    2. Click "OK" to close the "Format Cells" dialog box.
    3. Click "OK" again to apply the conditional formatting rule.

    The above formula checks if the current cell is not empty (A1<>""), not equal to the text "<" (A1<>"<"), and greater than the value in the cell above it (A1>INDEX($A$1:A1, ROW()-1)) and the value in the cell to the left (A1>INDEX($A1:A$1, , COLUMN()-1)). If any of these conditions are not met, the cell will be highlighted according to the specified formatting.

    The conditional formatting rule will be applied to the selected range, and any cell that falls outside the expected behavior will be highlighted.

    Text, steps and formula were created with the help of AI for time reasons and convenience :).

    Hope it helps you!

Resources