How to Sort Excel Values Horizontally in Each Row Without VBA
Question details
Sort values in columns horizontally from lowest to highest, independently for each row, without using VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing row-level data where each part number has varying numbers of assigned locations that need to be arranged in ascending order horizontally.
- Observed behavior
- The values are scattered unsorted across multiple columns in each row, and a formula or Power Query solution is needed to sort them left to right automatically.
Ensure you are using a modern spreadsheet version (like Microsoft 365, Excel 2021, or the latest WPS Office) to access dynamic array functions like SORT, LET, and FILTER.
Use Dynamic Array Formulas (SORT and LET)
This is the most efficient method for Microsoft 365 and modern Excel users, utilizing dynamic array formulas to sort each row independently while ignoring blank cells.
By combining the SORT, FILTER, and LET functions, you can create a single formula that evaluates the horizontal range of a specific row, removes any empty cells, and spills the sorted values into adjacent columns.
Click on the first cell in a blank column adjacent to your dataset (e.g., column J, assuming your data ends at column H).
Type the formula: =LET(data, B1:INDEX(H1:H1, COUNTA(B1:H1)), SORT(FILTER(data, data<>""),,,TRUE))
Press Enter. The sorted values will automatically spill horizontally into the neighboring columns for that row.
Click the bottom-right corner of the cell containing the formula and drag the fill handle down to apply it to all the remaining rows in your dataset.
Use Power Query to Unpivot and Sort
Ideal for complex datasets or users on older Excel versions who want a repeatable, non-VBA process for transforming and sorting row data.
Sort Rows Horizontally with WPS Spreadsheets
WPS Office provides built-in support for horizontal sorting. You can easily sort data left-to-right using the intuitive Custom Sort menu, or take advantage of modern dynamic array functions like SORT to handle large datasets effortlessly.
- 1. Open your file in WPS Office: Launch WPS Spreadsheets and open your target workbook.
- 2. Access the Sort menu: Highlight the row data you want to sort. Navigate to the Data tab and click 'Sort'.
- 3. Choose left-to-right sorting: Click the 'Options' button in the Sort dialog box, check 'Sort left to right', and click OK.
- 4. Use dynamic arrays for bulk tasks: For multiple rows, simply type =SORT(B1:H1, 1, 1, TRUE) in an empty adjacent column and drag it down.
Frequently Asked Questions
Can I sort data left to right using the standard Excel Sort menu?
Yes. Select your data, go to Data > Sort, click on 'Options', and select 'Sort left to right'. However, doing this manually requires you to sort one continuous block or row at a time, which can be tedious for hundreds of independent rows.
Why is the SORT formula returning a #NAME? error?
The #NAME? error occurs if your spreadsheet software does not support dynamic array functions. The SORT function is only available in Microsoft 365, Excel 2021, Excel for the Web, and modern alternatives like WPS Office.
Does Power Query permanently alter my original data?
No. Power Query establishes a connection to your source data, applies your sorting and unpivoting steps in the background, and loads the transformed result into a completely new table, leaving your original data untouched.
How do I ignore blank cells when sorting rows horizontally?
You can wrap your data range in the FILTER function to exclude blanks before passing it to the SORT function. For example: =SORT(FILTER(B1:H1, B1:H1<>""), 1, 1, TRUE).
