how do you apply the same conditional formatting to multiple columns at the same time?

Question

If i have columns A, B, C, and D (where all the columns are numbers with an average at the bottom) and I want to apply a conditional formula that makes the text green if the number is above the average and red if it is below the average, is there a way to do this for all 4 columns, or for column A,B and D without having to use the conditional formatting tool on each column separately?  If so, how would I go about doing that?  It would save me so much time.  Thanks for your help.

Answer 1

Hi,

assuming your values are in range A1:D10 and your average values are in range A11:D11 then:

• Select the range A1:D10
• Home tab > Conditional Formatting > New Rule > Use a formula to determine... > formula is: =A1>A$11
• Coose the format (fill red)
• Ok.
• Repeat the steps above but the formula is: A1<A$11
• Choose the format (fill green)
• Ok

--

Regards

Frank

Answer 2

Suppose your data is in A2:D100 and the averages to compare against are in A101:D101

Select the table to format, ie A2:D100 > Conditional Formatting > New rule > 'Use a formula...' > Format values, if this formula is true: =A2:D100>A$101 > Format as desired > OK

Note that only the row holding the avearge is made absolute!