Excel 2007 Conditional Formatting

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Excel has made numerous changes in its conditional formatting between 2003 and 2007. Most of the new features I think are great, though there are a lot of problems with backward compatibility.

One thing I discovered this week is a trap for the unwary.

excel 2007In Excel 2003 it was easy to create a conditional format for a cell, and then copy that cell or even just the formatting for that cell to other cells, and the conditional formatting would be copied also. Of course it pays to make sure that you correctly apply absolute and relative cell references in the conditional formatting if using formulas. And using paste special or the format painter you could copy just the formatting from once cell to another - including the conditional formatting.

In Excel 2007 this works fine if you are copying to cells that don't already have conditional formatting in them., BUT if you are copying to cell with existing conditional formatting the paste actually appends the new rule(s) to the existing one(s). This can cause some very strange results to occur.

Example

For example if you want all the cells in column A to be coloured green if they are not blank - E.g., have something in them, then add a formula rule to cell A1 as follows. This part works for Excel 2003 as well as 2007.

  1. Click on the cell, Click on Conditional Formatting on the ribbon (2007) and then New Rule. Excel 2003, Click Format | Conditional Formatting
  2. Add a formula rule =A1<>"" and format the pattern or fill for Green.
  3. Click OK back to the worksheet and then enter something in cell A1 to test. The fill colour should change.
  4. Now delete the cell contents and then copy the cell and select the whole column (CTRL SpaceBar) and Paste.
  5. Now any cell that contains anything other than blank will be formatted as per the conditional format you chose.

 

Excel 2007 only.

Now if you were to create a conditional format in some other cell - eg Cell C1 and then copy that cell to cell A1, it wouldn't over write the conditional format in A1 but add to it. Thus there would be two rules in cell A1.

If you wanted to overwrite the format in A1 you would first need to clear it, Select Conditional formatting and Clear Rules from Selected Cells before copying and pasting from C1.

Some other references

Eric Patterson one of the members of the Excel Program Management team has written a number of articles on the Microsoft Excel Blog maintained by David Gainer. This is useful from a technical perspective, but doesn't really help much when dealing with large multi sheet spreadsheets with complex formulas and formatting applied. I will stick by my first impression of Excel 2007, treat it  as a new application and don't try and make things compatible, or if compatibility is required use only 2003 features.

Eric writes on:

Compatibility mode

Compatibility Checker

Conditional Formatting Compatibility

Deprecated features for Excel 2007

Microsoft White Paper: Migrating to Excel 2007

Note this point:

Files containing new conditional formatting can be edited with earlier versions of Excel. Users can change cell values, sort ranges, add formatting, and do a number of other tasks without disturbing the conditional formatting. In general if the user does not make changes directly to the conditional formatting on a range, it will safely reappear as designed when the workbook is opened again in Office Excel 2007.

Summary

Excel 2007 has a lot of really nice and easily applied conditional formatting features. However my advice is don't use them unless you know you will not need to use the file in a Excel 2003 environment. We use them in internal files here at Jethro, but for all client files where they require 2003 and 2007 compatibility we are restricted to using what Excel 2003 can handle. (This advice applies to all Excel 2007 only features).

Be careful when copying cells containing conditional formats on top of other cells with conditional formats - it will not overwrite the format as it did in Excel 2003.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
jethro's picture

Hi Santi - you just caught me

Hi Santi - you just caught me at the computer - which is most of the time.
There is usually a way around most things!
Glad to help
Cheers from Australia
Tim

Anonymous's picture

I just what to know how does

I just what to know how does the default condition of icon set work? by default,
excel is using the 67% and 33% for 3 iconset but i want to know 67% of what
or 33% of what? how did they compute for that? how did they get it or how did
they compute it? i tried several ways of computing it using the 67% and 33%
but still it doesnt fit the default conditional formatting for icon set. is
it the 67% or 33% of the total of the range or what?
Regards Kevin

jethro's picture

Hi Kevin the default sets

Hi Kevin
the default sets evaluate the range being used and calculate the percentages based on the max and min of that range
Cheers

Anonymous's picture

Hi! do you mean 67% of max

Hi!
do you mean 67% of max value and 33% of min value?
in this case it should never get red icon becaus all number is bigger the 33% of that.
or 67% of max value and 33% of max value?

Rgds
Kevin

jethro's picture

Kevin - it works out the

Kevin - it works out the difference between the max and min values and that is the value it calculates the 66% and 33% on.
Cheers

Anonymous's picture

