Sort Multiple Rows Horizontally in Excel & Google Sheets
This tutorial demonstrates how to sort multiple rows horizontally in Excel and Google Sheets.
Sort Row Horizontally
Excel’s sort feature is used to sort data in columns, but you can also use it to sort rows horizontally. Say you have the following data set.
Typically, Excel tables are set up with field names in each column and rows of data underneath. In this example, each row has a field name and columns of data to the right. Row 2 has names, Row 3 has month-to-date sales for each person in Row 2, and Row 4 has year-to-date sales. Let’s first sort data horizontally by MTD Sales from largest to smallest (Row 3).
- Click anywhere in the data range (A3:G3), and in the Ribbon, go to Home > Sort & Filter > Custom Sort…
- In the Sort window, click Options…
- In the Sort Options pop-up, choose Sort left to right – i.e., horizontally – and click OK.
- In the Sort window, sort by Row 3 on Cell Values, in Largest to Smallest order. Click OK.
As a result, the data set is sorted by Row 3 (MTD Sales), descending.
There a few duplicates in MTD Sales that become apparent after the sort – for John and Celine ($1,280) and for Jennifer and Sarah ($750). If you need additional sort criteria, read on.
Sort Multiple Rows
To sort more specifically than just MTD Sales, add one more level of sorting – YTD Sales (Row 4). In this case, sort first by MTD Sales and then by YTD Sales, from largest to smallest.
- Select the sort range (B3:G4), and in the Ribbon, go to Home > Sort & Filter > Custom Sort.
- In the Sort window, click Add Level to add Row 4 to the sort conditions.
- In the second level, choose Row 4 for Then by and Largest to Smallest for Order. Click OK.
As a result, the data range is sorted first by MTD Sales, and then by YTD Sales.
Sort Rows Horizontally in Google Sheets
Google Sheets doesn’t have the same option as Excel to sort left to right, but it is possible to achieve the same using a combination of the SORT and TRANSPOSE Functions. The idea is to transpose data from horizontal to vertical, then sort data, and transpose it back to horizontal. Enter the formula in cell A6:
As a result, the data range is transposed below, with MTD Sales sorted descending. Let’s look deeper into the formula:
First, it transposes the range A2:G4 to vertical, to be able to sort it. Then, it sorts that range descending (FALSE) by the second column (2 – MTD Sales). Finally, it transposes the sorted range back to horizontal.
You can also sort more than one row horizontally. The only difference is that you need to include YTD Sales in the SORT Function. In this case, the formula in cell A6 is:
The only difference compared to the single row sort is another two parameters in the SORT Function. Column 3 (YTD Sales) is added as a second descending sort criteria.