2015/11/How-to-apply-conditional-formatting-across-rows-Excel-2013/

[SeanKilleen]

https://seankilleen.com/2015/11/How-to-apply-conditional-formatting-across-rows-Excel-2013/

[SeanKilleen]

Historical Comments

2020-03-18, Disqus User Noah:

Hi Sean, I have a question that I think is related, but somewhat different. I'm trying to apply a simple blue red color scale to a table of data, such that the blue red color scheme in each cell references only the values within that cells row. I can only seem to get each cell in the table to reference the whole table of values, rather than just the row, when I try to apply the rule to the whole table. The only way I can do this row by row is by individual highlighting one row, applying the rule, then copy pasting the formatting to each subsequent row individually. Do you have any better ideas?

2020-04-02, Disqus user Dan (replying to Noah):

Okay, so I shopped the problem around to my associates and only one of them had advice to speed the process up somewhat. Rather than pasting the formatting into each line.... format one line and copy, paste formatting into the next line, highlight and copy both lines, paste formatting into the next TWO lines, copy all four lines, paste formatting into the next FOUR lines, etc.

It speeds the process up considerably if you're dealing with several hundred lines like me. My associate agrees though that there is probably a method to achieve this through formula, but he doesn't know it and only had this to offer.

I'm having the same issue myself. Find myself doing this too frequently honestly. There has to be a better way...

2020-06-28, Disqus User Davis Hunt (replying to Dan):

I too, have an issue with doing what Noah questions. I have a worksheet of 8,000 rows of data. I want to highlight the max value of six cells adjacent cells in the each row and have the conditional formatting only look at the six cells in that row. Each row of the 8,000 rows needs to be formulated in a similar fashion. I have wasted a day in trying to figure this out. Any help would be greatly appreciated.

2020-10-13, Disqus User Marisa:

Thank you Sean! This has helped me immensely.

2020-10-25, Disqus User REJ_TEX:

Doesnt work. Tried it both ways using your article and other similar ones. the formula only looks at the two original cells

2020-11-10, Disqus user Atie:

Hey guys, you can just use this method for manual rule setup.

1. Highlight the column you want to apply the rules.
2. Select Conditional Formatting, click New Rule.
3. Pick "Use a formula to determine which cells to format".
4. Put the formula i.e. =C1="Done" (so if cell C1 has value Done, then the adjacent cell highlighted in number 1 will follow the format you set).
5. Just make sure to remove the $ in the cell you chose in no. 4 (C1). Then the rule will apply for each row.
6. Set the format (font, color, etc.)