Using Excel Find and Replace in the most efficient way (2023)

Find a value in a range, worksheet, or workbook

The following tips provide guidance on how to find specific characters, text, numbers, or dates in a range of cells, a worksheet, or an entire workbook.

1. First, select a range of cells to search. To search the entire worksheet, click any cell in the active worksheet.
2. open excelfind and replacedialog by pressingControl + Fabbreviation. or go todom> tabversiongroup and clickfind and select>Come across…
   
3. wfind thistype the search characters (text or number) and click one of themfind everythinglubricantfind the next one.
   

when you clickfind the next one, Excel selects the first occurrence of the lookup value in the worksheet, the second click selects the second occurrence, and so on.

when you clickfind everything, Excel will open a list of all occurrences, and you can click any item in the list to jump to the corresponding cell.

Find cells with specific format in Excel

To find cells with a specific format, tapControl + Fshortcut to openfind and replacedialog box, clickChoicesand then click the buttonForm…in the upper right corner and set your options in Excelsearch formatDialog window.

If you want to find cells that match the format of another cell in the worksheet, clear all criteria infind thisclick the arrow next to itForm, selectSelect Cell Formatand click the cell with the format you want.

UseMicrosoft Excel saves the formatting options you set. If you're looking for other data in your worksheet and Excel can't find the values ​​you know are there, clear the previous lookup's formatting options. To do this, open the filefind and replaceclick the buttonChoiceson buttonCome acrosstab, and then click the arrow next to itForm..and selectDelete search format.

Find cells with formulas in Excel

Con Excelfind and replace, you can only search formulas for a given value as explained inadditional search options in Excel. To find cells that contain formulas, use the methodgo to specialistmode.

1. Select a range of cells where you want to find formulas, or click any cell in the current sheet to search the entire sheet.
2. Click the arrow next to itfind and selectand then clickgo to specialist. Alternatively, you can press F5 to open the fileI willdialog box and click the buttonParticular…button in the lower left corner.
   
3. wgo to specialistdialog box, selectformulas, then check the boxes corresponding to the results of the types you want to find and click OK:
   • Numbers – Find formulas that return numeric values, including dates.
   • Text: Search for types that return text values.
   • Logical: Search for formulas that return logical values ​​TRUE and FALSE.
   • Errors: Find cells with error-causing formulas like #N/A, #NAME?, #REF!, #VALUE!, #DIV/0!, #NULL! and #NUM!.

   

If Microsoft Excel finds cells that match your criteria, they will be highlighted. Otherwise, it will display a message that no such cells were found.

Advice.to find quicklyall cells with formulas, regardless of the result of the formula, clickfind and select>formulas.

How to select and highlight all records found in a sheet

To select all occurrences of a value in a spreadsheet, open an Excel filefind and replaceenter your search term in the boxfind thisfield and clickfind everything.

Excel will list the entities it finds and you click on any occurrence in the list (or just click anywhere in the results area to move the focus there) and pressControl + Aabbreviation. This will select all occurrences found in bothfind and replacedialogue and on the sheet.

After selecting the cells, you canattractionchange the fill color.

Replace one value with another

To replace specific characters, text, or numbers in an Excel worksheet, usesubstituteExcel tabfind a replacementdialogue. Detailed steps are shown below.

1. Select the range of cells where you want to replace the text or numbers. To replace characters throughout the worksheet, click any cell in the active worksheet.
2. typeCtrl + Hshortcut to opensubstituteExcel tabfind and replacedialogue.

   or go todom> tabversiongroup and clickfind and select>substitute…
   

   if you just usedSearch in Excelmode and then just switch tosubstitutepato.

3. wfind thiswrite the value you want to search in the field as wellexchangefield, type the value you want to replace.
4. Finally, click on anysubstitutereplace the occurrences found one by one orreplace allreplace all entries at once.
   

Advice.If something went wrong and you get a different result than expected, click the buttonDispose ofbutton or pressControl + Zto restore the original values.

For additional features of Excel Replacement, click the buttonChoicesbutton in the right corner of the pagesubstituteZone. They are basically the same asExcel search optionswe just talked.

Replace the text or number with anything

To replace all occurrences of a specified value withNicoEnter the characters to searchfind thisbox, let'sexchangeempty field and click the buttonreplace allbutton.

How to find or replace a line break in Excel

Replaceline breakwith a space or other delimiter, insert a new line in the filefind thisare presented by pressingControl + J. This hash is the ASCII verification code for character 10 (line break or line feed).

when pressedControl + J, at first sightfind thisthe box will appear empty, but upon closer inspection you will notice a small blinking dot as in the screenshot below. Enter the wildcard wexchangebox, for example, a space character, and clickreplace all.

To replace a character at the end of a line, do the opposite: type the current characterfind thisbox and line break (Control + J) Wexchange.

How to change cell format in worksheet

In the first part of this tutorial, we discussed how to do this.find cells with specific formatusing the Excel Find dialog box. Excel Replace allows you to go a step further and change the format of all cells in the worksheet or the entire workbook.

• Opensubstitutein Excel's Find and Replace dialog box and click the buttonChoices
• Fencefind thisclick the arrow with the symbolFormselection buttonSelect Cell Formatand click any cell with the format you want to change.
• Fenceexchangeclick the buttonForm…and set up the new format with excelform of exchangeDialog window. or click the arrowFormselection buttonSelect Cell Formatand click any cell with the desired format.
• If you want to replace the format in thecomplete workbook, selectworkbookwWbox. If you only want to override the format on the active sheet, leave the default option (Disagreement).
• Finally, clickreplace allbutton and check the result.

How to find and replace wildcards in Excel

If you want to find actual asterisks or question marks in an Excel worksheet, type a dash (~) in front of them. For example, to find cells that contain asterisks, type ~*wfind thisbox. To find cells that contain semicolons, use~?as search criteria.

This way, you can replace all the question marks (?) in the spreadsheet with another value (in this example, the number 1):

As you can see, Excel successfully finds and replaces wildcards in both text and numeric values.

Advice.To search for tilde characters in a sheet, type a double tilde (~~) wfind thisbox.