enter image description hereI'm trying to apply a conditional formatting on multiple rows each of which has a different cell to format against.

As you can see in the image I want each of the cells in G2:R2 to highlight yellow if the value is below E2;

how do I go about formatting the remaining rows ie : Row 3 G3:R3 to highlight if below E3, etc...

I have over a thousand of them.

So far all my attempts at searching for an answer have failed and I do not want to have to apply a conditional formatting to each individual row.


Conditional formatting is done with a formula, and that formula can use relative references. In this case, we'd want the reference to the column to be anchored, but the row to be relative - a mixed reference.

I'm a little unclear in your reference image on which columns are which, since the column letters aren't included; I'm going to assume that the "Min" column is column E, and the data are running from column F to column Q. Obviously, adjust this answer as appropriate for your actual situation.

The easiest way to do the job is to start by selecting your range. You'll want to pay attention to which cell you start the selection from; you'll see why shortly. So in my sample set, I'm starting from F2:

Conditional Formatting - Selection

With the range selected, go to Home | Conditional Formatting | New Rule, and in the New Formatting Rule window choose Format only cells that contain.

In the bottom section, choose Cell value, then Less than or equal to. The last box is where we'll tell it which cells to check for the reference value.

This is where the note we took of which cell we started selecting from comes in handy. You need to make sure the row you enter here matches the row you started selecting from. Check the name box in the upper left if you're not sure - that shows the active cell. The two row numbers need to match.

Enter the cell reference in the format =$E2. This will keep the column absolute but the row relative, and the row will change appropriately as the formatting propogates down your rows.

Complete rule

This result should give you the highlighting you're looking for - this is what I saw when I hit OK:

Conditional Formatting - Result

  • Thank I have been using Use a formula to determine which cells to format =$G$2:$R$2<$E$2 (F column is hidden) How do i go about applying to multiples rows so the data from row 3 (Cells G3:R3) use cell E3 as a reference, data from row 4 (Cells G4:R4) use cell E4 as a reference and so on, unfortunately due to network restrictions I cannot view the images you posted, I have edited my original image to make it clearer. – Excel Padawan Jan 11 '17 at 15:26
  • The important part is that when you're entering the formula to check against the cell, use the $ only in front of the column, ie <$E2. That ensures that the formula will always look at Column E, but whichever row the cell being formatted is on. – Werrf Jan 11 '17 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.