Sorting Columns Instead of Rows in Excel

Sorting Columns Instead of Rows in Excel

Usually when you sort data in Excel, you sort the rows, to put them in a different order. For example, you could sort the rows in a Customer list by the customer names in column A. Occasionally, you might need to sort a list horizontally, to put the columns in a different order. Here are the steps for sorting columns instead of rows in Excel, and there’s a video too.

Dates in Different Columns

In this example, I’m working with a data download that dumps the previous 24 months of sales.

  • The download has no headings — just the data
  • The latest data (1 month ago) is in the first column
  • The oldest data (24 months ago) is 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.

You could use a macro to sort the columns, if you need to do this frequently, but here are the steps for  manually sorting columns instead of rows.

Create Temporary Headings

Here are the steps that I used, to sort the date columns in the Excel list. There aren’t any headings on the downloaded data, so I’ll add a heading row. Later, the new headings will be used for sorting columns into a different order.

SortHeadings

  1. Insert a blank row above the data, to use for temporary headings
  2. 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.
  3. In the blank row, above the first two columns to be sorted, type 1 and 2. These numbers will be used to create a series of numbers for the sort order.
  4. Select the cells that contain the the two numbers
  5. 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.
  6. 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

The next step is sorting columns, based on the new heading row.

  • 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.

CustomSortCmd

  • In the Sort dialog box, click Options

SortOptions

  • In the Sort Options dialog box, click Sort Left to Right, then click OK

SortOptionsLR

  • From the Sort By drop down list, click on Row 1, because that’s where the headings are located.

SortRow01

  • 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.

SortZToA

  • Click OK, to sort the columns.

After sorting, columns are now in chronological order, with the earliest sales data at the left, and the newest sales data at the right.

As a final step, you can delete the temporary row that contained the headings.

ColumnsReversed

Video: Sort Columns Instead of Rows

Watch this short video, to see a couple more examples of sorting a list to change the order of the columns, instead of rearranging the rows.

Learn More

Sort Data in Excel

Sort Multiple Columns in Excel

Sort Lottery Number Rows in Excel

Sort a Row in Excel Worksheet

Sort Data With Excel Custom Lists

Sort By Colour in Excel

_________________

14 thoughts on “Sorting Columns Instead of Rows in Excel”

  1. Thank you Debra! It’s really saving me alot of time. I never thought about the sorting rows option…

  2. I have a document which has 8 columns which I need to sort different ones several times to get the data I desire. One column is designated “CATEGORY”. When I sort columns other than this then look at the lines, the column number changes from what it is supposed to be, i.e. the “B” column category which does sort the columns numerically from 1 to 38. However, when I look at the column 6 (which is for auto expenses)and see that it shows “groceries” or some other entirely foreign item to “car expense”, I get worried!
    I am highlighting all columns except an accumulator column (which has a formula in it) from line 7 (form has headers which I do not want to sort) to line 865, the last line in the spread sheet. I have tried two methods of highlighting – one by highlighting line 7 (desired horizontal length desired), hold left button of mouse down and cursor on the up-down arrow in the positioning column on the right and dragging all the way to line 856, press shift key (which then highlights the entire area selected), then select DATA and AtoZ sorting, then column desired, then sort key in sorting selection block.
    The other method is to select line and highlight horizontally the length of line desired. Still holding down the left mouse key, drag the entire highlighted bloc to the last line and release. This highlights the area to be sorted, then proceed with the sorting operation. Both sorting ways have produced the undesired result of mixing information on a horizontal line.
    Sorry with the length of this note, but that’s what is happening. Any suggestions?
    Clayton Boyd

  3. This is amazing. Had no idea you could sort by row.
    You’ve saved me tons of time both now and in the future!

  4. Copy your row
    paste special on a new worksheet, but use the “transpose” option. Icon has rows to columns
    Sort the new worksheet as you would a single column
    Copy again
    paste back over original using the same transpose option

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.