The Excel Transpose function converts the blank cells into zeros. This is done to tell Excel that the output will be an array of data and not a single cell.

An array formula must be entered by pressing a key combination of Ctrl+Shift+Enter so that the formula bar puts a set of braces around the formula. Don't press Enter! You need to execute array formulas by pressing Ctrl+Shift+Enter.Now, enter the formula =TRANSPOSE(A1:F5).Suppose, if your table's range is 5圆, i.e., 5 rows and 6 columns, you must select 6x5 range, i.e., 6 rows and 5 columns, for the transposed data.TRANSPOSE is an array formula hence you need to select the exact number of cells.Select the range of cells where you want the output transposed data.Watch the demo displayed below to get a better understanding of how to use the TRANSPOSE function. Here, the array stands for a range of cells to be transposed. TRANSPOSE Excel FunctionĪs the name suggests, the TRANSPOSE Excel function can be used to transpose data in Excel. Now, let's move forward and understand Excel's in-built TRANSPOSE function. Look at the demo and follow the steps below to understand how to use the 'Paste Special' option to transpose data. This feature can be used to copy the original content and paste the transposed data. The Paste Special feature in Excel allows you to control the formatting of the content that is being pasted. The transposed data is successfully pasted to the new location. This step is the most crucial as we want the data to be transposed. Now, click on Values (to paste only values) and select the Transpose option on the bottom right. Step 5: Then the Paste Special dialog box appears.This option is also available when you right-click on the cell where you want to paste. Find this option by navigating to the Home tab → Clipboard section → Paste → Paste Special. Step 3: Next, select a new location where you want to paste the transposed data.Step 2: You will find the Copy option on the Home tab in the Excel ribbon or right-clicking the entire range.Step 1: First, select the range that you want to transpose.Follow the methods below, along with a detailed step-to-step guide, in order to transpose data effortlessly. How to Transpose in Excel (Best Three Approaches)

Excel row to column conversion