Table of Contents
How to Search in Excel?
If we want to search any text in excel, we can use FIND (CTRL + F) tool. Based on the value we type it will show us all the available matching results for us.
Similarly, if we can make use of Excel tools to search any text or values in a more organized and sophisticated way in excel we can make use of the these three methods listed below –
#1 – SEARCH Function in Excel
Let’s understand the practicality of the SEARCH function in excel with common examples in the real world.
Take a look at this letter PowerPoint.
In cell A1 one P is Uppercase and another one is Lowercase. Let’s apply the SEARCH Function in excel to find the position of the P in cell A1.
I have mentioned uppercase P as the find text in the cell A1, formula returned the result as 1. Even though there is one more P it has returned the first character position.
Now apply the formula with small p.
Even though I have mentioned lower case p the formula still returned the position of the character as 1 only.
In order to find the second p, we need to mention the position of the character we are looking for.
Ok, I have mentioned the starting number as 2. This means ignore the first P and find the second instance p in the text. It does not matter which case of P you are mentioning based on the starting number we mention it will show the position of the supplied character.
#2 – Search & Highlight Cells using Conditional Formatting
Now take a look at the below example. From the below data I want to highlight all the cells that contain the word MITCHEL.
Step 1: Select the data from A2 to B22.
Step 2: Go to HOME tab and select Conditional Formatting then Highlight Cell Rules and click on Text that Contains.
Step 3: In the below window type the word MITCHEL and chose the format color as per your wish.
Step 4: It will highlight all the cells which have the word MITCHEL.
It is wonderful to be highlighted the word of our choice. But here comes the difficulty or I would say limitation.
If I want to remove the highlight of the word MITCHEL and highlight the word JOHN I want to go to manage rules and change my rule as I have shown in the below image.
If I want to keep change my names to be highlighted based on other requirements I cannot just simply go back and edit my rule every now and then.
I want to keep change my names to be highlighted based on other requirements. I have shown in the below image.
#3 – Creating a Search Box in Excel
We can create custom formatting along with SEARCH formula to overcome this limitation in excel. Follow below steps to search and highlight all the related values as you type in the search box in excel.
Step 1: Create a list of values in a sheet and create a one search box in excel. For your reference, I have created one for myself.
Step 2: Select the data from A5 to B25. Go to Home then click on Conditional Formatting and select New Rules.
Step 3: In the below window select Use a formula to determine which cells to format.
Step 4: In the formula apply the SEARCH function as shown in the below image.
Step 5: Click on Format… It will open up the below dialogue box. Go to FILL and select the color you want to highlight your searched word.
Step 6: Click on OK to complete the process. Start typing in the cell B5, it will highlight words or characters which matches from the cell B5.
I have typed the character J in the cell B5. Whichever cell has the character J has highlighted by the conditional formatting. Now you can highlight the word as you type in the cell B5.
All the magic is done by SEARCH function inside the conditional formatting. I
Things to Remember about Search in Excel
- We can create a custom search for our filter i.e. based on our search we can filter our data. This topic we will see in the other article.
- SEARCH function matches even if there is only one character in the list and notify the conditional formatting. Based on the notification given by the SEARCH function conditional formatting will highlight the values in the list.
- SEARCH is not a case sensitive function. But there is a similar function FIND which is a case sensitive.
You can download this Search Box Excel template here – Search Box Excel Template
This has been a guide to Search in Excel. Here we discuss how to Search in excel using three methods – 1) Search Function 2) using Custom Formatting and 3) Search Box along examples and downloadable excel templates. You may also look at these useful functions in excel –