0504 Go To (Special) - Fill Intermittent Blank Cells - Case 1

Advanced Excel Crash Course Section 5: Special Cell Selections
5 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.73
List Price:  €128.43
You save:  €36.69
£78.50
List Price:  £109.90
You save:  £31.40
CA$135.42
List Price:  CA$189.60
You save:  CA$54.17
A$152.22
List Price:  A$213.12
You save:  A$60.89
S$133.77
List Price:  S$187.28
You save:  S$53.51
HK$781.89
List Price:  HK$1,094.68
You save:  HK$312.79
CHF 88.30
List Price:  CHF 123.63
You save:  CHF 35.32
NOK kr1,058
List Price:  NOK kr1,481.24
You save:  NOK kr423.24
DKK kr684.10
List Price:  DKK kr957.77
You save:  DKK kr273.66
NZ$164.15
List Price:  NZ$229.81
You save:  NZ$65.66
د.إ367.20
List Price:  د.إ514.09
You save:  د.إ146.89
৳10,978.23
List Price:  ৳15,369.96
You save:  ৳4,391.73
₹8,290.53
List Price:  ₹11,607.08
You save:  ₹3,316.54
RM471.80
List Price:  RM660.54
You save:  RM188.74
₦156,534.34
List Price:  ₦219,154.34
You save:  ₦62,620
₨27,950.66
List Price:  ₨39,132.05
You save:  ₨11,181.38
฿3,595.26
List Price:  ฿5,033.51
You save:  ฿1,438.25
₺3,229.78
List Price:  ₺4,521.83
You save:  ₺1,292.04
B$499.40
List Price:  B$699.18
You save:  B$199.78
R1,883.20
List Price:  R2,636.56
You save:  R753.35
Лв179.42
List Price:  Лв251.19
You save:  Лв71.77
₩133,286.20
List Price:  ₩186,606.01
You save:  ₩53,319.81
₪365.03
List Price:  ₪511.06
You save:  ₪146.02
₱5,561.09
List Price:  ₱7,785.75
You save:  ₱2,224.66
¥14,911.76
List Price:  ¥20,877.07
You save:  ¥5,965.30
MX$1,670.32
List Price:  MX$2,338.52
You save:  MX$668.19
QR364.82
List Price:  QR510.77
You save:  QR145.94
P1,359.04
List Price:  P1,902.71
You save:  P543.67
KSh13,398.66
List Price:  KSh18,758.66
You save:  KSh5,360
E£4,718.65
List Price:  E£6,606.31
You save:  E£1,887.65
ብር5,656.15
List Price:  ብር7,918.84
You save:  ብር2,262.68
Kz83,512.74
List Price:  Kz116,921.18
You save:  Kz33,408.44
CLP$93,810.03
List Price:  CLP$131,337.80
You save:  CLP$37,527.76
CN¥710.61
List Price:  CN¥994.89
You save:  CN¥284.27
RD$5,917.87
List Price:  RD$8,285.25
You save:  RD$2,367.38
DA13,435.55
List Price:  DA18,810.31
You save:  DA5,374.76
FJ$226.69
List Price:  FJ$317.37
You save:  FJ$90.68
Q780.94
List Price:  Q1,093.35
You save:  Q312.40
GY$20,949.18
List Price:  GY$29,329.69
You save:  GY$8,380.51
ISK kr13,659.63
List Price:  ISK kr19,124.03
You save:  ISK kr5,464.40
DH1,002.67
List Price:  DH1,403.78
You save:  DH401.11
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,652.42
List Price:  ден7,913.61
You save:  ден2,261.19
MOP$805.88
List Price:  MOP$1,128.27
You save:  MOP$322.38
N$1,866.55
List Price:  N$2,613.25
You save:  N$746.69
C$3,681.10
List Price:  C$5,153.70
You save:  C$1,472.59
रु13,266.49
List Price:  रु18,573.62
You save:  रु5,307.13
S/368.83
List Price:  S/516.39
You save:  S/147.55
K382.06
List Price:  K534.91
You save:  K152.84
SAR375
List Price:  SAR525.02
You save:  SAR150.01
ZK2,508.19
List Price:  ZK3,511.57
You save:  ZK1,003.37
L456.04
List Price:  L638.48
You save:  L182.43
Kč2,310.02
List Price:  Kč3,234.13
You save:  Kč924.10
Ft36,211.46
List Price:  Ft50,697.50
You save:  Ft14,486.03
SEK kr1,038.11
List Price:  SEK kr1,453.39
You save:  SEK kr415.28
ARS$85,066.49
List Price:  ARS$119,096.49
You save:  ARS$34,030
Bs691.23
List Price:  Bs967.75
You save:  Bs276.52
COP$389,029.38
List Price:  COP$544,656.70
You save:  COP$155,627.31
₡50,979.09
List Price:  ₡71,372.77
You save:  ₡20,393.67
L2,470.17
List Price:  L3,458.33
You save:  L988.16
₲730,160.41
List Price:  ₲1,022,253.79
You save:  ₲292,093.37
$U3,841.23
List Price:  $U5,377.88
You save:  $U1,536.64
zł395.31
List Price:  zł553.46
You save:  zł158.14
Already have an account? Log In

