The 4 Excel Find and Replace tricks I use to save hours
I used to waste hours in Microsoft Excel manually cleaning data, fixing formulas, and reformatting worksheets. That was until I realized I could use the Find and Replace dialog (Ctrl+H) to achieve the same results in seconds. As a result, I can now spend more time on the things that matter.
Replacing formatting selectively and en masse
Excel's Format Painter is a handy tool for quickly duplicating formatting across cells, but it doesn't let me selectively replace a single attribute, like only the background color, or apply changes consistently across multiple worksheets in a workbook.
However, with Find and Replace, I can swap one look for another instantly without touching the data itself. After launching the Find and Replace dialog (Ctrl+H), for the Find What option, I either click "Choose Format From Cell" to use an existing cell as the blueprint for the formatting I want to replace, or "Format" to define the formats manually.
Be sure to click "Clear" in the Alignment tab of the Find Format dialog box if you don't want alignment to be considered in the search. Otherwise, relevant cells might be overlooked.
Similarly, in the Replace With section, I can click either "Format" to specify all or just some of the new formatting, such as changing only the font color while keeping the original border, or "Choose Format From Cell" to nominate a pre-formatted sample cell.
Finally, after deciding whether I want the changes to be sheet-only or workbook-wide via the Within menu, I click "Replace All," and Excel instantly replaces the formatting. What would have taken hours now takes less than a minute.
Using wildcards for partial replacements
Data imported into Excel from external sources is often frustratingly inconsistent. Until I came across wildcards, I would spend forever cleaning up text variations—like Man U, Man Utd, and MUFC—manually.
Now, I use wildcards to fix these issues in one pass. The asterisk (*) represents any sequence of characters, and the question mark (?) represents any single character. So, to find all the variations of Manchester United, I search for "M*U*" and replace it with "Manchester United". I also check "Match entire cell contents" because, otherwise, Excel would match and replace partial text strings (like the m*u string in Bourne mouth).
For a more precise fix, such as finding Acct1, Acct2, and Acct3 but not Acct10, I can search for Acct? and check "Match entire cell contents" to only target single-digit accounts.
By default, Find and Replace ignores cases. If you need a case-sensitive search, check "Match case."
If my data literally contains an asterisk, I use the tilde (~) as an escape character directly before the wildcard character. For example, if I want to find products labeled Product*100 to Product*199 (including the asterisk), I search for Product~*1??. This tells Excel to treat the asterisk as a character itself, and leaves the question marks as single-character wildcards.
Remember to expand the "Look in" drop-down menu to decide whether you want to make the change in the current sheet only or the whole workbook.
Replacing line breaks
Another frustration with data imported into Excel from external systems is that it often contains in-cell line breaks. This causes text and numbers to be stacked vertically within a single cell, preventing sorting, filtering, and lookup formulas from working as expected.
Entering each cell and removing the line breaks manually would take too much time. Luckily, I can fix this with a little-known keystroke in the Find and Replace dialog.
After opening the Find and Replace dialog (Ctrl+H), in the Find What box, I press Ctrl+J. When I do this, it looks like nothing has happened—all I see is a tiny blinking dot. However, this actually indicates that I've correctly told Excel to look for the line break character.
In the Replace With field, I type a single space, dash, comma, or leave it blank altogether. Then, when I click "Replace All," all the text stacked vertically in cells is converted into usable, horizontal data strings.
Performing mass formula updates
One of the most time-consuming tasks in Excel is updating multiple formulas to reference a new cell. Suppose I have a home budget workbook, and my annual savings target is in cell $G$2.
However, now, I want to centralize all my assumptions in a dedicated worksheet, moving the savings target to Inputs!$B$1. The problem is that I have hundreds of cells across multiple worksheets that reference the old $G$2, and manually editing each one to reference Inputs!$B$1 is guaranteed to take hours and result in errors.
This is where the Find and Replace dialog comes in handy. After opening the window (Ctrl+H), I need to select "Formulas" in the Look In menu and "Workbook" in the Within menu.
In my case, cell $G$2 was used as the denominator in divisions to calculate percentages or proportions. This means that each time it was referenced in formulas, it was preceded by a forward slash (/$G$2). So, in the Find What field, I'll type /$G$2, and in the Replace With field, /Inputs!$B$1.
Click "Format" next to the Replace With field, and select the correct number format in the Number tab (in this case, "Percentage"). Since Excel remembers format settings from previous searches, this ensures that during the reference conversion, you end up with the correct number format .
When I click "Replace All," all the formulas that previously divided by the value in cell G2 now divide by the value in cell B1 of the Inputs sheet.
Typing just $G$2 into the Find What field would be a risky maneuver, as Excel would also act on references to cells $G$20, $G$21, and so on. However, I know that none of these cells are used as denominators, so adding the / symbol eliminates this risk.
While you could use this method to swap one function name (such as VLOOKUP ) for another (like XLOOKUP ), this can break all formulas, as functions use different syntaxes and arguments. This trick is best reserved for changing cell references en masse or simple functions that share the same structure (such as SUM and AVERAGE).
Excel's Find and Replace dialog isn't the only time-saving tool I use daily— Go To Special saves just as much time. Indeed, by helping me to instantly select and isolate formulas, highlight blank cells, and indicate which cells contain data validation rules, it allows me to cut hours of manual auditing and searching with just a couple of clicks or keyboard shortcuts .
