When working with large data sets in excel, you quickly become accustomed to the find and replace dialog box which can serve as a very powerful tool to save countless number of hours on activities like changing formats, replacing links, replacing text in formulas, removing text and so much more. In today’s post we would take you through useful find and replace tricks to help you work more efficiently and save considerable amount of time. Before we begin however, let’s introduce you to the find and replace dialog box incase you are not aware of it.
Find & Replace Dialog Box in Excel
The main purpose of the find & replace dialog box is to search for and perhaps replace information within your worksheet or workbook. Information such as specific characters, texts, numbers or dates can be quickly and easily found by using the find and replace dialog box. To display the find and replace dialog box, all you need to do is to use the keyboard combination Ctrl + F for find and Ctrl + H for replace.
If you aren’t a keyboard shortcut person, you can also display the find and replace dialog box by going through the Home tab > Editing group and clicking on the find and select drop down.
After clicking find as seen in the image above (or using the keyboard combination Ctrl + F), the find & replace dialog box comes up with the find interface as default.
The same process goes to pop out the replace interface in the find & replace dialog box. Click on replace as seen in the image below.
Replace becomes the default interface in the find & replace dialog box.
Now that you have gotten a hang of things, let’s move on to tips and tricks for maximizing this powerful feature in excel. In the paragraphs to follow, we would share 5 awesome tips for saving time and getting the best out of the Find & Replace dialog box. Let’s dive right in.
Replace all Occurrences of a particular text
With the Find & Replace dialog box, you can choose to replace all occurrences of a particular word you specify either in the current worksheet or in the entire workbook. For instance, our example in this section has cruise line names in column C appearing in multiple places. Let’s assume we need to change a cruise line’s name from Tango to Sierra, we just follow the steps below:
Step 1: Press Ctrl + H on your keyboard or follow the instructions on bringing up the Replace interface in the Find & replace dialog box as detailed in the previous section.
Step 2: Type in the existing name in the find what field and then type in the new name in the Replace with field. Then select whether you want to replace all occurrences in the current sheet or everywhere in the workbook.
Step 3: Click on Replace All and all you are done
Still confused? See our animation below:
Remove Text Using Wild Card Characters
This trick is very useful for removing unwanted text in a group of cells with similar structure. What do we mean by similar structure? We mean when there is a delimiter that separates a portion of the text. For example when parenthesis [ ( ) ], pipes (|) , Slashes (/ \) separate portions of text in a cell.
To illustrate, we have a list of some publicly traded companies and their stock symbol in parenthesis.
Our task is to delete the stock symbols because we no longer need that piece of information. How do we go about this? Simple, just follow these steps:
Step 1: Press Ctrl + H on your keyboard to bring up the Replace interface in the Find & replace dialog box
Step 2: In find what: (*) We use astericks here as a wildcard character that represents any number of characters within the Parenthesis
In Replace with: Leave blank
Result:
Change Cell References
If you work with large datasets, knowing how to quickly and easily change cells references in a formula is a must know. For example, I was recently working on a model that had various scenarios of the same formula workings and to save some time, I copied the initial workings and it in different sections of my worksheet. However the formula was still picking the same input cells and I needed to change them to the input cells of the various scenarios.
Now imagine if I didn’t know this trick we would touch on shortly, I would have needed to go through each formula and manually change the cell reference. That’s countless hours on just editing a couple of formulas when there is an easier way that can get the job done is merely seconds. Let’s get right into it:
The formula captured above is the SUMIFS formula which sums values in a column if they meet all the criteria specified. These criteria could be one, two, three or more. So to break it down, $A$1:$A$15 is the column we want to sum up, whilst subsequent ranges like $D$1:$D$15 are the criteria range, and single cells such as $H1, are the criteria.
Let’s assume that we need to change the column for summing up to column B, the first step would be to bring up the replace interface in the find & replace dialog box. Next, you type in the following:
Find What: $A$
Replace with: $B$
Click on Replace All and you get the notification below:
Then we have the following result:
Find & Replace Formatting
This feature in the find & replace dialog box allows you to quickly and easily change formats of multiple cells at once. Knowing this trick would help you save countless number of hours especially when you are dealing with large data sets that may need format update such as font size / type / color, background color and borders.
In our example below, the cruise Guest numbers for Tango cruise line have been formatted with the Gold color and we now need to change to a dark grey background with borders.
To achieve this, we just need to follow the steps below:
Step 1: Bring up the replace interface in the find & replace dialog box by using Ctrl + H
Step 2: Click on the options button to display more options
Step 3: You should see a format feature by the right of both Find What & Replace With. Click on the format button by the right of find what. A drop down with 2 options should appear (format and choose format).
Step 4: Choosing format from the drop down allows you manually specify the format you are looking for, but since we already have the format somewhere in our worksheet, choose format from cells would be a better option.
Step 5: Once you specify the format with either one of those 2 options, you should see a preview button appear to the left of the format button. Repeat steps 3 – 4 for the Replace with.
Step 6: After selecting format to replace with, click on Replace all
Result: You should get a notification on the number of replacements and then the new formats should appear.
To Remove Line breaks
In our post on how to wrap text, we touched on adding line breaks within a cell by using Alt + Enter. In this section, we would teach you how to undo that with Find & replace dialog box. Let’s go:
As with the previous sections, the first step is to pop out the Find & Replace dialog box by pressing Ctrl + H. The next step is to type in the following in find what and replace with fields.
Find What: Hit Ctrl + J (you wont see anything but a blinking dot but that’s fine)
Replace with: Spacebar character by hitting the spacebar once
Final step is to click Replace All. Please see the visual guide below:
