Excel 2010 Conditional Formatting Nightmare

Excel 2010 Conditional Formatting Nightmare

[Updated June 2018] I’m a big fan of Excel’s conditional formatting, and often use it to highlight cells on a worksheet. But this week I ran into a conditional formatting nightmare, 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

There are lots of ways to use Excel conditional formatting. It’s useful for alerting people to problems, hiding duplicate values in a column, or even hiding cells’ contents, until related cells are filled correctly.

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

Easier to Read

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

There is a full transcript of the tutorial following the video.

TRANSCRIPT FOR THE VIDEO

Conditional Formatting

In this workbook, I’ve got a couple conditional formatting rules, and I’m going to show you how those can get duplicated, so you end up with lots more rules than you started out with, and then how you can fix the problem.

So in this table I have two rules. 

–One puts a line at the top of a date, if it’s different from the date above.

–The other rule changes the price to green, if it’s greater than $500 dollars.

We’ll take a look at those rules. In the Home tab, go to Conditional Formatting, Manage Rules.

And there you can see the two rules. They’re each applied from row 3 to 19 in this table.

–Now this one is just a cell value greater than 500. 

–And this one is referring to a couple of cells. So if A2 is different from A3, we’re going to put a blue line, at the top of the cell.

How Problems Happen

And now, I’m going to insert a row, because I forgot to put in one of the records here. So I’ll click, Insert. And then just put some data in this row.

So that was a simple change to the table. And now I’m going to go back and look at my conditional formatting rules again. And suddenly I have a new rule, just for the row that I inserted. 

So row 11 has its own rule, and the previous rule is still there, going from A3 to E10, and then A12 to E20. So it’s skipping this row, because that row has a separate rule.

And if you insert lots of rows, you can end up with hundreds of these rules set up, without even knowing it.

The Solution

And what I do to fix it is select all the rows, except for the first one. Then go to Conditional Formatting, Clear Rules, From Selected Cells.

Now if I go back in, and Manage Rules…I’ll go back to This Worksheet, just so we can see everything. So we’re back to those two original rules.

Then, I select the first row. I’ll select the first row in the table, this time, instead of the whole worksheet.

And go to the Format Painter, and make sure you’re selecting this first row as well, and apply that formatting to all the rows again.

And when we go back to Manage Rules, we still just have those two original rules.

If you need to do this frequently, go to my website to get a macro that removes the duplicate rules. It’s designed for data in in an Excel named table.

End Of Transcript

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.

ConditionalFormatDuplicate06

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

Avoid Conditional Formatting Nightmare

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

