Hey everyone! I’m setting up conditional formatting in Google Sheets and need some advice. I want cells to appear blue if they hold a plain value, and black if they contain a formula. I’ve browsed through the available is functions but couldn’t locate something like isformula.
Does anyone know if there is a method to determine if a cell’s content is a formula? I’d appreciate any suggestions or workarounds that could help me tackle this issue.
Here’s a fresh example of what I’m aiming for:
function evaluateCell(input) {
if (checkValue(input)) {
return 'blue';
} else if (checkFormula(input)) {
return 'black';
}
return 'default';
}
Note that checkValue and checkFormula are just placeholders. I’m curious if Google Sheets offers built-in functions to make this distinction. Thanks for any insights!
I’ve actually encountered a similar challenge in my work, and I found a solution that might help you out. While Google Sheets doesn’t have a built-in isFormula function, you can use the ISTEXT function combined with a custom formula to achieve what you’re looking for.
Here’s the approach I used:
=ISTEXT(FORMULATEXT(A1))
This formula returns TRUE if the cell contains a formula, and FALSE if it’s a plain value. You can use this in your conditional formatting rules.
For your specific case, you could set up two conditional formatting rules:
Format cells if… Custom formula is =NOT(ISTEXT(FORMULATEXT(A1))) - set to blue
Format cells if… Custom formula is =ISTEXT(FORMULATEXT(A1)) - set to black
This method has worked reliably for me across various projects. Just remember to adjust the cell reference (A1 in this example) to match your specific range or use relative references as needed.
There’s actually a straightforward way to accomplish this in Google Sheets using the FORMULATEXT function. Here’s how you can implement it:
=IF(FORMULATEXT(A1)=“”, “blue”, “black”)
This formula checks if FORMULATEXT returns an empty string (which it does for cells without formulas) and outputs “blue” for plain values and “black” for formulas. You can use this in a custom conditional formatting rule to achieve the desired color coding.
To set it up, go to Format > Conditional formatting, select ‘Custom formula is’ and enter the formula, adjusting the cell reference as needed. Then set the text color accordingly for each condition. This method is efficient and doesn’t require any complex scripting.
