Highlight cells with same absolute value

Question

Hello

I would like to highlight cells in the same column if they have the same absolute value.

For example, if I have -10 and 10, or -2 and 2, I would like all these 4 cells to be highlighted (no need for colour codes, they can all be highlighted with the same colour).

Any idea on how to do this?

Answer 1

Let's say your data are in A2:A14.

Select this range. A2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=COUNTIF($A$2:$A$14,-A2)

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

Answer 2

If you specifically want to match positive to corresponding negative, you can use a formula in conditional formatting for that. Say your data is in column A starting at row 1, the formula could be:

=COUNTIF(A:A,-A1)>0

If you want to match any combination of matching absolute values (eg 1 and 1, 1 and -1 and -1 and -1) then you could use:

=(COUNTIF(A:A,-A1)+COUNTIF(A:A,A1))>1