#TRANSPOSE AN ARRAY IN EXCEL VBA HOW TO#

#TRANSPOSE AN ARRAY IN EXCEL VBA HOW TO#

For more information, please see How to convert rows to columns with Paste Special. However, if you change the original cells, the transposed cells won't get updated.

To perform a one-time conversion without creating a connection between the two tables, use the Paste Special > Transpose This will not only rotate values, but also text and cell formatting.

For automatic column-to-row conversions, the following macro can come in handy: How to transpose in Excel with VBA.

If you avoid using array formulas in your worksheets, combine the INDIRECT and ADDRESS functions to rotate a range with a normal non-array formula as shown in this example.

Depending on your particular task, you may want to use one of the following methods: The TRANSPOSE function is the most popular but not the only way to change columns to rows in Excel. For this, select all the values returned by your formula, press Ctrl + C to copy them, right-click, and choose Paste Special > Values from the context menu.ДЇor more information, please see How to convert formulas to values. If you'd like to break the connection between the two tables, replace the formula with calculated values. This means that whenever you change some value in the original table, the corresponding value in the transposed table changes automatically. When you flip a range by using the TRANSPOSE function, the source range and the output range get linked. To remove a Transpose formula from your worksheet, select the whole range referenced in the formula, and press the Delete key. To edit a Transpose formula, select the entire range the formula refers to, make the desired change, and press Ctrl + Shift + Enter to save the updated formula. How to edit a TRANSPOSE formulaДЄs an array function, TRANSPOSE does not allow changing part of the array it returns. The below tips will help you avoid typical mistakes. Tips and notes on using TRANSPOSE in ExcelДЄs you have just seen, the TRANSPOSE function has a number of quirks that may confuse unexperienced users. If the cell is blank, IF will return an empty string (''), otherwise supply the value to transpose:Д®nter the formula as explained above (please remember to press Ctrl + Shift + Enter to finish the array formula correctly), and you will have a result similar to this:

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 Important note! For the TRANSPOSE function to work, you must enter it as an array formula by pressing Ctrl + Shift + Enter.

Transpose an array in excel vba