How to easily rank data by absolute value in Excel?
Ranking data by absolute value in Excel is a common need for statistical analysis, financial modeling, and data comparison tasks. While Excel has built-in ranking functions, they don't directly support absolute value ranking. This comprehensive guide will walk you through several optimized methods to accomplish this task efficiently.
Rank data by absolute values with formulas
When you need to rank numbers based on their absolute values (ignoring whether they're positive or negative), the following formulas may do you a favor.
✅Formula 1: By using SUMPRODUCT function (All Excel versions)
This method uses the SUMPRODUCT function, which works across all versions of Excel.
Please enter the following formula into a blank cell where you want the result to appear, then drag it down to fill the remaining cells and display all ranks based on absolute values in descending order, as shown in the screenshot below.
=SUMPRODUCT(--(ABS(A2)<ABS($A$2:$A$13)))+1
- ABS(A2): Returns the absolute value of the current cell A2.
- ABS($A$2:$A$13): Returns the absolute values of all numbers in the range A2:A13.
- ABS(A2)<ABS($A$2:$A$13): Compares whether the absolute value of the current cell is less than each value in the range, returning a series of TRUE/FALSE results.
- --(...): Converts the TRUE/FALSE values into 1s and 0s (TRUE = 1, FALSE = 0).
- SUMPRODUCT(...): Adds up all the 1s to count how many values have an absolute value greater than the current one.
- +1: Starts the rank from 1, meaning the highest absolute value gets rank 1.
- To rank values in ascending order based on their absolute values, you can use the following formula:
=SUMPRODUCT(--(ABS(A2)>ABS($A$2:$A$13)))+1 - In older versions of Excel, you need to press Ctrl + Shift + Enter to enter the formulas as array formulas. In Excel 365 or Excel 2021, simply pressing Enter is sufficient.
✅Formula 2: By using XMATCH and SORT functions (Excel 365 and Excel 2021+)
If you're using Excel 365 or Excel 2021, you can take advantage of powerful dynamic array functions like SORT and XMATCH to rank numbers by their absolute values.
Please enter the following formula into a blank cell where you want the result to appear, then drag it down to fill the remaining cells and display all ranks based on absolute values in descending order, as shown in the screenshot below.
=XMATCH(ABS(A2), SORT(ABS($A$2:$A$13),,-1))
- ABS(A2): Returns the absolute value of the current cell A2.
- ABS($A$2:$A$13): Converts all values in the range A2:A13 to their absolute values.
- SORT(...,, -1):Sorts these absolute values in descending order (the -1 indicates descending).
- XMATCH(...): Finds the position of the current value’s absolute value within the sorted list—this position represents its rank.
To rank values in ascending order based on their absolute values, you can use the following formula:
=XMATCH(ABS(A2), SORT(ABS($A$2:$A$13),,1))Sort data by absolute values with Kutools for Excel
Sorting data by absolute values in Excel isn’t directly supported through standard sorting tools. While formulas can accomplish the task, they require extra columns and are prone to errors. With Kutools for Excel, you can easily sort your data based on absolute values in just a few clicks—no formulas, no hassle.
After installing Kutools for Excel, please do as this:
- Select the column that contains the values you want to sort. Then, click Kutools Plus > Sort > Advanced Sort in the Excel ribbon.
< - In the Advanced Sort dialog box, configure the sort settings:
- Choose the column header you want to sort from the Column section;
- From the Sort On dropdown menu, select Absolute value;
- Under the Order section, choose Smallest to Largest. You can also select Largest to Smallest if you prefer descending order.
- Once everything is set, click the OK button.
<
Now, the values in the list are sorted based on their absolute values, while keeping their original signs intact. This allows you to easily identify the smallest or largest values by magnitude, regardless of whether they are positive or negative.
- My data has headers: Make sure this box is checked if your data includes a header row;
- Case sensitive: Only applies to text; can be left unchecked for numeric sorting.
- Blank cells in front: Check this option if you want blank cells to appear at the top of the sorted list.
🔚 Conclusion
Ranking or sorting data by absolute values in Excel can provide deeper insights, especially when analyzing the magnitude of values regardless of their sign. This guide introduced multiple approaches to meet different user needs:
- Use the SUMPRODUCT function for a reliable ranking formula that works in all versions of Excel.
- Take advantage of XMATCH and SORT in Excel 365 and 2021+ for dynamic, modern formulas.
- For the fastest and most user-friendly solution, Kutools for Excel offers a one-click method to sort data by absolute values without writing any formulas.
Choose the method that best fits your Excel version and workflow—whether you prefer formula-based control or the efficiency of automated tools like Kutools. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!