Do you have wrong direction of a table and you do not want to rewrite the cells from a column in a row? Use a simple trick and Excel will do all that is necessary. Read in this manual, what needs to be done. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Transpose

It may be the case that we have filled the cells next to each other (in a row) and for some reason we want to have them below each other (in the column), or vice versa. Similarly, we want to overturn the whole table of 90 degrees, swap rows and columns. Row tables (with headlines for individual lines) are for Excel in many cases useless, because they do not support for example filtering or creating pivot tables. Therefore, in is suitable to change the table into columnar form.

All the above situations can be achieved by method “transposition”. Apply this procedure:

  1. Select the area you need to overturn.
  2. Make a Copy command (via CTRL+C).
  3. Click into the empty cell, from which to begin to fill the transposed region.
  4. Make a Paste Special > Transpose.
  5. Finish Copy mode by pressing Escape

See the entire procedure at the animation below.

Transpozice

Transpozice

TRANSPOSE function

In some cases, we work with a formulas from wrong oriented areas, which must be for the correct calculation also reversed. It is not necessary to transpose the area using the above procedure, the calculation can only rotate the region using special function Transpose. With this feature, we work in “array” formulas.

=TRANSPOSE(field)

  • field … range of cells you want to transpose

The result of the TRANSPOSE function is an area of several cells, and is always overturned about 90 degrees from its original location. However, the transfer is done only for the purposes of calculation and therefore nothing visible happens.

Leave a Reply

Your email address will not be published. Required fields are marked *