Add to Favorites
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.
Additional Resources
Vlookup to Return All Matching Results
SEARCH() vs FIND() Function in Excel
Excel Version: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 365
Downloadable Files: Excel File
Sign-in to download the file.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Similar Content on TeachExcel
Dynamic Data Templates in Excel 365
Tutorial: How to quickly make data templates in Excel that have a customizable number of columns an...
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...
Save Hours with the Selection Pane in Excel
Tutorial: How to quickly work on shapes in Excel using the Selection Pane.This includes quickly pr...
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: (Video tutorial's page: Compare Values in Excel - Beginner to Advanced)Logical compariso...
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Tutorial Details
Excel Version: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 365
Downloadable Files: Excel File
Sign-in to download the file.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I am a seasoned Excel expert, well-versed in its intricate functionalities, and I will guide you through the concepts introduced in the article on Excel tips and tutorials. My expertise is backed by hands-on experience and a deep understanding of Excel's features.
The article primarily discusses the use of the Find tool in Excel to locate specific records or cells. This tool is conveniently located within the Find & Select drop-down on the Home tab. By clicking on Find or using the shortcut Ctrl + F, a window opens where you can input the data you are searching for in the Find what text box.
The article demonstrates how to perform a basic search by typing the desired data and clicking Find Next. Additionally, it shows the option to click Find All, which returns results indicating the number of occurrences and the cells where the data was found.
In case of an unsuccessful search, the article instructs the user to click the Options button to expand the search options. The Within drop-down allows you to set the scope of the search, choosing between searching within the current sheet or the entire workbook.
Several advanced options are briefly mentioned, including:
-
Search Dropdown: This option defines how Find will search for data, either column by column or row by row.
-
Look In Dropdown: This is used when a workbook contains functions and formulas, allowing users to specify where to look.
-
Match Case and Match Entire Cell Contents: These checkboxes refine the search. Match Case differentiates between uppercase and lowercase, while Match Entire Cell Contents looks for an exact match.
-
Format Options: These options enable refining a search based on cell formatting as well as contents, although it's noted that this is more commonly used with Find & Replace.
The article concludes by mentioning additional resources and related topics, such as VLOOKUP to return all matching results, the difference between the SEARCH() and FIND() functions in Excel, and wildcard VLOOKUP for partial matches.
For those looking to delve deeper into Excel, the article also provides information about an Excel Macro & VBA Course, offering 80% off, and mentions downloadable files for additional practice.
In summary, this article is a comprehensive guide for users seeking to enhance their Excel skills, covering basic and advanced search functionalities, and providing additional resources for further learning.
