Most valuable professional
  • Excel Book Excerpt

Excel Move Columns by Sorting Left to Right

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

Move Columns by Sorting Left to Right

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?


Figure 1176 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.

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.


Figure 1177 Steps 2, 3, 6, and 7 are all shown here.

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 “Sort a Report into a Custom Sequence” on page 289.


<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: