Match positive and negative numbers in Excel

Spreadsheets are great at sorting and identifying numbers but what can you do to match positive and negative numbers in excel. If you sort the numbers the ‘matches’ are as far apart as could be possible.

YouTube Match positive and negative numbers

Matching numbers that have different signs

As shown below we have a list of numbers but they don’t have unique reference numbers. So we need to match them based on the actual amount, and to add to the complexity the ‘match’ will always be the opposite sign, so positive to negative matching.

Match positive and negative numbers in excel

Best Option- Absolute column to make numbers positive

We would add a new column that needs to show the amount in column C only as a positive number. To do this there is a function called ABS (absolute) which is very easy in that you just need to point at the cell i.e. =ABS(C2).

The benefit of this is that you have a column to work with that can genuinely be matched by sorting, but you still have the original data.

Match positive and negative numbers in excel

So if we now sort based on column D (largest to smallest), you suddenly have similar numbers close together. As shown below it looks like the first 2 rows are an easy match. But then we have 6 items that have the same number. Which belongs to which  will need to be investigated (Excel is good but not that good), but note that if you add all these up it comes to zero which is a good sign.

Match positive and negative numbers in excel

Out of interest note that we also have another column in column E called Matched. We use this to note which rows we are happy with (e.g. row 2 and 3 would say yes as it matches) so that eventually we are left with only the problems.

Use Data Filter to pick the amounts

If you switch on your Data Filter you can achieve something similar. Note below that when we click on the drop down arrow it shows the list of numbers in order (so not useful yet).

To find the matches, in the Search Box type a number you want to investigate. As shown below if we type 18 962 (the space is important) it only shows the items that CONTAIN that number and they are both ticked.

Match positive and negative numbers in excel

If you click OK now it will show only the items that contain that number as shown below. You can now decide whether they are matching or not. 

Note that this method is a lot more tedious and can be problemsome if you have decimals that perhaps don’t match exactly.

Want to learn more about Microsoft Excel and using it when budgeting or forecasting? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Excel negative numbers in brackets

Excel negative numbers

Comparing 3 cells in Excel

Identify positive and negative numbers in excel

Extract text from Excel cell AFTER an instance of a character