Thansk for yor time. It works

Thansk for yor time.
It works with big number list. If you use 2, 3 4, 5 get 2 an red icon. It should be yellow.

Regds
Kevin

jethro's picture

if you use 2,3,4,5 then 2 is

if you use 2,3,4,5 then 2 is red - as it shoudl be being less than 2 + ((5-2)*33%)
3 and 4 are yellow being greater than or equal to 2 + ((5-2)*33%) and less than or equal to 2 + ((5-2)*66%) and 5 is green being greater than 2 + ((5-2)*66%)
does that make sense?
the 33 and 66% values are of the difference between the max and the min - added to the min
Cheers
Tim

Anonymous's picture

Thanks Tim Now I understand

Thanks Tim
Now I understand how it works

auto repair mesa az's picture

The huge amount of high

The huge amount of high quality content on this particular site has indeed made me realize the massive authority your site carries. Fantastic posts and articles all ’round. Keep it going.

Ken's picture

I have a developed 2003

I have a developed 2003 construction estimating spreadsheet that uses a lot of conditional formatting such as changing cell background colors when a dollar amount is entered, displaying a "warning color if the dollar amount is greater than a certain amount etc. This has been working fine until I upgraded to Excel 2010. The same spreadsheet sometimes does not respond immediately when entering amounts of if it does respond, it does not return to default when the amount is removed. However, if I continue working in another cell it will revert (a delayed response to the conditional format) although I haven't been able to determine what "command" or action triggers the delayed conditional formating to respond. Is this a "bug" in the new Excel version? I haven't tried it in the 2007 version to see if it fails there as well. Any thoughts or information is greatly appreciated.

jethro's picture

Hi Ken I suspect that the

Hi Ken
I suspect that the conditional formats from your old file have not successfully been migrated to the new one. my suggestion would be to go through them all and re create them using the new system.
annoying i know. but there is far more flexibility in the new system as a bonus.
Cheers
Tim

Anonymous's picture

Hi, I have integers from 1 to

Hi,

I have integers from 1 to 100 from cell A1 to A100. I want the cells to highlight in green if its value is between cell B1 and B2. For example B1 is 50 and B2 is 80, then all the cell from A50 to A80 will highlighted in green.

It's a rather a simple conditional formating. However, I saw an interesting result if I use relative reference instead of absolute reference.

Here's what I mean. In the Edit Formatting Rule I put:

Cell Value Between =B1 =B2

This will result highlighting only Cell A2 which has the value 2.

But when I put:

Cell Value Between =$B$1 =$B$2

This will result what I intended. Cell A50 to A80 are highlighted in green.

Why is that?

jethro's picture

The absolute references are

The absolute references are what are needed because as the cells move away from A2, the relative ones will look in different places - they are relative.

Harry Parker's picture

The format of Excel is

The format of Excel is obviously copied by googledocs. Excel is just a great product that is.

greg's picture

I've been the comments from

I've been the comments from start to end its good to see an active admin that replies question. Thanks to this

a Different Greg's picture

I have a large excel

I have a large excel spreedsheet that has data that will be continually entered

However, I want the range "applies too" to stay locked even if i insert new data.

For example I have ten rules that apply to range $A$3:$A$5000. However, if i copy a line (A5) and insert above A5, it changes the "applies to" range to $A$3:$A$5001.
The "applies to" field will not except sets nor '$A$3:$A$5000' notation.

Any suggestions?

a Different Greg's picture

When I say it will not except

When I say it will not except '$A$3:$A$5000' notation, I mean that it will not allow you to make the reference range absolute by using the ' '.

jethro's picture

i understand your problem but

i understand your problem but not sure what to do. have you tried using a range name in the "applies to"? i think that would change also though.
the way i have got around this is to create a blank row at the top and bottom of the range and i always insert my new row inside the range. but then i want my range to expand as i add rows. Rather than using 5000 as a fixed number try creating a dynamic range that fills to the number of rows with data in them (assuming you have a contiguous data area)
hope that helps a little.

a different Greg's picture

Thanks! I'll keep fiddling

Thanks!

I'll keep fiddling with it, but i'm pretty sure that there is no easy solution built into excel at this time. Your solution is inovative, however, I find that by just hitting the insert key, cutting a line out, or deleting a line, reaks havoic on my ranges in a manner that in a non-uniform accross my conditions. If I think of a solution I will post it here.

Thanks again.

Greg

krishna's picture

Nice one, Clearly explained.

