How to Quickly Find Data Anywhere in Excel

Add to Favorites
Author:

Finding specific records and/or cells is easy when using the Find tool in Excel. It is located within the Find & Select drop down on the Home tab.

Just click on Find within that drop down (Ctrl + F works too) and the following window will open:

To use Find you simply type the data you are looking for into the Find what text box. Above, I am looking for records which contain the text Tyler. I then click Find Next. Excel then searches all the cells and selects the first cell it finds which contains Tyler.

Alternatively I could have looked for Jackson and clicked Find All. This returns results based on the search:

It shows me the number of times Jackson was found and what cells it was found in.

If a search is unsuccessful, the following error message will appear:

As the pop-up advised I will now click the Options button to expand the options for the search.

The Within drop down sets the scope of the search. Currently it is set to Sheet, so only searches within the current worksheet. As searching for email was unsuccessful within Sheet I will change this to within Workbook. I click Find Next and the whole workbook will be searched for email.

As you can see, this search was successful. Find took me to cell C1 on Sheet2 where it found a cell which contained email.

You may have noticed a number of other options within Find that I didnt use. Some of these are beyond the scope of this tutorial but I will give a brief description of these.

  • The Search dropdown doesnt really matter unless your data set is quite large and you want to save time. It just defines how Find will search for your data. (Either column by column or row by row)
  • As all my data is just simple text I dont need to change the Look in drop down. This is required when a workbook has lots of functions and formulas which I will cover how to use in a later tutorial.
  • The match case and match entire cell contents checkboxes are pretty self-explanatory. They are used to refine a search. If you choose to match case, searching for TYLER for example would be different to searching for Tyler. If you choose to match entire cell contents then Excel will look for cells which entire cell value matches what you are looking for. So if you typed Tyl instead of Tyler with match entire cell contents enabled you wouldnt find anything in the dataset I provided.
  • The Format options allow you to refine a search based on a cells formatting as well as its contents but this generally isnt used with Find. It is more commonly used with Find & Replace but this will be covered in a later tutorial as well.

 

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Quickly Switch the Columns and Rows of a Data Set in Excel
Tutorial: How to switch a data set in Excel so that the columns become rows and the rows become col...
Delete All Rows that Contain a Specific Value in Excel
Tutorial: Quickly find all rows in Excel that contain a certain value and then delete those rows. ...
Shade Every Other Row in Excel Quickly
Tutorial: How to shade every other row in Excel quickly without using the Table feature. This method...
Logical Comparison Operators in Excel - How to Compare Things
Tutorial: Logical comparison operators allow you to compare values in Excel to see if they are equal...
Minimize or Maximize the Ribbon Menu in Excel
Tutorial: This is a simple way to get more space in Excel, reduce clutter, and streamline the inte...
Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Tutorial: Find the Min or Max value in a range and, based on that, return a value from another rang...