In above solution, the helper cells are giving us running numbers from 1 to 6 (and 1 to 7). Sometimes, we cannot really use a helper column. See the illustration below to understand how this works.įormula Solution #2 – Using INDEX formula & no helper cells Copy this formula all over and you are done!.Now use INDEX formula to transpose data like this:.Similarly, write numbers 1 to 7 beside it (cells B23:B29).About this, write numbers 1 to 6 (cells D20:I20).That means, the transposed table will have 7 rows & 6 columns. Lets also say myData has 6 rows & 7 columns. Lets say, we have named our original data as myData. Formula Solution #1 – Using INDEX & Helper cells to transpose a table So whenever original numbers change, you must waste precious key strokes & time re-doing the transpose. Go to an empty area and open Paste Special (CTRL+ALT+V)Īlthough this approach works, it creates a copy of your original data.Excel has a built-in feature that lets you transpose data with a single click. The easy solution – use Paste Special > Transpose That is, we want to take all rows in our data & make them columns.
#HOW TO TRANSPOSE AN ARRAY IN EXCEL HOW TO#
Here we discuss the TRANSPOSE Formula and how to use the TRANSPOSE Function along with practical examples and downloadable excel templates.Today lets tackle a familiar data clean-up problem using Excel – Transposing data. This has been a guide to the TRANSPOSE Function. Thus, If we make any changes in the source table, it will automatically reflect the result table. Here, data is linked with the source table. It will display the data in the same format as you want. Select the data range from the source table for which we want to transpose as an argument and press F4.Ĭlose the bracket and press CTRL+SHIFT+ENTER. Go to the formula bar and write the TRANSPOSE function. Now click on cell B28 and select the above blank area. First, we will copy and paste the Product name from column to row & Day name from rows to columns. Now we want to convert this data from rows into columns. Let’s consider we have an Electronic store sales data day wise:
In other words, we can say exchanging a row or a column. It takes a row and converts it into a column or a column and converts it into a row. The TRANSPOSE function is a built-in function.
#HOW TO TRANSPOSE AN ARRAY IN EXCEL DOWNLOAD#
You can download this TRANSPOSE function Excel Template here – TRANSPOSE function Excel Template Overcome to this problem, and the TRANSPOSE function comes into place. The resulting data is not linked with the source data here. This means we need to make the changes accordingly in our result set too. If we make any changes in the source data, then it will not affect our result set. When we have a simple set of data, this method is easy to transpose, but when working on a complex set of data like Tables or Function, this method is not preferred to use. It will convert the data from columns into rows. Under this option, click on the 4th option, “ Transpose”, as per the below screenshot: Press Right-click, and you will see the PASTE OPTIONS segment. Then click on the cell where we want to see the result. Select anywhere in the data and press CTRL+A for selecting whole data and press CTRL+C for copying that data. Let’s assume the below data, and we want to convert this data from columns into rows. It’s the simpler way of transposing the data. Now we will see that when we have Copied and Paste feature of excel, what is the use of the Transpose function? TRANSPOSE Formula in Excel Excel functions, formula, charts, formatting creating excel dashboard & others
0 kommentar(er)
