I recently had an error because a column of dollar amounts had a field that was not a number, but a string. It was correctly formatted to look like a currency amount, but it was treated as zero by the sum function.

I expect the app should have some way to let me easily identify this. Is there?


I run into this for other reasons but one thing you can do is use conditional formatting to mark when types or formats are not right, so they stand out.

This is done by:

  1. Select the column in question.
  2. Right-click and select Conditional Format
  3. Enter the range as a bottomless range. If you are looking at Column C and it has a header on it, you would enter C2:C.
  4. Format cells if... Select CUSTOM FORMULA IS...
  5. In the entry field put: =NOT(ISTEXT($C:$C))
  6. Then set your formatting to what will get your attention.

I will try to put a picture below:

Showing Conditional formatting for marking wrong types and formats.

  • To be clear... this helps me mark entries that are NOT TEXT. I have to do this because I am using ID numbers, and they often have leading zeros. If Sheets changes the entry to a number, the zeros disappear and do not match other entries in other Sheets. So this helps me to keep an eye on formatting and types. – Jim Garner Oct 21 '16 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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