In this blog, you will learn how to search in excel? When working with a lot of data in Excel, it can be difficult and time-consuming to locate specific information. You can easily search your workbook using the Find feature, which also allows you to modify content using the Replace feature. There are many types of search option in Excel. We can search a word or letter based on Cell, Column, Row, Sheet, Cell color and much more. In our this tutorial we are going to learn
How to Search in Excel?! Let’s go through the following steps to solve some case studies!
Case study 1:
Search the cell that contains particular letter or word: Use of Find.
- Create a table same as like above picture.
- This table contains two columns, One contain Serial numbers and another one contains Name.
- Now we will find those cells which contain “ay” in the Name list.
- Click on Home tab
- Click on Find & Select from Editing group.
- Now click Find to open Find and Replace dialog box with an auto selection of Find tab by default.
- You can use a shortcut command to open Find: Ctrl+F
- See, there are 2 tabs in Find and Replace dialog box.
- After click Find and open Find and Replace dialog box it automatically selected Find tab.
- If we clicked Replace (or Ctrl+H) from Find Select drop-down list, the Replace tab would be selected from Find and Replace dialog box.
- Now in Find What box type “ay” to search those cell which contains “ay”.
- Click Find Next.
- Cell B2 will be selected which contain Jay.
- Again click Find Next.
- Cell B3 will be selected which contain Vijay.
- Both of these words contain ay.
In this way, we can search one by one cell that contains ay. If we want to select all cells to contain ay, then follow next step.
Click Find All. Now below the Find and Replace dialog box we can see a list of cells with detail that contain ay in the word. See above picture. This list contains 6 information about those cells that contain ay:
- Book: Excel file name.
- Sheet: Sheet name that contains this cell.
- Name: Name of the cell if any.
- Cell: Cell number of the searched cell. In our example, we found $B$2, $B$3.
- Value: Value of that cell. In our example, we found Jay and Vijay as the value that contains ay.
- Formula: If the cell contains any formula it also will be listed in this searched result list in Formula column.
From this result list, if we click on any result, it will highlight that particular cell in the Excel sheet. So, By following these steps we can easily search any word or letter inside a sheet’s Cell.
How to Search from another Sheet or whole Workbook
Step 1: In the Status Bar, Click on New Sheet button to create a new sheet. This is our Sheet2. Now in this new sheet create a table like above picture.
Step 2: Till now we just simply searched in an existing sheet that we are working on. Now we will search from the whole workbook. Say we will open Sheet1 but search a value that included in Sheet2 like Simran. So click on Sheet1 and open Find and Replace dialog box. Click Options. Now we will get some new advanced search options in Find and Replace dialog box. Click On List and select Workbook instead of Sheet. Now it will search for the whole Workbook. In Find What box type Simran.
Step 3: Now click Find Next button to see the Magic! It will jump to Sheet2 with find and select the cell to contain Simran as like above picture. So, We have searched a word successfully that exist in another sheet but in the same workbook.
Search by cell color!
Now let see how to search or find by cell color by following steps!
Step 1: Go to Sheet1 and colored some cells like above picture.
Step 2: Open Find and Replace box. Click Format and select Format from Format drop down list.
Step 3: Find Format dialog box will appear. Now click Fill tab and select Red color from Background color list as like above picture. Now click OK.
Step 4: In Find and Replace dialog box we can see Red color in Preview to search the Red-colored cell in our sheet. Clear Find What box if there is any word typed, because we are going to search based on color, not word.
Step 5: Now click Find Next button and see the Magic! It is selecting that Red colored cell. In the same way, If we can found Green colored cell also.
So, now we have successfully searched many things using many ways in excel! Enjoy Excel Tricks…!