How to Fix Excel Conditional Formatting Duplicated Rules

About this tutorial:

Video duration: 3:10
Visit this page to download the sample file for this video. The example is on the DupRules sheet in the sample workbook.

In Excel, you can use the conditional formatting to highlight one or more cells, based on rules.

In this example, the conditional formatting rules are duplicated automatically, when rows are inserted or deleted in the table.

This can cause a huge mess of rules in the workbook, with many duplicates. Your workbook could slow down in some cases.

See how to remove the duplicates quickly, and get back to just the…

Post Author:

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

19 thoughts on “How to Fix Excel Conditional Formatting Duplicated Rules

    Rez Resendez

    (December 30, 2019 - 4:14 am)

    You are the best! Thank you 🙂

    ilike people1

    (December 30, 2019 - 4:14 am)

    omg thank you

    stelu12stelu constantin

    (December 30, 2019 - 4:14 am)

    How to use conditional formatting while the file is shared ?

    Vital Data

    (December 30, 2019 - 4:14 am)

    It helps me, thx 🙂

    Ruben MV

    (December 30, 2019 - 4:14 am)

    Thank you for the workaround, but this is ridiculous, still present on Excel 2016. Google spreadsheets works fine with rules.

    Joseph Johnson

    (December 30, 2019 - 4:14 am)

    This is a very smart solution to a real annoying problem. It makes sense too! Conditional Formatting is…after all…formatting. So it makes sense to use the format painter to fix it easily. In my case, the problem was happening whenever I refreshed the sharepoint data that I was feeding into the spreadsheet. But the solution given above works perfectly every time. Thanks.

    Joeri Trauschke

    (December 30, 2019 - 4:14 am)

    This isn't a fix to the problem, just a workaround….

    Does anyone know how to prevent Excel from duplicating and messing up the existing rules??
    From google it seems this problem has excised for many years and versions…

    Even when you put absolute values like "=$A:$A" (as is often mentioned) it still gets messed up when you copy or insert cells in this column and most users wont notice this problem.

    Is there a way to format for duplicates that doesn't get messed up this way?
    or a permanent fix that you don't have to do every time?

    PS: Copy/pasting 'values only' doesn't mess up the conditional formatting.

    Sascha Ziemann

    (December 30, 2019 - 4:14 am)

    Ever watched what happens, if more than one formatting condition is true for the same cell? In your case, if the cell value is greater than 500 and the dates differ, too. The formatting directives are not merged.

    Gabriel Jacob

    (December 30, 2019 - 4:14 am)

    I think it's really helpful but I don't know how to add blue line and fix above 500. please help me from starting.


    (December 30, 2019 - 4:14 am)


    Jayden Lawson

    (December 30, 2019 - 4:14 am)

    Thanks!! This has been annoying me for maybe a year!

    Doug H

    (December 30, 2019 - 4:14 am)

    Great tip…thanks!


    (December 30, 2019 - 4:14 am)

    Sensacional, muito obrigado.


    (December 30, 2019 - 4:14 am)

    Great Trick : ) Thanks!

    Kevin Lehrbass

    (December 30, 2019 - 4:14 am)

    wow Thanks Deb! I didn't know this!


    (December 30, 2019 - 4:14 am)

    Smart! 🙂

    eCabinets tips and tricks

    (December 30, 2019 - 4:14 am)

    Thanks! That was very helpfull

    bill butler

    (December 30, 2019 - 4:14 am)

    Thanks, had the same problem and it took me a while to realise what was happening, good clear example


    (December 30, 2019 - 4:14 am)

    Thank U! 🙂

Leave a Reply

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