Conditional Formatting using Icon Sets to highlight changes in linked data

**BenPro** · 10-28-2015, 05:27 PM

I have an excel workbook set up as a static, formatted template made up of multiple tabs. The data residing in the "data input" tabs is linked to and automatically updates the formatted templates which are then converted to PDF for printing. The formatted templates illustrate multiple columns of linked data - with the 1st column representing current data and all remaining columns representing proposed data. The data is made up primarily of numbers but can include some text, though rarely. I want to format the columns representing "proposed" data using Conditional Formatting (specifically, the Icon Set of small directional arrows) to highlight the data in each cell of the "proposed" columns that ends up being higher than the corresponding cell in the "current" column. How can I achieve this? Thank you very much for your help.

**FDibbins** · 10-28-2015, 05:34 PM

Apart from the "Use Formula" option, all other CF rules can only be applied to the cell they reside in, they cannot be applied to another cell, nor can they be based on data from another cell.

And the "Use Formula" option is restricted to font and/or color changes, it cannot use icon sets

**BenPro** · 10-28-2015, 05:40 PM

Thank you, Ford, the quick reply. I had a feeling...I spent hours trying to figure out how to format the columns of data collectively...seemed the only way I could get my idea to work was by formatting each individual cell, which was extremely time consuming, given the size of the workbook. Thanks again.

**Aardigspook** · 10-28-2015, 05:52 PM

Hi, welcome to the forum.

If I understand you correctly, you don't want the icon(s) in the 'current' column, but in any 'proposed' column which is higher than 'current'?
If that's correct, then in the Icon Sets CF, select 'Formula' in the right-hand drop-down box, enter '=$A$2' in the 'Value' box (assuming your 'current' column is column A), select '>' instead of '>=' in the drop-down to the left of that and select the icon you want (green up arrow?) at the left - so you end with:
'↑' when value is '>' '=$A$2' 'Formula'
Set both the next icons to 'No Cell Icon' so you only get this icon, not any others for 'proposed' values less than or equal to 'current'.
You can then apply this to all the columns in row 2. The big problem is that icon sets can't accept relative references, so you'll have to redo this for each row, to replace '$2' with '$3' etc - if you only have a few rows, that might be okay, but if you've got lots, then this probably isn't the solution you want - I would suggest using plain fill colours instead.

Hope that's of some help - at least with deciding how you want to proceed even if it doesn't actually solve your problem.

**BenPro** · 10-28-2015, 06:34 PM

Thank you very much! I will try this and see if the formatting remains constant as the "proposed" data changes (since these reports are updated constantly).