How to highlight cells in each row if their value are above/below the average that affects to each row only

Question

How to highlight cells in each row if their value are above/below the average that affects to each row only? Because I have the table that contain numbers in 5 column and have all 100 rows and I want to hightlight cells in each row if their value are above/below the average in each row only but when I try create a rule in condition formatting menu by

1. Select 5 cells of all 100 rows

2. Go to Home > Styles > Condition Formatting > New Rule

3. Select "Format only values that are above or below average"

4. Press "Format" menu at the right bottom

5. Select "Fill" Tab" and then Choose the colour

6. Select OK > OK

, It highlight the cells that above the average from all cells in 100 rows instead of the average of each rows.

The reason that I knew is when I try to create the same rule but apply on only 5 cells of one rows, the excel highlight the a few cells that in the past that I applied the rule to 5 cells of all 100 rows, they are not get highlighted.

Can you help me,please?

Answer 1

Create a conditional formatting rule based on the following formula,

=A2<>AVERAGE($A2:$E2)