[SOLVED] Fix Duplicate Conditional Formatting Rules in Excel 2016

You have conditional formatting rules in Excel 2016 and all of a sudden they are duplicated all over the place and super messy. This fixes it.

me@jaykilleen.com wrote this almost 4 years ago and it was last updated almost 4 years ago.


← Back to the Posts

I've been doing conditional formatting in Excel for ages. Back before the 2016 days. It always annoyed me that rules would become duplicated. For example, I'd set rules on a range of cells like A1:D4 but if I inserted a column then my rules would get duplicated and split into two rules around the location where I entered the new row.

This is annoying and I have finally decided to fix it. Wasn't very easy to find so below is the link to what I found as well as a copy of the steps to fix this. In case the site ever goes down and I need to refer back to those steps.

https://support.microsoft.com/en-us/help/3114374/december-8-2015-update-for-excel-2016-kb3114374#reg

  1. Start Registry Editor:
    In Windows 10, go to Start, enter regedit in the Search Windows box, and then select regedit.exe in the search results.
  2. Locate and then select the following registry entry:
    HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options
  3. On the Edit menu, point to New, and then select DWORD Value.
  4. Enter ReplaceCFOnPaste, and then press the Enter key.
  5. In the Details pane, press and hold (or right-click) ReplaceCFOnPaste, and then select Modify.
  6. In the Value data box, enter 1, and then select OK.
  7. Exit Registry Editor.