When we handle a significant amount of data, sometimes we need to locate specific data in the book. To facilitate this task, there is a search tool. We will find it in the tab Home > Search and select.
To access the Find & Replace, Choose Home >> Find & Select >> Find, or press Control + F Key. See the illustration below.
After clicking the Find Next Button. We will go from one cell to another, among those that contain the entered value. It will search from where we are located, so we must place ourselves in the first cell to search the entire sheet. Also, if we have multiple cells selected, it will only search for them.
We can also press Search all to show in the window itself a list with the location of each of the cells in which the data is found. The list of results will be a set of links, and simply clicking on one of them will place us in the cell in question.
The Options button allows us to expand the search window with extra options. We can choose where to search (if on the sheet we are on or in the entire book) if we only search for cells with a specific format (for example, only cells with currency format).
We can substitute the data in the Replace tab from the same window or the option Replace Button Search and select (keys CTRL + H), changing one original value for another. The replacement option is not limited to values only. It can also refer to formats. For example, we could find all cells that contain the value 0 in currency format and replace it with the same value but with a red cell colour to highlight them.
Let’s take a look at the different choices available in the Find dialog box.
- Within: The search should be defined in a Sheet or Workbook.
- Search By: The internal search method may be defined by rows or columns.
- Look In: Select this option if you want to find text in the formula as well.
- Match Case: Choose this option if you want to match the case of terms, such as lower case or upper case.
- Match Entire Cell Content: Choose this option if you want an exact match between the word and the cell.