Where is transpose function in excel

But doing that creates duplicated data. Note: If you have a current version of Microsoft , then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Excel inserts curly brackets at the beginning and end of the formula for you.

For more information on array formulas, see Guidelines and examples of array formulas. First select some blank cells. But make sure to select the same number of cells as the original set of cells, but in the other direction. For example, there are 8 cells here that are arranged vertically:.

Now type the range of the cells you want to transpose. In this example, we want to transpose cells from A1 to B4. Just stop typing, and go to the next step. An array formula, in short, is a formula that gets applied to more than one cell. Because you selected more than one cell in step 1 you did, didn't you? You don't have to type the range by hand.

Just click and drag from the beginning of the range to the end. Need text and cell formatting to be transposed as well? Try copying, pasting, and using the Transpose option.

But keep in mind that this creates duplicates. However, the output transposed by the copy-paste method remains the same. The following table shows the average marks of seven students in a school. The two functions check whether a cell is blank or not. Otherwise, the formula is supplied a value to transpose. There is a list of IDs in the range B4:B7.

The range is transposed and the prefix is added to every cell, as shown in the following image. The steps are listed as follows:. Step 2: Press the F9 key. The numbers of the range B4:B7 are combined in the single cell D5. The output is shown in the following image. The following table shows the books on different subjects in a library. Every book is placed on a shelf denoted by the location.

We want to retrieve the location of the books second subsequent image using the data in the range B4:E6. This creates an array of three rows in a column. Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures.