Conditional Formatting Help - red/green or icons based on previous cell

[bigazonk]

Hello, I'm looking for an Excel conditional formatting to help me achieve what I'm needing. I have a column of data, and I'd like each new cell of data entered to be represented by an icon set based on the previous cell data only (either red (increase), yellow (no change), or green (decrease). Please see the below for what I'm looking for.

171
166 (green triangle)
165 (yellow triangle/minus sign)
161 (green triangle)
163 (red triangle)

 

[tdh777]

Hi.

Excel Icon sets specifically exclude the ability to use relative references in formulas. However, you can get around this in a number of ways :

1. Use the INDIRECT function in your formula.

2. Create a single Conditional Format rule for the first cell in your range (using an absolute Cell Reference) - and then COPY the FORMAT down to the other cells.
(in other words each cell has it's own conditional format referring to the cell above, rather than one conditional format that "applies to" a range of cells.

3. Create a little "helper column" next to the values with the desired result of your formula and put the conditional format on THAT column.

The steps below illustrate technique 3.

A	B
171	0
166	5
166	0
161	5
163	-2

<tbody>
</tbody>

.. where the formula in Column B2:B5 is "=A1-A2".

Then put an Icon Set on Column B eg as follows :

Format Style : Icon Sets
Icon Style : 3 Symbols (circled)

When value >= 1 Number Green Icon
When < 1 and >= 0 Number Orange/Yellow Icon
When <0 Red Icon

Tick the "Show Icon Only" box.

An image of the basic setup and result is here at https://pasteboard.co/HbDGiZW.jpg

If you are using Excel 2010 and above you can also mix and match Icons from Multiple sets, using eg The Green Tick and Red "x" from the 3 Symbols circled set, and taking eg the Orange Dash from the Three Triangles Icon set instead of the Yellow "!" in the 3 symbols circled set.

Cheers,
Warren K.