Excel custom formatting and the hazards of cut and paste

Was assembling a project tracking spreadsheet in Excel with plenty of custom formatting rules to categorize and tracks those things that need to be categorized or tracked. It is not something I had done in a while, since I am used to being in Mac shops, but here Numbers was not an option.

So I set up my rules for each column and then started inserting new rows and cutting and pasting things into them all over the place.

Went to go change one of my rules for a column and discovered it now had not the original six custom formatting rules, but something closer to sixty. So what happened?

Well, what happened is when setting up the initial rule for a column in Excel 2016 using the method Excel steers you into, it doesn’t set the rule for the column, it set the rule for all the cells in the column. So when I inserted a row, it split the rule into three parts, everything above the new row, everything below the new row, and the new row. So insert a dozen rows and your six rules becomes as much as 6 x 14 = 84 rules, in each column. And that many rules can really bog down Excel when opening a file, as well as making rules management nigh impossible.

They key is that bit about where it styles all the cells in the column and not the entire column, even though you selected the column header. So you think you set rules for $A:$A and you really set rules for $A$1:$A$1048576.

The fix is pretty simple, if an annoying extra step. Go into **Conditional Formatting > Manage Rules ** and clean up all that extra cruft. Remember, Excel requires a range, so if you are not spanning columns make sure the single column is listed as both. Then do some inserting and cutting and pasting and check again to make sure it worked the first time.

After that, insert, cut and paste without Excel helpfully adding rule after rule after rule.