Transcript

In this video, let me share with you one trick which I have found extremely beneficial in my working with Excel. And in fact, wherever I've gone in terms of corporate training, this trick has been the most popular amongst all. Now, let's find out why. On the left hand side lies a series of data and notice there are several blank cells. The point is all these single line items are supplier name and the respective numbers and a lot of respective transactions against them. Now, unless I fill this blank cell with the appropriate data point, which you saw in the first line, will not be able to apply filter or source or pivot table correctly.

Why so if I choose this data set and apply filter, you'll notice at the point of filtering the list with one particular supplier number or name, let's say out metallic power. One transaction appears before me and not the three or four transaction which you are supposed to be associated with this particular supplier name. Very simple reason, because in my data set, there was only one occurrence of that particular name. So unless I fill these blank cells with the appropriate name, I will not be able to apply filter, I will not be able to correctly apply sorting. And eventually when you start working with pivot table, you will not be able to create those wonderful reports that pivot table has to offer. So, the point is, do I do this manually, of course, I can tell you a shortcut key which helps you do that fast that is simply choosing the data and the blank cells below it and pressing Ctrl D. It simply copies and pastes the data automatically.

A lot of people do that. But the point is in case you're working with a very large organization, and the data set is quite large, you cannot expect to do that for 500 It means or maybe 5000 names. Let's see a better technique that we go back few steps where we had started. So I choose the entire block of data, restricting myself to only column A and column B. Now how do I do that using a shortcut key, I put my cursor on the fourth supplier number, I press Shift, right arrow key. So shift right arrow key will help you jump one step at a time.

So shift right arrow, and appointed if you press Shift control, and then down arrow, Down Arrow, Down Arrow, it goes one step at a time. If you have 5000 names, it may be a little tedious. So let me share the best trick. Starting Point, holding the first supplier number cell, Shift Ctrl N the key on the top right corner of the keyboard you'll find empty shift Control Enter. it chooses the last use cell. Now the advantage is you have been able to cover significant area of the data.

Now you have the power of shift left left, shift left left left until you are holding the correct region you are supposed to after this Ctrl G will go to special will choose blanks. Now the logic is all these blank cells should hold formula which says wherever you are, feel free to borrow the value from the above cell. Now, what I've done is I've started to put an equal to please note that still at this point, all my blank cells are selected equal to above cell. And if I press Ctrl, enter, what just happened? It created a chain of formula and every blank cell says please follow the value from the above cell immediately above sell. The logic otherwise would have been executed.

Like this equal to about sell, enter equal to above sell enter, but that would have taken significant amount of time. So do we go very quickly cap How did I achieve this, I choose before cell Shift Ctrl N key, shift left, left left left Ctrl G, I'll press S, because that will help me click on the button special or an esky. Thereafter, the option blanks the key letter key has been underlined, I simply press key and I'm going to press enter. So far, I have not used the mouse. Now immediately I press an equal sign and I choose the value from the above sale. Now the last and final part Control Enter works, although I will still have one recommendation for you, in case you ever encounter this problem, that is these cells which you currently filled are still holding formula.

If the first cell gets deeded, the chain gets broken You might be able to see 0000. If you don't want that to happen, make sure that you've also paste special value. So I select the first number, shift right, Shift Ctrl down. I copy this, and I'm going to use the shortcut key Ctrl, Alt, V all pressed together, immediately it gives me a special box. The third item values the letter V has been underlined. So I press V from the keyboard, and there I go, enter.

Now, none of these cells are containing formulas they are pure and pure data. Now Feel free to use sort filter pivot table, we stops you. So let's see how this has an advantage compared to where we started off with the filter. I now click on the same vendor name of Mattel power. This time, I get exhaustive number of transactions against that particular supplier name. try this out.

Very importantly,

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.