Find All Matches at Once in Excel: Step-by-Step

This guide shows you how to locate every cell that matches a search term, value, or condition in Excel — all at once, without manually scanning each row. You’ll learn the built-in tools (Find & Select, Go To Special, and filters) that instantly highlight or list all matching cells, and how to handle partial matches, case sensitivity, and multiple criteria. No prior Excel expertise is needed, just a basic understanding of selecting cells and using the ribbon.

What You’ll Need

• Microsoft Excel (2016 or later for the steps as written; older versions have similar functions).
• A worksheet with the data you want to search.
• Basic familiarity with selecting cells and using the ribbon menu.

Step-by-Step

1. Open your worksheet and click anywhere inside the data range you want to search. If you want to search the entire sheet, click any single cell (Excel will default to the whole sheet).
2. Press Ctrl + F to open the Find and Replace dialog box. This is the fastest way to access the search tool.
3. In the Find what field, type the text, number, or value you’re looking for. For example, type Pending to find all cells containing that word.
4. Click Options >> to expand the dialog and see more search settings. Here you can adjust:
   • Within: Choose Sheet or Workbook to search the entire workbook.
   • Search: By Rows or Columns (usually leave as By Rows).
   • Look in: Select Formulas (default) to find values as they appear in the formula bar, or Values to find the displayed result.
   • Match case – check this if you want an exact case match (e.g., “apple” vs “Apple”).
   • Match entire cell contents – check this to find only cells that exactly equal your search term, not partial matches.
5. Click Find All (not just “Find Next”). A list will appear at the bottom of the dialog showing every matching cell, along with its sheet, cell reference, and value.
6. To select all those matches at once, click the first result in the list, press Ctrl + A (or click the top entry and drag to the bottom while holding Shift). This highlights all matching cells in your worksheet.
7. If you want to highlight the matches permanently (e.g., with a fill color), with the cells still selected, go to the Home tab, click the Fill Color icon (paint bucket) and choose a color. Or use Conditional Formatting for dynamic highlighting (see Tips).
8. To close the dialog, click Close. Your selected cells remain highlighted.

For more advanced “find all matches” (like finding all cells that contain a specific word within a sentence), you can use the Filter feature:

9. Select your data range (including headers), go to the Data tab, and click Filter (or press Ctrl + Shift + L).
10. Click the drop-down arrow on the column you want to search. In the search box (or the list), type or select the value you’re looking for. Excel will instantly hide all rows that don’t match, showing only the matching ones.
11. To copy or delete these filtered matches, select the visible rows, then copy or delete as needed. Remember to clear the filter when done (click the filter icon and choose “Clear Filter”).

Common Mistakes

• Forgetting to check “Match entire cell contents” – if you search for “Cat”, you’ll also get “Category” and “Catfish”. Use the checkbox when you need exact matches.
• Searching in the wrong “Within” scope – if you only want the current sheet but the search is set to “Workbook”, you might get unexpected results from other sheets.
• Not using “Find All” – using “Find Next” repeatedly – this only jumps one by one, not showing all at once.
• Forgetting to select all results before closing the dialog – if you close the dialog without pressing Ctrl + A, the selection is lost.
• Using Filter on a range that includes blank rows – this can cause the filter to apply incorrectly, hiding rows that should be visible.

Tips

• Use wildcards – in the Find what field, you can use ? (any single character) and * (any sequence of characters). For example, s* finds all cells starting with “s”.
• Highlight with Conditional Formatting – if you want matches to be highlighted automatically as you type or when values change, use Conditional Formatting with a formula like =ISNUMBER(SEARCH("Pending",A1)) applied to your range.
• Search within a specific column – before pressing Ctrl + F, select the entire column (click the column letter) to limit the search to that column.
• Use “Find All” to jump to a match – clicking any result in the list will jump to that cell, which is handy for quick navigation.
• Copy the list of matches – after pressing Ctrl + A in the results list, you can press Ctrl + C to copy the cell references and values to another sheet.

Troubleshooting

• Find All returns no results – Double-check your spelling, ensure you’re searching the correct sheet, and check if you have “Match entire cell contents” checked when you actually want partial matches. Also verify that the “Look in” is set to “Values” if you’re searching for displayed numbers rather than formulas.
• Matches are not being selected when I press Ctrl+A – Make sure the focus is inside the results list (click on any result first). If you press Ctrl+A while the cursor is in the “Find what” field, it will select the text in that field instead.
• Filter is not showing all matches – Ensure your data range has no blank rows or columns within it. If there are, select the entire dataset manually and then apply the filter.
• Case-insensitive search not working – If you want case-insensitive search, leave “Match case” unchecked. If you want case-sensitive, check it. Excel defaults to case-insensitive.
• Search finds too many matches – Use more specific terms, or enable “Match entire cell contents” to narrow down.

Frequently Asked Questions

1. Can I find all matches across multiple sheets at once? – Yes, set “Within” to “Workbook” in the Find and Replace dialog. The results list will show matches from all sheets.
2. How do I find cells that contain a specific word, not just exact matches? – Leave “Match entire cell contents” unchecked. For example, searching “apple” will find “apple pie” and “green apple”.
3. Can I use wildcards in Find All? – Yes, you can use * (asterisk) for any sequence of characters and ? (question mark) for any single character. For example, a*e finds “apple” and “axe”.
4. How do I highlight all matches with a color? – After using Find All and selecting all results (Ctrl+A), go to the Home tab and click the Fill Color icon. Alternatively, use Conditional Formatting for automatic highlighting.
5. Why does Find All not find numbers that are formatted as text? – If a number is stored as text, you may need to search for it as text. Try using the “Look in” option set to “Values” and ensure the cell format is not causing a mismatch.
6. Is there a way to find all duplicates at once? – Yes, use Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights all duplicate cells in the selected range.

Related

• Find Hidden Apps on Samsung Galaxy Phones
• How to Customize iPhone: A Complete Guide
• How to Add and Assign Roles on Discord