0308 Paste Special - Transpose vs. TRANSPOSE()

Advanced Excel Crash Course Section 3: Formatting Tricks
4 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€91.74
List Price:  €128.44
You save:  €36.70
£78.54
List Price:  £109.95
You save:  £31.41
CA$135.28
List Price:  CA$189.39
You save:  CA$54.11
A$152.35
List Price:  A$213.30
You save:  A$60.94
S$133.70
List Price:  S$187.19
You save:  S$53.48
HK$782.09
List Price:  HK$1,094.96
You save:  HK$312.87
CHF 88.44
List Price:  CHF 123.82
You save:  CHF 35.38
NOK kr1,060.96
List Price:  NOK kr1,485.39
You save:  NOK kr424.42
DKK kr684.80
List Price:  DKK kr958.74
You save:  DKK kr273.94
NZ$164.32
List Price:  NZ$230.05
You save:  NZ$65.73
د.إ367.22
List Price:  د.إ514.12
You save:  د.إ146.90
৳10,977.27
List Price:  ৳15,368.62
You save:  ৳4,391.35
₹8,288.18
List Price:  ₹11,603.79
You save:  ₹3,315.60
RM470.40
List Price:  RM658.58
You save:  RM188.18
₦159,205.14
List Price:  ₦222,893.57
You save:  ₦63,688.42
₨27,945.54
List Price:  ₨39,124.87
You save:  ₨11,179.33
฿3,570.70
List Price:  ฿4,999.13
You save:  ฿1,428.42
₺3,209.07
List Price:  ₺4,492.83
You save:  ₺1,283.75
B$499.58
List Price:  B$699.43
You save:  B$199.85
R1,877.33
List Price:  R2,628.34
You save:  R751
Лв179.79
List Price:  Лв251.71
You save:  Лв71.92
₩133,020.69
List Price:  ₩186,234.29
You save:  ₩53,213.60
₪366.08
List Price:  ₪512.52
You save:  ₪146.44
₱5,560.69
List Price:  ₱7,785.19
You save:  ₱2,224.50
¥14,903.11
List Price:  ¥20,864.95
You save:  ¥5,961.84
MX$1,671.47
List Price:  MX$2,340.12
You save:  MX$668.65
QR364.79
List Price:  QR510.72
You save:  QR145.93
P1,358.93
List Price:  P1,902.56
You save:  P543.62
KSh13,752.81
List Price:  KSh19,254.48
You save:  KSh5,501.67
E£4,780.94
List Price:  E£6,693.51
You save:  E£1,912.56
ብር5,681.93
List Price:  ብር7,954.93
You save:  ብር2,273
Kz83,641.63
List Price:  Kz117,101.63
You save:  Kz33,460
CLP$93,803.57
List Price:  CLP$131,328.75
You save:  CLP$37,525.18
CN¥719.51
List Price:  CN¥1,007.35
You save:  CN¥287.83
RD$5,921.90
List Price:  RD$8,290.90
You save:  RD$2,369
DA13,438.45
List Price:  DA18,814.37
You save:  DA5,375.92
FJ$226.07
List Price:  FJ$316.51
You save:  FJ$90.44
Q780.87
List Price:  Q1,093.26
You save:  Q312.38
GY$20,947.35
List Price:  GY$29,327.13
You save:  GY$8,379.77
ISK kr13,640.79
List Price:  ISK kr19,097.66
You save:  ISK kr5,456.86
DH1,002.59
List Price:  DH1,403.66
You save:  DH401.07
L1,770.35
List Price:  L2,478.57
You save:  L708.21
ден5,647.93
List Price:  ден7,907.33
You save:  ден2,259.40
MOP$805.83
List Price:  MOP$1,128.19
You save:  MOP$322.36
N$1,866.41
List Price:  N$2,613.05
You save:  N$746.63
C$3,683.13
List Price:  C$5,156.53
You save:  C$1,473.40
रु13,265.46
List Price:  रु18,572.18
You save:  रु5,306.71
S/368.80
List Price:  S/516.34
You save:  S/147.53
K382.03
List Price:  K534.86
You save:  K152.83
SAR374.99
List Price:  SAR525.01
You save:  SAR150.01
ZK2,507.99
List Price:  ZK3,511.28
You save:  ZK1,003.29
L456.51
List Price:  L639.13
You save:  L182.62
Kč2,309.58
List Price:  Kč3,233.51
You save:  Kč923.92
Ft36,119.60
List Price:  Ft50,568.89
You save:  Ft14,449.28
SEK kr1,035.66
List Price:  SEK kr1,449.97
You save:  SEK kr414.30
ARS$85,041.86
List Price:  ARS$119,062.01
You save:  ARS$34,020.14
Bs691.17
List Price:  Bs967.67
You save:  Bs276.49
COP$389,392.21
List Price:  COP$545,164.67
You save:  COP$155,772.46
₡50,975.09
List Price:  ₡71,367.17
You save:  ₡20,392.07
L2,469.97
List Price:  L3,458.06
You save:  L988.09
₲730,103.58
List Price:  ₲1,022,174.22
You save:  ₲292,070.64
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.04
List Price:  zł553.07
You save:  zł158.03
Already have an account? Log In

