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.
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.
Nội dung
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).
Step 3: 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 …Or go to Home tab -> Clipboard group -> click the small arrow below Paste -> 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 the horizontal cells. This is where the new cells will appear after you make 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(array). Where ”Array” is the data range you want to convert to rows.
The next step is to press CTRL+SHIFT+ENTER You will fix the error of converting columns to rows in Excel
When learning to fix errors while converting columns to rows in Excel, you might wonder why it’s necessary to use CTRL+SHIFT+ENTER instead of just pressing ENTER, as with most regular functions.
This happens because the TRANSPOSE function works with array formulas, which are designed to handle multiple cells at once. When you selected multiple cells in step 1, Excel expected an array formula to fill those cells. Pressing CTRL+SHIFT+ENTER confirms that you are using an array formula, ensuring the TRANSPOSE function applies correctly to all selected cells.
Above is the content of how to convert columns to rows on Excel. Simple, right? Hopefully, the article will bring you useful information. Wish you success.
See at: Transpose (rotate) data from rows to columns or vice versa
See more: 4 Ways to Copy Tables from Excel to Word