Nice one, Clearly explained. This is clearly new to me

Julia2's picture

I'm having a strange issue

I'm having a strange issue with conditional formatting, that I've not found in any recent posts. I've set up an extensive set of rules for conditionally formatting a section of cells in a worksheet. When I copy and paste "all" to a new workbook, the precedence of the rules is inverted. That is, rule #1 (on top) becomes the last rule applied in the new workbook and the last rule becomes rule #1. Ack!!! Since I'm creating a template for a year's worth of data analysis for several coworkers, it would be onerous to have to re-order the rules every time someone updates the workbook with new worksheets.

Ever hear of this problem and how to solve it? Is this dependent upon absolute vs. relative references used in the rules, or upon the "stop if true" condition?

Thanks for your response!
Julia

jethro's picture

I have to say i havent heard

I have to say i havent heard of that one Julia - sounds tricky.
I would love to have a look at it and see what its doing.
With large complex conditional format sets and rules I would probably create dynamic range names for them, and then set them using a macro on auto open or something like that. this would get around the problem anyway. It also would solve the problem of the ranges getting confused when inserting or deleting rows/columns in the ranges.
Cheers
Tim

Christina's picture

I would like to format my

I would like to format my excel spread sheet to show a cell colour of either green, amber or red dependent on whether the number in that cell is higher, the same or lower than the previous cell e.g. cell D2 - 495.42 & E2 - 499.16 - Cell E2 would be 'green' because number entered is higher - Can you help a relative 'newby' to Excel!?

jethro's picture

you need to use a formula to

you need to use a formula to do this in the conditional formatting area. the formula needs to start with your second cell and reference the first cell and the second cell using relative formulas - eg no$
so in your example the formula would be entered for cell e2 as =e2>d2 and the result if true needs to be a green fill.
you can then copy this conditional format to the right and it will continue to work with each cell relative to the next
Cheers

Marie's picture

I'm trying to put conditional

I'm trying to put conditional formatting on a cell that is supposed to equal two other cells added together. If the cell doesn't equal the other two cells added together, there is a problem and it should be highlighted. I can't seem to get it to work. I'm going to Conditional Formatting, Highlight Cells, More Rules. Under "Select a Rule Type:", I have picked "Format only cells that contain". Under "Format only cells with:" I picked "Cell Value", "not equal to", then picked cell 1 plus cell 2 for the last box. My highlighting is showing up even if those two cells are adding up correctly. It seems to work fine for the hours columns I have, but not for the dollars. Could it just be a rounding issue and is there a way to account for that?

thanks

jethro's picture

rather than selecting "cell

rather than selecting "cell value" use "formula"
then you will need to use relative cells in your formula if you want to copy it
so if the cells are a1,b1 and total in c1 use a formula of c1<>(a1+b1)

Marie's picture

The issue is with the

The issue is with the rounding. I don't want the numbers rounded for actual calculations, just within the conditional formatting to compare them. Just comparing 2 cells that should be equal, I tried the following formula in conditional formatting with no success. Any ideas?
="ROUND($I$114,0)<>ROUND($W$102,0)"

Nick's picture

The issue I see with your

