0603 Sort Trick - Add Alternate Blank Rows In-between Existing Rows

Advanced Excel Crash Course Section 6: Sort and Filter
2 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
€92.26
List Price:  €129.16
You save:  €36.90
£78.90
List Price:  £110.47
You save:  £31.56
CA$135.76
List Price:  CA$190.06
You save:  CA$54.30
A$153.66
List Price:  A$215.14
You save:  A$61.47
S$134.30
List Price:  S$188.03
You save:  S$53.72
HK$782.11
List Price:  HK$1,094.99
You save:  HK$312.87
CHF 88.79
List Price:  CHF 124.31
You save:  CHF 35.52
NOK kr1,070.17
List Price:  NOK kr1,498.28
You save:  NOK kr428.11
DKK kr688.01
List Price:  DKK kr963.24
You save:  DKK kr275.23
NZ$165.58
List Price:  NZ$231.83
You save:  NZ$66.24
د.إ367.20
List Price:  د.إ514.10
You save:  د.إ146.89
৳11,008.59
List Price:  ৳15,412.47
You save:  ৳4,403.87
₹8,303.29
List Price:  ₹11,624.95
You save:  ₹3,321.65
RM473.19
List Price:  RM662.48
You save:  RM189.29
₦156,506.34
List Price:  ₦219,115.14
You save:  ₦62,608.80
₨28,049.28
List Price:  ₨39,270.12
You save:  ₨11,220.83
฿3,608.38
List Price:  ฿5,051.88
You save:  ฿1,443.50
₺3,233.78
List Price:  ₺4,527.43
You save:  ₺1,293.64
B$503.03
List Price:  B$704.27
You save:  B$201.23
R1,898.90
List Price:  R2,658.54
You save:  R759.63
Лв180.15
List Price:  Лв252.22
You save:  Лв72.06
₩133,999.91
List Price:  ₩187,605.24
You save:  ₩53,605.32
₪366.35
List Price:  ₪512.91
You save:  ₪146.55
₱5,593.09
List Price:  ₱7,830.55
You save:  ₱2,237.46
¥15,066.66
List Price:  ¥21,093.92
You save:  ¥6,027.26
MX$1,690.23
List Price:  MX$2,366.39
You save:  MX$676.16
QR365.81
List Price:  QR512.15
You save:  QR146.33
P1,369.30
List Price:  P1,917.08
You save:  P547.77
KSh13,273.67
List Price:  KSh18,583.67
You save:  KSh5,310
E£4,710.13
List Price:  E£6,594.37
You save:  E£1,884.24
ብር5,696.11
List Price:  ብር7,974.78
You save:  ብር2,278.67
Kz83,441.65
List Price:  Kz116,821.65
You save:  Kz33,380
CLP$94,734.02
List Price:  CLP$132,631.42
You save:  CLP$37,897.40
CN¥719.86
List Price:  CN¥1,007.84
You save:  CN¥287.97
RD$5,933.04
List Price:  RD$8,306.49
You save:  RD$2,373.45
DA13,480.42
List Price:  DA18,873.13
You save:  DA5,392.71
FJ$226.79
List Price:  FJ$317.51
You save:  FJ$90.72
Q783.08
List Price:  Q1,096.35
You save:  Q313.26
GY$20,985.05
List Price:  GY$29,379.92
You save:  GY$8,394.86
ISK kr13,738.62
List Price:  ISK kr19,234.62
You save:  ISK kr5,496
DH1,007.92
List Price:  DH1,411.13
You save:  DH403.21
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,685.15
List Price:  ден7,959.43
You save:  ден2,274.28
MOP$807.89
List Price:  MOP$1,131.08
You save:  MOP$323.19
N$1,891.51
List Price:  N$2,648.20
You save:  N$756.68
C$3,691.90
List Price:  C$5,168.80
You save:  C$1,476.90
रु13,305.26
List Price:  रु18,627.89
You save:  रु5,322.63
S/369.78
List Price:  S/517.70
You save:  S/147.92
K378.11
List Price:  K529.37
You save:  K151.26
SAR374.99
List Price:  SAR525
You save:  SAR150.01
ZK2,562.18
List Price:  ZK3,587.15
You save:  ZK1,024.97
L458.93
List Price:  L642.52
You save:  L183.59
Kč2,328.03
List Price:  Kč3,259.33
You save:  Kč931.30
Ft36,496.67
List Price:  Ft51,096.80
You save:  Ft14,600.13
SEK kr1,049.30
List Price:  SEK kr1,469.07
You save:  SEK kr419.76
ARS$85,200.41
List Price:  ARS$119,283.99
You save:  ARS$34,083.57
Bs693.12
List Price:  Bs970.40
You save:  Bs277.27
COP$390,541.29
List Price:  COP$546,773.43
You save:  COP$156,232.14
₡51,032.06
List Price:  ₡71,446.93
You save:  ₡20,414.86
L2,476.89
List Price:  L3,467.75
You save:  L990.85
₲731,475.55
List Price:  ₲1,024,095.03
You save:  ₲292,619.48
$U3,846.42
List Price:  $U5,385.15
You save:  $U1,538.72
zł398.58
List Price:  zł558.03
You save:  zł159.45
Already have an account? Log In

Transcript

Either. This is for those who are using salt for quite some time, I have a challenge before you. And the data is such that you have a list of the different region north, east, west and south. Now you have to convert this data into the output which have given on the right hand side that is, you need to insert a blank row after every line item. Now imagine if you're given a data with spans over thousand rows. And you were to do this through traditional method, how would you proceed, you would select the entire row, right click Insert row, or better still, you might say choose the next row and press Ctrl Shift plus sign.

This will add another row, or else you might also tell me Look, I already know about a shortcut key which repeats my last action, which is function key f4. But all these techniques are manual, because 4000 rules you have to repeat those steps thousand times. So I need us to learn a technique which allows us to create create two additional blank rows between every line item and that too quickly, irrespective of the data line in length, I would want that you pause the video, ponder over this challenge. And after you have given a thought a thought, then we'll see what the solution is. Please pause the video. So what happened?

Did you try it? Okay. If you had trouble in finding the solution, let me share the solution with you. Now the trick, the trick to be able to explain the trick, let me just simply copy this data to an extra blank sheet. Yep, there you go. Now what I'm going to do is I'm going to add another row heading, which is called DME serial number, this only for our reference purpose dummy field number.

What I'm going to do is I'm going to put one I'm going to put two thereafter and selecting both one and two. I'm going to double click at the bottom right of the selection, so that you get a perfect list of features numbers. Now, whenever you double click all the C lumber that gets generated is already selected, so I need to copy that I need to paste that. What happens as I reduce the zoom factor, you'll notice the sales numbers have come up two times because you copy paste it. Now when I choose the entire list, I'll go to data. I'll go to sort.

And I'll say, please sort by dummy serial number. Imagine what is going to happen if I sort the list by the column of WC lumber in the smallest to largest format. The ones and the ones are going to be together, followed by twos and twos and threes and threes, and so on so forth. As I press OK. This is what we were looking for. Quickly, you are able to add an extra blank row in your data and you take the same time, irrespective of how big the data is.

So try a super special trick with respect to sorting

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.