Sorting Columns Instead of Rows in Excel
Usually when you’re sorting data in Excel, it’s by entries in a column. For example, you might have a list of customer names in a column, with the customer addresses and phone numbers in the adjacent columns.
You can sort the rows by the customer names in column A, and all the address information moves with the customer name.
Occasionally, you might need to sort by column, instead of by row. For example, I’m working with a data download that dumps the previous 24 months of sales, with the oldest data in the first column, and the latest data in the last column.
In my reports, I want to show the data in chronological order, so I need to reverse the order of the columns. I’ll use code to sort the columns, but here are the steps to do it manually.
Create Temporary Headings
- Insert a blank row above the data, to use for temporary headings
- In the blank row, type headings above the text column, as placeholders. For example, in the column above the client names, type Client, or simply type Column01.
- In the blank row, above the first two columns to be sorted, type 1and 2. These numbers will be used to create a series of numbers for the sort order.
- Select the cells that contain the the two numbers
- Point to the AutoFill handle at the bottom right of the selected range, and when the pointer changes to a small black cross, drag to the right.
- Drag to the last column that you want to include in the sort. In this example there are 24 columns, so I’ll drag across all 24 columns to create heading numbers.
Sort the Columns
- Select all the columns that you want to sort. In this example I’ll select columns B:Y, which contain the 24 months of data.
- On the Ribbon’s Home tab, click Sort & Filter, then click Custom Sort.
- In the Sort dialog box, click Options
- In the Sort Options dialog box, click Sort Left to Right, then click OK
- From the Sort By drop down list, click on Row 1, because that’s where the headings are located.
- In this example we want to reverse the column order, so in the Sort dialog box, click the drop down arrow for Order, and select Z to A.
- Click OK, to sort the columns.
The selected columns are now in reverse order, and you can delete the temporary row that contained the headings.