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
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.