MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2024: Sort Left to Right

April 22, 2024 - by

Excel 2024: 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.

9 columns of data are presented in a crazy order. Last Name is in A. Apartment is in B. Street is in C. Company in D.
9 columns of data are presented in a crazy order. Last Name is in A. Apartment is in B. Street is in C. Company in D.

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

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

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

Insert a temporary row 1 above the headings. Type the numbers 1 through 9 in the correct sequence. For example, First Name should be 1. Middle Initial is 2. Last Name is 3. Company is 4. Street is 5. Select all the data and open the Sort dialog. Click the Options button in the top right and there are three choices in Sort Options: Case Sensitive. Sort Top to Bottom. Sort Left to Right. Choose Sort Left to Right and sort by Row 1.
Insert a temporary row 1 above the headings. Type the numbers 1 through 9 in the correct sequence. For example, First Name should be 1. Middle Initial is 2. Last Name is 3. Company is 4. Street is 5. Select all the data and open the Sort dialog. Click the Options button in the top right and there are three choices in Sort Options: Case Sensitive. Sort Top to Bottom. Sort Left to Right. Choose Sort Left to Right and sort by Row 1.

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

In this screenshot, you've selected Sort by Row 1, Sort on Values, Smallest to Largest.
In this screenshot, you've selected Sort by Row 1, Sort on Values, Smallest to Largest.

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

The data is now presented left to right in a logical order. First Name, Middle Initial, Last Name, and so on. At this point, you can delete the temporary row 1.
The data is now presented left to right in a logical order. First Name, Middle Initial, Last Name, and so on. At this point, you can delete the temporary row 1.

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

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Pablo García Saldaña on Unsplash