Differentiating between a Formula and Hard Entered (Static) Data - Excel

Occasional Visitor

I have the problem of trying to conditional format a cell based upon whether the contents of each cell are hard entered (static) data or if there is a formula in the cell. 

Example of this is when I am calculating dates and I want to actualise this information:


AItem IDLeaving DateDurationArrival Date
B Item A18-Oct-211401-Nov-21
C Item B01-Aug-212324-Aug-21


Cell contents:

AItem IDLeaving DateDurationArrival Date
B Item A18-Oct-2114=B2+B3
C Item B01-Aug-212324-Aug-21


I want cells B2, C2 and C4 to be highlighted by conditional formatting as they are hard entered, however, due to the fact B4 is a formula calculated by leaving date + expected duration then it shouldn't be highlighted until actualised.


Any further information required please advise, thanks.


Kind regards,



1 Reply


I don't understand what you want (and your tables have switched the row numbers and column letters), but you could create rules of type 'Use a formula to determine which cells to format'. In the formula for the rule, you can use the ISFORMULA function.

ISFORMULA(B2) will return TRUE if B2 contains a formula and FALSE if it doesn't.