# highlighting cell with value closest to zero

For example, I have this data 1.5, 1.2, -0.98, 1.01, and I want the cell with the value closest to zero to be highlighted. I tried to create the corresponding rule (see picture) but EXCEL does not color any cell. In my example, the cell with -0.98, since this is the value closest to zero, should be colored.

My first attempt

• What about formula like as `=ABS(P74)=1/MAX(ABS(IFERROR(1/\$P\$74:\$P\$79;0)))`? Commented Jul 19, 2022 at 11:21
• No, this does not work either.
– Zorg
Commented Jul 19, 2022 at 11:24
• Once more - not "cell value" but "formula" (last position in list of options) Commented Jul 19, 2022 at 11:35
• Sorry I retried it and it worked! Maybe write an answer so I can accept it
– Zorg
Commented Jul 19, 2022 at 11:36
• I don't have Excel with an English interface right now, so I won't be able to take the necessary screenshots. Feel free to post your answer and I'll upvote it. Commented Jul 19, 2022 at 11:41

## 3 Answers

here is another formula, it's easier to read, also it'll highlight also 0 (which won't be highlighted by currently posted answer).

`=ABS(C2)=MIN(ABS(\$C\$2:\$C\$11))`

• ,, nice one +10 ☺ Commented Jul 20, 2022 at 9:41

This is the solution created by @JohnSUN, who asked me to provide screenshots of an English EXCEL version. The trick is to use his formula and 'Use a formula to determine which cells to format' as rule type, see screenshot.

• ,, nice one +10 ☺ Commented Jul 20, 2022 at 9:41

This also fixes the issue:

N.B.

• I've given little twist & used cell references in the formula for Conditional Formatting, which makes it dynamic.
• Formula in use: `=ABS(D31-\$E\$31)=MIN(ABS(\$D\$31:\$D\$37-\$E\$31))`
• Adjust cell references in the formula as needed, as well the Criteria in cell `E31`.