Exceljet

Quick, clean, and to the point

How to find all matches at once in Excel

Excel has a great feature that allows you to find all cells that meet your find criteria in one step.

Let's take a look.

When using the Find and Replace dialog box in Excel, there are actually two options for finding matches - Find next, which we've already covered, and Find All.

The Find All button will build a list of every cell that meets the current search criteria and report a total at the bottom.

In this case, there are 19 cells that match the search for Ann.

The results list includes the name of the workbook, the name of the worksheet, the name assigned to the cell, if any, the value in the cell, and, finally, the formula in the cell, if any.

You can resize the window to show more files in the list.

If we click on any item in this list, Excel will immediately select that cell in the worksheet.

We can also step through each item in the list using the up and down arrow keys.

The "Find all" results list respects the current search options. If we enable Match case, and click Find all again, the number of cells that match the criteria is goes down to 9.

If we further restrict the search to Match entire cell contents, results are reduced to 8, since "Annie" is now excluded from results.

Finally, you can select more than one item in the list, using shift, or control to extend the selection. For example, we can click the first item, hold down the shift key, and click the last item in the list.

With multiple cells selected, you can enter a value into the Formula bar, then press Control-enter to replace the contents of all selected cells at once.

This is similar to using "Replace all", but with the flexibility to manually select just the cells you'd like to update.

Also note that once you've selected cells, you can close the Find window and Excel will keep these cells selected.
 

Course 
Core Excel

Related shortcuts

Author 
Dave Bruns