Find & Replace – Cell Format

Advanced Excel Crash Course Section 12: Data Cleaning
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 particular video session, we'll talk about one of the challenge questions from the view of Find and Replace. And let me make sure that this is question is very much clear before I proceed with the solution. Now, what I'm going to do is I'm going to randomly select certain cells and make them yellow. Okay, even this one, if somebody asks you that all the cells which have been colored yellow, you want to replace the color with green. Now, how do you do that?

How do you replace the color not the data, just the color from yellow to green? Let's find out. If I choose the data Ctrl H, I go to the Options tab. Why? Because I'm not concerned with the data or contains the cell contents. I'm concerned with the color so I go to option.

You notice on the right hand side, there's a drop down under format. Well, it says format either you choose from format of this current cell, which I tried to avoid. Why because there are minor differences. Let's say the border on the right or maybe bold that can be Get into the sample format. So I go to the format. Okay, I go to Phil and I pick up the same yellow color and I press OK. Notice there is nothing written in this particular box, it's just the preview color that you're able to see right now.

Now you see find what any cell any data have with the cell color yellow and replaced with what with a certain color green. Okay, once I do that, I say replace all notice what is going to happen? Yep, as I had required, now, it happens. Sometimes your staff or your colleague may have colored certain cells red or green depending on certain logic and you want to replace that color with some other color. So, there is no other technique other than macros, which helps you do that. So this is a very fascinating technique, you can find a certain format which you want to change to a certain other format.

And I can take a similar example for example, I want that whichever data are in Times New Roman, let's time hero. A lot of the fonts that I've used, and that should get converted into Arial font. Okay, if that is so I go ahead to the radar Ctrl H, I'm going to clear the fine format and I'm going to clear the Replace format. Now I go ahead and I say format. Font should be Times New Roman. Now I'll have to click on this box and find Times New Roman.

It doesn't take me there. Let me just quickly find out lm m, p q rst. Should be there down. Okay, thank you, Roman. Okay, once I've done so, and this is the preview, what I want to change this with is a different font, let's say Arial. Yep, there you go.

Arial. And just to see the difference, what I'm gonna do is make the font a little bigger than usual, as I press OK. So this is the sample which you want to find, and this is what you want to replace with, say replace all. Yep. It's not only replaced with the data As we had seen in the previous video is also works with format of the data, how fascinating, try different techniques. But make sure after you've completed the exercise, please please please clear the fine and the clear replace format if you don't do so, the next time you want to apply a certain technique, although when you press Ctrl H, it doesn't show you the format, so it doesn't get applied the correct way.

And I just take few seconds to illustrate that. For example, if I press Ctrl H, I go to option I go to Format, I choose a format, let's say fill color yellow, and he plays with a fill color green. You apply this when good. Are there is no yellow, hence it's not being able to show me let me say format. Go to green. replace that with some other color, in this case blue.

Okay. And I say replace all once done. Let's say you did the exercise. Then you went to the second tab and then you press Ctrl H under option if I click away this options you will not get to see this format option and if you say let's say find what m replaced with R, it says no such data has been found. Why because it is still looking for the letter M, which is in cell green. So make sure you first clear fine and clear replace format after you've performed this color, replacing exercise.

And that's all about the Find and Replace the options within

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.