'%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 Use Excel SORT Formula for Sorting Rows and Columns
Question details
The user needs to sort horizontal rows and two-dimensional ranges using the Excel SORT function, as the standard formula syntax only sorts vertical columns correctly.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user entered horizontal data across multiple rows and attempted to sort each row numerically using standard SORT formulas, but the data remained unchanged.
- Observed behavior
- The SORT function sorts columns correctly but leaves horizontal row values in their original order because the formula is missing the specific parameter to sort by column.
Ensure you are using a version of Excel that supports dynamic array functions (such as Microsoft 365, Excel 2021, or Excel for the web), as the SORT, TOCOL, and WRAPROWS functions are unavailable in older standalone versions.
Sort Horizontal Rows Using the by_col Parameter
Adjust the SORT function's syntax to include the fourth argument, forcing Excel to sort across columns horizontally.
By default, the Excel SORT function sorts data vertically (by row). To sort data horizontally (across columns), you must explicitly set the [by_col] argument to TRUE.
Click on an empty cell where you want the sorted horizontal row to begin spilling its results.
Type the formula =SORT(A1:E1,,,TRUE), ensuring you replace A1:E1 with your actual horizontal data range.
Press Enter. The numbers or text will now sort horizontally in ascending order across the adjacent cells.
Sort and Reshape a Two-Dimensional Range
Combine SORT, TOCOL, and WRAPROWS functions to sort all values across a multi-row and multi-column dataset.
Correct Google Sheets Syntax in Excel
Fix open-ended reference errors caused by pasting Google Sheets formulas directly into Microsoft Excel.
Easily Sort Rows and Columns with WPS Office
WPS Spreadsheet offers powerful dynamic array functions to help you sort, filter, and reshape your data instantly. Experience seamless formula handling and an intuitive interface that makes organizing complex data effortless.
- 1. Download and install: Download WPS Office for free from the official website and install it on your device.
- 2. Open your workbook: Launch WPS Spreadsheet and open your existing data file.
- 3. Enter the formula: Click on an empty cell and enter the =SORT() or =WRAPROWS() formula tailored to your data range.
- 4. Apply dynamic arrays: Press Enter to see the dynamic array automatically spill your beautifully sorted rows and columns.
Frequently Asked Questions
Why does my SORT formula leave horizontal rows unchanged?
The SORT function defaults to sorting data vertically (by row). To sort data horizontally (by column), you must provide the fourth argument in the formula, [by_col], and set it to TRUE. For example: =SORT(A1:E1,,,TRUE).
Can I sort a row from largest to smallest using a formula?
Yes. You can dictate the sort order using the third argument, [sort_order]. Set this argument to -1 for descending order. A complete formula for a horizontal row would be =SORT(A1:E1, 1, -1, TRUE).
Why does =SORT(A3:FR) return an error in Excel?
Open-ended range references like A3:FR are standard in Google Sheets but are invalid syntax in Microsoft Excel. To fix the error, you must specify the exact ending row, such as A3:FR100.
What does the TOCOL function do in the sorting formula?
TOCOL takes a two-dimensional range or array and transforms it into a single vertical column. This allows the SORT function to easily sort all values in a grid numerically before they are re-wrapped into a table using WRAPROWS.
