The layout of columns and rows in an Excel spreadsheet affects its readability and ease. Moving the columns in your Excel spreadsheet will help you enhance your spreadsheet.
Manually entering data from one column to another may be the best option if the column has few cells, but this procedure will be torturous in bigger columns. Fortunately, there are techniques for easily moving columns in Excel.
1. Move Columns With Drag and Drop
The simplest approach to move columns in Excel is to drag and drop them where you want them.
- Choose the column to be moved. This is accomplished by clicking the column header (for example, the letter B).
- Hold Shift and grasp the column’s right or left border.
- Place the column in its new location. As you move the column around, you’ll see that certain boundaries on the spreadsheet will be highlighted to show where the column will be positioned.
This method isn’t limited to columns; you can also use it to shift rows or groups of cells. All you have to do is pick them and then drag and drop them while holding Shift.
2. Move Columns With Cut and Paste
Another simple way is to clip and paste the column to a new location. To do this, just clip the column’s text and paste it into a new column.
- Choose the column you wish to move.
- On your keyboard, press Ctrl + X. You may also right-click on the columns you want to cut and choose Cut. The cut column will be highlighted with a dotted line.
- Select the target column.
- To paste the column’s content, use Ctrl + P on your keyboard. You may also utilize the Paste option by right-clicking on the column.
3. Move Columns With Data Sort
This approach is a little more complicated than the others, but it is quite useful if you need to transfer a huge spreadsheet with many columns. When you use Data Sort to move columns, you may move many columns at once. To use Data Sort to relocate columns, add a row to the top of your spreadsheet and declare the sorting order there. The columns may then be sorted using Data Sort.
You now have your columns in the desired location. There is still one little annoyance, and that is the first row. Let’s do away with it.
- Insert a cell from the first row by right-clicking it. This will add a row on top of the first.
- Insert the sorting order for your columns on top of each column in the new row. For example, to make the first column the fourth column in the new arrangement, enter 4 in the field above it. Choose the complete data table.
- Select Sort from the Sort & Filter option from the Data menu. The Sort dialog box will appear.
- Click Options in the Sort box. Check Sort left to right under Orientation.
- Return to the Sort window and choose Row 1 from the Sort by option. This will sort your table based on the first row, which contains the numbers.
- Under Sort on, choose Cell Values. The columns will be sorted depending on the values in the first row.
- Select OK.
- Order from smallest to largest. This manner, the first column will be at the top, followed by the others in order. Finally, when everything is in place, click OK.
- Select a cell in the first row by right-clicking it.
- Select Delete from the menu. A popup will appear asking you to confirm your deletion.
- Select Entire row and press OK.
Your data table is now arranged exactly as you intended it. If you need to sort dates in your data table, you should read our article on how to sort by date in Excel.
- Select Insert from the context menu when you right-click the first row. This will add a new row to the top of your table. Insert the number order you want to use to arrange the columns within the new row.
- Choose the cell where you wish the new table to be placed. Enter the following formula in the formula bar:
- =SORT(array, 1, 1, TRUE) (array, 1, 1, TRUE) Instead of an array, use your cell range (e.g., A1:E10). The sort index is set to 1, which implies that the sort index will be the first entry in the table. This is the row containing the orders that we just generated.
The SORT tool allows you to reorder your columns without losing the original layout.
4. Move Columns With the SORT Function
You may use the SORT function to create a rearranged replica of your data table while leaving the original intact. You can rearrange and relocate the columns in a data table in a single act, and then remove the old table if you’re satisfied with the new one.
SORT is similar to Data Sort in that it moves columns via a function, but the original table is not affected.
The SORT function syntax is as below:
=SORT(array, sort_index, sort_order, by_column)
You now have a sorted copy of your columns in the location you want. We have the same undesirable top row as in the prior way. This time, you can’t simply remove the row since it’s part of an array formula, and array formulas can’t be changed. However, you may still conceal the row.
- The sort order is set to 1, indicating that the data will be sorted ascending. The column labeled 1 comes before the column labeled 2, and so on. Finally, we set the by column argument to true since we want to sort the columns rather than the rows.
- Right-click on the row number and choose Properties.
- Select Hide Rows from the menu.
- In the formula bar, enter the formula below:
=SORT(array, 1, 1, TRUE)
- Insert your cell range (e.g. A1:E10) instead of an array. The sort_index is set to 1, which means the first row in the table will be used as the sort index. This is the row that we just created with the orders in it.
- The sort_order is set to 1, which means that the data order will be ascending. The column marked 1 will come before the column marked 2 and so forth.
- Finally, the by_column parameter is set to true because we want to sort the columns and not the rows.
- Press Enter.
Data organization is an important aspect of your spreadsheet. You may enhance the readability of a spreadsheet and make it simpler to identify particular data by appropriately organizing and aligning the columns.
- Right-click on the row number.
- From the menu, select Hide Rows.
Put Your Excel Columns Where You Want
We went through four different strategies for moving columns in Excel in this tutorial. With what you’ve just learned, you can move columns by dragging and dropping them, or you can move many columns at once by using Data Sort or the SORT function in Excel.
In this article, we walked through four methods on how you can move the columns in Excel. With what you just learned, you know how to simply move columns by dragging or dropping them or to move multiple columns in one go using Data Sort or the SORT function in Excel.
You are looking for information, articles, knowledge about the topic How to Rearrange Columns in Excel (4 Ways) on internet, you do not find the information you need! Here are the best content compiled and compiled by the appsladder.com team, along with other related topics such as: How To.
Related videos about How to Rearrange Columns in Excel (4 Ways)