How to Rearrange Columns in Excel to Match Another Sheet (4 Ways)

Here, we have taken an Excel workbook that contains multiple worksheets that contain the same dataset with different arrangements. The dataset contains the Names of students and their Percentage Number of different subjects.

Method 1 – Using SHIFT and Drag to Rearrange Column to Match Another Sheet

We want to match the order of the columns from the sheet named “Drag & Drop Command” to the sheet named “Dataset.”

Steps:

Select the column you want to move. I have selected the column named
Hold the SHIFT key and then drag the left or right border of the column.
Drop the column in the position you want. I have dropped the column after the column named Name.

You can see that I have moved the Physics column successfully.

Follow the same steps to move the column Chemistry.

Move the column Biology to Column F to get your desired order as in the sheet “Dataset.”

Here, you will get the rearranged column matches to another sheet.

It should be noted that while you are using this method, there can not be any merged columns in the table. If there are any merged columns in the table, it will show an error and will not be able to perform the task successfully.

Method 2 – Using the Sort Command to Rearrange Column

We are matching the order of columns from sheet “Using Sort Command” to the order of the columns of sheet “Dataset2.”

Steps:

Insert a new row above the table.
Right-click on the first row and then select Insert from the Context Menu. That will insert a new row at the top.

Insert the new sorting order from your desired sheet. I have inserted the sorting order according to the order of the sheet “Dataset2.”

Select the entire data table. Here, we selected the cell range B4:F13.
Go to the Data menu and then select Sort from Sort and Filter.

You will see that the Sort Dialog box will open.
Click Options and mark on Sort left to right, and select Ok

Back in the Sort dialog box, select the row where you have inserted the sorting order in the Sort by menu. I have selected Row 4.
Select Smallest to Largest in the order field.
Select OK. That will sort your columns according to the order.

Select the Entire first row you inserted at the beginning then Delete.

You can see that the column order of my “Using Sort Command” sheet matches exactly with the “Dataset2” sheet.

Method 3 – Using the XLOOKUP Function to Rearrange Columns to Match Another Sheet

We want to match the column order of the sheet “XLOOKUP Function” with the column order of the sheet “Dataset3.”

Steps:

Insert the new column order in the sheet you want to rearrange the columns. I have inserted the column order from the “Dataset3” sheet into Row13 of the “XLOOKUP Function” sheet.

Select the cell from where you want to rearrange your data according to the column order. Here, I have selected cell B14.
Enter the following formula in cell B14:

=XLOOKUP($B$13:$F$13,$B$4:$F$4,XLOOKUP(B5,$B$5:$B$9,$B$5:$F$9))

Here, in the XLOOKUP function, I selected cell range B13:F13 as lookup_value and B4:F4 as lookup_array. Next, I used another XLOOKUP function as a return_array, where I selected cell B5 as lookup_value, B5:B9 as lookup_array, and B5:F9 as return_array.
I used absolute cell reference so that the selected range remain the same while using the AutoFill.

Now, the XLOOKUP function will give you the results for the B5 cell from the return_array.

Press ENTER and you will see that the data are rearranged according to the new order.

Use the Fill Handle tool to Auto-fill the formula for the rest of the cells.

Now you will see that all the data are sorted according to the new order.

We have our desired table according to the order of the sheet named “Dataset3”.

Method 4 – Using the MATCH Function to Rearrange Column Values

We want to rearrange the order of the Name column from the sheet “MATCH Function” to match the order of the name column of the sheet “Dataset”.

Steps:

Insert a new column in your sheet.

Enter the following formula in cell B5:

=MATCH(C5,Dataset!$B$5:$B$12,FALSE)

Here, in the MATCH function, I have selected C5 as lookup_value, selected cell range Dataset!$B$5:$B$12 as lookup_array from the Dataset sheet, and also selected FALSE as match_type. Now, this will return the order in which it matches the lookup_array.

Press ENTER, and you will get the new sort order for the row.

Use the Fill Handle tool to Auto-fill the formula for the rest of the cells.

You will get the Sorting order for the whole table.

You will have to sort the rows using the Sort command.

Select the range. Here, I selected the range B4:F12.
Go to the Data tab >> select Sort from Sort & Filter.

The Sort dialog box will appear.

In that Sort dialog box, click on Options and click on Sort top to bottom.
Select OK.

Back in the Sort dialog box, select the column in which you have inserted the sorting order in the Sort by menu. Here I have chosen the Helper Column.
Select Smallest to Largest in the Order menu.
Select OK. That will sort your rows according to the order.

You can delete the Helper Column and get your exact rearranged columns.

Here, you can see that all the columns in sheet “MATCH function” are rearranged according to the columns in sheet “Dataset”.

Read More: How to Rearrange Columns in Excel

Things to Remember

While using the Sort command, you must insert the column order carefully; otherwise, you will get the wrong column arrangement.
For the drag-and-drop method, there can not be merged cells. If you have any, it will give you an error.