Buzz
The feature of converting data from vertical to horizontal columns in Excel is highly sought after because it is one of the most common and utilized operations in most Excel spreadsheet work.
1. Converting data from vertical to horizontal columns using the Copy-Paste command
The Copy-Paste command is widely used, but few people know that it allows them to quickly convert data from vertical to horizontal columns in Excel, saving them time.
Step 1: Highlight the entire content you wish to convert from vertical to horizontal columns and press Ctrl + C to copy all the selected content.
Step 2: Next, choose an empty cell to begin converting the data into horizontal rows and Right-click to select Paste Special....
Step 3: When the Paste Special window appears, check the Transpose option and press OK to perform the data paste operation.
Step 4: The system will automatically convert all the data you copied into horizontal rows and paste them into the empty cell you selected. To enhance the appearance of the content, adjust the cell size and format the content inside.
2. Convert data from vertical to horizontal using the Transpose command
The Transpose command functions similarly to other commands in Excel and it is designed to convert data into horizontal rows.
Step 1: Choose a range of empty cells horizontally (not fewer than the number of rows to be converted from vertical).
If you are unsure about the number of rows in the vertical column, randomly select a number of horizontal cells, but the more you select, the better; selecting extra cells is also acceptable.
Step 2: Input the command “=transpose” and the coordinates of the column you want to convert data into horizontal rows.
Example: In this instance, Mytour will transpose data from Column A, starting from row 2 and ending at row 10. The command would be as follows:
| =transpose(A2:A10) |
Once the command is completed, press Ctrl + Shift + Enter to proceed with converting data from vertical to horizontal.
Step 3: The system will automatically convert the data for you based on the coordinates of the column and row you input in the command.
Step 4: For the remaining columns, you simply need to repeat the same steps to convert the remaining data.
Step 5: After converting the vertical columns into horizontal rows, format them for better visibility.
3. Convert data from vertical to horizontal by replacing data
The Excel data replacement tool can also help you accurately convert data from vertical to horizontal, but it will take more time.
Step 1: At the cell where you want to convert data into horizontal rows, enter the content as follows:
| Tên thay thế + Tọa độ dòng đầu tiên của cột muốn chuyển |
Example: In this section, Mytour will set the replacement name as “ttpm”, convert data from Column A starting from row 2, so the coordinate will be A2. The content to be entered into the cell is “ttpmA2”. Do the same for Column B and coordinate B2.
Step 2: Then, select all the newly created replacement data cells and drag the bottom right corner to fill the data into adjacent cells.
Step 3: By this step, you have completed replacing the data on the Excel file.
Step 4: Press Ctrl + H to open the Find and Replace window.
Step 5: In the Find what field, enter the Replacement Name you created in Step 1 to search for cells with that content. For the Replace with field, simply enter “=” to automatically fill in the content from the created cell.
To initiate the conversion, select Replace All.
Step 6: The system will automatically convert data from vertical to horizontal columns for you. Once completed, a notification window will appear saying “All done. We made... replacements”.
Step 7: Finally, format the cell and its content to make them display more beautifully.
The methods for converting data from vertical to horizontal columns in Excel shared in the above article can be applied in every scenario and they will help you save a lot of time. Hopefully, these insights will be beneficial for you when working with Excel files!
