Conditional formatting, entire row based
=$G1="X"
would be the correct (and easiest) method. Just select the entire sheet first, as conditional formatting only works on selected cells. I just tried it and it works perfectly. You must start at G1 rather than G2 otherwise it will offset the conditional formatting by a row.
Use the "indirect" function on conditional formatting.
- Select Conditional Formatting
- Select New Rule
- Select "Use a Formula to determine which cells to format"
- Enter the Formula,
=INDIRECT("g"&ROW())="X"
- Enter the Format you want (text color, fill color, etc).
- Select OK to save the new format
- Open "Manage Rules" in Conditional Formatting
- Select "This Worksheet" if you can't see your new rule.
- In the "Applies to" box of your new rule, enter
=$A$1:$Z$1500
(or however wide/long you want the conditional formatting to extend depending on your worksheet)
For every row in the G column that has an X, it will now turn to the format you specified. If there isn't an X in the column, the row won't be formatted.
You can repeat this to do multiple row formatting depending on a column value. Just change either the g
column or x
specific text in the formula and set different formats.
For example, if you add a new rule with the formula, =INDIRECT("h"&ROW())="CAR"
, then it will format every row that has CAR
in the H Column as the format you specified.
To set Conditional Formatting for an ENTIRE ROW based on a single cell you must ANCHOR that single cell's column address with a "$", otherwise Excel will only get the first column correct. Why?
Because Excel is setting your Conditional Format for the SECOND column of your row based on an OFFSET of columns. For the SECOND column, Excel has now moved one column to the RIGHT of your intended rule cell, examined THAT cell, and has correctly formatted column two based on a cell you never intended.
Simply anchor the COLUMN portion of your rule cell's address with "$", and you will be happy
For example: You want any row of your table to highlight red if the last cell of that row does not equal 1.
Select the entire table (but not the headings) "Home" > "Conditional Formatting" > "Manage Rules..." > "New Rule" > "Use a formula to determine which cells to format"
Enter: "=$T3<>1" (no quotes... "T" is the rule cell's column, "3" is its row) Set your formatting Click Apply.
Make sure Excel has not inserted quotes into any part of your formula... if it did, Backspace/Delete them out (no arrow keys please).
Conditional Formatting should be set for the entire table.
You want to apply a custom formatting rule. The "Applies to" field should be your entire row (If you want to format row 5, put in =$5:$5
. The custom formula should be =IF($B$5="X", TRUE, FALSE)
, shown in the example below.