fbpx

How to Search in Excel?

Yodalearning>Tutorials>How to Search in Excel?

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!

Search the cell that contain particular letter or word
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

how to search in Excel ?

  • 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

  • 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
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:

  1. Book: Excel file name.
  2. Sheet: Sheet name that contains this cell.
  3. Name: Name of the cell if any.
  4. Cell: Cell number of the searched cell. In our example, we found $B$2, $B$3.
  5. Value: Value of that cell. In our example, we found Jay and Vijay as the value that contains ay.
  6. 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.

Search from another Sheet or whole Workbook 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.

Now we will get some new advanced search options in Find and Replace dialog box 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.

It will jump to Sheet2 with find and select the cell contain 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

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.

Click Format and select Format from Format drop down list

Step 2: Open Find and Replace box. Click Format and select Format from Format drop down list.

Format dialog box will be appear

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.

Find and Replace dialog box we can see Red color in Preview to search the Red colored cell in our sheet

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.

! It is selecting those Red colored cell

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…!

Excel Dashboard
Limited Period Offer.

30-Day Money-Back Guarantee

Includes:


11 hours on-demand video
Full Lifetime Access
Success on mobile and TV
Certificate of Completion
Downloadable Case Studies
Lifetime Support