Excel Magic Trick # 160: Find & Replace Format or Formula

Find all the occurrences of a word, number, format or formula and then change or replace all of them! See how to use the Find and Replace feature in Excel.
Find, Replace, Go To, GoTo, Find All, Replace All Change All Edit All.

---

Closed Caption:

welcome to XL magic number a hundred and
sixty hey if you want to download this
workbook and follow along
click on my youtube channel and click on
my college website link and you can
download the workbook XL magnetrixx 155
to 167 k and trick number 160 want to
see how to use replace replace and we're
going to see four different
possibilities
it's a great feature it's similar to
find now what I like to do is start off
by trying to find a particular
formatting and the fine feature will see
how to find format a number of formula
and a word and we're going to use the
select all feature in replace now let's
highlight this whole range what I want
to do and this is a little small table
imagine if we had a gigantic table and
how awesome this trick would be I want
to find all of the purple with the
border and a white font find all of it
and then select it
the great thing about this trick is you
could search through a whole spreadsheet
and then just since it will select all
of them you can then change the format
now find and replace or over here and
edit in right here this little drop-down
fine replace go to all of these here
we're going to use replace i always use
ctrl H but in if you know ctrl f which
is all you need for fine then the
replace tab is right behind it now in
2003 you go to the Edit menu I'm going
to control want to have it highlighted
ctrl-f now i remember H and I use it all
the time because they use replace all
time but if you just don't want to
clutter your mind with keyboards
remember ctrl-f because then you can get
to replace i'm going to ctrl h and what
I want to find find what I don't see
something for format i click options and
I want to go in this cell right here i
want to say the format choose format
from cell no way i'm simply going to
click there and now it found it
now i want to say find all and sure
enough it finds them all and down here
it's got immediately shows you all the
cells and i'm going to control a and
then I close it
what that did by controlling a after
clicking find all is it highlighted all
of them now I can go ahead and add my
different format
i'm going to add this dark one here like
that
now the next thing we want to do is for
use that same feature but find all of
the numbers control eh i'm going to get
rid of this clear fine for mac to say
find 0 and then i'm going to say find
all so really we're finding all we're
not replacing you could use replace to
which we will do in a second but right
now we're just finding all once you see
this down here control a and then i can
close it and look at that it found them
all loops that doesn't work because it's
got a bunch of we found 20 there so
let's try that again highlight the whole
thing be careful of that control age now
what do we want we want to say match
entire cell content find all control a
and then closed now I want to just hit
the Delete key now that they're all
highlighted just want to hit delete
because I really want to get rid of
those alright let's try it again but
with a formula and there's a bunch of
sums here now we're going to come you
have a whole big spreadsheet you
highlighted ctrl h and what I want to
find is equals s um open parenthesis and
watch this
I want to replace it with equals hope I
can spell here and look in what formulas
it's definitely going to look in the
formulas and it will actually replace
all of those i'm going to say replace
all
oh it that when their match entire cell
contents I don't want that i want just
part of it and then replace all and sure
enough
xls complete the circle makes 13
replacements click ok click OK
sure enough look at that it changed all
of them to average is that not amazing
now one last thing let's find words
control
I'm sorry our control h and i want to
say fine what I want to find the word
total and I want to replace it with
average and then be careful sometimes
replace all can be disastrous you know
if we had click that replace all when we
are doing zeros that would have just
replaced them if we weren't paying
attention sometimes replace all can be
deadly
i'm going to try a find all again just
to see that that sometimes the safe won
control a that selects them all then you
can close it and you can just see and
then type average control enter notice
if you have this cell highlight in this
one if I type something in one of having
hit ctrl enter it and puts it all
that's fine and dandy we got our
averages here but we want to remind
ourselves of the average function and
how it works i'm going to double click
here and hit f2 now the average function
does not look at blank so it's not
included in the calculation if in fact
you want this to be a zero you have to
type it in here 40 right and then that
tells the average function that you
should include that in the calculation
and then it's significantly reduced we
could use our find here ctrl H find a
blank and we're going to match entire
cell contents and we're going to replace
it with a zero and then replace all and
sure enough it made three replacements
another way to do that would be to
highlight it and hit the f5 to which is
go to special blanks and click OK
and then type zero control enter now you
you want these zeros here only when
there were actually 0 sales and you want
that zero included in the average
calculation all right time when we come
back in our next trip will do all the
cool tricks for go to write see you next
time