How to drag transpose formula in excel
The Excel TRANSPOSE Function
Timecodes:
in this short excel video i want to show
you
how to use the transpose function in
excel here i am in an excel spreadsheet
that i've started using
and i'm regretting a little bit how i
laid out this information
i don't know if you've ever had that
experience where you start typing in
data you put in your column headings you
put in your rows
and then you say to yourself wait a
minute maybe i should have put
in this case the names vertically in
column a
instead of horizontally in row one i
know that's happened to me a few times
in other cases the way i've entered the
data is fine
and it's what i want but i might want to
have it also displayed
in the opposite way maybe on a separate
spreadsheet so we're going to look at an
excel function called transpose
that will help us do that and i'm going
to begin by clicking on this plus sign
to add
sheet number two and i'll go to the
upper left corner of where i want the
new data to appear
and i'll tap the equals sign and type in
transpose you can see that excel
recognizes the function that i'm trying
to use
and it gives me a description it
converts a vertical range of cells to a
horizontal range
or vice versa so now i'll type left
parenthesis
and excel is now expecting an array
in other words a range of information so
i'm going to click back
on sheet 1 and then i'll simply click
and drag to
highlight the range of information that
i would like to transpose
i think i got everything and now i'll
just type
the right parenthesis here in the
formula bar you can see that same
formula that i started on sheet2
is still present with me here through
the formula bar
and i put in the right parenthesis it
usually works fine without the right
parenthesis but i'm trying to get in the
habit of always putting it in there
so now i'll tap enter on the keyboard
and i get a little message
saying my formula spilled the formula
returned multiple values
so we spilled them into the neighboring
blank cells got it
that sounds fine to me so let's see how
this worked
just like i expected it took the data on
sheet 1
that has people's names across the top
and phone number
donation amount and state across the
left side of the spreadsheet
and on sheet 2 it flipped all of that
that's exactly what i wanted
but to help us see this better i'm going
to click and drag
from a to d here at the top in the
column letters
and then i'll just double click between
any two of those columns
how about here between c and d right on
that line just double click
that will automatically resize the
columns so that the information in the
columns
fits perfectly let's take a look at this
data
so for the most part it looks like it
worked well let's test it out with
catherine
mcinturf if i go back to sheet1 i can
see that katherine mcinturf
her phone number ends in 58 she donated
58
that's a coincidence and she's from new
jersey
let's test that out catherine mcinturf
58
58 new jersey so it looks like the
information
was transposed accurately and safely
having said that it did change the way
some of the data
is displayed for example it added a zero
here
and unfortunately i can't just delete
that now the reason why
is because even though it says zero
there's something other than zero in
that cell
if i select it you can see it here in
the formula bar that's where my
transpose
formula is located so if i delete that
cell
and tap enter it erases all of the data
off of sheet
2. it's still on sheet 1 but not sheet
2. so i'm going to hold ctrl and tap z
to undo that
so i need to leave that there looks like
my phone numbers got messed up a little
bit
so i can click on b go to the home tab
and in the number group i can change the
number format
by going here to more number formats and
i can go to special
phone number now those are converted to
phone numbers i could do the same kind
of thing
with the dollar amounts but for the most
part this has been a very successful
transposition that i've done now it's
important to know
that if i change the information in
sheet 1 like the donation amount for
morgan let's say he donates a little
more morgan's information will also
change
on the linked sheet when it transposes
the links
stay intact if that's not what you want
you should probably transpose using copy
and paste
special and if you want to see how to do
that watch my tutorial
on transposing data in excel the other
thing you could do
is now that you've transposed the data
you can click and drag to highlight
all of the data and then hold ctrl and
tap c to copy the data
and then you could go to another sheet
let's say or somewhere in the same sheet
click in the upper left corner of where
you would like that data to be displayed
and then go to the home tab home ribbon
clipboard group
and i'm going to click here on the
bottom part of the paste button
and i'll just go here to paste values
now let's try it if i delete that 0 the
data is still there
why because when i did paste special and
pasted the data as
values it converted the data from
information that was created by the
transpose function
and formula that i wrote it changed it
from that and converted it to
simply values so now at this point if i
wanted to
i could delete sheets 1 and 2 and sheet
3
is still intact this is now independent
data
i hope that you found this tutorial on
the transpose function to be helpful
if you did please like follow and
subscribe and when you do
please click the bell so you'll be
notified when i post another video
if you'd like to support my channel you
can do that through my patreon account
and you'll see a link to that in the
description below speaking of patreon
i want to give a quick shout out to my
five dollar patreon supporters
thank you so much for all you do to
support my channel
Related queries:
how to enter transpose formula in excel
