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 for Excel?! Let’s go through the following steps to solve some case studies!
- 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, here 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 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 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 contain ay.
- Formula: If the cell contain 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.
Case Study 2:
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 contain Simran as like above picture. So, We have searched a word successfully that exist in another sheet but in same workbook.
Case Study 3:
Search by cell color!
Now let see how to search 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 be 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 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…!