Conditional Formatting - How to Properly Copy + Paste without Duplicating Rules

[Skiier89]

Hello,

I thought I had quite a good grasp of Excel's conditional formatting rules and behaviors, but I need some clarity + guidance from this terrific community!

I work with several large workbooks (15-20 KBs) that continue to grow in size and complexity. As a matter of preserving processing speed, I'm revisiting the conditional formatting rules applied on one specific worksheet. The worksheet is very large - roughly 3,000 rows by 150 columns - containing 105,000 cells with formulas.

The sheet is divided into sections which repeat themselves. This means that while the formulas are in similar locations within each section, the cells are not directly adjacent to one another; surrounding cells in each section are blank. When a formula is updated in one location, I've used the conventional method of dragging the updated formula down a column, along with appropriate blank cells in between so the formula is applied to the relevant cells.

What I've observed is that conditional formatting rules will duplicate themselves when this happens. This creates a lot of redundancy which I believe is slowing down the workbook and adding to its size. Below are my questions:

1) When copying and pasting from cell to cell, how do I preserve existing conditional formatting rules, without copying over rules applied at the source cell?

• Dragging updated formulas down a column is the default method because 1) paste special with formulas only would mean manually selecting hundreds or thousands of cells at a time 2) find and replace isn't feasible given each formula uses relative cell references on the sheet 3) other team members are not fluid with Excel and only know how to drag

2) What will lead to slower processing in the workbook: a) One conditional formatting rule applied to thousands of cells or b) Thousands of rules applied to each cell individually?

• Learning from this process I've discovered that there is a 8,191 character limitation in the "applies to" section of each rule. The cell references alone produce over 100,000 characters, so option (a) above still isn't very straightforward. Seems like it would create a headache.

3) What are some tricks and tips you've learned in managing conditional formatting rules?


Thank you!

 

[6StringJazzer]

Conditional formatting rules can get messy with copy/paste, insert, and delete.

how do I preserve existing conditional formatting rules, without copying over rules applied at the source cell?

You must either Paste Values or Paste Formulas. If you simply copy/paste, it includes all formatting, and conditional formatting is part of the package deal.

2) What will lead to slower processing in the workbook: a) One conditional formatting rule applied to thousands of cells or b) Thousands of rules applied to each cell individually?

I don't know; I have never done a performance test like that but my money is on a) being faster. I am taking an educated guess that one rule might be somehow "precompiled" and cells can be quickly evaluated against it, whereas with multiple rules they would have to be each evaluated from scratch. But honestly it's a guess. I do not know how Excel is implemented; I just know as a programmer how I would try to optimize it if I could.

3) What are some tricks and tips you've learned in managing conditional formatting rules?

Format an entire column whenever possible. This mitigates issues with inserting/deleting/copying/pasting rows. BU--and this is a big BUT--if the formula can be interpreted as an array formula, it will evaluate the entire column of over a million cells, whether they are used or not. This is a massive performance issue. So you need to understand how array formulas work. In older versions of Excel where you must hit CTRL+SHIFT+ENTER to enter an array formula in a cell, the same is not true of conditional formatting formulas.

So for example, this rule is pretty good. Applies To range is $A:$G and will format all cells in a row when the value in column A is "Complete"

=$A1="Complete"

I'll try to come up with an example of what to avoid.