SOLVED

# What is the most efficient way to implement conditional formatting?

Brass Contributor

# What is the most efficient way to implement conditional formatting?

Hi,

I currently manage conditional formatting of cells that require a reasonably complex formula by placing a formula in a cell, then have the conditional formatting formula point to that cell.

My question is:

Am I sacrificing efficiency and speed by defining additional formulas in the sheet rather than configuring the formula in the conditional formatting dialogue?

For example, I have this formula in cell AG190.

``=AND(OR(AG\$172:AG\$189),OR(Var1<10,Var1>18))``

or more precisely, this formula is filled across cells AG190:AO190

Elsewhere, I have a range of cells with conditional formatting that simply refer to these cells i.e.

``=AG\$190``

so using this example, my question is

would my sheet be more (or less) efficient/faster/slower if I placed the formula in the conditional formatting rule? i.e. change the =AG\$190 to =AND(OR(AG\$172:AG\$189),OR(Var1<10,Var1>18)) and just forget about using the interim calculation cells?

TIA

RedNectar

4 Replies
best response confirmed by RedNectar (Brass Contributor)
Solution

# Re: What is the most efficient way to implement conditional formatting?

When implementing conditional formatting in Excel, both methods you described—using an intermediate cell to hold the formula and directly placing the formula in the conditional formatting rule—have their pros and cons. The most efficient method in terms of speed and performance can depend on several factors:

1. Directly Using Formulas in Conditional Formatting:

• Pros:
• Cleaner Sheets: Keeps your worksheet cleaner by reducing the number of helper cells.
• Reduces Memory Usage: If your sheet has a large number of formulas, placing them directly in conditional formatting can reduce the number of formulas calculated in the sheet, potentially improving memory usage.
• Efficiency: Excel evaluates conditional formatting rules in memory, and doing so directly in the rule may sometimes be faster, especially if the same rule applies to a large range.
• Cons:
• Complexity: Complex formulas in conditional formatting rules can be harder to debug or maintain, especially if multiple rules are in place.
• Calculation Overhead: If the conditional formatting formula is complex and applied to a large range, it could potentially slow down the sheet more than if the formula were calculated once in a helper cell.

2. Using Helper Cells for Conditional Formatting:

• Pros:
• Easier Maintenance: By breaking down complex logic into helper cells, it becomes easier to manage and understand. If the logic needs to be changed, you only need to update the helper cell formula.
• Performance: For very complex formulas, calculating them once in a cell and then referencing that cell in the conditional formatting can reduce the overall calculation time. This is because Excel recalculates conditional formatting formulas frequently, especially during actions like scrolling or filtering.
• Flexibility: Allows for easier debugging and testing of the formula without affecting the conditional formatting directly.
• Cons:
• Additional Cells: Increases the number of cells and formulas, which might clutter the worksheet and slightly increase the file size.
• Potential for Errors: If the reference cell changes or is deleted, the conditional formatting rule may break or reference incorrect data.

Performance Considerations:

• Small Datasets: For small datasets or worksheets, the difference in performance between these two approaches is usually negligible. In such cases, direct formulas in conditional formatting might be preferable for simplicity.
• Large Datasets: For large datasets or complex sheets, using helper cells might offer better performance because Excel will calculate the helper formula once per cell and then simply reference the result, rather than recalculating the conditional formatting formula multiple times.

Recommendation:

For complex and large datasets, using helper cells is generally more efficient and easier to maintain. It reduces the number of calculations that Excel needs to perform repeatedly. For simpler or smaller datasets, placing the formula directly in the conditional formatting rule is more straightforward and keeps the worksheet cleaner.

• If the formula is not too complex and doesn’t apply to a very large range, you could place it directly in the conditional formatting rule.
• If the formula is complex and/or the range is large, using the helper cells as you are doing might be the better approach in terms of performance.

If performance becomes a noticeable issue, you might want to experiment by applying the formula directly to the conditional formatting and comparing the responsiveness of the workbook with both methods. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

# Re: What is the most efficient way to implement conditional formatting?

Grateful thanks to NikolinoDE - your answer is comprehensive and informative. I really appreciate the effort you've put into this answer. It is similar to what Gemini told me, but so much better to read a human response than an AI generated one 😉 Thanks again.

# Re: What is the most efficient way to implement conditional formatting?

Haven't you noticed this part in Nicos answer?

"The text and steps were edited with the help of AI."

# Re: What is the most efficient way to implement conditional formatting?

1 best response

Accepted Solutions
best response confirmed by RedNectar (Brass Contributor)
Solution

# Re: What is the most efficient way to implement conditional formatting?

When implementing conditional formatting in Excel, both methods you described—using an intermediate cell to hold the formula and directly placing the formula in the conditional formatting rule—have their pros and cons. The most efficient method in terms of speed and performance can depend on several factors:

1. Directly Using Formulas in Conditional Formatting:

• Pros:
• Cleaner Sheets: Keeps your worksheet cleaner by reducing the number of helper cells.
• Reduces Memory Usage: If your sheet has a large number of formulas, placing them directly in conditional formatting can reduce the number of formulas calculated in the sheet, potentially improving memory usage.
• Efficiency: Excel evaluates conditional formatting rules in memory, and doing so directly in the rule may sometimes be faster, especially if the same rule applies to a large range.
• Cons:
• Complexity: Complex formulas in conditional formatting rules can be harder to debug or maintain, especially if multiple rules are in place.
• Calculation Overhead: If the conditional formatting formula is complex and applied to a large range, it could potentially slow down the sheet more than if the formula were calculated once in a helper cell.

2. Using Helper Cells for Conditional Formatting:

• Pros:
• Easier Maintenance: By breaking down complex logic into helper cells, it becomes easier to manage and understand. If the logic needs to be changed, you only need to update the helper cell formula.
• Performance: For very complex formulas, calculating them once in a cell and then referencing that cell in the conditional formatting can reduce the overall calculation time. This is because Excel recalculates conditional formatting formulas frequently, especially during actions like scrolling or filtering.
• Flexibility: Allows for easier debugging and testing of the formula without affecting the conditional formatting directly.
• Cons:
• Additional Cells: Increases the number of cells and formulas, which might clutter the worksheet and slightly increase the file size.
• Potential for Errors: If the reference cell changes or is deleted, the conditional formatting rule may break or reference incorrect data.

Performance Considerations:

• Small Datasets: For small datasets or worksheets, the difference in performance between these two approaches is usually negligible. In such cases, direct formulas in conditional formatting might be preferable for simplicity.
• Large Datasets: For large datasets or complex sheets, using helper cells might offer better performance because Excel will calculate the helper formula once per cell and then simply reference the result, rather than recalculating the conditional formatting formula multiple times.

Recommendation:

For complex and large datasets, using helper cells is generally more efficient and easier to maintain. It reduces the number of calculations that Excel needs to perform repeatedly. For simpler or smaller datasets, placing the formula directly in the conditional formatting rule is more straightforward and keeps the worksheet cleaner.

• If the formula is not too complex and doesn’t apply to a very large range, you could place it directly in the conditional formatting rule.
• If the formula is complex and/or the range is large, using the helper cells as you are doing might be the better approach in terms of performance.

If performance becomes a noticeable issue, you might want to experiment by applying the formula directly to the conditional formatting and comparing the responsiveness of the workbook with both methods. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!