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:
To cleanse your worksheet of these pesky blanks:
- Select the range of cells you want to cleanse
- CTRL+F to open the Find dialog box.
- Find what: <-leave this blank (or clear it if it has contents)
- Click: Options > Look in: Values <-Without this it won’t find the text blanks
- Click: Find all
- CTRL+A to select ALL of the matching cells. Your Find dialog box should look something like this:
- Press: ESC to close the Find window
- 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:
- Select the cells you want to search, or simply have just one cell anywhere in the worksheet selected.
- CTRL+H to open the Find and Replace dialog box > click the Options button to expand the dialog box
- In the Find what field I’ll enter ‘apples’
- In the Replace with row click on the ‘Format’ button and set your formatting. Your dialog box should look like this:
- 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.
- CTRL+H to open the Find and Replace dialog box > click the Options button to expand the dialog box if it isn’t already
- Click on the down arrow on the Format button and select ‘Choose format from cell’
- The mouse pointer will now be a pen and you can click on any cell containing the format you want to change.
- 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.
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.
It is really fantastic. I love this.
🙂 Thanks, Hasmik.
Thank you, thank you, thank you for Gem #2! The blanks left by “” in formulas have been an irritant and now I know how to eliminate them. I can’t wait to apply this new trick!
Thanks, Lisa. Glad you will find it useful too.
#2 is awesome! Just a little twist could make thing so different. Thanks for sharing!
For #3, I think that is another “under-valued” function of Excel which, when used properly, could save us huge amount of time.
Another good one, MF. Thanks for sharing your tip.
More hidden gems pulled from the Pandora’s Box of Excel!
Tip #3 is interesting. What’s weird is that if you Find “apples” and Replace with a format of say bold red font (but leave the Replace with text field blank), Excel will merely change the font, leaving “apples” intact in each targeted cell. If you had the same setup but without the Format change, Excel would replace “apples” with blank! So it seems that as long as the user specifies something to change in EITHER the text OR the format selection fields (or both), then Excel will effect a Replace (i.e. the text & format fields are not seen as independent of each other), whereas my initial expectation was that “apples” would be replaced with blank, making the format change somewhat superfluous.
That’s interesting. I hadn’t noticed that twist.
Thanks for sharing.