Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: A Fast Find-Next.
You already know how to find information in an Excel worksheet by using the searching features built into the program. Using the Find tool (press Ctrl+F) you can search for just about anything in your worksheet. I find myself using Find quite often in the course of editing a worksheet, particularly if the worksheet is quite large.
There is one thing about Find that bothers me, however. I hate having the Find and Replace dialog box block part of my worksheet as I am stepping through occurrences of a search string. Clicking on Find Next works great, but that bothersome dialog box is still blocking my view.
To overcome this, I generally do the following when I am searching for something:
This procedure works the same as clicking Find Next repeatedly, and it is just as fast, but it gets rid of the annoying Find dialog box.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3426) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: A Fast Find-Next.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel provides some great tools for finding information in a worksheet or a workbook. Changing the default settings used ...
Discover MoreDo you need to find cells that are formatted with a particular color? How you accomplish this task depends on your ...
Discover MoreWildcard characters can be used within the Find and Replace tool, but what if you want to actually search for those ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-07-10 14:27:44
Neil
Thanks Allen, the box in the way has annoyed me too. Great tip!
2017-07-10 09:11:24
Shandor
I do what Dennis does, since I'm usually looking for several items and the Find All list conveniently shows them all, without causing a blocking problem.
2017-07-10 06:27:46
JD Murphy
To find the previous occurrence, I press Shift+Ctrl+F4.
2013-05-28 08:08:46
Joe Marten
I also like to use the Find All button on the F&R dialog. I often use the Control +H to display the dialog. But if you want to display the all directly you can use
Alt + HFDRI.
Looks like a lot letters but here is how i can remember Alt + H for the home ribbon
FDR like the President's initials and then finally the I
Alt +HFDRI
2013-05-27 14:36:19
Dennis
If you want to see all the "found" items more concisely, you can choose the Find All button in the F&R dialog rather than the Find button. Excel will display a list of all cells with the search term and each cell's value. So you might be able to find what you want just by visually skimming the list. Alternatively you can select items from the list to have Excel go to the cell. After selecting one item try pressing the down-arrow to go to the next item. Finally, the F&R dialog has an Options button to let you expand the search to the whole workbook.
2013-05-26 04:36:05
Mike
I'd never thought of that, great tip.
I have another issue regarding find & replace. If I use F&R the for the first time, the cursor starts in the Find box, I enter what I want to find, press tab, enter what I want to replace it with.
But when I press CtrlH the next time, the cursor again starts in the Replace box. If I'm starting a new F&R, this is irritating, as I have to then click in the Find box before I start. Is there a way that I can get the cursor to always start in the Find box, rather than Replace?
The one option I can think of is always press CtrlF, enter the new Find data, press CtrlH, and enter the replace data. There has to be an improvement on this.
2013-05-25 07:24:24
AGlewis
This is an excellent tip, thanks Allen.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments