Excel 2010 Magic Trick 796: Excel Flip Table Rotate Table TRANSPOSE or INDEX or Paste Special

Download file: https://people.highline.edu/mgirvin/E...
Rotate Table:
1. See how to rotate a table 90 degrees using the array function TRANSPOSE
2. Flip table 90 degrees with Formula with INDEX and ROWS and COLUMNS
3. Paste Special Transpose.
4. Keyboard shortcuts for Paste Special
5. Flip table 180 degrees with Formula with INDEX and ROWS Mixed Cell References

---

Closed Caption:

welcome to exile magic number 796 if you
want to download this workbook exercises
trick 795 to 796 click on the link below
the video in this video here we have a
table and we want to flip it on its side
so we really have one two three columns
so we want to end up with three rows and
we have 12345678 rose so we need eight
columns now let's see a couple different
ways to do this with the formula and
also with pay special now if you don't
need the data linked then we don't need
to make a formula so I'm simply going to
show you do the paste special transpose
i'm going to highlight and control C for
copy once the dancing answer dancing
around in 2007 and $YEAR 10 you click in
the cell you want you go up to the home
ribbon paste and in 2007 you have to
click here and there it is right there
in two thousand i think i still got the
dancing dance in 2010 you can simply go
for the transpose button they're
actually in 2010 you can control V and
then notice this right here ctrl-v to
open up the smart tag i hit ctrl and
then tu have to you know learn this that
that's a tea if you do this all the time
still one other way to in 2010 only as
you hit the right click key the
right-click key is in but is on the
right-hand side in between control and
window so i hit right click and I can
see right up here it's got a tee
I'm going to hit T and then enter so
that was actually the right-click tea
and enter ok so those were Pee special
that's totally easy way to do it but
what if you need it linked well there is
a transpose function and it's an array
function now you have to count one two
three columns so that means i need three
rows 1 to Phi I think that's eight rows
that means they need eight columns so
you have to highlight this range and
then the active cell you type your
transpose function highlight the array
is just simply that array right there
notice i don't know i'm not going to
lock this with the f4 key the array
function is different than normal
functions or formulas now I've closed
parenthesis and in order to populate all
this and transpose the table you have to
hold ctrl and shift together and then
enter it automatically populates the
whole table and you can see it's b22 d9
and every single one of those that's an
array function you could see up here the
curly brackets those are automatically
put in one downside to the transposes if
if you ever wanted to like insert for
some crazy reason like you insert a
column here i'm in a row here and you
want to insert a column here and copy
the formula over
array array will not let you do that so
there are other ways to do this let's
look at a second formula method how
about the index function the index
function is a lookup function and we're
going to need the whole array but now
i'm going to hit f4 now comma what does
the index function actually need while
we have the whole array it indexes doing
going to do a two-way look up so it
needs to you need to tell what row and
what column now let's think about this
here's this first cell what rodeo I need
one but what about when i move the
format copy the formula over here like
this in this next column what do i need
i need the second row when I'm over in
the third column i need the third row so
in the row argument we need to have
increment the numbers 1 2 3 as
we moved to the side but want to go down
to the next row we need 123 again the
columns with an s function is perfect
now i'm sitting in be 15 so i'm going to
type dollar sign b15 colon be 50 now
what does this do it's locking the
column here but not here this B is
allowed to move because there's no
dollar sign so when it goes over one
it'll turn to see well right now in this
cell wonder how many columns are between
B&B one when this copies to the side
this will turn to a seesaw how many
columns are between B&C to now this is
pretty cool we're using the columns in
the row comma well the exact inverse of
that for the column number we want as we
go down 123
so we're going to use rose now Rose
similar to college but it's going to
count rows not column so we're going to
a be and then dollar sign 15 notice the
row reference the number is what we're
locking this time
alright and so that'll give us 123123123
control-enter and i'm going to copy it
over to the side and down so each one is
giving us the same results but you can
see how this one is a little bit
trickier obviously you'd want to use the
transpose because it's just so easy i
use transpose all the time
one other thing what if you want to flip
it upside down just so if you are
watching this and have a question
well you use index and the array would
be this now think about this i'm going
to go over today and then to shift so i
want to lock only the numbers not the
columns I want bees to turn to to cease
when I go when I copy it this way comma
and what I want all i need is a row
number here because i did my relative
cell references
I mean there's a relative on the column
locked on the two
nine is similarly locked because I set
this up as a mixed cell reference i only
have to put a row input so here
oh but wait a second if I'm flipping it
upside down any this one first then this
one
well i'm going to use the Rose function
and i'm going to highlight this whole
range right here
the only trick is is that I want it to
as I copy this down here i want this
blue range to expand so it says b2b nine
when I go down one row i wanted to say
b32 benign so I'm going to lock the last
one and I'm going to do it this way i'm
going to say only the number reference
right this is not an expanding over here
we have an expanding range here we have
a contracting range close parenthesis
control-enter drag it down drag it over
let's see what i mean by contracting I
we should have gone over here looked
what I meant by expanding here you could
see how goes from b15 2d 15 so it's
literally as it's copied to the side
it's continually expanding the range is
getting bigger the number of columns you
can count are getting more getting
bigger but here look at this
Oh be 49 so it'sit's actually
contracting you could see that little
green piece right there
it's contracting each time that's a
contracting range alright that's a bunch
about flipping to the side 90 degrees
upside down and then lots of paste
special stuff
alright see you next trick