Three Excel Find and Replace Tips and Tricks You Wish You’d Known Sooner
As Excel develops more ways to complete tasks, it is even more important to find shortcuts that accelerate our work. Excel’s Find and Replace feature is a powerful tool that allows users to quickly search for specific data and replace it with new values throughout an entire workbook. It offers several advanced options, such as case sensitivity and whole words search. Here are three features, though, you may not have used before:
Find Within Worksheets and Workbooks
Your insurance company is analyzing data to see where sales are coming from in the US and who to promote in each region. This is the data you are given, roughly entered by multiple sources onto multiple sheets of a large workbook:
Before you make a PivotTable to do the analysis, you need to unify inconsistent entries such as “south east” to “Southeast” and “Central” to “Midwest” across all sheets. To do this quickly, use the “Ctrl + H” keyboard shortcut to open the Find and Replace dialog window already opened to the Replace tab.
Click the Options<< button to open more search criteria.
Learn More: Easily Master Microsoft® Excel® PivotTabless
How to Create PivotTables Using the Quick Analysis Tool in Excel
Type the word you want to change in the Find What: field. In this example type: “South east”.
Type “Southeast”, the correct spelling, in the Replace with: field.
SAVE $10 AND TRAIN ON THIS TOPIC TODAY
Now, select where you want the change applied using the Within dropdown. Within: Workbook will fix errors across your entire Excel workbook file, while Within: Worksheet will only affect the data on the currently open sheet. Because you want the data consistent for all four quarters, select Workbook.
Click Replace All to apply the change to the entire workbook without reviewing each change, then repeat for any other terms that need replacing. Using the example, here is what the data looks like after running Find and Replace using Within: Workbook on all sheets:
Search and Replace Within gives you the control over what data you are affecting so only what you want is replaced.
Find and Replace Formulas and Find Values
Next, let’s build a more complex scenario: In your insurance company, You need to update data to reflect new policy terminology that has come into effect. You are asked to change the formulas for the State Code column to include this new term.
Our formula in the State Code Column creates a standardized string based on the State and Type columns, with an exception for Florida that requires a different code than the other states. It is this Florida code that’s changing from “Vehicle” to “Motor”.
Learn More: What is the Excel Concatenate Function and How Does It Work?
Unleashing the Flexibility of Excel Text Formulas
Press “Ctrl + H” to bring up the “Find and Replace” dialog.
In the Find what field, type “Vehicle”.
Type “Motor” in the Replace with: field. The Look in: dropdown is set to Formulas, and is, in fact, the only option available on the Replace tab.
Click Replace All to apply the change to the entire worksheet without reviewing each change.
You can see that the text in the formula has been replaced with the new text and applied to the cell results. (BE SURE that the text you are searching for does not appear anywhere else that you do NOT want to be replaced. Or, you can select only the range of cells that you want to affect.)
BUT, what if you want to search for the resulting state code to find an invoice number later? Using CTRL+F to find the string “motor” returns EVERY cell in the column because every cell includes the string in the formula.
Instead, we want to find the cells with the value – or result of the formula – that includes the string “motor”. To do this, type CTRL+F to open the Find and Replace dialog to the Find tab.
Change the Look in: dropdown to Values.
This returns only the two rows with Florida auto insurance. Note that you can also use the Find tab to search in notes and comments. To change the value of data that is a result of a formula, use the Find and Replace to adjust the formula as described above.
In combination, Finding and Replacing elements of formulas and then being able to find specific results of those formulas can be a powerful editing tool that will help you manage data that changes frequently and then make sure that your changes have been applied accurately.
Find And Replace Formatting
You may also find yourself in a position where you don’t need to change any data, but you want to change its formatting. (This will be for times when the change is a one-time, cosmetic effort and not a case for Conditional Formatting).
In this familiar example, we have received data where some of the Sales column entries are bolded in the Currency number format, and some are normal in the Accounting number format. We would like them to be consistent without having to change each cell individually.
Type CTRL+H to open the Find and Replace dialog on the Replace tab.
Leave the text fields blank and click the Format button on the Find what: row.
In the Find Format dialog, choose the format you want to adjust. In this case we will select Currency on the Number tab. Click OK when you are done.
Then, click the Format button on the Replace with: row.
Select the Accounting number format on the Number tab, AND the Normal style on the Font tab. Click OK when you are done.
Click Replace All to apply the change to the entire worksheet or workbook without reviewing each change.
Excel finds all cells that are formatted in the Currency number format and changes the formatting to the Accounting format in the normal style. This quickly changes just the cells we want without changing the format of other bolded cells on the sheet such as in our header row, for example.
There can be many uses for adjusting formatting of multiple cells in a single, quick action! From creating consistency, to anticipating a printed final draft, Find and Replace Formats can turn a tedious task into a few steps.
Just remember, that like all other bulk editing features, be sure to check your results and make sure that nothing was missed, and that nothing was changed that you didn’t want!
What are other ways that you have used Find and Replace to save time?
