Let’s take a look. To find a value in Excel, use the Find and Replace dialog box. You can access this dialog 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, and 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 include the 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 treats case as important and only finds values that begin 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 the 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 in the current selection. For example, if we select just a subset of our table, Excel will only search in that selection. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
