Discover 4 useful ways to search in Excel, from basic to advanced

If you often use Excel but haven’t quite figured out how to search efficiently and quickly in Excel, then let’s discover 5 common ways to search in the Excel software to pocket and apply right away!

The benefits of searching in Excel

When you know how to search in Excel, instead of spending time sitting around filtering and manually searching for data. Now users can quickly and easily search for data through the available tools in the Excel software.

After finding the desired data, users can replace or correct the data errors. At the same time, you can search a large amount of data in a short period of time, saving you time and effort.

Top 4 common uses of searching in Excel

Come to the top 4 common ways to search in Excel. For each method, you will be guided in detail by FPT Shop with step-by-step instructions and accompanying images.

Searching for data in a data range, worksheet, or file

Step 1: Users highlight, select the data range they want to search. Then, press the Ctrl + F key combination or open the Home tab => Editing => Find & Select and then select Find… to open the Find and Replace dialog box.

Step 2: In order to find the correct information you are looking for, you need to enter the correct text and format. In the Find What empty box, the user enters the character, value, or data that you want to find. Then, you click Find All or Find Next, in which:

• Find All: is the command that opens a list that lists all occurrences of the value you are searching for in the data range you have selected and you can navigate by selecting the arrows on the list.
• Find Next: is the command to select the first occurrence of the search value on the worksheet. When you continue to select Find Next the 2nd time and subsequent times, the value will continue to appear 2nd and so on for the last occurrence of the searched value in the data range.

Using advanced search options in Excel

When you want to search for data in Excel in a more advanced way, you continue to select the data range you want to search for information. Then, open the Find and Replace dialog box. Next, you click on the Options button as shown in the picture and more advanced search commands will appear with:

• The Within button: Allows you to search for a specific value in the worksheet you are pointing your mouse (Sheet) or the entire file you are opening (Workbook).
• The Search button: Helps users search for values from top to bottom (By Columns) or from left to right (By Rows).
• The Look in button: Supports searching for specific data types in three forms: Value, Comments, and Formulas.
• The Match case check button: Helps search for lowercase or uppercase letters accurately.
• The Match entire cell contents button: Supports searching for cells that only contain the characters you need to search for and have entered in the Find What box at first.

Using Excel to search for cells with specific formats

To search for cells that already have a specific format in Excel is very simple. You can refer to the steps to search for cells with predefined formats as follows:

Step 1: You press the Ctrl + F key combination to open the Find & Replace dialog box. Then, you select the Options command and then select Format…Then, you choose the format that you want to search in the Find Format dialog box that appears.

In case you want to find cells that match the format of a preselected cell in your worksheet or change any criteria in the Find What dialog box, you just need to click the arrow next to the Format command, then choose Choose Format From Cell and select the cell with the format you need.

Step 3: Your cell format has been changed according to your needs.

If you search and Excel gives an error and cannot find the value you want, it means that the format you are looking for is not present on the worksheet you are using. To fix this error, you can go back to the Find & Replace dialog box => Find tab => Select Options => Click on the arrow next to the Format command => Use Clear Find Format and re-enter the data you are looking for.

Searching in Excel for cells with formulas

When searching in Excel for objects that are cells with formulas, the cells that meet your requirements will be highlighted so that you can see them more easily. How to use Go to Special to search as follows:

Step 1: Select the object you want to search for in the worksheet (data range or select any cell). Click on Find & Select and select the Go to Special command.

Step 2: The Go to Special dialog box will appear => Continue, select the Formulas command => Mark the appropriate choices for the formula results you want to search. Finally, click OK. Explanation of some command buttons that need to be selected:

• Numbers: Search for formulas in Excel that contain numerical values (including dates).
• Text: Search for formulas that only include text values.
• Logicals: Search for formulas that contain two types of values TRUE or FALSE and both.
• Errors: Search for cells that contain formulas with error messages such as #VALUE!, #DIV/0 !, #NULL!, #NUM!, #N/A, #NAM ?, and #REF!.

Conclusion

In this article, we have shared with you 4 ways to search in Excel from basic to advanced with detailed instructions and illustrated images. We hope that through this article, you have updated more useful information about Excel and use it to search for objects more and more skillfully.