If you wish to return blank cells instead, nest the IF function inside your TRANSPOSE formula to check whether a cell is blank or not. If one or more cells in the original table is empty, those cells will have zero values in the transposed table, as shown in the screenshot below: How to transpose data in Excel without zeros for blanks The screenshot below shows that our source table was transposed successfully and 4 columns were converted into 4 rows:
In no case you should type them manually, that won't work. Once you press Ctrl + Shift + Enter, Excel will surround your Transpose formula with that are visible in the formula bar and are a visual indication of an array formula. Why do you need this? Because the formula is to be applied to more than one cell, and it is exactly what array formulas are purposed for. Press Ctrl + Shift + Enter to finish your array formula properly. Then, select the source range using the mouse or type it manually:įinally, type the closing parenthesis, but don't hit the Enter key! At this point, your Excel Transpose formula should look similar to this: With a range of blank cells selected, type the Transpose formula:įirst, you type the equality sign, function name and opening parenthesis: =TRANSPOSE( In our case, we select a range of 5 columns and 4 rows: So, you select a range of empty cells that occupies the same number of rows as the original table has columns, and the same number of columns as the original table has rows. Your new table will contain the same number of cells but will be rotated from the horizontal orientation to vertical or vice versa. Select the same number of cells, but change the orientation Keeping these figures in mind, proceed to the next step. Our source table has 4 columns and 5 rows. In this example, we are going to transpose the table that shows the volume of fresh fruit exports by county:
You will need these numbers in the next step. Count the number of columns and rows in the original tableįor starters, find out how many columns and rows your source table contains. If you don't have much experience with Excel formulas in general and array formulas in particular, please make sure you follow the below steps closely. A trickier part is to enter it correctly in a worksheet. The syntax of TRANSPOSE leaves no room for mistakes when building a formula. How to use the TRANSPOSE function in Excel Step 5: Go back to your Word document, press Ctrl+V to paste the transposed table.Īfter rotating the data successfully, you can delete the original data.Important note! For the TRANSPOSE function to work, you must enter it as an array formula by pressing Ctrl + Shift + Enter. Step 4: Press Ctrl+C to copy the transposed data in Excel. The rows become the columns and the columns become the rows. Right-click the first cell where you want to paste the data, and pick Transpose. Select the cells ranges you want to transpose, and then press Ctrl+C to select it. Step 3: Use transpose feature to rotate data in rows and columns. Open an Excel workbook, click in a blank cell on the worksheet, press Ctrl+V to paste it. Step 2: Paste table data to an Excel worksheet. In Microsoft Word, select all data in Word table, then press Ctrl+C on your keyboard to copy it. Step 1: Select and copy data in Word table. Steps to Convert Data from Rows to Columns in a Worksheet However, with the help of transposing rows and columns feature in Excel, you can quick way to transpose data from rows to columns or vice versa. Word does not have a built-in way to transpose a table. This is a guide on how to convert data from rows or columns in Word table. How to Convert Data from Rows to Columns in Word Table