identifying decimals under .06 in excel spreadsheet

Occasional Contributor

Hello,

I need to highlight all numbers in a spreadsheet that have a decimal less than .06

 

for example I want to highlight  .06, 1.02, 12.03, but not 2.11 , 3.08 etc..

8 Replies
You can use Conditional formatting. Highlight entire area and then Home -> Conditional Formatting - New Rule - Custom Formula (on the formula line type): =MOD(A1,1)<=0.06 This assumes that the area includes cell A1. If not use the top left most cell in the selected area.
everything was highlighted
wait, it did work.
can I please ask one more thing. I need greater than .00 and less than .06. How do I edit the formula?
=(MOD(A1,1)<=0.06)*(MOD(A1,1)>0)
Thanks this worked real well. How do I now identity just those cells that I have highlighted?
put some sort integer in next column next to cell? or true false statement?
conditional formatting is purely a visual (formatting) tool.
If you want a value next to each cell (and not manually applied) you can use the same formula in a cell offset from A1 and then copy it into a range = to the size/shape of the range you want to check.
With Excel 365 (or 2021 I believe) you could also use dynamic array to output the range. For example
=(MOD(A1:D200,1)<=0.06)*(MOD(A1:D200,1)>0)
will 'SPILL" a table with 4 columns and 200 rows that correspond to the results from the range A1:D200
Thank you so much! Have a great weekend