MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sort Left to Right


July 31, 2017 - by Bill Jelen

Sort Left to Right

Excel can sort left to right

Every day, your IT department sends you a file with the columns in the wrong sequence. It would take them two minutes to change the query, but they have a six-month backlog, so you are stuck rearranging the columns every day.

Sample Data Set
Sample Data Set

You can reorder the columns with a left-to-right sort.

  1. Add a new row above the data. Type numbers to represent the correct sequence for the columns.

  2. Select Data, Sort. In the Sort dialog, click the Options... button and choose Sort Left to Right. Click OK.

    Sort Options
    Sort Options
  3. Specify Row 1 in the Sort By dropdown. Click OK.

    Sort by Row 1
    Sort by Row 1

The problem: The column widths do not travel with the columns.

But it is easy to select the data and Press Alt + O, C, A or select Home, Format, Column, AutoFit.

Bonus

Another Way to Move columns

Select one cell in the column you want to move. (You can also do this with multiple columns.)

Select One Cell in the Column You Want to Move
Select One Cell in the Column You Want to Move

Press Ctrl + Spacebar to select the whole column.

Ctrl + Space Bar to Select the Whole Column
Ctrl + Spacebar to Select the Whole Column

Shift + Drag the edge of the selection to a new location. The columns move and get inserted where you drop them. The remaining data shifts over.

Shift + Drag
Shift + Drag

Tip

If you need to move rows, press Shift + Spacebar to select the whole row. Follow with Shift + Drag.

Watch Video

  • Excel can sort left to right
  • Insert a row with the correct column sequence
  • Use the Sort dialog
  • Click Options, then Sort Left to Right
  • Also note this dialog offers case-sensitive sorting
  • Column widths do not correct, but Alt + O C A will fix
  • Bonus tip with Shift + Spacebar and Ctrl + Spacebar
  • How to remember which is which?
  • Ctrl starts with C, just like Column
  • Shift key is wider than Ctrl key, just as a row is wider than a column
  • Once you select the entire row or column, Shift + Drag it into location

Download File

Download the sample file here: Podcast1993.xlsx

Title Photo: Fruchthandel_Magazin / pixabay


Bill Jelen is the author / co-author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.