Find and replace data
Start learning on Udemy today from hunderds of courses.
When you work with Excel, sometimes you have to deal with very large amounts of data. As a result, finding a particular value can be very time-consuming. Fortunately, Excel comes with a powerful search tool, thanks to which you can quickly find the data you want.
To open the Find and Replace window, go to the HOME >> Editing >> Find & Select >> Find or use one of the keyboard shortcuts: Ctrl + F or Shift + F5.
When a new window appears, you will notice that the tool in its basic form doesn’t contain many options.
If you want to have access to more settings, click Options >> button.
Here, you will find here three drop-down menus.
You can choose whether you want to scan for data in this sheet only or in the entire workbook.
Here, you can decide whether the values should be searched by rows or columns. This means that when a Search option is set to By Columns and you click the Find Next button, Excel will start searching in the first column and then in the next.
You can choose whether Excel searches for data in formulas, values or comments.
On the right side, you will find two checkboxes. The first one is Match case. If this option is unchecked and you start searching for the phrase “John Smith”, Excel will also return “john smith”, JOHN SMITH” or “John SMITH”. When this option is checked Excel will display the result only if the text is exactly “John Smith”.
Click the Format button to set additional parameters, such as font, background, etc. For example, if you type "John Smith" and choose blue background color, then the text must meet the two conditions: it must match the phrase “John Smith” and the cell background has to be blue.
If you are not sure of the format of the cells which you are looking for, click the small triangle next to the Format button. A drop down menu will appear. Click Choose Format From Cell... and then click on one of the cells you are interested in.
You will find a Replace button in HOME >> Editing >> Find & Select >> Replace. You can also use the Ctrl + H keyboard shortcut.
This tab is very similar to the Find tab. The difference is that in the Replace tab there is an additional textbox called Replace with. There, you can enter the text to which you want to replace the text inside the Find what textbox. You can also select the formatting style.
If you don't want to change the formatting style, but the format style is already set, you can remove it by clicking on the triangle on the Format button and then selecting Clear Replace Format.
If you want to change the formatting to normal, click on the triangle, then select Choose Format From Cell and click on the empty cell.