Excel Conditional Formatting based on Adjacent Cell Value

0 votes

In order to accomplish the following, I'm attempting to use conditional formatting in Excel on a range of cells based on the value of the next cell:

Expense Conditional Formatting

If a value in Column B (Actual Expense) exceeds its neighbouring value in Column C, it should be highlighted in red (Expected Expense). I have complied with several tutorials that instructed me to use conditional formatting by choosing Conditional Formatting>New Rules. Applying the criteria =$B4>$C4 would format the "Actual" cell red if it were greater than the "Expected" cell. Use a Formula to Determine Which Cells to Format. When I try to apply this to a group of cells, it doesn't work as effectively as it does for one cell.

enter image description here

I'd rather not make individual rules for every row. Is there a way to consolidate this into one rule?

Oct 10 in Others by Kithuzzz
• 20,660 points
202 views

1 answer to this question.

0 votes

The row number used in the formula should match the first row of data, therefore if you are applying this to the ("applies to") range $B$2:$B$5, it must be this formula. You need to remove the $ signs before the row numbers in the formula.

=$B2>$C2

Excel (implicitly) modifies the formula for each row in the range if you use that "relative" version rather than your "absolute" one, as if you were copying the formula down.

answered Oct 10 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20 in Others by narikkadan
• 37,660 points
46 views
0 votes
0 answers

Merge rows based on value (pandas to excel - xlsxwriter)

I'm attempting to use xlsxwriter to output ...READ MORE

Nov 2 in Others by Kithuzzz
• 20,660 points
143 views
0 votes
1 answer

Sort Excel worksheets based on name, which is a date

Sorting sheets of a workbook are rather ...READ MORE

answered Oct 17 in Others by narikkadan
• 37,660 points
51 views
0 votes
1 answer

Excel automatically converting 7 digit CAS number to another number (date?)

Looks like you could use: The formula in D2: =SUBSTITUTE(F ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
64 views
0 votes
1 answer

Thousand and million formatting for negative numbers (excel/ googlesheets)

Its not possible. What you could do is ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
69 views
0 votes
1 answer

Excel Issue- custom format for Turkish currency

What about: #.##0,00 [$₺-tr-TR] Where #.##0,00 € would do for ...READ MORE

answered Oct 10 in Others by narikkadan
• 37,660 points
140 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
45 views
0 votes
1 answer

Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

answered Oct 31 in Others by narikkadan
• 37,660 points
61 views
0 votes
1 answer

Excel Define a range based on a cell value

Let's say that cells A1, A2, A3, ...READ MORE

answered Nov 8 in Others by narikkadan
• 37,660 points
46 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP