Microsoft Excel functions banner image.

OK, so the following scenario catches me every time: Conditional Formatting of cells based on values in a separate range of cells! In the example below, we have rows of data where we want the cells in one column (column B) to be formatted conditionally based on the values in a second column (column C). This is a Grade 12-level Excel question!

We want to highlight the months in column B where the month’s Sales figure in column C is below R 50,000.00.

Advanced Excel Conditional Formatting example.Advanced Excel Conditional Formatting example results.

Remember: do not add the currency symbol in the criteria, use an integer value.

  1. Select the range of cells to which the conditional formatting should apply
    Click on the Conditional Formatting button and select the New Rule option from the list
  2. Select the Use a formula to determine which cell to format option
  3. Add the formula in the Format values where this formula is true: — pay special attention to the use of the $ to create an absolute column reference
  4. Select your preferred Format options (I have used an orange fill)
  5. Click on OK.

As mention in Step 3, the trick here is in the use of the absolute value $ symbol to lock the formula onto the column, but not the row.


By foxbeefly

PHP / MySQL Developer. HTML, CSS and some JavaScript.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.