'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Sort Numbers Using a Formula in Excel 2013
Question details
The user needs a formula-based method to sort a column of numbers in ascending order in Excel 2013, avoiding the manual Data Sort command despite the absence of the modern SORT function.
- Product
- Microsoft Excel 2013
- Device & OS
- not provided
- Scenario
- Automating the sorting of a numerical dataset into another column dynamically using formulas.
- Observed behavior
- Excel 2013 lacks the native dynamic array SORT function, requiring a complex legacy array formula combination to achieve the automated sort.
Verify if your dataset contains duplicate numbers, as standard array sorting formulas may require a helper column to break ties without returning errors.
Use an INDEX, MATCH, and COUNTIF Array Formula
This method utilizes a legacy array formula to dynamically rank and sort numbers without utilizing the manual Data Sort tool.
Because Excel 2013 does not support the modern SORT function, you must combine INDEX, MATCH, and COUNTIF. This calculates the rank of each number by counting how many values are smaller than or equal to it, then extracts them in ascending order.
Click on the first cell of your target column where you want the sorted list to begin (for example, cell C2).
Type the following formula exactly: =IFERROR(INDEX($A$2:$A$15,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$15,"<="&$A$2:$A$15),0)),""). Adjust the range $A$2:$A$15 to match your actual data.
Instead of pressing Enter, press Ctrl + Shift + Enter simultaneously. Excel will wrap the formula in curly brackets {} to indicate it is an array formula.
Click the small square at the bottom-right corner of the cell (the fill handle) and drag it down to apply the formula to the rest of the column.
Use a Helper Column to Preserve Duplicates
Use this approach if your list contains duplicate numbers, as the standard array formula without tie-breakers will fail to list duplicate values correctly.
Sort Data Instantly with WPS Spreadsheet
Upgrade your spreadsheet experience with WPS Office. Newer versions of WPS Spreadsheet fully support modern dynamic array functions like SORT, saving you from typing complex legacy INDEX/MATCH formulas to arrange your data.
- 1. Open Your Document: Launch WPS Spreadsheet and open the document containing the numbers you want to sort.
- 2. Select the Target Cell: Click on the blank cell where you want the sorted numbers to appear.
- 3. Enter the SORT Function: Simply type =SORT(A2:A15) (adjusting the range to match your data) and press Enter.
- 4. View Sorted Results: The sorted numbers will automatically spill down into the adjacent cells in perfectly ascending order without any helper columns or special keystrokes.
Frequently Asked Questions
Why does my formula return a #VALUE! or #N/A error?
This usually happens if you press only Enter instead of Ctrl+Shift+Enter. Legacy array formulas in Excel 2013 require you to confirm them with Ctrl+Shift+Enter so Excel knows how to evaluate the array correctly.
Can I sort data in descending order using this formula?
Yes. You can change the "<=" operator in the COUNTIF segment of the formula to ">=" to count values that are greater than or equal to the current value, which reverses the sorting order.
Will this array formula work for sorting text alphabetically?
Yes, the COUNTIF function evaluates alphabetical order as well. However, it is case-insensitive, so strings like 'apple' and 'Apple' will be treated as having the exact same rank.
