Excel can hold huge chunks of data at once. If you need to search for a Text, string, or numerical value inside Excel it will consume a lot of time. It gets even harder to search inside multiple Excel files. Luckily, there are several ways to search inside Excel files. You can also search for cells that have formulas and those that have conditional formatting. This article shows you how to search inside Excel files.
Use windows to search for Text in Multiple Excel Files
Windows helps you to search for Excel file extensions by use of simple steps as shown below;
1. Open Start and type index
2. Go to index and select Indexing options from the choices that appear
3. Open Advanced button from the choices that appear
4. Click on File Types tab. Now select the file extension that you want to be in your searches.
5. Click on Index Properties and File Contents
After this step, the Filter Description text column changes to the filter that should be used to open the Excel file type. In case you do not find the file type you are looking for, it means that there is no app for that file type.
6. Continue by typing the file extension to the Add New Extension to List box
7. Press Add
This step adds the text to the index so that it becomes easy and shows results when searching for it.
8. Press okay and navigate to the folder you want to search for text
9. Click on advanced options and tick file contents
Search for text in Excel using the Find feature
Follow the easy steps below to do this;
1. Open Excel
2. Go to the Home Tab
3. Select the Find and Select the icon that is found in the Editing group
4. Select the word Find. A dialog box will open
5. Select Find What Text Box, then insert the text or the numerical value of what you want to find
6. You can click either of the following according to what you want to find;
7. Find Next; enables you to find the first cell that stats from the one you currently selected
8. Find All; enables you to find and list all the cells which have the text that you typed in the step above
9. Click on the Close ICON to remove the Find and Replace dialog box
Using Shortcut method
You can easily search for text or numbers using a shortcut.
1. Open the Excel File you wish to search inside
2. Press Ctrl+F keys simultaneously
3. Type the text or numerical values you wish to find on the type box
4. Press find
Excel will automatically find and highlight all the matching values.
You can also use a shortcut to find n replace values. This can be useful, especially when you need to change multiple values at once. In addition, when you need to edit typos in the Excel document.
1. Press Ctrl + H
2. Enter the value you wish to find in the first text box
3. Enter the value you wish to replace within the second text box
4. Customize the settings (For example, you can set match case)
5. Click Find all
6. Excel will find and replace all values with new values
How to search for formulas in Excel files
Excel has two choices that will help you find cells that have formulas. These are;
1. Display of formulas in cells rather than numbers
This method has two clear ways i.e.
- Use the keyboard keys Ctrl+ ` (the character that appears above the tab key and is adjacent to number 1 on the keyboard)
- Open the Formulas tab and select Show formulas that are found in the Formula Auditing choice
2. Selecting and highlighting cells that have formulas
Follow the easy steps below;
- Open the Home Tab
- Select Find, and Select Icon found in the Editing choice
- A dropdown menu will appear
- Select on Formulas
In this case, Excel will show you all the cells that have formulas.
Using the Search Sheet bar to search in Excel files
Use the following method;
1. Click on the Search Sheet at the top of the spreadsheet
2. Type in the numbers or texts you want to find
3. Click on Enter
4. Use the keyboard keys Ctrl + F on PC or Command+ F on Mac
Create your own custom search bar using conditional formatting
Conditional formating helps to highlight the data that you are searching for. It also helps to set rules to make searching easier.
Conditional formating Search bar template
1. Highlight the range that you wish to search then select Conditional Formatting>>New Rule in the home tab
2. Select “use a formula to determine wich cells to format” on the “select a rule type” section
3. Type the following formula =ISNUMBER(SEARCH($B$2,A5))
Note that $B$2 is the search box in the sheet While A5 refers to the starting range of the data in the sheet.
4. Press format and choose the fill color then press okay
5. Now type any word that you want to search on your search cell. For instance B2 in this Example
All the cells with the name that you have searched will be highlighted.