83 thoughts on “Excel 2010 Conditional Formatting Nightmare”

  1. 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?

      1. My tip for pasting values is “Ctrl-Alt-V” and then press “V” again for “Values” and Enter. On a mac it’s “Ctrl-Cmd-V”.

    1. @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.

      1. 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!

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

    1. @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.

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

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

  3. 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!

    1. @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.

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

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

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

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

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

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

    1. IMHO, your solution is the best. Using INDIRECT() in CF rules makes them harder to write and read, BUT once the rules are set, editing the table data just works, plain and simple. No more auto-duplicated rules.

      Why does it work? I think it is because INDIRECT() allows to avoid any reference to the active cell, which makes the rule invariant across the whole range of cells (i.e. Excel does not need to transpose the rule for each cell). And invariant rules are NOT affected by the copy/paste problem.

      For the same reason, CF rules that are based on cell content (rather than based on a formula) are not affected by the copy/paste problem, because they are invariant across the range (they do not contain any reference to the active cell).

      Example.
      Imagine you need a CF rule to change the font of the cells that are calculated by formulas. If your selected range is the whole sheet you could make a CF rule based on this formula:
      =ISFORMULA(A1)
      A1 is the active cell, so Excel auto-translates the A1 reference for every cell in the range. It works, but it is affected by the copy/paste problem.
      Alternatively, you could do the same with a CF rule based on this formula:
      =ISFORMULA(INDIRECT(“L”&LINE()&”C”&COLUMN();FALSE))
      Arguably less readable, but totally invariant across the whole range of cell. There is no explicit reference to the active cell that Excel would need to translate. Consequently, that rule is not affected by the copy/paste problem.

      1. The INDIRECT formula I used above was improperly translated from French to English. A proper (and shorter) CF rule for my example would be:
        =ISFORMULA(INDIRECT(“RC”;FALSE))
        That reminds me that R1C1-styled adresses are different across localized versions of Excel, so using them as text (with INDIRECT) can break them if we share a file worldwide. For my example, a safer CF rule (automatically localized by Excel) would be:
        =ISFORMULA(INDIRECT(ADDRESS(ROW();COLUMN())))
        To make that easier to use, we can go to the Name Manager and define the name “currentCell” as a proxy for “=INDIRECT(ADDRESS(ROW();COLUMN()))”.
        My example of CF rule then becomes:
        =ISFORMULA(currentCell)

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

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

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

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

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

  13. 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…

  14. 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?

  15. 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"

  16. 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!

  17. 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! 😀
    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

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

  18. 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!

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

  20. 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!

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

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

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

  24. 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?

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

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

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

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

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

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

    1. Paul,
      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.

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

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

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

  34. 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” 😉

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

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

  37. 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.
    btw,
    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).

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

  39. 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”.

  40. 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].

  41. 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:
    =IF(INDIRECT(ADDRESS(ROW(),COLUMN()+1))=INDIRECT(ADDRESS(ROW()+1,COLUMN()+1)),”Same”,”Line”)
    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.

  42. 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
    Application.Undo
    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.

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

    2. Very nice!

      If you change “Target.PasteSpecial Paste:=xlPasteValues” to “Target.PasteSpecial Paste:=xlPasteFormulas” then you can have the same result as Paste Values AND have the capacity to type formulas!

      Hope this helps.

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

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

  45. 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?

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

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

  47. 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:
    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/11570862-improve-merging-of-conditional-formatting
    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/8778211-improve-the-conditional-formatting-manager
    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/17706094-conditional-format-cut-and-paste
    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/19676413-make-paste-and-merge-conditional-formatting-the

  48. 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…

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

  50. I’m using MSO365 and I love conditional formatting (c/f) and use it for a huge variety of reasons and have many. So, once a week, while doing my backup routine, I make a copy of the sheet with all the c/f on it, go to the original and clear all formatting. Then I select one cell in the 1st column – eg A4 (I have 4 columns with c/f in it, most of the rules applying to more than one column), press ctrl-c, go to the original sheet, select the whole column A and click paste formatting (which I’ve put on my Quick Access toolbar). I obviously delete the duplicated sheet once I’m done.
    Sounds tedious but it goes quick and has proven not to drive me insane.
    I have asked MS to work on their c/f feature for years now. When working in the C/F Rules Manager, one cannot make the space to work in any bigger and are confined to that small claustrophobic window. Also one can’t hi-light your “applies to” rule info and paste over it. You have to hi-light, delete and then paste. These are just two of a few things they can hugely improve on.
    Still a brilliant feature which I don’t think I could work without anymore, so I continue to work around it’s shortcomings 😀
    Thanks for your tips and tutorials.

  51. I agree with what has been posted previously; copy and paste values (or paste formulas) will totally work to not split up the conditional formatting.

    I’m a little lazy for that. And I don’t want those who use my spreadsheets to have to do that. I’m also not confident that they will remember to do copy and paste values. 🙁

    I don’t know if this solution will work for your needs, but I resorted to deleting all conditional formatting and reapplying the correct conditional formatting every time the workbook is opened.

    Because this macro runs every time the workbook is opened, the user does not need to change the way they copy and paste. They don’t need to know that the macro is even there. They don’t need to manually run the macro; it is automatic. I feel this creates a better user experience.

    Please keep in mind that this code needs to be copied and pasted into the “This Workbook” module; not a regular module.

    Private Sub Workbook_Open()
    ‘This will delete all conditional formatting and reapply the conditional formatting properly.
    ‘After copying and pasting the conditional formatting get split into two or more conditional formattings. After a few
    ‘weeks there are so many conditional formattings that Excel crashes and has to recover.

    Dim ws As Worksheet, starting_ws As Worksheet

    Set starting_ws = ActiveSheet ‘remember which worksheet is active in the beginning
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name “InvErr” Then
    ws.Activate
    Cells.FormatConditions.Delete
    ”Every Other Row Tan
    Range(“A4:M203″).FormatConditions.Add Type:=xlExpression, Formula1:=”=ISODD(ROW(A4))”
    Range(“A4:M203”).FormatConditions(Range(“A4:M203”).FormatConditions.Count).SetFirstPriority
    Range(“A4:M203”).FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    Range(“A4:M203”).FormatConditions(1).Interior.ThemeColor = xlThemeColorDark2
    Range(“A4:M203”).FormatConditions(1).Interior.TintAndShade = 0
    Range(“A4:M203″).FormatConditions(1).StopIfTrue = False

    ”Highlight Duplicates Red
    Columns(“B”).FormatConditions.AddUniqueValues
    Columns(“B”).FormatConditions(Columns(“B”).FormatConditions.Count).SetFirstPriority
    Columns(“B”).FormatConditions(1).DupeUnique = xlDuplicate
    Columns(“B”).FormatConditions(1).Font.Color = -16383844
    Columns(“B”).FormatConditions(1).Font.TintAndShade = 0
    Columns(“B”).FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    Columns(“B”).FormatConditions(1).Interior.Color = 13551615
    Columns(“B”).FormatConditions(1).Interior.TintAndShade = 0
    Columns(“B”).FormatConditions(1).StopIfTrue = False
    End If
    Next

    starting_ws.Activate ‘activate the worksheet that was originally active
    Application.ScreenUpdating = True

    End Sub

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.