If not managed property Conditional formatting can lead to performance issues in you worksheet. One of the simplest solutions is to keep a watch on what rules are in there.
Check conditional formatting applied for cells (Alt+O+D). On the Conditional Formatting Rules Manager (Excel 2010), in Show formatting rules for select "This Worksheet". You should now be able to view all rules applicable for the worksheet.
- Identify duplicate rules (quite possible if you copy data from another worksheet having conditional formats). Duplicates rules are those which have same/similar condition but has same/different ranges in Applies To column.
- Delete the duplicates
- Define only one rule and select all cells (under Applies To column) for which the rule is applicable.
If you frequently copy and paste from other sheets to this sheet, Excel is pretty notorious. If you just do a Ctrl+C and Ctrl+V Excel will keep the conditional formatting of the newly copied cells. As a result, even if you follow above steps to define single rule, it will break the ranges to exclude the newly copied cells. Over a period of times there will be additional conditional formatting rules equal to the number of times you paste into the sheet. If the Stop if true column is unchecked in the original sheet then each rule will be executed for each cell in ranges mentioned Applies To column.
Solution is to use Paste Special (Alt+E+S) and select All merging conditional formats. This will not break existing ranges in the rule as above. But it still adds another rule. This is easier to manage as you can identify such rules easily for deletion.
No comments:
Post a Comment