Sort Data Horizontally in Excel (Left to Right)

Excel sorts top to bottom by default, but you can sort horizontally too, so whole columns swap places based on the values in one row. There are two ways: the Sort dialog's Sort left to right option, and the SORT function with its by_col argument. This guide shows both on one small table, plus SORTBY and the problems that trip people up. It is for anyone whose data runs across the sheet, such as months or regions in columns.

When do you need to sort horizontally?#

You need a horizontal sort when each item is a column instead of a row. This layout is common in summary reports, where regions, months or products run across the top.

Here is a regional summary, with each region in its own column:

A normal sort would reorder rows 2 and 3, which isn't what you want. To rank the regions by sales, you need to move whole columns, keeping each region's sales and units together. That is a left-to-right sort.

If your data could just as well run down the page, it is often easier to flip it first. See how TRANSPOSE turns rows into columns.

How to sort left to right with the Sort dialog#

The Sort dialog can reorder columns in any version of Excel. Follow these steps on the table above:

1. Select B1:E3. Leave out column A, because it holds the labels.
2. Go to Data > Sort.
3. Click Options…, choose Sort left to right and click OK.
4. In Sort by, choose Row 2 (the Sales row).
5. In Order, choose Largest to Smallest and click OK.

The result:

Each region kept its own sales and units. The steps are the same on a Mac: Data > Sort, then Options and Sort left to right.

Step 1 matters most. In a left-to-right sort, Excel has no "My data has headers" option for the label column, so if you select A1:E3, the words Region, Sales and Units are sorted along with the data. For more on the Sort dialog, see how to sort data in Excel.

How to sort horizontally with the SORT function#

The SORT function can sort columns too, and its result updates when the data changes. Set its fourth argument, by_col, to TRUE:

=SORT(array, [sort_index], [sort_order], [by_col])

For the regional table, type this in an empty cell such as B5:

=SORT(B1:E3,2,-1,TRUE)

• B1:E3 is the data to sort, without the labels.
• 2 is the row inside that range to sort by. Row 2 of B1:E3 is the Sales row.
• -1 sorts largest first. Use 1 for smallest first.
• TRUE sorts columns. If you leave it out, SORT sorts rows instead.

The result spills into B5:E7, starting with East in B5, then North, South and West. Type the labels Region, Sales and Units in A5:A7 next to it. SORT needs Excel 2021 or Microsoft 365. The SORT function guide lists every argument.

To sort the regions alphabetically by name instead, sort by row 1 in ascending order: =SORT(B1:E3,1,1,TRUE) returns East, North, South, West.

How to sort columns with SORTBY#

SORTBY sorts by a separate range, and when that range is a row, it sorts columns automatically. No by_col argument is needed:

=SORTBY(B1:E3,B2:E2,-1)

This gives the same result as the SORT formula: East, North, South, West. SORTBY is easier to read when the sort row sits outside the data, or when you want to sort by two rows. The guide to SORT and SORTBY compares the two in detail.

How to sort horizontally in a custom order#

Use a custom list when the order isn't alphabetical or numeric, such as North, South, East, West. In the Sort dialog, after choosing Sort left to right, open the Order drop-down and choose Custom List…. Type your order, one item per line, and click Add.

The custom sort guide explains custom lists, which work the same way for left-to-right sorts.

Why is my horizontal sort not working?#

Most problems come from the selection, not the sort itself. Check these:

1. The label column was included. Undo with Ctrl+Z (Mac: ⌘Z), select only the data columns and sort again.
2. Sort left to right wasn't chosen. Without it, Excel sorts rows. Open Options in the Sort dialog to check.
3. Hidden columns. Excel doesn't move hidden columns in a left-to-right sort. Unhide them first.
4. Merged cells. Excel can't sort a range with merged cells of different sizes. Unmerge them.
5. Formulas that point outside the range. Relative references can break after sorting. Check totals afterwards.
6. SORT without TRUE. =SORT(B1:E3,2,-1) sorts rows and gives a different result.

For more fixes, see why Excel sort is not working.

Sort or filter: which do you need?#

Sorting changes the order of the data. Filtering hides what you don't need. Excel's AutoFilter works on rows only, so for data laid out across columns, sorting with SORT or the Sort dialog is often the simpler tool. If you can turn the layout around so each item is a row, you get both: sort and filter in one table. The lesson on filtering a table shows how.

Practise it#

The SORT function is the quickest way to get a sorted copy, across or down. Practise SORT in a live spreadsheet: you type the formula and it checks it instantly. Then try the exercise on this page and sort the regions by sales with by_col set to TRUE.