# Conditional Formatting Based on Percent Difference

1. ## Conditional Formatting Based on Percent Difference

I'm trying to highlight cells within a column that have a difference of 15% or greater from each other. An example would be if cells A1-A4 contained the values 90, 88, 93, and 62. I would want 62 to be highlighted because it differs by more than 15%

any thoughts? thanks

2. ## Re: Conditional Formatting Based on Percent Difference

Originally Posted by dontcare
...a difference of 15% or greater from each other.

Once you can state that precisely then should be straightforward to define the correct formula to use in Conditional Formatting.

3. ## Re: Conditional Formatting Based on Percent Difference

62 would differ from the mean of the data (83.25) by 25% or so.

4. ## Re: Conditional Formatting Based on Percent Difference

CF Formula is =ABS(A1/AVERAGE(A\$1:A\$4) - 1)>15%

5. ## Re: Conditional Formatting Based on Percent Difference

OK cool that worked for the one column i had. Now if I was going to add a bunch of other columns is there a way to apply that condition all at once?

6. ## Re: Conditional Formatting Based on Percent Difference

You can highlight the cells you formatted, click on the Format Painter, then highlight the cells you still want to format. Conditional Formatting is carried along by the Format Painter just like fonts and shading. If you used a formula just like shg showed, then your column is relative and will be painted correctly into other columns.

7. ## Re: Conditional Formatting Based on Percent Difference

Before you do as 6SJ suggests (which is abolutely correct), look at the dollar signs in that formula. Those control how the references will change when the format is copied, just as for copying a formula. See Help about About cell and range references

8. ## Re: Conditional Formatting Based on Percent Difference

In the CF menu where you can find all the rules you created, you can find there the range where you want to apply the CF Rule.

There are currently 1 users browsing this thread. (0 members and 1 guests)