Exceljet

Quick, clean, and to the point

How to find a value in Excel

In this lesson, we'll look at how to find things in Excel.

Let's take a look.

To find a value in Excel, use the Find and Replace dialog box. You can access this dialog by using the keyboard shortcut control-F or, by using the Find and Select menu, at the far right of the Home tab on the ribbon.

Let's try looking for the name Ann. Nothing happens until we click the Find next button. Then, each time we click, Excel finds another match.

Note that Excel finds other names that contain ann as well - Annie, Ann, Danny, then Hannah. If we continue clicking Find Next, Excel will eventually return to the first match.

By default, Excel searches left to right, first through rows, then columns. You can hold down the shift key when you press "Find next" to move backwards.

Now let's review the Find options.

The first option allows you to restrict the search to the current worksheet or, expand it to the include entire workbook. If we select workbook, Excel will now find matches on Sheet 2.

The "search" option determines the order that Excel looks through cells. The default is "By Rows". If we switch to "By Columns", Excel will find all matches in one column before moving on to the next column.

There is also an option to search Formulas, Values, and Comments. We'll look at these in a future lesson.

If we enable "match case", Excel treat case as important, and only find values that beginn with a capital A.

If we enable "Match entire cell contents", Excel will only find cells where the value is exactly 'ann'. This is a good way to find only the name Ann.

With the Find dialog closed, you can still find things with the keyboard shortcut Shift F4. Excel will use current Find settings and select the next match.

Finally, it's important to understand that if you have more than one cell selected, Excel will search only the current selection. For example, if we select just a subset of our table, Excel will only search in that

However, if you switch "Within" to workbook, Excel will drop the current selection and search all cells. At this point, you can switch back to Worksheet to search only the current worksheet.

Course 

Related shortcuts

CtrlShiftF4
G
ShiftF4
G
Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.