馃攳
Excel's Find and Replace (surprising features) - YouTube
Channel: Leila Gharani
[0]
Today's video is about
Find & Replace in Excel.
[3]
Now you're probably
familiar with this feature
[5]
from Office applications,
[7]
so you will be wondering is
it even worth doing a video
[11]
on Find & Replace?
[12]
Isn't it super straightforward?
[15]
I think you're gonna be surprised.
[18]
(upbeat music)
[21]
Let's use these demo
files to do some examples.
[24]
First question, how do we
get to the Find dialog box?
[28]
We can go to Home, Find & Select
[31]
and click Find from here
[32]
or we use the shortcut key Control + F.
[36]
You're probably familiar
with the Find functionality,
[39]
not just from Excel but also
from other Office applications
[43]
so I'm not gonna bore you with that.
[45]
What I just wanna show you
[46]
are some additional options
that can come in really handy
[50]
in Excel and they're hidden
under these tab options.
[54]
So, if you're looking for something,
[55]
let's say I'm looking for leila,
[57]
I can decide if I wanna look for it
[59]
within the sheet or within
the entire workbook.
[62]
So, by setting this to Workbook,
[64]
it goes through every single tab
[66]
and it looks for the name
that you typed in there.
[69]
You can search by rows or by columns,
[71]
that just decides the search direction,
[73]
so if it should go this way
[75]
or if it should go this way.
[76]
This part, look in, can come in handy
[79]
especially if you're looking
for something inside Comments.
[83]
So, let's say I wanna look
for leila inside Comments.
[86]
I just have to switch this to Comments
[88]
and I'm gonna switch this as well
[90]
and click on Find All or Find Next.
[94]
The advantage of Find all is
that it gives you the list
[98]
of all the occurrences
[100]
and Find Next just jumps to the next one,
[103]
so there is a comment here
[104]
and it has my name in there.
[107]
There were two other options in there
[109]
which was Formulas and Values.
[111]
These are pretty much the same
[113]
except that formulas also
looks at the formula text.
[118]
If I had this on Formulas
[119]
and I wanna look for leila,
[121]
and I click on Find Next,
[123]
it goes through every tab in the workbook
[126]
and it doesn't find there
[128]
because leila only exists
inside the comment.
[131]
The list that you get here
[133]
can also come in really handy,
[134]
so let's say instead of leila,
[136]
I'm gonna look for game
[138]
and I'm just gonna look
for it on this sheet
[141]
and not in Comments but in Formulas
[143]
and I'm gonna click on Find All.
[145]
This gives me all the occurrences
[148]
that game has occurred on this sheet.
[151]
The good thing is this.
[152]
I can press Control+ down
to highlight everything here
[156]
and it doesn't only highlight it here
[158]
but notice it also
highlighted it on my sheet.
[161]
So, if I leave this, I can see
all of these are highlighted
[165]
and I could change the color of the cells
[168]
to yellow or do any
other type of formatting
[171]
that you'd like.
[172]
Another thing that can come in handy
[174]
is if you're searching
and replacing something
[177]
and you also wanna change the formatting,
[179]
let's go back to our dialog box
[181]
but this time let's jump to the
Replace dialog box directly.
[186]
So, what's the shortcut for that?
[187]
It's Control + H.
[189]
But here let's replace game with Health
[193]
but instead of just
replacing the words here,
[197]
let's also change the formatting,
[199]
so on the right-hand side,
[200]
we have the ability to decide
the format that we want.
[204]
Click on Format here, I'm gonna go to fill
[207]
and I'm gonna select New
Color and go with that.
[211]
Now I'm just gonna click Replace All
[213]
for everything that's on this worksheet.
[216]
And it made 12 replacements.
[220]
This part of searching and replacing
[223]
for specific formatting
can come in really handy.
[226]
For example, let's say
that your company decided
[230]
to change its logo color
[232]
and you need to replace the
colors that you used before
[236]
with the new colors.
[237]
So, let's say in this case,
[239]
I have this light blue color used here.
[242]
I'm using it on a lot of tabs
[244]
and I need to replace these
with a light green color.
[247]
Here I can use Find & Replace to do that.
[250]
I'm gonna go back with Control + H.
[253]
Since I'm not looking for specific words,
[255]
I'm just gonna delete them from here
[258]
but instead I wanna look
for a specific formatting.
[262]
I'm not exactly sure what
type of blue this is,
[265]
so I'm gonna click on Format
[267]
and say Choose Format from Cell.
[269]
So, I can be lazy here
and just click on this,
[273]
I picked up that format,
[274]
now I'm gonna go and decide the format
[276]
that I wanna have instead.
[278]
Let's go with this lighter green.
[281]
On this sheet, I'm gonna replace it all,
[284]
it made five replacements.
[287]
It didn't touch these
and it didn't touch this.
[290]
Why?
[291]
That's because it picked up all attributes
[294]
of the cells, it wasn't just
the color that it picked up
[296]
but it picked up the number formatting,
[298]
so notice this one has percentage
[300]
and not number, this one is bold,
[303]
so it also has a different
formatting to this.
[306]
This means that if you
just wanna change the color
[309]
and not take a look at the
other aspects of the cell,
[312]
then make sure that you actually just pick
[315]
that specific color from the dialog box.
[318]
So, let's go and check what
color blue this actually is.
[322]
I'm just gonna go to the Fill Color here,
[324]
go to More Colors, under Custom here,
[328]
I need this code, so that's the RGB,
[331]
let's memorize this and let's go back
[333]
to our Find & Replace,
I'm gonna go Control + H
[336]
and the first thing I'm gonna do
[338]
is to completely clear
this formatting here,
[342]
now I'm gonna go to Format
[344]
and pick up that color that we saw.
[347]
So, under Custom, let's type in that code.
[352]
Now let's do this for the entire workbook,
[355]
click on Replace All.
[358]
That looks through every single tab
[361]
and wherever it found that color,
[364]
it replaced it with the new color.
[366]
So, you see, Excel's Find
& Replace can do a lot more
[370]
than just search for text.
[372]
I can also search and replace formatting
[376]
which can come in really handy too.
[378]
Let me know in the comments
[379]
if you've used this aspect before.
[382]
And if you like this video,
[383]
click on that thumbs up
[385]
and do consider subscribing
if you would like
[387]
to get updates when I
post new videos here.
[391]
(upbeat music)
Most Recent Videos:
You can go back to the homepage right here: Homepage