Move Columns by Sorting Left to Right

September 18, 2023 - by Bill Jelen

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?

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.

• 4. Select Data, Sort.

• 5. Click the Options button at the top of the Sort dialog.

• 6. Choose Sort Left to Right under Orientation. Click OK to close the Sort Options dialog.

• 7. In the Sort By dropdown, choose Row 1.

• 8. Click OK to rearrange the columns.

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

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

This article is an excerpt from Power Excel With MrExcel

Title photo by Nick Fewings on Unsplash