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 office. He thanked me, packed up, and left office. Excel Find and Replace feature is super powerful if you know how to best use it.
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 4 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 of hard labor”.
#1 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 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.
Here are the steps to do this: 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 the instances would be replaced by $B$1.
#2 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: 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.
#3 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: And Woosh! It would magically remove all the line breaks from your worksheet.
#4 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.
If you have a huge data-set, removing the parenthesis and the text between it can take you hours. But Find and Replace in Excel can do this in less than 10 seconds. I hope you find these tips helpful. If there are any other tricks that helped you save time, do share it with us!!
24 Excel Tricks to Make You Sail through Day-to-day work. 10 Super Neat Ways to Clean Data in Excel Spreadsheets. 10 Excel Data Entry Tips You Can’t Afford to Miss. Suffering from Slow Excel Spreadsheets? Try these 10 Speed-up Tips. How to Filter Cells with Bold Font Formatting in Excel. How to Transpose Data in Excel.
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)