The issue I see with your formula is that there are quotes (") around it. They will need to be removed before the formula will calculate correctly.

The <> operator checks to see if the first item does not equal the second item. So this should highlight the cell if they are not the same value once rounded.

Anonymous's picture

I have the following formula

I have the following formula in cell A1 - it works just fine - but when i copy the forluma to cells A2 - A456 the forluma shows up but the data so not show correctly - how do i get it to work correctly? i am in Excel 2007

Correct
(Forluma)=D1&B1&D1&E1&B1&F1
(Result)"165306",165306.JPG"

Result not correct
(Forluma)=D2&B2&D2&E2&B2&F2
(Result) =160133160133

Anonymous's picture

never mind - i figured out

never mind - i figured out what was wrong - but thank you

Chaz's picture

Hey! I have just been

Hey!
I have just been upgraded to Office 2010 - I'm sure it will be nice once I know and can find where everything is! - and I am now experiancing a strange phenomina with "Conditional Formatting". I am creating an intricate working model of a program that someone will create for me in Lotus or Secal and I started off in Excel 2003.

I have some very simple stuff such as when this is true =LEFT(AH12,6)="Denovo", fill the cell with the selected color. There are 4 such cells (actually 4 groups of merged cells) and they should all change color at the same time that the left hand 6 characters of AH12 = "Denovo". In practice, one of the cells changes and the other 3 do not - Unless - you do something to activate focus on the field. This might be double click in the cell group or just highlight the line containing one of the unchanged cells (They are all on different lines). The same thing happens going the other way so removing the entry in AH12 has the effect of only removing the color in that one cell while the other three remain filled until highlighted or double clicked in (single click highlight does not have the same effect as highlighting the row).

I tried unchecking the "Stop" check boxes and making changes to the trigger code but with the same effect.

Hope there is enough information here for you to come up with an idea or solution.

Thanks

jethro's picture

two solutions - unmerge the

two solutions - unmerge the merged cells (worst idea ever in excel) and set the range for each individually
or just try adjusting the range to cover them - i think the first is the best option as im not sure the second will work

Chaz's picture

Thanks for responding so

Thanks for responding so quickly. When I transferred the file from Excel 2003 to 2010 it automaticall calculated the merged range and inititially I thougt that was the problem and changed the location to the first cell in the range - no joy.

I have used the "unmerge" but the result is the same i.e. unless I highlight the line or double click in the cell, the cells in question do not change state. What is frustrating is that one of the four fields in question functions perfectly.

I have just tried cutting out the working field and pasting it over a non-working field - it did NOT function correctly! Cut and paste it back to where it came from works perfectly again! Mind boggling......

Chaz's picture

Ok, well, I fixed this

Ok, well, I fixed this peculiar Conditional Formatting issue by making the assumption that is was "one of those Microsoft quirks!". Consequently I copied the whole sheet, pasted into a new sheet and reconnected all the links (boring process!) to the rest of the workbook. Now works perfectly but I wish I could understand what goes wrong so that - perhaps - I could fix it without the need for going down the route I took.

Cheers!

jethro's picture

aha - it might well be an

aha - it might well be an issue with the conversion process - the conversion is not automatic, nor is it necessarily good/accurate. I will be doing a bunch of these soon for a client so might well encounter the same problem. if i work out a solution there will likely be another blog post!

NellyHK1's picture

Hi I have a large spreadsheet

Hi
I have a large spreadsheet with conditional formatting set up to show red, amber & green depending on target cells.
Now informed I'm using the 'wrong' green!
Any way to 'find and replace' formats within conditional formatting, as you can in normal (non conditional) cell formatting?
Cheers

jethro's picture

what version of Excel are you

what version of Excel are you using?

NellyHK1's picture

ah, yep would help if I said!

ah, yep would help if I said! Excel 2007.

jethro's picture

theres no easy way

theres no easy way unfortunately. you could write some VBA code - but potentially this would take longer than doing it manually. essentially you would need to create a loop that cycled through every cell that had conditional formatting in it and looked the the green property and replaced the colour.
My suggestion, select the entire sheet, go into conditional formatting and manage rules and edit each rule that has the green in it.
Good luck!

NellyHK1's picture

Thanks for the swift reply.

Thanks for the swift reply. I'd better get started then!

Jennifer's picture

Help Please! I am trying to

Help Please! I am trying to set up a conditional format in Excel 2007 I'm pretty sure I need to use an IF formula to do it. I want cells A5:G5 to be highlighted if the text in G5 is 'Yes'. I want to make a second format condition that if H5 is 'Yes' then A5:H5 the font becomes Bold. I want a third condition that only if G5 andH5 are both 'yes' then to highlight in a different color than if only G5 is 'Yes'. I think it should be a nested IF formula but when I try to do it I keep either getting an error message, or the conditions don't work. Please tell me what my formula should be and what it should look like so I can copy and paste it into both my current workbook and a MACRO. I am sure I will need this format condition on numerous occasions.

Thank you,

Jenn

jethro's picture

you will need to add these

you will need to add these all as separate conditional format statements int he conditional format manage box. alternatively you could use hidden columns that contain the individual logic for each condition and then use a single conditional format statement to utilise that result. doing this in VBA code would be somewhat different however

Anonymous's picture

Hi, please can anyone tell me

Hi, please can anyone tell me how to copy a cell that has been conditionally formatted ? each cell reacts to 10 other cells in the same column at the top of the page. there are 200 cells at the bottom of the column which i need to format, but if i drag down, copy and paste or use the format brush, the 200 cells react with each other instead of remaining seperate and individually reacting with the 10 cells at the top of the column.

jethro's picture

sounds like you need to get

sounds like you need to get the relative and absolute cell references setup correctly. check this article out
http://www.spyjournal.biz/back-basics-using-absolute-references-excel