A bit stuck with conditional formatting based on figure in cell above

[SeanWRX]

Hi all,


I'm a newbie so please go easy! I've tried to find whether this has been asked before, but while I find similar queries, I'm unable to find something that answers my particular predicament. I nearly got there with conditional formatting and format only based on an average, but it still isn't quite how I want it.


I have monthly data showing the quantities of computers at my clients sites. I need to reflect the data with the fluctiations being highlighted by colour. My data looks like this:


Birmingham
Jan 500
Feb 550
Mar 450
Apr 300
May 700
Jun 800


I need each cell to "react" to the one above it by turning a certain colour based on drop or rise in quantity compared to the previous month. In my situation increases are bad, so I want those cells to turn red, and decreases should be blue.


So in the above example, with my intended colours in brackets:


Birmingham
Jan 500 (first month so no colour)
Feb 550 (red as this figure is an increase on the Jan figure)
Mar 450 (blue as this figure is a decrease on the Feb figure)
Apr 300 (blue as this figure is a decrease on the Mar figure)
May 700 (red as this figure is an increase on the Apr figure)
Jun 800 (red as this figure is an increase on the May figure)


I have lots of customer sites to report on and the quantities can fluctuate wildly (I used dummy figures here, obviously) so I'd love something to automatically handle this formatting for me so I don't have to manually colour-code them each time.


Is what I need possible, and if so how?

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this

Assuming your data begin in row 2, select B2:Bn, i.e.,till the end of your data, being b2 the active cell (the one not shaded)

Home > Conditional Formatting > New Rule > Use a formula ...
insert this formula
=AND(B1<>"",B2>B1)
Format button (fill --> red)

do the same to insert the other rule and insert this formula
=AND(B1<>"",B1>B2)
Format button (fill --> blue)

Hope this helps

M.

 

[Joe4]

Welcome to the Board!

Let's say that your actual data starts on row 2 and your data shown is in columns A and B.
Then highlight B3 down to the end of column B, and enter this Conditional Formatting formula:

=$B3>$B2

then choose the red formatting color.

Then, repeat the exact same steps on the same range, adding a NEW rule what does the reverse:

=$B3 < $B2

and choose the blue formatting color

 

[SeanWRX]

Thanks both, much appreciated.

I've done it both ways.

Joe4 - It doesn't look right when I use your suggestion, but that may well be me and my sausage fingers. I'd upload a screengrab but this forum has restrictions.
Marcelo - Got yours working great.

My client has now changed the goalposts and wants a 'traffic light system' based on the numbers fluctuating, so it's back to the drawing board for me!!

 

[Joe4]

Do you have blanks in your data that you need to account for?
That is the only major difference between Marcelo's suggestion and mine.

Other than that, the most common error I see people make with this is the choose the wrong row references in their formulas.
The row references you use MUST reflect the first cell in the range you have selected for Conditional Formatting.
My suggestion talks about starting on row 2. If you have selected all rows, including row 1, you would be off by one row on your highlighting.
Marcelo's shows what the formula would look like if you started on row 1.

You just need to make sure that whatever range you select to apply the Conditional Formatting to, the formula needs to select the very first cell in that selection.
If you have used your Absolute and Relative Referencing correctly in the formulas, Excel will automatically adjust for all the other cells in your Conditional Formatting range selected.

 

[SeanWRX]

Noted, thanks Joe. Yes, I've definitely gone wrong with one of the details.

I'm going to go over and over this particular task until it is second nature to me (I am still new to Excel).

 

[SeanWRX]

Me again.

Had a curveball thrown at me now. Rather than turning the cell green, my customer wants to see green or red dots (red = increase, green = same/decrease), traffic light style.

I'm on Excel 2003 and while I can see the icon sets they seem a separate option to the method of doing conditional formatting > new rule > use a formula, because the formatting button only lets you fill the entire cell with a colour, not put a red/green circle in there.

Any ideas?