I am looking for a conditional formatting rule that will highlight cells in a given row that contain a number only, but NOT a formula and NOT blank cells.

Example of the sheet:

Number of cases assigned to Agent Case Agent ID Case Number
4 1234 2345678
1234 2236789
1234 2312759
1234 2367842
3 5632 2198423
5632 1948863
5632 8574337

One cell in Row 1 SHOULD contain a formula like such: "=COUNTIF(B:B,B2)" which then prints the number of cases assigned to the particular case agent ID. However occasionally a new user of the sheet will just type "4" instead of ensuring the formula is entered in the first adjacent cell. However, we don't need all cells in row 1 to list the number of cases assigned to case agent, we just want one cell to contain that number.

Is there a way to use conditional formatting to change the background color of the cells in row 1 where the cell contains a plain number and leaves cells alone that are blank OR that contain a formula that prints a number? The hope is that this color change will prompt users to make sure the formula is present in row 1 instead of just typing the number of cases they just entered into the spreadsheet?

  • Welcome to Web Applications Stack Exchange. It sounds like you are confusing columns with rows. In the table you show, row 1 contains headings. Column A contains the numbers 4 and 3, together with five blank values. Jul 13 at 21:44

1 Answer 1


To format cells in A2:A that contain a number but do not contain a formula, ignoring blank cells, use this conditional formatting custom formula rule:

=isnumber(A2) * not(isformula(A2)) * not(isblank(A2))

To prevent entering a plain number in the column in the first place, use this custom formula rule with Data > Data validation for the range A2:A:

=isnumber(A2) * isformula(A2)

Click Advanced options and choose Reject the input to finish the rule.

The rule will prevent entering numbers or text in those cells, and will only accept formulas that produce a number.


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.