One of Excel’s many capabilities is the ability to transpose data in Excel, which is a useful tool that can be used for a variety of tasks. When you transpose data in Excel, you switch the data’s orientation from rows to columns or vice versa. In this article, we will talk about how to use the transpose function to transpose rows to columns and columns to rows in Excel data.
Transpose Data in Excel
Excel’s Transpose method is used to change an array’s orientation. It helps us arrange unformatted data into the proper order by converting the vertical range to a horizontal range or vice versa. You will discover three distinct methods in this article for transposing data in Excel.
#1. Paste Special Transpose
Follow the next steps to transpose data.
- Choose the A1–C1 range.
- To copy, right-click, and then click.
- Choose cell E2.
- Click Paste Special after right-clicking.
- Verify the Transpose.
- Select OK.
#2. Transpose Function
Follow these instructions to insert the transpose function
- Choose the new set of cells first.
- Enter =TRANSPOSE(.
- Choose the A1:C1 range and end with a parenthesis.
- Press CTRL + SHIFT + ENTER to complete.
Note: the curly brackets around the formula in the formula bar show that it is an array formula. Select the range E2:E4 and hit the Delete key to remove this array formula.
- Simply choose cell E2 and type the TRANSPOSE function in Excel 365 or Excel 2021, then press type. The curly brackets are gone.
#3. Transpose Table without Zeros
Excel’s TRANSPOSE function turns blank cells into zeros. To resolve this issue, just utilize the IF function.
- For instance, the cell B4 below is empty. This blank cell (cell G3) becomes a zero using the TRANSPOSE function.
- The IF function below produces an empty string to transpose if the value is blank, which is two double quotes with nothing in between.
#4. Transpose Magic
Although the ‘Paste Special Transpose’ option is excellent for transposing data, you need to use a few magic methods to connect the source cells to the destination ones.
- Choose the range A1–E2.
- Click Copy after doing a right-click.
- Choose cell A4.
- Click Paste Special after right-clicking.
- Select Paste Link.
- Choose the A4–E5 range, then change each equal sign to a xxx.
- To transpose this data, use the ‘Paste Special Transpose’ command.
- (The exact reverse of step 6) Choose the range G1:H5 and change every instance of “xxx” to an equal sign.
Note: For instance, alter the value from 16 to 36 in cell C2. Additionally, the value in cell H3 will increase from 16 to 36
Transpose row to column in Excel
Turn Rows Into Columns in Excel ( Transpose)
This section applies when you want to arrange rows of data into columns in Excel.
- Choosing the data to be transposed
- Select with the right mouse click.
- Click Copy on the context menu that appears or can also choose the row and click Ctrl +C.
- Activate the cell you want to show as the destination for the column.
- Perform a second right-click to open the context menu, as displayed below.
- Select Option Paste Special from the list of past choices.
- Click ‘Transpose’ in the ‘Paste Special’ dialogue box and then click ‘Okay.
Turn Columns Into Rows in Excel (Transpose)
- Choose the column that contains the weekdays.
- Select Copy from the context menu when you right-click on the selection.
- Turn on the cell in which you want the row to show.
- Perform a second right-click to open the context menu.
- Select Paste Special from the Options menu.
- Click “Transpose” in the “Paste Special” dialogue box and then click “Okay
Common Errors While Transposing Data in Excel How to Fix
You could run across several typical errors like #REF, #VALUE!, and #NAME? while transposing data in Excel. These issues happen when the data range chosen is invalid, the data itself includes mistakes, or the data is formatted incorrectly. Make careful you use the suitable data range and prepare the data appropriately before transposing it to avoid making these mistakes. The error-checking features of Excel may also be used to find and correct the mistakes.
The #NUM! mistake is another frequent error that can happen while transposing data in Excel. This mistake happens when the data transposition formula generates a number that is either too big or too little to fit in the cell. However, you may try altering the cell’s format to scientific notation or enlarging the cell to display the complete number in order to correct this issue.
The Benefit of Transposing Data in Excel
The key advantage of transposing data in Excel is that it gives your data a more practical and readable structure. Transposing rows and columns creates a new dataset that is simpler to comprehend, understand, and precisely interpret. Transposing data also saves time and effort because you don’t have to manually enter the data again. When working with huge datasets that contain hundreds or thousands of rows and columns, this method is quite helpful.
You can conduct several calculations and actions that are not feasible with the original dataset when you transpose data in Excel. For instance, the TRANSPOSE function may be used to produce a matrix multiplication, which is helpful in a variety of industries including banking, engineering, and statistics. Transposing data also enables you to spot patterns and trends that were perhaps hidden in the initial arrangement.
How to Paste Horizontal Data Vertically in Excel
Here are steps on how to paste horizontal data vertically in Excel:
#1. Open Excel and Copy Vertical Rows
Open the spreadsheet containing your dataset in Excel after starting it. Click and drag your cursor over the group of cells to choose which cells you want to transpose. After choosing the dataset, press “Ctrl” and “C” on your keyboard to copy it. Additionally, you have the option to right-click on the cells and choose “Copy” from the menu.
#2. Choose the Location Where You Want to Paste the Data.
Choose the location where you want to paste the data vertically next. Select an empty space in your worksheet. Highlight the precise number of cells in your dataset with your mouse. If you had two columns with five cells each, for instance, you would highlight two rows with five cells each. Excel can accurately transpose the data if the right number of cells is chosen.
#3. Right-Click & Find “Past Special”
Transpose of the data after highlighting the new location of your cells. Right-click the area and select “Paste Special” from the drop-down menu to accomplish this. “Paste Special” is located in the menu’s “Paste Options” section. The “Paste” symbol, which is a sheet of paper on top of a clipboard, is located just beneath this button.
#4. Click “Ok” After Selecting “Transpose”
Upon clicking “paste special”, a new window opens. You may paste your data in a number of different ways using the “Paste Special” box. The “Transpose” check box may be found close to the window’s bottom. To confirm that you want to flip the duplicated cells, click on the box. To make your changes effective, click the “OK” button. Your data ought to now be shown vertically on your Excel sheet.
How to Paste Horizontal Data Vertically in Excel Using the Transpose Function
#1. Highlight a Set of Cells
Select the spreadsheet containing your data in Excel after opening it. Select a vertical block of cells with the same number of cells as the data you want to transpose in a blank area of the worksheet. However, Excel is now able to transpose the data while maintaining the proper formatting. For instance, if you want to paste a single column that has 10 cells, you need first pick those same 10 cells vertically.
#2. Use the transpose function and select your dataset
By entering = “ Transpose”, you may utilize the transpose function after you’ve highlighted a blank region of cells. Select your dataset after that, and then include it in the transpose method. Click and drag your mouse over the cells that contain your dataset to accomplish this. The range of cells can also be manually typed. You would put “A2:B10” in parentheses after the transpose function if your dataset contains the cells A2 through B10.
#3. Press “Ctrl,” “Shift” and “Return”
Press “Ctrl,” “Shift,” and “Return” on your keyboard to activate the function. Normally, inserting a function involves pressing the “Enter” key. However, the array formula used for the transpose function results in an error when you hit “Enter.” Use the keyboard shortcuts “Ctrl,” “Shift,” and “Return” to prevent the mistake. Excel shows your data vertically in the chosen rows after applying the shortcut.
How Do You Swap Columns in Excel?
Drag the column to a different spot while pressing and holding the Shift key. You will see a thin “I” bar running the whole length of the column and a box showing the location of the new column. I’m done now! Release the mouse button while holding down the Shift key to discover the column has been shifted.
What Is the Shortcut for Transpose in Excel?
- To copy the table you wish to transpose, select it and click Ctrl + C.
- Choose the range’s upper-left cell as the location for the transposed data to be pasted.
- Type Ctrl + Alt + V, then E to paste a specific transposition shortcut.
- Key in Enter.
Is There a Way to Swap Cells in Excel?
To accomplish this, click the first cell in the row or column you want to swap, then drag the mouse across or down the row or column while holding down the mouse button to pick a group of cells. When you do this, a green box surrounds the cells you’ve chosen.
Reference
