Aug 09 2024 03:35 PM
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
Aug 10 2024 12:32 AM
SolutionWhen 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:
2. Using Helper Cells for Conditional Formatting:
Performance Considerations:
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.
Given your scenario:
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!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Aug 10 2024 03:01 AM
Aug 11 2024 03:23 AM
Haven't you noticed this part in Nicos answer?
"The text and steps were edited with the help of AI."
Aug 11 2024 01:28 PM
Aug 10 2024 12:32 AM
SolutionWhen 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:
2. Using Helper Cells for Conditional Formatting:
Performance Considerations:
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.
Given your scenario:
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!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.