Friday, December 26, 2014

How to avoid duplicate conditional formatting rules with copy/insert

When working on an Excel sheet which has Conditional Formatting rules, creating additional rows/columns/cells automatically create duplicates entries of Conditional Formatting. 

Below is the screenshot of first instance of Conditional Formatting entries

After copying a blank row to extend the table, Excel automatically creates duplicate entries as shown below in Conditional Formatting Rules Manager window

In order to avoid getting duplicate rules created, the workaround is to Insert a new row/column/cell and then copy over only the formulas to the new row/column/cell - this will retain originally created Conditional Formatting rules and will not unecessarity duplicate those rules.

This is applicable for Excel 2007/2010.

No comments: