Transpose Paste Special Example by k01nsY8

VIEWS: 1 PAGES: 4

									Transposing Data


Instructions

On the exercise sheets is some information we have received (yellow).
We want to (quickly and easily) present this information in different ways.

In the green cells you need to perform the following:

In the first set of green cells, you want to transpose (convert rows to columns and columns to rows) just the values.

In the second set of green you want to transpose the formula so that they remain linked.


In the 3rd, 4th and 5th set of green cells you want to (quickly and easily) link the cells so that the narration is correct.


Hints
For the first set of green cells, use the paste special command i.e. copy the area, select where you want it to go and
then
click EDIT, PASTE SPECIAL and then tick TRANSPOSE. You can decide here whether you only want values or to
leave the formula


For the second set of green you want to use the TRANSPOSE function.
You need to highlight a set of cells that matches the area you require and then activate the FUNCTION WIZARD.
Find the TRANSPOSE function and follow its requirements by referring to the yellow cells.
DON'T FORGET THAT YOU NEED TO CLICK CTRL, SHIFT and ENTER TO ACTIVATE ARRAY FORMULA


In the remaining green cells you need to highlight the cells in the yellow section you want to use (hold the CTRL button
down to highlight cells that are not next to each other).
Then copy the selection, click on the cell where you want it to go and then click EDIT, PASTE SPECIAL
and then PASTE LINK.
Do this for the other cells as well
                                     Transpose Values Only
Sales           2000   2001   2002
Div 1            100    120    140
Div 2            120    140    160
Div 3            140    160    200
                 360    420    500
Cost of Sales                        Transpose Formulae
Div 1            50     60     70
Div 2            60     70     80
Div 3            60     80     90
                170    210    240
Gross Profit
Div 1            50     60     70
Div 2            60     70     80    Paste Link the relevant cells
Div 3            80     80    110    Div 1                2000       2001
                190    210    260    Sales
                                     Cost of Sales
                                     Gross Profit

                                     Div 2                2000       2001
                                     Sales
                                     Cost of Sales
                                     Gross Profit

                                     Div 3                2000       2001
                                     Sales
                                     Cost of Sales
                                     Gross Profit
2002




2002




2002

								
To top