Forum Discussion
Conditional Formatting
I have a column with location data (5 different locations) and when I choose a location the cells in that column change fill colour. This is easy. What I want to do is then change the fill colour in ...
Select the cells which you want to format.
Example A1:Z100
Go to Conditional formatting ->
Use a formula to determine which cell to format.
Enter the formula
=IF($A1="India",1,0)
In Format, select the required colour.
Repeat the same formula for other countries.
Only change the format colour.
Do let me know if any clarification or further information is required.
Vijaykumar Shetye,
Spreadsheet Excellence,
Panaji, Goa, India
Example A1:Z100
Go to Conditional formatting ->
Use a formula to determine which cell to format.
Enter the formula
=IF($A1="India",1,0)
In Format, select the required colour.
Repeat the same formula for other countries.
Only change the format colour.
Do let me know if any clarification or further information is required.
Vijaykumar Shetye,
Spreadsheet Excellence,
Panaji, Goa, India
THanks for your reply. Excel didn't like the IF statement. It seems it is not required.
Regards
David,
It's not required, but in general conditional formatting shall work with IF (i didn't test above particular formula). You may use in the rule any formula which returns TRUE or FALSE or their numeric equivalents. That's for "normal" formatting, not when you apply icons and like.
Another point, not to hardcode countries' names within the rules you may use helper column with countries name. Let say in AA:1 "India", in AA:2 "USA", etc.
When you have one rule for each color, rule formula will be like
=($A1=$AA$1)
If one day you'll decide to exchange colors for the countries, or change the country name ("USA" => "United States") you may simply make such changes in helper column without editing formatting rule. The only when you need to do that if to add new color which is not in existing rules. But even in this case you may add few dummy rules for future needs.
However, if your data is static one time hardcoding works fine.
Hi, i'm looking for some help RE Conditional Formatting as well. Essentially I want to have 3 conditional formats for an entire spread sheet. They would look at a different sheet in the same document, compare the corresponding cells (Sheet 1-A1->Sheet 2-A1) and if Sheet 1 was bigger, sheet 2 would become yellow. If they were equal, it would become white. If it was smaller it would become green.
Is this possible without doing the cells one by one? The format painter is not working, I believe as a result of the use of different cells across the formula.
Thank you,
Matt
Hi Matt,
Not sure what exactly you'd like to receive. Let for example compare every cell in both sheets. If we use the rule
=(Sheet2!A1 < Sheet1!A1)
and when apply to entire Sheet2 as
=$1:$1048576
every cell in the second sheet the value of which is less than the value of the cell with same address in Sheet1 will be colored as we define in the rule.
The idea is what Excel for the each cell in the range applies the rules, to simplify the same way as we copy/paste the formula. If in the rule relative references the will be checked for each cell within range.
If we change the rule on
=(Sheet2!$A1 < Sheet1!$A1)
the colored will be only rows where value in Column A for the cell less than the value of the cell in same row of the Sheet1 column A.
Format painter woks the same way. The key point when you transfer formatting is to be sure you properly use relative and/or absolute references.
