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 for something 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”.

How to search for something in Excel using Find and Replace

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

How to add new sheet in ExcelHow 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.

Steps for Find and Replace in ExcelStep 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.

How to Find & Replace in Sheet2

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.

How to format search in Excel

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

How to fill background color in Excel using Find Format

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.

How to use Preview Button in Find & Replace

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.

How to filter in finding something using colors

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

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
December 10, 2018
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018