Fools need order
genius rules over chaos.
(Albert Einstein)
Initial data
If you use conditional formatting rules to highlight cells or entire rows in your Excel spreadsheets, then you most likely have already encountered this problem. If not yet, then it is waiting for you in the very near future – I guarantee.
To make it easier to understand what, in fact, the matter is, let’s look at a simple example. Suppose we are working with such a simple table where sales are recorded:
For clarity, three conditional formatting rules have been added to the table:
The first rule makes blue histograms on the column with transaction amounts. Created via Home — Conditional Formatting — Bar Charts (Home — Conditional formatting — Data bars).
The second – highlights in yellow the cells with the names of managers who did not fulfill the plan, i.e. the amount of their trade is less than the green cell H2.
Third – makes the bottom border of the entire line red if the day changes to the next, i.e. the date on the current line is not equal to the date on the next.
The second and third rules are created through Home — Conditional Formatting — Create Rule — Use Formula to Determine Cells to Format (Home — Conditional formatting — Create rule — Use formula to determine which cells to format) with entering the corresponding formula (2) and setting the cell format (3):
For now, I hope everything is clear and simple. The table stores data, and conditional formatting highlights rogue managers, separates dates, and visualizes cost.
The path to disaster
Suppose that in the process of working with the table, we needed to remove any row from the middle of the table – well, let’s say the 10th. After performing a harmless deletion, we get the following picture:
The red line between March 2nd and 3rd somehow disappeared, and our conditional formatting rule for separating dates fell apart into two, with one of them with a #REF error (i.e. does not work), and the other applies to two non-contiguous ranges A2:E8 and A10:E29 (not to the whole table!).
Gorgeous, right?!
Now let’s imagine that Kirill Krasnov repeated his deal in Tolyatti with the Lenta store (line 25) and you need to enter this data into the table.
What will you do?
Most likely, like any normal person, you will copy the 25th line and paste it at the end of the table, right?
Yeah, and you will inherit such a mess in the rules of conditional formatting:
For some reason, Excel duplicated the same rules for the added row instead of just stretching the range in the field It applies to (Applied to).
Well, for dessert, let’s try something else harmless – for example, insert an empty line in the middle of the table, between lines 4 and 5:
In the list of conditional formatting rules, this will lead to the appearance of another duplicate and fragmentation of the range of the already existing 5th rule into a bunch of fragments:
You can continue for a long time, but I think you have already caught the idea or remembered how you encountered this trouble before (this problem has existed in Excel since 2007). Performing completely harmless and natural operations with a table (inserting and deleting rows, copying, cutting and moving) leads to:
- the appearance of countless duplicates of the same rules
- fragmentation of the ranges of application of these rules
- appearance of broken rules with errors #LINK!
After working with the table for a couple of hours, you can end up in a situation where, out of two or three initial highlighting rules, you get tens or even hundreds of their clones with fragmented ranges.
On English-language Excel forums on the Internet, such a picture is sometimes called “hell” or “conditional formatting nightmare” (“Conditional Formatting Nightmare” or “Conditional Formatting Hell”).
Moreover, all this rapidly growing mess will very soon begin to mercilessly slow down. Conditional formatting, in itself, is a very resource-intensive thing, because. Excel recalculates UV rules much more frequently than the same formulas. And when there are several dozen of these rules, then even the most powerful PC will start to “stupid”.
Well, the icing on the cake will be the inability to resize the window Conditional Formatting Rules Managerto see all this chaos (in the screenshots above, I did it in a graphical editor). You will have to long and painfully scroll through the entire list in a small window with a scroll bar.
How to fix everything?
Method 1. Manually
Despite the apparent neglect, all this hell is treated quite easily. The idea is that the UV rules break, usually for lines below the first. The first line, in most cases, remains in order. Therefore, in order to fix everything, we just need to clear all the rules in the table and re-propagate them from the first row to all the rest.
To do this, do the following:
- We select all rows in our table except the first one.
- Remove all conditional formatting rules from selected cells via Home — Conditional Formatting — Delete Rules — Delete Rules from Selected Cells (Home — Conditional formatting — Clear rules — Clear rules from selected cells).
- Select the first line, press the brush button Sample format on The main (Home — Format Painter) and select all the other lines, copying the format from the first to them.
Method 2. Macro
If there is a feeling that you will have to do this procedure more than once, then it makes sense to automate the entire process using a macro. For this:
- Press keyboard shortcut Alt+F11 or on the tab developer button Visual Basic (Developer — Visual Basic).
- In the macro editor window that opens, add a new module to our book through the menu Insert – Module.
- We insert our macro into the created empty module:
Sub Fix_СF_Hell() 'create references to ranges Set rngAll = Selection Set rngRow1 = Selection.Rows(1) Set rngRow2 = Selection.Rows(2) Set rngRowLast = Selection.Rows(rngAll.Rows.Count) 'delete all formatting rules from all rows except the first one Range(rngRow2, rngRowLast).FormatConditions.Delete 'Copy the formats from the first row to all others rngRow1.Copy Range(rngRow1, rngRowLast).PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End Sub
Now it will be possible to simply select all the rows in the table (except for the header) and run the macro through Developer – Macros (Developer — Macros) or keyboard shortcut Alt+F8.
And everything will be fine 🙂
PS
And don’t forget to save the file in macro-enabled (xlsm) format.
If you need to use this macro in other files, then it makes sense to place it in the Personal Macro Workbook.
A slightly improved version of this macro is already built into the latest version of my PLEX add-on 😉
- How conditional formatting works in Excel
- Highlight rowsets with conditional formatting
- Dividing line between rowsets