Why is conditional formatting so fast?

[J.Ty.]

Hi,

I have made the following experiment:

1. I have made a full column (1M rows) of random numbers in the range [1;1000000] (no formulas, just numbers).
2. I have inserted the formula (R1C1 notation) =COUNTIF(C1,RC[-1])>1 in the first 1000 rows.
3. Recomputation of the formulas has taken about 30 seconds on my machine.
4. I have removed the formulas and inserted conditional formatting in the first 1000 rows, with the formula =COUNTIF(C1,RC)>1
5. Reformatting with the formulas has taken a fraction of a second on my machine (the performance meter in the task manager has not reached 100%, so the time has been shorter than its probing frequency).

The question arises: why is computing the same formulas for conditional formatting so much faster than computing the same formulas to produce values?

J.Ty.

P.S. The formulas are (copies of) =COUNTIF(A:A,A1)>1 and =COUNTIF(A:A,A1)>1 in A1 notation.

 

[mole999]

no idea, but a thought, if you then junk the CF and go back to the formula, does the time taken revert to the original

is it any quicker with both applied

like PEDMAS/BODMAS is there an order of execution within excel that puts CF ahead of formula in execution, does CF on its own execute a screen updating false etc...

 

[J.Ty.]

I have probably found an explanation: it seems that CF is executed only for cells which are currently visible on the screen. When I set zoom to 10%, every PageDown causes a considerable amount of computation to take place. The results seem to be cached, so returning to an area I have already seen does not cause recomputation.

J.Ty.