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

  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 1and 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.
    SortHeadings

Sort the Columns

  1. 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.
  2. On the Ribbon’s Home tab, click Sort & Filter, then click Custom Sort.
    CustomSortCmd
  3. In the Sort dialog box, click Options
    SortOptions
  4. In the Sort Options dialog box, click Sort Left to Right, then click OK
    SortOptionsLR
  5. From the Sort By drop down list, click on Row 1, because that’s where the headings are located.
    SortRow01
  6. 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
  7. 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.

ColumnsReversed

_________________

You may also like...

9 Responses

  1. topsris says:

    Thanks Debra Dalgleish, I never sort data in excel file by row . I just see excel can sort by row with your tip.

    Thanks

  2. Ernie says:

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

  3. Dan says:

    Thank you for posting this… just used your method and it did the trick perfectly. Much appreciated!

  4. clayton boyd says:

    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

  5. Madala says:

    Thanks a lot.. it saved lot of my time. Great work.

  6. Dave says:

    Thanks you have made it so easy

  7. Anthony says:

    This is amazing. Had no idea you could sort by row.

    You’ve saved me tons of time both now and in the future!

  8. Erin says:

    SUPER helpful!! I had 67 columns that I need to flip the order of and this is a major time saver! THANK YOU!!

  9. Geoff says:

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *