When it comes to tables, calculations or formulas, you usually work with Excel. The famous spreadsheet program is part of the Microsoft 365 package and is available for Windows and macOS operating systems. It is an ideal tool to clearly structure values , especially if they are numerous. However, when files include a lot of data, they can get a bit confusing. In this case, if you need to find certain combinations of letters or numbers and substitute them, you can use the find and replace functions in Excel, which allow you to do it with just one click of the mouse. Next, we explain them step by step..
Both functions are closely related . To run them, you can use the corresponding option in the menu or various keyboard shortcuts. The two functions are in the same window, although in different tabs. Follow these steps to access and use them:
Do you have a large Excel table with many values and you are looking for a specific combination of letters and numbers? The Search function is the ideal tool for this. To open it, select the? Home? Tab. from the menu, and then open the drop-down menu for? Find & Select ?. Press? Search? to open the window, where you will find all the options.
You can open this function even faster using a keyboard shortcut: press? Ctrl? and? B? on the keyboard at the same time..
This opens the? Find and Replace? Window. in Excel. You will automatically find yourself in the "Search" tab. To find a specific combination of letters and numbers, enter it in the box? Search ? and then click the button? Find next ? or press the return key on your keyboard. Excel will highlight the first cell that contains the term. Every time you click on? Find next ?, the program will show you the next cell that includes it.
The function ? Search all ? it is especially useful in large Excel tables with many values. If you click on this option, Excel shows you all the cells in the table that contain the search term. If you choose one of the results by clicking with the mouse, the program will highlight the cell where it is located. Additionally, Excel offers the option to sort the results. To do this, click on the column title..
Excel offers many other possibilities to refine your search. First of all, open the advanced options by clicking the "Options" button.
In this way, Excel expands the window and offers several possibilities : if you open the drop-down menu next to? Search ?, you can set the range in which the term is searched. If you select? Sheet ?, Excel will only search the current worksheet, while if you choose? Book ?, the program will search all sheets. You also have the option to search by rows or columns, which does not affect the search results themselves, only the order. This setting only determines in which direction the search is performed, starting from the currently selected cell. Finally, Excel offers the option to search in specific ranges using? Search in ?, where you can choose between formulas, values and comments.
The formulas are not visible, because Excel only displays the results in the cell. For example, if a cell contains the Excel function SUM? = SUM (4 + 4) ?, the cell will only display? 8 ?, the result. With the function? Search in: Formulas ?, the program also finds the implicit number? 4 ?.
If upper and lower case letters must be taken into account, do not forget to check the corresponding box. When the option of? Match the contents of the whole cell? is checked, Excel only displays cells that contain only the search term.
To search for cells with a specific format , click the button? Format ?. A new window will open in which you can set the format you are looking for. If you prefer, open the drop-down menu by clicking the arrow to the right of the button. Press? Choose cell format? and then click on a cell that has the desired format.
Do you have to correct any value in the table, but don't remember exactly where it is? Do you need to replace the content of a cell more than once? With the function? Replace? you can do it easily. In this case, Excel replaces one value with a different one, after automatically searching for the content.
If you have just performed a search, the window for the replacement function will already be open: just select the corresponding tab. It is also possible to use the shortcut from the? Home? Tab. from the menu, in which you must open the drop-down menu? Find and select? and press the option? Replace ?.
To open the function? Replace? more quickly, you can use a keyboard shortcut : press the? Ctrl? and? L? at the same time.
The find and replace functions in Excel have a very similar structure. To substitute a value, first enter the contents of the cell in the? Box. Search ?. If you leave it like that, the program will only look for those values in the table. To do this, proceed as we explained in the previous sections. Instead, to replace the combination of letters and numbers you are looking for with a different one, enter it in the? Box. Replace with ?.
There are two ways to find and replace in Excel: if you select? Replace ?, you will modify the content of the cells one after another. This ensures that you have a better overview , especially for large documents with many cells.
If you are sure that you want to replace the content that you have indicated in all cells, click the button? Replace all ?. A dialog box will automatically open showing you how many cells have been modified.
If you want to remove the search term , simply leave the line? Replace with ?.
As with the search function, Excel also offers advanced settings for replacing table values, which are largely identical to the search options. Just the option? Search in? It differs a bit as it is limited to formulas and does not include the option to choose values or comments.
If several people work on the same table, it is easy for a value to be entered twice by mistake. Excel also offers a handy function to solve this problem, which allows you to eliminate duplicate values. We explain how to use it in our article on how to remove duplicate values in Excel.
In Excel documents, especially longer ones, you may want to search for an undefined term . To do this, Excel gives you the possibility of using wildcard characters . In this case, the program searches for all letter combinations that meet the indicated requirements. Here are the only wildcard characters that Excel recognizes:
The question mark represents a single undefined character . For example, if you search for? N? Nca ?, Excel will show you results like? Never ?.
The asterisk has a similar function to the question mark. The difference is that it does not represent a single character, but any number of characters : for example, if you enter? N * a ?, Excel will find? Payroll? or never?. If you need to find all the words that begin with a letter, such as S, you must enter? S * ?.
You wonder how to find these same characters when we don't want to use them as wildcards. In this case, the tilde is used, which you must enter just before the corresponding character: for example, you can search for? ~ ?? or? ~ * ?. You can also find the chip itself by entering? ~~ ?.
The complete suite of Microsoft 365 offers all the necessary office applications for day-to-day work.