#TRANSPOSE AN ARRAY IN EXCEL HOW TO#
In this tutorial, I will show you how to combine multiple cells by using the CONCATENATE function. While you can enter the reference one by one within the CONCATENATE function, it would not work if you enter the reference of multiple cells at once (as shown below):įor example, in the example above, while the formula used is =CONCATENATE(A1:A5), the result only shows ‘Today’ and doesn’t combine all the cells.
![transpose an array in excel transpose an array in excel](https://trumpexcel.com/wp-content/uploads/2016/06/Transpose-Data-in-Excel-Dataset.png)
Read our Ultimate Guide To Highlighting Duplicate Values here. When Transposing Data you can be left with redundant data, and will want to highlight them all at once. In this case verify that you’re selected the correct destination range, in terms of dimensions. For example, if your original range was 2 rows by 9 columns and you select 5 rows by 2 columns, instead of 9 rows by 2 columns all your data will not be copied. If you’re using an older version of Excel and you do not select the correct destination range dimensions. Selecting the incorrect destination range dimensions The solution to this is to follow the steps correctly and select the entire destination range first. If you’re using an older version of Microsoft Excel and you do not select the entire destination range first but only one cell and then press CTRL-SHIFT-ENTER. In this case repeat the steps needed for older versions of Excel, only this time ensure that you press CTRL-SHIFT-ENTER. If you’re using an older version of Microsoft Excel and haven’t pressed CTRL-SHIFT-ENTER once you’ve entered your formula, then you will get a #VALUE! error. With the range selected, enter the following formula in the formula bar:.So, select the entire destination range first which in this case is A4:B12.You will need to use the IF Function in combination with the TRANSPOSE Function. If your original data set contains blanks, then the TRANSPOSE function will add a zero instead of a blank when it rotates the data set.įor both newer and older versions of Excel do the following. Troubleshooting with the Transpose Function Blanks in the Data set
![transpose an array in excel transpose an array in excel](https://cdn.educba.com/academy/wp-content/uploads/2019/05/VBA-Transpose-Example-2-5.gif)
![transpose an array in excel transpose an array in excel](https://www.techonthenet.com/excel/formulas/images/transpose001.png)
When you need your data to be transposed and updated as the source data changes, use the TRANSPOSE Function.The curly braces in the formula bar are automatically added once you press CTRL-SHIFT-ENTER and the range is transposed.įor more pages on Excel read our Comprehensive Guide to Applying Conditional Formatting in Excel.