NOTE: To edit the formula, select the cell where it was originally entered - B4. There is a light blue border arounde the range.

The formula will automatically spill into adjacent cells (B4:C7), to transpose the data.

Select the top left cell in the range where you want to display the data vertically - cell B4 in this example.

To transpose a 2 row x 4 column horizontal range to a 4 row x 2 column vertical range, follow these steps: With the TRANSPOSE function, you could link to your original horizontal data, without changing its layout.

If data is arranged horizontally in a worksheet, you can use the TRANSPOSE function to list the data vertically, in a different location.įor example, in a summary sheet, or dashboard, a vertical layout might be best. If the screen tip shows SORT and SORTBY, you have the new functions.To check if your version of Excel has Dynamic Array formulas: For Excel versions without Dynamic Array formulas.For Excel versions with Dynamic Array formulas.The TRANSPOSE destination range must have the same number of rows and columns, respectively, as the array has columns and rowsįor the 1st example shown below, there are 2 sets of instructions.TRANSPOSE must be entered as an array formula, by pressing Ctrl+Shift+Enter.

The TRANSPOSE function has a couple of traps, unless you are using newer versions of Excel, with dynamic array functions: array is an array or a range of cells to be transposed.The TRANSPOSE function has the following syntax: Instead, use a Paste Special command, as described below. NOTE: You can also change the orientation of data without using the TRANSPOSE function. combine TRANSPOSE with other Excel functions, to show a person's total salary over their best 5 consecutive years.change horizontal data to vertical, in a different location on the worksheet.The TRANSPOSE function can change the orientation of data, or be used with other functions. There are written instructions below the video. Watch this short video to see 3 examples of ways that you can use the Excel TRANSPOSE function. It can be used on its own, or combined with other Excel functions. The Excel TRANSPOSE function changes the layout of data, from horizontal to verical, or from vertical to horizontal. More Functions Tutorials Video: TRANSPOSE Function

How to rotate columns in excel