Excel Find Tool

You’ll be Amazed at what you can CTRL+F in Excel

Excel’s Find tool is one of the first features you’re likely to learn and for many of us we only use its most basic feature, which is to find a particular word or value in a cell.

It is such a diverse tool that I reckon you could almost write a whole book on it. Don’t worry I’m not going to bore you to death with every feature of the Find tool, but I am going to share a couple of gems.

Find Gem # 1 – Find Backwards

Did you know that if you hold down the SHIFT key and click on the ‘Find Next’ button Excel will find backwards?

It’s handy if you want to find the last instance of a value in a column/worksheet.

This is the kind of tip that makes me ask 'who knows that kind of stuff?'. There’s not even a hint that this might work in the tool tips.

Well, I learnt that tip from Excel MVP Bob Umlas. If you’d like to learn more tips like that then you’re in luck. Bob has written a book containing over 100 Excel tips called ‘This isn’t Excel, it’s Magic!’. Bob gave me an autographed copy when I met him at the Microsoft MVP Summit and I’ve been making my way through it since and have learned quite a few tips that are new to me already.

Click here to get your own copy of Bob’s book, or here to get his other Excel Book called ‘Excel Outside the Box’.

Find Gem # 2 – Find Blanks left by “” in Formulas

This tip was originally given to me by Drazen and then again recently by Excel MVP Ron Coderre after he read my blog post on deleting rows containing blanks returned by double quotes in formulas.

Just to recap, the blanks I’m talking about are the ones as a result of a formula like this which returns a blank if A1 is empty:

=IF(A1="","",A1)

To cleanse your worksheet of these pesky blanks:

1. Select the range of cells you want to cleanse
2. CTRL+F to open the Find dialog box.
3. Find what: <-leave this blank (or clear it if it has contents)
4. Click: Options > Look in: Values <-Without this it won’t find the text blanks
5. Click: Find all
6. CTRL+A to select ALL of the matching cells. Your Find dialog box should look something like this:



7. Press: ESC to close the Find window
8. Press: DELETE

Gone are the formulas that evaluate to "".

Note: if you have pasted your formulas as values then you can skip step 4 above.

And with only keystrokes:

CTRL+F > ALT+I > CTRL+A > ESC > Delete

Thanks to Drazen and Ron Coderre for this clever tip.

Gem # 3 – Find and Replace Formats

And lastly a tip from me, did you know you can use the Find tool for formatting?

Let’s say we have a list of fruit and we want to colour the font for the instances of Apples in red. One way is to use Conditional Formatting, but we can also use the Find tool. Here’s how:

1. Select the cells you want to search, or simply have just one cell anywhere in the worksheet selected.
2. CTRL+H to open the Find and Replace dialog box > click the Options button to expand the dialog box
3. In the Find what field I’ll enter ‘apples’
4. In the Replace with row click on the ‘Format’ button and set your formatting. Your dialog box should look like this:



5. Click the Replace All button. Job done:

A variation on this is to find all values formatted in a certain way, let’s say you wanted to change all of your headings from one formatting combination (colour, font, size) to another.

1. CTRL+H to open the Find and Replace dialog box > click the Options button to expand the dialog box if it isn’t already
2. Click on the down arrow on the Format button and select ‘Choose format from cell’



3. The mouse pointer will now be a pen and you can click on any cell containing the format you want to change.
4. Next set your new format by clicking the Format button on the Replace with row, or you can click the down arrow on the Format button and select a format from another cell.

So, there you have 3 uses for the Find tool that far exceed what it appears to offer at first glance. I hope you find them useful.

Thanks

I'd like to say thanks to Bob Umlas for allowing me to steal gem #1 from his book and share it with you. And a double thanks to Drazen and Ron Coderre for gem # 2 which, after all these years, has relieved me of my frustration with the double-quote blanks.

If you'd like to thank Bob, Drazen or Ron you can do so by clicking the buttons below to share the knowledge with your friends and colleagues on LinkedIn, Google+, Facebook and Twitter, or leave a comment below.