Efficient Searching (Excel 2007/2007 Training)


When you need to search for specific information in a worksheet, the typical way to do this is to press CTRL+F to display the Find and Replace dialog, type what you are looking for and click Find Next.

in larger worksheets, if there are many instance of what you are looking for, you may have to click the Find Next button many times before you find what you are looking for.

Most Excel users don’t realize this but you can limit your search to a specific range of cells. For example, you may know that what you are looking for is located in a specific column but, by default, Excel searches the entire worksheet. This may not be a problem on a relatively small worksheet but if there is a lot of data in the worksheet it could possibly take a lot of clicking.

To limit your search to a specific range, you simply select the range you want to search before pressing CTRL+F.

For example, you may need to search for a last name Smith (last names are in column C) but you don’t need to find ‘Smith Crescent’ in column D or ‘Smith Falls’ in column E. You can limit your search range by selecting column C before pressing CTRL+F.

Find the last occurrence of something

One thing I find myself doing often is searching for the last occurrence of something in a worksheet. One option is to repeatedly click Find Next until you find it. A faster way is to start from the bottom and search backwards.

Need to search the entire worksheet?

1) Press CTRL+END to go to the last used cell on your worksheet;

2) Press CTRL+F to open the Find & Replace dialog and type what you want to search for;

3) Hold down the SHIFT key while you click Find Next and Excel will search backwards.

Need to search a specific column?

1) Click the column heading. By default, the cell in row 1 is now the Active Cell;

2) Press CTRL+F to open the Find & Replace dialog and type what you want to search for;

3) Hold down the SHIFT key while you click Find Next and Excel will search backwards from the Active Cell. Since the Active Cell is in row 1, the search jumps to the last row in the column and searches upward.

Need to search the entire workbook?

Here’s something else I find most Excel users are unaware of. You are not limited to searching each worksheet individually. You CAN tell Excel to search the entire workbook.

There are two ways to to this.

You can either select all of the sheet tabs by right-clicking a sheet tab and choosing Select All Sheets or you can select the Workbook option from the Within dropdown on the Find dialog.

Search (or Find and Replace) the enire workbook