Using Find and Replace in Excel

Watch Video – Useful Examples of Using Find & Replace in Excel

Last month, one of my colleagues got a data set in Excel, and he was banging his head to clean it.

Since I was the only one in the office at that wee hour, he asked me if I could help.

I used a simple technique using Find and Replace in Excel, and his data was all clean and polished.

He thanked me, packed up, and left the office.

Excel Find and Replace feature is super powerful if you know how to use it best.

Using FIND and REPLACE in Excel (4 Examples)

Find and Replace in Excel can save a lot of time, and that is what matters most these days.

In this blog, I will share four amazing tips that I have shared with hundreds of my colleagues in my office.

The response is always the same – “I wish I knew this earlier. It could have saved me so much hard labor”.

To Change Cell References Using Excel Find and Replace

Sometimes when you work with a lot of formulas, there is a need to change a cell reference in all the formulas.

It could take you a lot of time if you manually change it in every cell that has a formula.

Here is where Excel Find and Replace comes in handy. It can easily find a cell reference in all the formulas in the worksheet (or in the selected cells) and replace it with another cell reference.

For example, suppose you have a huge dataset with a formula in that uses $A$1 as one of the cell references (as shown below).

If you need to change $A$1 with $B$1, you can do that using Find and Replace in Excel.

Find and Replace in Excel - Change Reference

Here are the steps to do this:

  1. Select the cells that have the formula in which you want to replace the reference. If you want to replace in the entire worksheet, select the entire worksheet.
  2. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H).
  3. In the Find and Replace dialogue box, use the following details:
    • Find what: $A$1 (the cell reference you want to change).
    • Replace with: $B$1 (the new cell reference).Find and Replace in Excel - Change Reference
  4. Click on Replace All.Find and Replace in Excel - Replace All

This would instantly update all the formulas with the new cell reference.

Note that this would change all the instances of that reference.

For example, if you have the reference $A$1 two times in a formula, both instances would be replaced by $B$1.

Also read: Absolute, Relative, and Mixed Cell References in Excel

To Find and Replace Formatting in Excel

This is a cool feature when you want to replace existing formatting with some other formatting. For example, you may have cells with an orange background color and you want to change all these cell’s background color to red. Instead of manually doing this, use Find and Replace to do this all at once.

Here are the steps to do this:

  1. Select the cells for which you want to find and replace the formatting. If you want to find and replace a specific format in the entire worksheet, select the entire worksheet.
  2. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H).
  3. Click on the Options button. This will expand the dialogue box and show you more options.Find and Replace in Excel - Click on Options Button
  4. Click on the Find what Format button. It will show a drop-down with two options – Format and Choose Format from Cell.
    • You can either manually specify the format that you want to find by clicking on the Format button, or you can select the format from a cell in the worksheet. To select a format from a cell, select the ‘Choose Format from Cell’ option and then click on the cell from which you want to pick the format.Find and Replace in Excel - Choose format from cell
    • Once you select a format from a cell or manually specify it from the format cells dialogue box, you will see that as the preview on the left of the format button.Find and Replace in Excel - preview
  5. Now you need to specify the format that you want instead of the one selected in the previous step. Click on the Replace with Format button. It will show a drop-down with two options – Format and Choose Format from Cell.
    • You can either manually specify it by clicking on the Format button, or you can pick up an existing format in the worksheet by clicking on the cell that has it.Find and Replace in Excel - Replace Choose format from cell
    • Once you select a format from a cell or manually specify it from the format cells dialogue box, you will see that as the preview on the left of the format button.Find and Replace in Excel - find and replace with formats
  6. Click on the Replace All button.

You can use this technique to replace a lot of things in formatting. It can pick up and replace formats such as background color, borders, font type/size/color, and even merged cells.

To Add or Remove Line Break

What do you do when you have to go to a new line in an Excel cell.

You press Alt + Enter.

And what do you do when you want to revert this?

You delete it manually.. isn’t it?

Imagine you have hundreds of line breaks that you want to delete. removing line breaks manually can take a lot of time.

Here is the good news, you don’t need to do this manually. Excel Find and Replace has a cool trick up its sleeves that will make it happen in a snap.

Here are the steps to remove all the line breaks at once:

  1. Select the data from which you want to remove the line breaks.
  2. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H).Find and Replace in Excel - Home Replace
  3. In the Find and Replace Dialogue Box:
    • Find What: Press Control + J (you may not see anything except for a blinking dot).
    • Replace With: Space bar character (hit space bar once).Find and Replace in Excel - Control J
  4. Click on Replace All.

And Woosh! It would magically remove all the line breaks from your worksheet.

To Remove Text Using Wildcard Characters

This one saved me hours. I got a list as shown below, and I had to remove the text between parenthesis.

Find and Replace in Excel - Wildcard Character

If you have a huge data-set, removing the parentheses and the text between it can take you hours. But Find and Replace in Excel can do this in less than 10 seconds.

  1. Select the data
  2. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H)
  3. In the Find and Replace Dialogue Box:
    • Find What: (*)
      Note I have used an asterisk, which is a wildcard character that represents any number of characters.
    • Replace With: Leave this Blank.Find and Replace in Excel - Wildcard

I hope you find these tips helpful. If there are any other tricks that helped you save time, do share it with us!!

You May Also Like the Following Excel Tips & Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

14 thoughts on “Using Find and Replace in Excel”

  1. Ok, let’s say that I need to exclude cells that have particular added values, while I need to find all the others that don’t have them. Example;
    5 cells that contain the word “Bread” and 5 more that contain the words “White Bread”. Let’s say that I need the “Find” command to show me only the cells with the “Bread” value. If I hit CTRL + F and type “Bread” it will return all 10 cells. Is there a wildcard which will do the opposite of “*”? So that if I place it in front and at the end of the letter/letters/word that I need excel to exclude from the search, it will comply? For example, let’s say thhat this wildcard is the “#” symbol. If I hit CTRL + F and type “#Wh#Bread” it will give me only the 5 cells with just the “Bread” value?
    Also if you select multiple cells by holding down the CTRL key, is there a way to deselect a mistakenly selected cell, without having to repeat the whole process again from the beginning?
    Thanks in advance.

    Reply
  2. #4 Explaining how to remove words just saved me at least a couple hours today! Thank you!!!!

    Reply
  3. Sumit. This is a great blog indeed. I downloaded your super-useful ebook, then came here to explore more and more useful tips.
    Thank you so much for taking the time to write, illustrate and share your valuable experience. This is noble indeed.
    Much appreciated.

    Reply
  4. Great stuff, I can still remember when I first discovered that I could use Find/Replace to modify formulas. Changed my life! I use Find/Replace the most when updating file paths for external links to other workbooks.

    Another good tip is you can perform Find/Replace across multiple worksheets by simply highlighting (aka grouping) all the worksheets you want to search within (I used to click through each tab and do a Find/Replace for the active tab, very time consuming if you have a bunch of tabs to cycle through)

    Reply
    • Dave.. You gave me the idea and I tried something different to do the same thing (to copy references).
      Select all the cells that have formula reference that needs to be copied –> change the format to text –> click F2 and hit Control + Enter. Now copy wherever you want, change the format to general, click F2 and press Control + Enter

      Reply
      • Sumit – Cool approach; very clever and quick. Just tested on a Mac (need to use control-U instead of F2). Works great!

        Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster