Excel 2010 Conditional Formatting Nightmare

I'm a big fan of Excel's conditional formatting, and often use it to highlight cells on a worksheet. It's also useful for alerting people to problems, and other devious plots, like hiding cells' contents, until other cells are filled correctly.

But this week I ran into a conditional formatting crisis, and had to start from scratch. Fortunately, the fix didn't take too long, but with complex formatting, things could have been much worse.

Separate the Dates in a List

Earlier this year, I posted a tip for separating dates in a list, by using a red border at the start of a date change.

ConditionalFormatSections04

It's a technique that I use in one of my own workbooks, which I update every morning. The red border really makes the list easier to read, especially if I'm looking at it before my morning coffee!

The conditional formatting was set for cells A2:F9, and uses a simple formula to see if the date is equal to the date in the row above.

ConditionalFormatDuplicate01

Quick Copy and Paste

In the workbook, I track some daily statistics for my website, and use the same headings every day. So, to save typing time, I copy and paste the headings from the day before.

For example, in the screen shot above, I could copy the regions and products headings from B2:C4, and paste them into cell B10:C12, for the latest day's data.

ConditionalFormatDuplicate02

The Excel table automatically expands to include the new rows, and fills in the formulas in column F. Awesome!

And that copy and paste trick is what I've been doing every day, for months on end.

Trouble With the Lines

Suddenly, this week, I started having trouble with the red border between dates – it wasn't going across the full table.

So, I went into the Conditional Formatting Manager, to fix the problem. But, instead of one simple rule, there were hundreds of rules! You can see the tiny scroll box at the bottom right in the screen shot below. That should give you some idea of how long that list was.

ConditionalFormatDuplicate04

Every time that I copied and pasted within the conditional formatting range, it created another rule for the worksheet. Yikes!

Let's take a look at the smaller example, where I just copied and pasted the Region and Product headings. Now, it has two rules, instead of one, after one copy and paste within the table.

ConditionalFormatDuplicate05

Cleaning Up the Mess

I scrolled through the list, and deleted a few of the rules, but quickly realized that it would take far too long to delete all of them. So, to clean up the mess, I cleared the conditional formatting from the worksheet, and set it up again.

ConditionalFormatDuplicate06

With all the extra rules gone, the conditional formatting borders were working correctly again.

Avoid the Conditional Formatting Problem

Now, when I want to save some typing time in the daily list, I copy the previous headings, and use the Paste Values button to create the new records. I've got a copy of that button on Excel's Quick Access Toolbar, so it's easy to click.

ConditionalFormatDuplicate07

That adds the text, and the table expands, including the formulas – but the conditional formatting doesn't create a new rule.

Be careful out there -- don't let this conditional formatting nightmare happen to you!

Another Solution

Update: Thanks to Khushnood, who suggested leaving 3 blank rows at the bottom of the table, and inserting new rows above that, when adding new data. Copy and pasting within the table doesn't seem to produce the duplicate conditional formatting.

It's still an extra step though, instead of a simple copy and paste.

________________

