A table usually consists of several columns and the order of the columns is arranged based on logical conditions in order to facilitate analysis. However, there are times when we need to reorder or reorder columns for better analysis purposes. In other words, we need to shift or move certain columns so that we can display the table in a column order that is more suitable for analysis purposes.
For example, we may need to pan and drag a number of columns to view data side by side. Of course we can hide the unnecessary neighboring columns for a moment, but this method is not always the best. Because we may still need to see the data from the point of view of the hidden columns.
So, on this occasion we will discuss various alternative ways to move or shift the column. It may seem trivial, but rest assured .. this will really help work effectiveness if you can practice it properly.
Broadly speaking, this discussion includes
Shift the column using drag & drop.
Move columns by cutting and pasting
Move columns by copying, pasting and deleting
Re-order columns using macros
How to Shift Columns Using Drag & Drop Techniques
Drag & drop techniques to move ranges may be familiar to most Excel users. But the common way is usually the range that is moved will overwrite and delete the data in the range where it is dropped.
The method that will be explained here is how to drag & drop to shift the column to another location without deleting/overwriting data in other columns.
Suppose we want to move the Category column to the beginning of the table:
Select column D, then while pressing the SHIFT key, move the pointer on the side of the selected column until the cursor changes to an arrow pointing 4 cardinal directions.
Click the left mouse button. Then while still holding down the SHIFT key, drag the column to the desired location. Release the mouse click on the appropriate location, for example to the first column as shown below:
So the result is that the Category column is shifted to the first column in the table as shown below:
Note: It is important to note that this Drag & Drop process must be done while holding down the SHIFT key. If not, then the sliding column will overwrite / delete the other column.
This technique can also be used to shift adjacent columns at once. Unfortunately, this method cannot be used to directly shift multiple non-adjacent columns, or several selected columns by clicking the column header while pressing the CTR key.
In addition to shifting columns, this technique can actually be used to shift rows as well. Please try it yourself.
Move the column by Cut & Paste
If the drag & drop method has problems, for example mouse problems, then you can use the cut & paste method. This technique will also depend on the need to move a single column or several columns.
Move Single Column
Select the column by clicking on the column header.
Press CTRL + X to run command Cut on the selected column. It can also be done by right clicking, then clicking Cut .
Select the column where you want to move the Cut column to that location, then right click and click Insert Cut Cells .
Then the final result moves the column according to the desired order.
If you feel more comfortable using shortcuts and keyboards, you may like the following methods more.
Select one or more cells horizontally according to the number of adjacent columns that you want to move, then press CTRL + SPACE to select the full column.
Press CTRL + X to cut or Cut the selected column
Select another column location as the location to move the column, then press CTRL + SPACE to select the full column. You can also select only the top cell.
Then press CTRL + SHIFT together by pressing the plus button (+). If you are using a keyboard that has numeric keys, you can press CTRL and the plus sign (+) without pressing the SHIFT key .
How to Move Multiple Columns in Excel
The cut / paste technique can only work on one column or several adjacent columns. If you try this technique for multiple columns that are not directly adjacent, this method will end up in an error.
To rerorder multiple columns we can do one of the following or a combination of the following:
Drag & Drop columns one by one as explained in the beginning of this article. This method seems the fastest.
Cut and Paste each column. This method may not be the best choice if you are working with lots of sliding columns. But maybe it will be the best choice if the position of the column to be shifted is far away from its new position so that it is not visible on one computer screen.
Copy, Paste and Delete : this method is similar to cut and paste, only a little longer the process because the initial column is not deleted, so you have to delete the column again. The method is described below.
Shifting Columns by Copy, Paste and Delete.
If under certain conditions the dragging technique using the mouse does not work, then you can try to arrange columns using this method.
Select the column you want to move by clicking on the column header, or by selecting one of the cells in the column you want to move, then pressing CTRL + SPACE.
Copy the selected column by right clicking then clicking copy. Or you can also press the short cut button CTRL + C.
Then right-click on the destination column, and click Insert Copied Cells .
Then a duplicate will appear from the column that was copied earlier.
Then we can delete (delete) the initial column that is not needed.
It should be noted: The results of the copy technique and insert copied cells may give unexpected results, for example, the data in the copied column may not match the data in the initial column, or even errors. This happens usually when the column being copied contains a formula.
Changing Column Order Using Macro / VBA
If there are lots of similar tables but located in many different sheets or workbooks, then we need to change the column arrangement of all the tables. What is the most effective way ?.
Editing one by one table by drag & drop, cut / paste, or copy/paste/delete seems to only take your days.
In this case, the Macro takes its role. No need to bother even though you don’t understand the vba language. Because here we only need to do a macro record for the process of shifting columns in the first table. As for the next table, just run the macro.
Enter one of the sheets containing the table to arrange the column arrangement
Go to the Developer tab, then click the Record Macro button.
Then the K brain Record Macro Dialog will appear
In the Macro Name field, type the name of the macro you want, for example Column Arrangement
Determine the shortcut key, for example, CTRL + q, I chose the letter ” q ” in consideration of the ease of accessing it using the finger of the left hand.
Store Macro In, determine the location of the code macro storage file, I select Personal Macro Workbook.
Click the OK button.
The flowchart for starting a recorded macro is illustrated as follows:
Next, do the steps of shifting the column with the technique previously described. You can use one or a combination of the drag & drop, cut & paste, or copy, paste & delete techniques.
If the column settings are complete, then click the Stop Recording button.
You now have a Column Arrangement macro ready to use to arrange the column arrangement of other similar tables.
The way to use it is simple, just enter each sheet that contains a similar table, then press CTRL + q (or any other shortcut according to the settings you made), then the Column Arrange () macro will work for you.
Want it even faster?
If you don’t want to open the sheets one by one to run the Column Arrange () macro, then with a little VBA code, you can do it all at once for all the sheets in one file.
The following VBA code will work with the assumption that all sheets in the workbook contain the same table column arrangement and will be converted into a specific arrangement.
Sub Set Column At Once ()
Dim WS As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If not all sheets contain the same table, and you only want to edit a portion of the sheet that contains the table, then you must name the sheets in a structured manner.
For example, all sheets containing tables must be named sheet with table prefixes such as table1, table2, table3, table4, and so on.
And an example of VBA code that can be used for this case is as follows:
Sub Set Column At Once ()
Dim WS As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If Left (ws.Name, 5) = “table” Then
In order for it to be used, the code must be typed or copied in the same module as the Column Set () macro code location that we created using the previous macro record.
Click the Visual Basic button on the Developer ta, or press the short cut key ALT + F11. This step will take us to the Visual Basic Editor window
In the project explorer section, look for the module where the code is placed. In this case the module is in the PERSONAL.XLSB workbook, module1 .
Double click the module1 to enter the code window.
In the code window, we will see the Column Arrange code () which is generated automatically according to the steps we recorded earlier. (In the example shown the code is short and simple, in actual fact it may be longer depending on how many drag & drop columns or copy / cut paste processes are recorded.
Type or copy the code macro Set Simultaneous Column () in the module.
Look at the following image for more details.
After the code Set Simultaneously () is copied or typed in the VBA module, the next step is to create a shortcut so that the code can be more easily accessed and executed quickly.
Exit the VBA window and return to the spreadsheet.
From the Developer tab, click Macros, then the Macro dialog box will appear.
On the Macro Name List, select PERSONAL.XLSB! Set Column Simultaneously , this name corresponds to the name of the file where the macro code is stored, and the name of the macro itself.
Click the Options button
In the Macro Options window, specify the desired shortcut key. Examples CTRL + SHIFT + C . Again, I made this shortcut considering the ease of access by the finger of the left hand. The shift sign will appear automatically if we type in capital letters in the shortcut key field.
Click the OK button
Next is Close from the macro dialog box.
Look again at the illustration of the steps for creating a macro shortcut:
After the steps above have been carried out without exception, then we can run the ColumnSimultaneous Arrange () macro to change the column arrangement of all tables in a file.
The method is very easy. Just open the file which consists of the sheet containing the table, where the sheet names have been arranged in a structured manner. In this example table1, table2, table3 etc.,
Then press the shortcut key CTRL + SHIFT + C or another shortcut according to the settings you like. Then the Next Column Arrange () macro will complete your task quickly.
This is a discussion of some techniques for moving and arranging columns in tables in Microsoft Excel. May be useful.