Transpose: creating vertical data from horizontal data ...

Transpose: creating vertical data from horizontal data (& vice versa)

excel tips transpose2

Transposing

- can be used to convert your current vertically formatted data into horizontally formatted data and vice versa. In this example we see the ‘horizontal’ data transposed to a ‘vertical’ format.

  1. Highlight the cells of data and copy (Control+C) whiteRow image 1
  2. Click into the first cell of the new location on the same or a different sheet.
  3. On the HOME tab click the dropdown arrow under Paste to show the full paste menu. Click Paste Special.whiteRowimage 2
  4. Click Transpose at bottom right.whiteRowimage 3
  5. Click OK. The data and its formats are applied in the new location.whiteRowimage 4

 

Linking and transposing data

 

Clicking into the new cells will show quite neatly that the formulas have been maintained including references to cells that have changed their location. Those familiar with Paste Link will wonder if it is possible to both link and transpose the data. However, because Paste Link is disabled if you select Transpose (and vice versa) it looks like there is no way to link the two formats.
whiteRowThere is a work around that allows you to achieve this. It seems complicated but once done you can see it is quite simple.

  1. Highlight and copy your source datawhiteRowimage 5
  2. Click into the new location then, as before, open the Paste Special dialog and click Paste Link at bottom left.whiteRowimage 6
  3. Note that while the cells will now contain links to the original data the cell formats are lost.whiteRowimage 7
  4. Highlight this new set of data. Click Control+H to open the Find & Replace dialog. In Find what: enter an equal sign. In Replace with: enter an asterisk and an equal sign (no spaces). Note: the asterisk has no special function in this case; it is only to create text in the cell.whiteRowimage 8
  5. Click Replace All then OK the confirmation dialog.
  6. The new interim data table will appear as below displaying the non-functional formulas in the cells.whiteRowimage 9
  7. Highlight and copy the cells. Click the new location where you will transpose the cells.
  8. Paste Special > Transpose > OK. Now to reconvert the cells to linked formulas.
  9. Highlight the cells and click Control+H to open the Find & Replace dialog. Reverse the entries for Find what: and Replace with: entries you made earlier (as below).whiteRowimage 9a
  10. Click Replace All then Close.whiteRowimage 10

Notice now that if you make changes to the original data source they are reflected in the transposed table. You can now delete the interim data table you create and reformat the cells to suit.


Leave a comment

Your email address will not be published.

Required fields are marked with required


required

required

required