49 comments to Excel 2010 Conditional Formatting Nightmare

  • Niefer

    That is the problem not only with Excel 2010 but with earlier versions too.
    Btw don't you think that context menu is a bit more convenient than Paste Values button?

  • Hi Debra,

    This is a known bug in Excel 2007. You should be fine doing the copying "as usual" in Excel 2010.

  • Khushnood Viccaji

    I've faced this problem ever since Excel 2007, when Microsoft introduced the new conditional formatting features (more than 3 conditions; icons / graphics; and other stuff).

    Luckily I discovered a different solution before it could get as bad as your worksheet :)

    Basically, you should keep at least 3 blank records at the bottom of the table (which need not be a "table" table).
    Whenever I need to add more data in the table, I insert the required number of blank rows above the second-last row (with one blank row left *above* the new rows being added). Then I simply use Ctrl+D (fill down) from the last blank record upto the second last record in the table.

    e.g. Data is in the range A1:E50
    I keep rows 48, 49 and 50 as blank records (which may include formulas, but no data)
    When I need to add say, 5 more blank records, I goto row 49, and insert 5 rows above it.
    Then I staying in row 49, I fill down upto row 54 (which is the new second-last row in the table).

    For some reason, if you do the above steps with only the last row blank (i.e. starting from row 50), it creates the same problem that you described in your post -- multiple conditions keep getting added to the worksheet.

    • @Khushnood, thanks, that's an interesting solution, and it inspired me to try another test. I dragged down the marker at the bottom right of the table, to make it 3 rows longer. Then, I was able to do a normal copy and paste to the new rows, without adding a new conditional formatting rule.

      • Looks like the bug that is still there is that pasting below the table causes expanding of the table, which then adds the CF of the table on top of the CF of the pasted cells.
        Might be useful to file a bug for this Debra.

    • Alphonse

      As far back as I can remember, Excel duplicates the formatting of the row above the one being inserted. In your example, inserting a row in 48 will copy the formatting from 47. When inserting in 49, it copies from 48, which in your case is non-formatted. You don't really need the third non-formatted row, 50.
      If I may clarify something, the filling down is only necessary if you have formulas on row 48, but not if it is blank.

  • Been there, done that. And I had one of those complex sheets! :(

  • Danièle

    Thanks! I just found that same horror to one table that is "updated" regurlarly for the last few weeks.
    I deleted all conditional format, and started afresh to find that the problem repeated itself a few days later.
    I Will use the three lines empty at the bottom as a safeguard as it is easier to enforce with other users then going to the paste values without organising for each one a paste value button on their ribbon. :)
    Thanks!

  • I didn't know you sold staplers too! The price is a little high, but I'm sure the quality is up to the same stellar standards as the rest of your site(s), so expect my order soon.

    FYI, in contrast to Jan Karel's comment, it happens for me in cells that aren't part of a table.

    The first time this happens, it's scary. I've always just gone into the CF dialog, deleted the dupes, and reset the range on the original. This way is way better.

  • Greg Konneker

    I too have the same problem in an semi-automated template that has over 200 columns and between 30-500 rows of data depending on the user's needs. Unfortunately, the users' requirement to be able to insert blank rows anywhere within the data area makes using the last 3 row solution impractical for me.

    the template has conditional formatting in about 50% of the columns, and a user may have anywhere from 3 to 30 tabs. This really creates a lot of unnecessary additional conditional formatting.

    Wish there was a better solution to clean up the mess or prevent it from happening.

  • Eduardo Ramirez

    Slightly off-topic, but I find I use paste by value so often I long ago memorized the keyboard shortcut: alt-e, s, v. I also find myself using the keyboard for other special paste commands (alt-e,s,t for formats, alt-e,s,f for formulas, etc) but paste by value is far and away the most common for me.

  • OmarF

    I'm running into this with a table hooked up to a query. As the query expands and contracts the table, it is adding the conditional formatting problem talked about here. This is on 2007. I haven't got the right data driver on my system with 2010, so can't test if it is resolved for me.

  • Mischa

    I have the same issue as Greg, the user needs to be able to add rows anywhere in the table and that messes up the CF and the 3 rows or the paste as values do not help there. Any ideas on how to prevent that?

    Mischa

  • Mischa

    Ok Greg,

    I just found a solution on OZGRID thanks to Batman

    He suggested using the INDIRECT function in the conditional formatting formula, so i tried
    =INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1)

    tried to enter a new row in the middle of the table and surprise, surprise it worked perfectly ;-)

    Mischa

  • Eric

    OK, I guess I'm just a little surprised that you haven't combined your earlier idea of a one-click formatting of your spreadsheet to include conditional formatting. Without patting myself on the pack too hard (got to watch out for that elbow... don't want to injure it) I've been doing it for quite a while. A little more difficult to code but worth the time to ensure that you don't open up your spreadsheet and get frustrated.

  • Darin

    This is a great example of the possible issues arising from using the simple paste instead of paste special. When pasting normally, this could be more accurately called Paste ALL. There are over 80 ‘top level’ properties of a range, and more if you consider the collections of properties for each range. Paste All will bring over almost all of these properties.

    The majority of the time, pasting without using paste special will not cause problems, but I am in the camp of pasting exactly what I want into the cell and not bringing any extra baggage along. I use paste values for the majority of my work, and the other paste special options such as pasting formulas, formats, or transpose are indispensable. I tried applying the new paste special option in 2010 (All merging conditional formatting) but it didn’t appear to handle the issue as I had hoped. I would agree that using paste special values is the most appropriate solution.

    A quick tip on using the quick access toolbar:
    No need to use a complicated keyboard shortcut to navigate the ribbon, and no need to create a shortcut key with VBA. As soon as you add an item to the quick access toolbar, it is assigned a shortcut key. I keep paste special values as the first item in the toolbar, so it has the shortcut ALT 1. The next item would be ALT 2, and so on.

  • Benjamin

    Conditional formatting used to work in a logical way, and pasting cells carried absolute or relative conditional formatting as any informed user would expect, irrespective of how it was accomplished. However, failed attempts to improve the product by bloating it with useless excess as well as needless changes of conventions has created this problem. This kind of nightmare is typical of Microsoft - an authoritarian company that imposes its bloatware on suffering users everywhere. Each time a new often worthless and at times positively destructive tweak is added to a Microsoft product, millions of users must relearn how to use the software, and discover workarounds to the new bugs. This must be costing the world economy billions in lost productivity, of not trillions. This is the penalty we pay for the monopoly that Microsoft has become.

    • Manoj Kumar

      You are absolutely right. It was a breeze to make and use Conidtional Formatting in previous versions when there was no "applies to range". As you said, the formatting worked when copied "in a logical way, and pasting cells carried absolute or relative conditional formatting as any informed user would expect".

      Now all rows refers to the formula for the first cell and gives unexpected results down below and there is no way to change it.

  • Jason

    I have been testing Office 2013 and this is still an issue... Some work books would get as big as 50 MB and after the cleanup back down to 35kb. lame...

  • Russell

    Hello,

    I hope you can help. I have 3 cells in a row. D4 (form number), E4 (case number), and F4 (score between 1-100).

    I have set up conditional formatting for E4 successfully to change the color of the cell based on the value contained within it. 0-60, red; 61-70, orange; 71-80, yellow, 81-90, light green, and 91-100 dark green.

    Fantastic, but now I want to match the conditional formatting of cells D4 and E4 to F4. So that based on F4's number those cells change color too.

    Logically, here's what I have in my 5 formula bars for my 5 rules in conditional formatting:
    ="e4=f4=>90"
    ="e4=if(f4=>81,f4=70,f4=61,f4=<69)"
    ="e4=f4=<60"

    But nothing happens. Please help. How do I get cells D4 and E4 to match the color conditional formatting for cell F4?

  • Russell

    Weird, it posted the formula's incorrectly.

    Here they are:
    ="e4=f4=>90"
    ="e4=if(f4=>81,f4=70,f4=61,f4=<69)"
    ="e4=f4=<60"

  • Clay

    Russell, try setting the "Applies to" field for each rule to: =$d4:$f4

  • Alarix

    Well I have a sheet with 150 lines, where different columns and ranges use total of 17 conditional formatting conditions. If had to start all over again every time, I'd go mad. There must be a better solution!

  • Alarix

    I think I solved the problem for myself: thanks of course to info here about pasting as values.
    I have a sheet of users where some data are values/constants, other data are formulas.
    I needed to duplicate a row and then erase all constants from it. Then manually entering the ID brought all other user data from related files.
    My macro for adding new row used to copy a selected row, and insert paste one row above. Then it used Goto/Special/Constants in the selected newly pasted row and Clear content of selected constants.
    New macro selects an entire row where the cursor is positioned, inserts a new blank row, then row above is selected, copied and pasted as formulas only to the row below. Then the constants are cleared the same way as before and that's it.
    The trick was in pasting as formulas: I was looking at first how could I paste the formulas only into the row pasted as values only - but MS Excel does not paste the non-contiguous ranges. But pasting as formulas pastes the constants as constants and formulas as formulas. :-)
    Thanks for the inspiration above, I have made it! :-D

    Sub NewLine()
    ' NewLine Macro
    ActiveCell.EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.EntireRow.Offset(rowOffset:=-1, columnOffset:=0).Activate
    Selection.Copy
    ActiveCell.EntireRow.Offset(rowOffset:=1, columnOffset:=0).Activate
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    End Sub

    • Kieranz

      Hi Alarix
      Nearly neat. But if the row above is blank then the code bombs (error at Selection.SpecialCells(xlCellTypeConstants, 23).Select ).
      Any idea how to fix this!
      Thks
      Rgds
      KNZ
      PS/ Also how can i do count of
      Selection.SpecialCells(xlCellTypeConstants, 23)

  • Matt

    Hello, I don't know if this is the place for this question or not, but I'm new to Excel and it's vast number of capabilities. I am working on a pivot table with Excel 2010 and i inserted some conditional formatting icons in some of the cells. Everything worked and looks great on my version, however, I sent the file to a co-worker who is operating on Excel 2007 and all that shows up are my numerical values i set to condition. Is there any way around this, where the conditionally formatted cells would maintain their rules within a pivot table from 2010 to 2007?

    any help would be much appreciated!

  • If you use Excel 2011 for Mac, you might encounter similar issues. I was copy-and-pasting rows with a different background color fill. Changing the fill color removed the default cell borders. Unlike simply copying and pasting, then changing the background fill color to your spreadsheet default (e.g., white), using "Paste Values" preserves the default cell borders.

  • Joseph

    I've had this same problem, except it does it every time (no matter what I do) when trying to add something in the cells to the right of my conditionals. It changes every single rule to affect the entire row, very specific to that cell, too. So, when I attempt to add comments to the right, my rules get screwed up and their "Applies To" becomes unmanageable!

  • Yaz Bilimoria

    I'd like to use conditional formatting to flag the two cells in a list of increasing numbers that bracket a fixed value. For example:
    Fixed value: 130.5
    List of increasing numbers: 127 128 129 130 131 132 133
    130 and 131 bracket 130.5, so 130 and 131 should have a different format (say, red background)from the rest of the list.
    Any suggestions on how to do this? Thanks.

  • Xabier Jense

    Thank you, the comments are a big help. I thought I was another Excel nitwit, but it's real problem.

  • Alana

    I had this problem before and I fixed it using the same solution Mischa posted above of using INDIRECT formulas. After a while the spreadsheet started running so slow that I had to get rid of the INDIRECT formulas. I have resorted to running a macro to clean up the conditional formatting.

  • [...] again, They can be many different issues ... Have a look at Debra's recap : Excel 2010 Conditional Formatting Nightmare | Contextures Blog [...]

  • g

    Hi
    I have just discovered conditional formatting and i tried to do to use icon set, and criteria. i tried create icon for 100 percent, >75, >50, >25. It would work fine for 100 percent but for all others, it does not work. I am not sure what i am doing wrong.

  • derek

    I created a new dashboard and handed it off just before the company "upgraded" from 2003 to 2010. Thank goodness I did, because what took me a minute in 2003 would take me a day in 2010. What will I do when they ask me for the next one?

  • derek

    Can't use formulas with icon sets, can't use relative references with icon sets. They're really badly implemented.

  • [...] Could this be the problem? Excel 2010 Conditional Formatting Nightmare | Contextures Blog [...]

  • Monimonika

    One way I go about keeping track of my conditional formatting rules is to edit the rules like so:

    Before: A1=B1
    After: AND(1/1=1, A1=B1)

    Before: less than "=2"
    After: less than "=1/1*2"

    The "1/1" part can be "2/2", "10/10", or any other fraction resulting in 1. I use this to number each of my rules in order, so I can easily see which ones get duplicated/split.

    I also avoid using relative references and instead use INDEX(ROW(),COLUMN()) to refer to relative cells. This way I just need to update the ranges and not worry if the rules will use the correct relative starting cell(s).

  • Steve

    We ran into the CF accumulation and file bloat; slowdown. My solution was simply to .clear any destination before I copy to it. If you are coying in a for-next loop, it's faster and you may have to take the .clear out of the loop, clearing the whole range at once. Likewise if you can CF the whole range at once, also reduces file size, and runs faster.

  • gareth davies

    I have set up Conditional Formatting in Excel 2007, but when I have opened the file in 2013, the rules are not being applied. Does anyone know a solution please ? I am trying to colour cells depending on values using the formula method. Thanks

  • Scott

    Yes the conditional formatting is not much fun when you insert/delete cells. WIth keywords 'disable delete excel' i came across a macro that disabled the right click options to delete or insert, instead leaving clear contents.

    My other problem with it is when using an offset function for a dynamic named range, conditional formatting will not update with the defined range. It seems there is no solution there.

  • Paul

    I've done a lot of digging on this Conditional Formatting rule "split" phenomenon lately (I'm using Excel 2010 btw). I've learned a few things that might help folks avoid this problem. Basically it boils down to 2 things:

    - Understanding "why" this is happening, and that what's happening actually makes sense (even though it's not what I or most people want). I've observed the splits happen only when certain modes of addressing are used in the CF formula, and not with others.

    - Once one understands the "why", one can usually construct CF formulas that are immune to this phenomenon. I've managed to do so.

    I'll give you the workaround now, then explain it below. In fact, it's already been posted here (by Misha in reference to something posted by Batman), but just having a solution without knowing "why" makes it hard to generalize it so you can apply it to all cases. I'll try to give you the "why" also.

    So the solution to avoid splits from row insertions/moves:
    Whenver your CF formula refers to a cell that is not on the "same row" (in the context of how the CF evaluation steps through the "Applies to" range), you must either use a row-absolute address (e.g. A$1), which generally won't meet your needs anyway, or (this will almost always be the case) you must reference that location indirectly, either with INDIRECT() or with OFFSET(), and use ROW()-x math to establish where you want it to land.

    Note the same logic would apply to avoid splits due to *column* insertions or moves; just replace row-absolute above with column-absolute (e.g. $A1) and replace ROW() with COLUMN() math.

    To be immune to both row and column inserts, all "out of cell" references must use full absolute addresses ($A$1), or use INDIRECT (or OFFSET) with both ROW()-x and COLUMN()-y type math in them.

    Ok, so that's the solution that's working for me. Here's the explanation why...

    My test case is to have a range of data, define some CF rules across that range, then either a) insert a row in the middle of the range (e.g. expand), or b) cut a row and insert it somewhere else in the range (e.g. move/reorder). You don't even need to go so far as to copy/paste anything into the blank row you created to get the problem to occur.. if it's going to occur it will do so immediately following a row insert or a move.

    Observations:

    1. CF formulas with references to cells within the same row (as evaluation traverses the "Applies to" range) will not split due to a row insert or move. E.g. a CF formula like =$K6="abc" is immune, given the "Applies to" range has it's anchor on the same row as the address in the formula, such as $A$6:$B:29 .

    Why? Because this formula can stay typed exactly as it is in the CF rule and still "work" (dependent cells still refer to same original data) despite a new row being wedged in the middle. This is just like cells out on the sheet (not in CF formulas) - you insert a row, and sure, Excel changes addresses in formulas in the rows below, but the formulas still all follow the same pattern.

    Continuing with this analogy to a formula on a sheet, if your sheet formula references a cell one row above it, and you insert a row between the two, Excel has to change the address in the formula so it keeps pointing to the original data which is now two rows above it. This is fundamentally different to the first case in that now this new formula is not following the same "pattern" (e.g. refer to one row up).

    So, back in CF land, when this happens (the second case), the CF rule can't represent both patterns (formulas that refer to one row up and others that refer to two rows up) so the only thing it can do is split the rule into two rules, one with a formula that refers to one row up and another that refers to two rows up, and then sets the "Applies to" ranges accordingly.

    2. CF formulas with references to cells by row-absolute addresses (e.g. K$6) won't split due to a row insert or move.

    If you think about it, this makes sense too. Just like out in a cell on a sheet, if there are multiple rows with formulas that all refer to the same row-absolute location, and you insert a row between the existing rows, the "pattern" in the formulas above and below the inserted row need not differ. So back in CF land, there is no need to split a rule that uses row-absolute addressing.

    (and again, if inserting columns not rows, think column-absolute addressing).

    One caveat:
    - I've observed that nesting an INDIRECT or an OFFSET call within any other function (such as AND or IFERROR, etc) somehow messes up the result. I don't know why this happens. I'd consider it a legitimate bug. For instance, =INDIRECT("$K$"&ROW()-1)="March" will result in TRUE when the cell in column K one row above the one being evaluted contains "March", but =AND(INDIRECT("$K$"&ROW()-1)="March",TRUE) will result in FALSE even though semantically this is an identical formula. To accomplish ANDing, I've had to resort to this form instead: (A=B)*(C=D). That means literally enclose the terms in parens, and use the asterisk to "multiply" each term's TRUE/FALSE value to get an equivalent to AND. It's a pain, but it works.

    Again, thanks to Batman (and Misha) for the clues about using INDIRECT and OFFSET. I just hope I've provided a little more context so that you can generalize this solution. For instance, you won't have to rewrite every CF function you have.. many forms of addressing are immune and won't need indirection. But knowing which forms will need it, you can fix the minimum set of rules and be split free.

  • Mahabaleshwara

    Hi,
    I have list of weekdays from A1 to A7 (Sunday to Saturday) and a dropdown list for the same.
    I would like to colour the respective weekday on the list (A1-A7) when selected from dropdown list at B1, through conditional formating
    I tried the lookup formula for conditional formating as given below but not working. kindly help
    =LOOKUP($B$1,$A$1:$A$7)=$A1

  • Larry

    Very frustrating. My old version worked perfectly and made my sheet look good. Now, what a waste. BTW, Windows 8 sux.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>