Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to easily rank data by absolute value in Excel?

Author Sun Last modified

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

rank data by absolute value by SUMPRODUCT function

🔍 Explanation of this formula:
  • 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.
Tips:
  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
  2. 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))

rank data by absolute value by XMATCH and SORT functions

🔍 Explanation of this formula:
  • 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.
Tips:

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

After installing Kutools for Excel, please do as this:

  1. Select the column that contains the values you want to sort. Then, click Kutools Plus > Sort > Advanced Sort in the Excel ribbon.
    click Advanced Sort of kutools<
  2. 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.
      configure sort settings in the dialog box

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.
the values in the list are sorted based on their absolute values

Additional Options:
  1. My data has headers: Make sure this box is checked if your data includes a header row;
  2. Case sensitive: Only applies to text; can be left unchecked for numeric sorting.
  3. 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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in