Transcript

Hello, everybody, in this video session, we'll talk about the transpose formula versus the Paste Special transpose the difference between the two. And the advantage of one over another. Well, at a very basic level, if you want to convert this vertical data form into an a horizontal form exactly the way it is shown, I can do that very quickly. I can copy the data, I can put my cursor on one of the blank cells, I can say right click, Paste Special. And then values if you want values at this moment, I don't want any value I want, just the way it is. But the main thing is you must choose transpose, okay, and I do that.

Now watch. If I change one of the numbers in the January of 2008, notice it doesn't change in the corresponding cell of the horizontally please state data, but it does change in the other data output, which is the table number two on the right. Now how do I make that happen? Well, the procedure is somewhat like this, if you have a simple data somewhat like this one, two, and three. And once I make that posted on my screen, I wish that it must be pasted horizontally, at the same time, it should be connected to the original cell, so that whatever changes are made here should reflect in these horizontal cells. Well, what I'm gonna do is, I am going to count how many instances of the numbers are there, there are three, so I'll choose three cells.

Then I'll write equal to transpose, that's the formula. And I'll press the Tab key to finish the entire formula. Now it asks for array. So I choose the designated array. Now when I close the bracket, do not press enter, not even Control Enter. This is supposed to work like a group formula array formula, details of which I'll discuss more in detail later.

But at this moment, they'll be to activate an array formula, Ctrl, Shift, Enter. And once I do that, Notice if I change any one of the three values, it has an equal and effect in the final output. Something similar I'm going to do, but you might ask me, Look, this was a simple data. But the data that we are working with is slightly large. It's difficult for me to remember how many rules that I need to select horizontally. Correct.

So we combine the power of both p special transpose as well as transpose formula, how I'll first choose the data. Copy that I will right click on one of the designated cells PASTE SPECIAL transpose. Okay, now I No need this pay special transpose. I did this because I can delete the data and what I am left with is the selection. This selection is important because here I don't have to count how many cells I need to select. So once that is there on board, I will straightaway type in equal to notice when I typed in equal to the selection is still on, and that's what I want.

So do not click on a cell separately. Because that will take over the selection. So at this moment I continue with our formula transpose. Now I will choose the entire array, something like this not leaving any of the cells. Once I close the bracket, I will press Ctrl, Shift, Enter. And once I do that, the change whatever change I make let's thousand all of them.

Notice all the cells where I have changed it 2000 has an equivalent effect in the table with which have got transferred as transposed formula. So I'll show this quick trick once again. First, you copy the designated cells. You do a paste special PASTE SPECIAL transpose, okay, delete immediately. What you are left with is a selection I would want start with equal to transpose the cells are still selected. I made a mistake no problem.

I'll do this again. Copy perfect. Do a paste special pay special transpose okay. Basically delete the data is to transpose correct spelling of the formula, then choosing the entire designated area and closing the brackets. Finally, before I close this entire thing Ctrl Shift Enter. So there you go.

This was transpose formula versus pay special transpose

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.