Excel - Conditional formatting with icon set and formula

Question

Hi everybody, thanks for reading the message.

It would be great if someone would be able to help:

I have a spreadsheet divided in different tab.

Tab1 Main tracker Row - Project Vs Column - Target. Each cell is conditional formatted as green, amber or red according with the status of the project (i.e. doc up to date, doc in place, doc not in place ..ecc). this is the main tab where the data are input in the spreadsheet

Tab2, this tab is use as report, is not filled and is just conditional formatted with colour and a numeric rate (predetermined) to show the progress of the doc control: Red=0, Green=8, Amber=1 to 7

In order to check the evolution of the job I was looking a visual system to show if the project was better, worst or equal the previous month. I found the conditional formatting icon set that could be exactly what I am looking for but I was wondering if is possible compare the same cell in different tab and have:

If my value is more than my previous month, display the green arrow.

If my value is equal to my my previous month, display the orange arrow.

If my value is smaller than my previous value, display the red arrow.

(see sample below without formula)

Can anyone suggest the formula?

Answer 1

Hi Gabriele,

As per your description, it seems you want Conditional Formatting with Icon Sets using relative reference. Create a conditional formatting in a column that will look to the value in the previous month column e.g. if value higher than reference value then green, if equals then orange, otherwise red. You can use OFFSET formula e.g. in the conditional formatting, using the formula "=OFFSET($C$3,ROW()-3,0,1,1)" for the "green".

Here is for your reference: OFFSET function.

If any other community members have any better suggestion, please share your good suggestion here with us we will highly appreciate your shared suggestion.

I appreciate your time and understanding.

Best Regards

Waqas Muhammad

Answer 2

Hi Gabriele,

May I know if you have some updated information? Feel free to post back if you need further assistance.

Best Regards

Waqas Muhammad

Answer 3

Good afternoon Muhammad,

thank you for your reply.

I tried the formula you posted and it works!

However, there is an issue: you can't highlight all the column and conditional format, otherwise the cells refers all to the same one (in our example C3) nor copy and paste on multiple cells or drag the corner of the cell. Currently I found possible it only if I fill singularly each cell using every time "format painter". Is there a way to create the rule for all the cells in one shot?

Regards

Answer 4

Hi Gabriele,

Thanks for writing back with us.

In conditional formatting rules, cell references are relative to the top-left most cell in the applied range. So, when making a new rule, you can simply pretend as if you are writing a formula for the top-left cell only, and Excel will "copy" your formula to all other cells in the selected range.

Please try to highlight all the cells in the sheet to which you’ll apply the formatting rules. Do not select headings then click Conditional Formatting>Select Highlight Cells Rules>more rule and see it work for you.

For reference: Format cells by using an icon set

I appreciate your time and understanding.

Best Regards

Waqas Muhammad