Excel: Sorting Left to Right

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: My IT department produces a report every day, and the columns are in the wrong sequence. It would take them two minutes to rewrite the query, but they have a six-month backlog and don't have time to get around to it. How can I rearrange the columns?

  1. The columns are not in a logical sequence.

    Strategy: You can sort the columns left-to-right. The quick way is to add a new row with column sequence numbers. If you really have to rearrange these every day, however, it would make sense to add a custom list with the proper sequence of the columns.

    Follow these steps for the quick method:

    1. Insert a new row above the headings.
    2. In the new row, enter the numbers 1 through n to specify the desired sequence for the columns. If you want company name first, number that column 1, and so on.
    3. Select the range of data to be sorted. Use Ctrl+* to select the current range. If you don't explicitly select the whole range, the Sort command tends to remove the numbered row 1 from the sort.

  2. Steps 2, 3, 6, and 7 are all shown here.
    1. Select Data, Sort.
    2. Click the Options button at the top of the Sort dialog.
    3. Choose Sort Left to Right under Orientation. Click OK to close the Sort Options dialog.
    4. In the Sort By dropdown, choose Row 1.
    5. Click OK to rearrange the columns.
    6. Because the column widths do not sort with the data, select Home, Format dropdown, AutoFit Column Width to fix all column widths. The columns are rearranged.
    7. You can now delete the temporary row 1.

    Alternate Strategy: If you defined a custom list of Company, Attn:, Address, Suite, City, State, Zip, you could skip the first two steps above. When defining the sort, you would specify Company, Attn:, Address as the sequence. For information on defining a custom sort sequence, see .