0

I'd like a conditional formatting rule to mark cells with formulas one way, and cells with user-typed values instead of formulas another way. This way you can quickly see which cells are filled by formulas and which are not.

For example (this is a trivial, made-up example to make my point): a cell contains a 1 in it. I want it bold if that 1 comes from some formula, like =if(A1=today(), 1, 0), and I want it not bold if the user actually just typed a 1 into the cell. Make sense?

Again, I'm looking for a general-purpose way to identify formula-filled vs user-typed cells.

I am unable to figure it out. Choosing the "Custom formula is" allows you to enter a custom formula to run, rather than matching if a custom formula exists in that cell.

See also: Google's help: Use conditional formatting rules in Google Sheets

Improve this question

2 Answers 2

Reset to default
2

You have FORMULATEXT, that returns the formula as a string and an error if there's no formula:

=IFNA(FORMULATEXT(A1),"It's not a formula!")="It's not a formula!"

I suggest reading all "Blind Spots" comments! Then, I'm leaving here that I just found that there is a function called ISFORMULA, that will return true if it's a formula:

=ISFORMULA(A1)

Or, for user input:

=ISFORMULA(A1)<>TRUE
Improve this answer
2
  • 1
    Thank you for all your input! It's truly worthy!
    – Martín
    Jul 5 at 22:59
  • isformula() is just what I needed! Upvoted. Thank you. I have extended your answer with a screenshot and additional instructions in my answer here.
    – Gabriel Staples
    Jul 8 at 0:07
-1

How to apply conditional formatting in Google Sheets to only cells which contain a formula:

As @Martin says, =isformula() works! I upvoted that answer, but want to extend it by:

  1. showing a screenshot, and
  2. fully answering the question, which is: "how to use Conditional formatting to make cells with formulas bold, and cells with user-typed data normal"

Here, I selected the entire D column by clicking the column heading, then I went to Format --> Conditional formatting. I chose Custom formula is, and =isformula(D1) to apply my custom formatting to all cells in Column D which contain a formula. For the custom formatting, I chose no fill, and bold. Now, all cells in Column D which contain a formula are automatically made bold! But if I manually type in a value, they are not bold. Perfect!

enter image description here

Improve this answer
3
  • @BlindSpots, I did upvote the other answer. I also marked it as correct to give the person credit for their work. My answer is not a "thank you comment". It's a stand-alone answer with additional value and screenshots, and which fully answers the question, which is: "how to use Conditional formatting to make cells with formulas bold, and cells with user-typed data normal".
    – Gabriel Staples
    Jul 7 at 23:39
  • @BlindSpots, I think your downvote is out-of-place.
    – Gabriel Staples
    Jul 7 at 23:39
  • 1
    I'm not downvoting your answer but I won't upvote it unless it adds something more substantial.
    – Blindspots ♦
    Jul 7 at 23:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.