How to Sort Data Horizontally and Vertically with SORTBY Function in Spreadsheets
Question details
The user is inquiring whether the SORTBY function can be used to organize data horizontally from left to right, in addition to standard vertical sorting.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Sorting a dataset where the primary sorting criteria is laid out in a row rather than a column, requiring a horizontal reordering of the columns.
- Observed behavior
- The user needs clarification on how the orientation of the 'by_array' argument dictates the sorting direction to successfully execute a horizontal sort.
Ensure that the source array and your sorting criteria array (the 'by_array') have the exact same dimensions in the direction you are sorting to prevent a #VALUE! error.
Sort Data Horizontally Across Columns with SORTBY
By providing a horizontal range as your sort criteria, the SORTBY function will automatically recognize the array shape and sort your data from left to right.
The sorting direction of SORTBY is entirely dependent on the shape of the array you supply in the formula. If your sorting criteria is horizontal (e.g., a single row spanning multiple columns), the output will be sorted horizontally.
Determine the horizontal range of data you want to return (e.g., B1:E1) and the corresponding horizontal row containing the values you want to sort by (e.g., B2:E2).
Click on an empty cell where you want the leftmost value of your sorted horizontal array to begin.
Type `=SORTBY(B1:E1, B2:E2, 1)` into the formula bar. The '1' signifies an ascending sort order. You can change it to '-1' for descending order.
Press Enter. The results will dynamically spill horizontally across the adjacent columns in the specified sorted order.
Sort Data Vertically Down Rows with SORTBY
Use a vertical column as the sort criteria to organize your dataset vertically from top to bottom.
Easily Organize Data with Dynamic Arrays in WPS Office
WPS Spreadsheet fully supports powerful dynamic array formulas, including the SORTBY function. You can seamlessly sort complex datasets across columns or rows without relying on complicated macros or manual data shifting.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing the data you wish to sort.
- 2. Select a blank output area: Click on a blank cell ensuring there is sufficient room either below (for vertical sorts) or to the right (for horizontal sorts).
- 3. Write the SORTBY formula: Type `=SORTBY(` and highlight your source data array. Type a comma, then highlight your sorting array (row or column), followed by your sort order.
- 4. Generate the results: Press Enter to instantly populate the dynamic array, displaying your correctly sorted data.
Frequently Asked Questions
What causes a #VALUE! error when using the SORTBY function?
A #VALUE! error typically occurs if the array you want to return and the array you are sorting by (the 'by_array') do not match in size. For a horizontal sort, ensure both the return array and the sort array span the exact same number of columns.
Can I sort horizontally by multiple rows using SORTBY?
Yes, you can add additional horizontal arrays to act as secondary sorting levels. For example, `=SORTBY(B1:E1, B2:E2, 1, B3:E3, -1)` will sort horizontally first by row 2 in ascending order, and then resolve ties by sorting row 3 in descending order.
What is the difference between the SORT and SORTBY functions?
The SORT function sorts data based on an index number corresponding to a column or row within the selected source array itself. The SORTBY function, however, allows you to sort an array based on a completely separate range or array that does not necessarily need to be part of the final output.
Will my SORTBY results update automatically if the original data changes?
Yes. Because SORTBY is a dynamic array function, any modifications made to the values in your source range or your sorting criteria array will immediately reflect in the spilled output cells without needing to refresh.
