Excel 2010 Conditional Formatting Nightmare

Updated March 2017. 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.


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.


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.


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.


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.


Cleaning Up the Mess - UPDATED

UPDATE - March 2017: I've found a quicker way to fix the problem, as you can see in this video. Get the detailed steps on my Conditional Formatting page.

The previous solution is listed below, in case you'd rather try that.

Cleaning Up the Mess - ORIGINAL SOLUTION

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.


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.


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.



You may also like...

77 Responses

  1. Niefer says:

    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?

  2. Hi Debra,

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

    • @Jan Karel, thanks, this was happening in Excel 2010, although the file was built in an earlier version, probably 2003. I get the same results in a new Excel 2010 too.

      • @Debra,

        Odd, I thought they fixed this one. Obviously not entirely!

        • zygomatic says:

          Oxymoron: Microsoft Works

          • Daniel Baker says:

            I have Excel 2013. Had some fairly complex conditional formatting appied via VBA. Was all working since I created the spreadsheet and today has just stopped and gone crazy. It keep making the formula in the conditional format reference the first row where the formula was entered and if I remove the dollar signs it just puts them back in. More confusing still whilst the formatting appears wrong whenever you look at the formulas behind it, sometimes it still actually works!

  3. Khushnood Viccaji says:

    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 says:

      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.

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

  5. Danièle says:

    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. :)

    • @Danièle, thanks, and I’ll try to remember to use empty rows at the bottom of the table too. It’s a pain that extra steps are required though, for something that should be simple.

  6. Doug Glancy says:

    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.

  7. Greg Konneker says:

    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.

  8. Eduardo Ramirez says:

    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.

  9. OmarF says:

    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.

  10. Mischa says:

    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?


  11. Mischa says:

    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

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


  12. Eric says:

    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.

  13. Darin says:

    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.

  14. Benjamin says:

    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 says:

      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.

      • Ray says:

        Agree. Conditional formatting should be simple. For the rules just write an if statement, or visual basic routine (I refuse to call them macros) just as one would for any cell calculation. The only difference being the output is the cell format. I find the preconfigured rules and formats utterly useless, and the means of writing and implementing a rule frustrating. It seems the programmers don’t follow exisiting Excel conventions or even use Excel. priro to this awful implementation I always wondered why conditional statements were so limited. Then they put this junk in.

  15. Jason says:

    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…

  16. Russell says:


    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:

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

  17. Russell says:

    Weird, it posted the formula’s incorrectly.

    Here they are:

  18. Clay says:

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

  19. Alarix says:

    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!

  20. Alarix says:

    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
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.EntireRow.Offset(rowOffset:=-1, columnOffset:=0).Activate
    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
    End Sub

    • Kieranz says:

      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!
      PS/ Also how can i do count of
      Selection.SpecialCells(xlCellTypeConstants, 23)

  21. Matt says:

    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!

  22. Marcus says:

    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.

  23. Joseph says:

    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!

  24. Yaz Bilimoria says:

    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.

  25. Xabier Jense says:

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

  26. Alana says:

    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.

  27. g says:

    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.

  28. derek says:

    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?

  29. derek says:

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

  30. Monimonika says:

    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).

  31. Steve says:

    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.

  32. gareth davies says:

    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

  33. Scott says:

    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.

  34. Paul says:

    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.


    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.

    • Drew says:


      Good explanation, however, it doesn’t work in my case – I always use absolutes for both row and column in my conditional formatting, yet, whenever I delete, cut, or insert rows, I always get conditional formatting bloat and fragmentation.

  35. Mahabaleshwara says:

    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

  36. Larry says:

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

  37. Alan says:

    It amazes me the arrogance of Microsoft. At one point, the power users were more or less ignored because that is not where Microsoft made most of their money. The folks who knew less would have their complaints reviewed and sometimes changed because they were paying the bills. Now, Microsoft could care less about anybody. They spend more money removing themselves from products that used to fill 10 or 12 floppy disks, but worked. Of course, now they give whatever garbage they want us to have. I’ve been using Excel since the start of Microsoft and for the best part they have come a long way. But greed is at the forefront and will continue until they come crashing down, like all giants do. We get more help from sites like Contextures and others who donate peoples time and resources to pickup the ball where Microsoft is weak. There are so many “Known Issues” with Microsoft for so many years that one must sit down while they wait, so they don’t get tired.

  38. Pascal says:

    Thanks Paul (June 30, 2014 at 12:28 am),
    Your explanation – together with some other similar references – was extremely enlightening to me.
    However, unfortunately, I could not make it work.
    Open question: do I need to use the indirect references in the “applies to” area ?
    Here is my specific case:
    1./ want to have one column with red-amber-green indicators depending on the content of the cell (eg. “R”, “A”, “G”) ; hence a pure row criterium
    2./ want to “hide” the content of other cells if they are the same as then one immediately above … and on top only doing so if on another “absolute-referenced” cell the entry is “hide”
    3./ I frequently want to add, copy, delete rows … (and columns occasionally)
    I could – so far – not make it work without seeing the “CF-Nightmare” ;-)

  39. Jeroen says:

    Hi all,

    I do use an Excel sheet to do the bookkeeping for my (small) company.
    For tax purposes I ry to do 90% of my business through the bank.

    Therefor I have made a sheet with 2 sections. Bank transactions and Non-Bank transactions.
    My ledger is symbolized by the columns, and obviously the same for both types of transactions (hence the reason they are in the same sheet)
    My symptoms are similar to the ones mentioned above. My sheet contains approx. 10 conditional formats per row (is the date of the current bookyear/is it a tax booking/is the column name empty/is it an end-year-booking etc).
    I have created the sheet with the rows I expect to use this year (because I already noticed the above issue when extending everything with ‘Insert copied cells’). But even when I copy paste within an existing range; this problem occurs (copy-paste_values is not a solution since I need the formula’s and copy-paste is in my blood; undo crashes Excel – reproducable error by-the-way)

    My range with the formats is A1:BC1000, and when starting a year, I have the sheet with 10 formats.
    Then I copy the data+fomula’s from row 10 to row 15 (for example), because both are the payment of petrol and with my formulas I only have to adjust the amount
    Result is 30 conditional formats:
    Range A1:BC14
    Range A15:BC15
    Range A16:BC1000
    All 10 formulas for each range

    No wonder Excel turns unworkably slow after a day’s editting.

    I don’t know if I can convert everything to 2 tables and if that would solve my problems. But how do I convert it to a table?
    I have issues since the move to Office2007 and meanwhile I work with 2010.

    If the support would not have ended for XP, I would really consider reverting to an XP machine with Office2003 since that is by far the most stable Microsoft environment I have encountered.

  40. Marty says:

    Conditional formatting is a very nice feature but can drive you nuts. After repeated attempts of determining what was wrong, a VB macro was created to add the desired conditional formatting to the spreadsheet. The symptom I was experiencing prior to that was – part of the conditional logic kept disappearing. At least now I could add it back when it got lost. I kept thinking that something was overwriting the logic. The VB allowed for all kinds of testing and the only conclusion that made sense was, either the spreadsheet got corrupted or it was a bug in Excel 2010. Fortunately I also had a copy of Excel 2013. I can report that the problem was solved in 2013. BTW, I am running Fusion on a Mac Air with Windows 7. The two versions of Excel are under that. Hope this helps..

  41. Chuck Trese says:

    Here’s the trick: Insert, THEN Copy/Paste-Special

    Let me explain…

    This behavior, though annoying, is not a bug. Excel is simply trying to figure out what you want (aaaanndd not succeeding).
    The trick to inserting rows without fragmenting the conditional formatting is to use this sequence:
    1) BEFORE copying the existing row, insert the new row (inside area where conditional formatting exists). If you do this Excel will understand what you want and it will update the conditional formatting correctly for you.
    2) THEN, copy (previously existing) row with existing data/formulas.
    3) use Paste Special -> Formulas-only to paste those values/formulas into your new row/column. (Formulas-only is better than Values-only because Formulas-only will actually get both manually entered values as well as formulas.)

    So, to simplify:
    1) Insert Row
    2) Copy Row
    3) Paste-Special -> Formulas-only

    So, to simplify further, remember to “Insert first, THEN Copy/Paste-Special”

    DO NOT use this sequence:
    1) Copy
    2) Insert copied cells
    Why? In this case you are inserting cells into a range covered by conditional formatting, but since this new row is coming from who-knows-where, Excel doesn’t know if you want this new row to be like the other rows. It seems like it could be smarter, and recognize that the paste is coming from within the region, and GUESS that you therefore would want them the same, but it doesn’t. To be fair, I don’t know what other use case the MS engineers had in mind, so it is hard to say it is a “bug”. I wouldn’t consider it a bug, but it does seem less intelligent than it could have been.

    Debra’s 1-step-simpler solution (in her OP) works when adding lines AT THE END, because the Table feature of Excel kicks in a does one of the steps for you (it in effect inserts lines at the end, THEN pastes the data using Paste-Special Values. In that case you’re doing the same thing, but it is a single/special use-case. The more general use case is inserting a row into a table – anywhere in that table, even if it is not defined as an Excel “Table”.

    With Excel 2013 at least, more conditional-formatting intelligence seems to be embedded if you already have your table set up as an Excel Table. The end result is that it is harder to fragment the conditional formatting, but it can still be done – without you knowing. Again, the solution is insert, THEN copy/enter values (but still use Paste-Special if copying).

  42. Jawaid says:

    I’m also finding problem in conditional formatting. The same conditions on the same worksheet, some time its work and some time doesn’t work. If I go in conditional formatting and just open edit and apply formatting without doing something, it starts working. One more thing it (conditional formatting) shows a lot of rules while I have applied only few. Can’t understand.

  43. MichaelS says:

    Top answer and explanation, thanks Chuck.
    There are other ways to tackle this problem:
    (1) Don’t apply Conditional Formatting until after you’ve finished inserting rows (impractical).
    (2) Use VBA to format your cells with the touch of a button (not all users will enable VBA).
    (3) Lobby Microsoft to enhance Conditional Formatting by allowing [Applies To] named or dynamic ranges (preferred, but a major object design change, I suspect).
    (4) Stick to Insert (new row) then copy/paste-special (Chucks answer and the only reasonable workaround).
    It boils down to poor Excel design. In other words “defects by design”.

  44. MichaelS says:

    I’ve completed an audit of Excel 2010 actions and what their effect on Conditional Formatting Rules actually is:
    (1) Copy > Insert Copied Cells – CORRUPTS
    (2) Insert > Copy > Paste (all) – CORRUPTS
    (3) Insert > Copy > Paste-Special (Formats) – CORRUPTS
    (4) Insert > Copy > Paste-Special (Values) – OK!
    (5) Insert > Copy > Paste-Special (Formulas) – OK! (copies formulas + values)
    (6) Cut > Insert Cut Cells – OK!
    (7) Delete – OK!
    I also suggest that you create a Named-Range (either dynamic or once only) of your Conditional Format affected range and also a text copy of your Conditional Format formulas (with a ‘ at the beginning of the cell data) because the fastest way to get rid of the thousand or so rules is to remove them all and recreate them from scratch (5 rules max). Excel will convert the Named-Range to absolute cell references as soon as you [Apply] or hit [OK].

  45. MichaelS says:

    I hope Debra reads this as I replicated her problem in another spreadsheet.
    Put simply, the technique of selecting a row, then Insert (a new blank row formatted like the others), then copy and paste only formulas works well only if your Conditional Format Formulas refer to cells in ONE row. If the formula refers to cells above and below, then CF hell breaks loose.
    Now I had a section of my SS that behaved properly CF-wise but the left side of my SS that I wanted to format the line for was “Corrupted” because I looked to the row below and compared it to a value in my current row. When you insert a row, suddenly, Excel thinks that the existing CF formula is split by a new row (correct), so it adjusts the CF formula of affected existing rows and creates a unique CF formula just for the new row. This is what I call “corruption” because my neat Applies To region is now carved-up into 3 or more regions.
    Now my solution was to create a hidden evaluation column and use the formula:
    Notice there’s no references to screw-up if it’s copied to another row.
    Then my CF formula simply looks at this value in the current row to decide whether to rule a line or not.

  46. Sonny says:

    Another workaround that does not require users to have knowledge of excel-specific insanity is to use this VBA code to automatically undo all pasting and redo it as Paste Values:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Application.CutCopyMode = xlCopy Then
    Application.EnableEvents = False
    Target.PasteSpecial Paste:=xlPasteValues
    Application.EnableEvents = True

    End If

    End Sub

    Downsides are that undoing pasting is now impossible for users. And pasting formulas will not work anymore unless the vba is (temporarily) disabled.

    • Dan says:

      Just wanted to say thank you. This basically solves the issue, as it seems to prevent any new rules from being created, and all the existing rules adapt to incorporate the change in rows/columns. Much Appreciated.

  47. Mark says:

    Excel’s Conditional Formatting does not highlight the text I typed and copied into the next cell to valid it actually works?

  48. Recklyn says:

    I found this site because a data file I have was starting to bog down and I began to suspect CF, having had to struggle with not enough of them in Excel 2003. When I bought Excel 2010 they seemed unlimited and I went mad with them. :)

    I have a large table (20 Columns x 50 Rows by 7 CF’s) on my first page which reflects what is happening elsewhere in my workbook. It updates randomly throughout every weekday.

    Imagine my horror when I read right through this blog and checked my table. The 7 CF’s were multiplying 20 x 50, or 1,000 times whenever this table updated. No wonder the data file was bogging down.

    I discovered that there is a feature in the CF part of the ribbon that removes all of the formatting in cells (Clear Rules), either a block of cells (my table), or the whole file.

    My solution was to create a row at the top of the table without the “=” at the beginning of the formulae for each column, but with the CF’s installed. Thus my macro now uses the Clear Rules command for the table block proper at its beginning, and towards the end it copies this inactive top line down into the first row of the table and adds the “=” to it for each column, then does a Fill Down.

    Also at the beginning is the command “Application.ScreenUpdating = False” and at the end, “Application.ScreenUpdating = True”. It now works quickly and perfectly.

    I just hope that this little discovery can help someone else out there.

  49. Basant Bista says:

    Condition format for duplication is not working in MS office 2010 and Ms office 2013 when you insert the copied row, but It was nicely working in Ms office 2007! any solution do you have guys?

  50. Watt says:

    This is indeed an annoyance. Unfortunately, the Format Painter method on your referred-to webpage does too much: if you have other (ordinary) formatting in the target rows, that will be lost, superseded by the formatting of the source row. (I’m using Office Professional Plus 2013.)

    • Yes, if you have ordinary formatting in some rows, that will be lost.
      You could follow the step to clear the formatting in all rows except the first row.
      Then, go into the Conditional Formatting Manage Rules window, and select the correct range for each rule.

  51. Jon Wittwer says:

    Debra’s method for cleaning up the CF rules is very efficient … much better than repairing everything via the CF Rules Manager.

    I love Darin’s tip of adding the Paste Values command in the Quick Access Toolbar. I’d recommend adding the “Paste Formulas ” and the “Paste and Merge Conditional Formatting” commands as well.

    Here are a few ways I’ve found to copy/paste to avoid fracturing CF rules (some of these may have been mentioned in other people’s comments):
    1) Instead of using Insert Copied Cells, first use Insert Row (which automatically copies formatting from the row above) then Copy the row you want and use Paste Formulas.
    2) Copy only ONE cell at a time (works for format painter as well). The CF rule fracturing seems to only happen when copying multiple cells
    3) Copy using the drag handle (single or multiple cells).
    4) Use CTRL+d to copy the cell(s) or row immediately above the selected cells(s) or row
    5) Use the “Paste and Merge Conditional Formatting” command (or the equivalent option via Paste Special). You can do this with multiple cells or rows.

    Here are a few suggestions from excel.uservoice.com that you can vote on if you are interested in getting Microsoft to do something about this:





  52. nelson says:

    My issue with conditional format is copy and paste.
    Currently setting up data base where i have to highlight stock based upon min/max ranges.
    i.e. D7 is min stock, E7 max stock, F7 is stock on hand.
    F7 shall highlight red if over Max level or Yellow for under stock levels and no highlight for in stock range.
    That’s done.. so now instead of doing this 500 times.
    I follow the format painter way or try copy paste (formula) down from F7 down to F30 for test.
    It look great yet all F colum reading from D,E7 regardless if they in cell F30.
    They not reading from F30 or the row they assigned too..
    I don’t want do every single line by line action…

  53. Richard says:

    I have found a way that seems to avoid the adding additional rules when you insert a new row within a range to which conditional formatting is applied.

    1. Insert the new row.
    2. Copy the row above it (or I imagine any row within the originally defined range).
    3. Use “Paste Special” and select the option that says: “All, merge conditional formats”.

    Let me know if it works consistently for you.

  1. March 30, 2013

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

  2. May 23, 2013

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

  3. March 16, 2017

    […] written about this problem before, and there are a few ways to prevent the problem from occurring. Now that I’ve found an easy […]

Leave a Reply

Your email address will not be published. Required fields are marked *