Do you often encounter situations where a row doesn’t match the format you entered? Check out the method for convert columns to rows and vice versa in this article.
Nội dung
Summary of ways to convert columns to rows in Excel
There are many ways to convert columns to rows in Excel, but in this article, accountant Le Anh would like to send readers the most common and simple ways that anyone can do.
Method 1: Use Transpose in Paste Special to convert columns to rows in Excel
Step 1: On Excel, open the data file that you want to convert columns to rows on Excel.
Step 2: Next, highlight all the columns (or rows) you want to convert to rows (columns) => After highlighting, press Ctrl + C to copy (You can also right-click and select the copy function).
Next, move the mouse to the position where you want to convert the data and right-click on that cell => continue to select Paste Special …
After completing this step, the dialog box on Excel will appear Paste Special, check the box before Transpose
After completing, select OK to complete. Thus, you have fixed the error of columns to rows on Excel.
Note: Using Transpose in Paste Special is the simplest way to convert columns to rows on an Excel spreadsheet. Those who are not familiar with Excel can do it after reading the instructions, limiting the need to manually enter data if there is an error.
Method 2: Fix the error of converting columns to rows by entering the TRANSPOSE function directly on the spreadsheet
The most common way you use is to rotate and transpose cells by copying, then you paste and use the swap option. However, this will cause the data created to be duplicated. So if you do not want duplicate data, you should choose to use the TRANSPOSE function.
Step 1: You select the blank cells
Select some empty cells. Be careful to select the same number of cells as the original set of cells but in the opposite direction.
Then you select eight horizontal cells, as shown below. This is where the new cells will appear after you perform the swap.
Step 2: Enter =TRANSPOSE(
While still selecting those blank cells, enter: =TRANSPOSE(
Notice that eight cells remain selected even after we have started entering the formula.
Step 3: Select and range of source cells
Next, you will re-enter the cell ranges that you want to transpose, the formula that will be calculated will be =TRANSPOSE(A1:B4).
The next step is to press CTRL+SHIFT+ENTER You will fix the error of converting columns to rows in Excel
When learning how to fix the error of converting columns to rows in Excel, you will wonder why the common error is using the CTRL+SHIFT+ENTER keys instead of ENTER-like normal functions.
Since this TRANSPOSE function is only used with array formulas this is the way to go with an array formula (a formula that is applied to multiple cells). You were selected because you selected multiple cells in step 1, if you did that then you were using a formula that applies to multiple cells.
The article on how to convert columns to rows on Excel was made by the chief accountant team of Le Anh Accounting Center. You can choose 1 of the ways to fix the error of converting columns to rows on Excel in this article to work better.
See more: 4 methods Ways to Copy Tables from Excel to Word