Excel 2016 For Dummies
Book image
Explore Book Buy On Amazon

In a large data list in Excel 2016, trying to find a particular record by moving from record to record — or even moving ten records at a time with the scroll bar — can take all day. Rather than waste time trying to manually search for a record, you can use the Criteria button in the data form to look it up.

When you click the Criteria button, Excel clears all the field entries in the data form (and replaces the record number with the word Criteria) so that you can enter the criteria to search for in the blank text boxes.

For example, suppose that you need to edit Sherry Caulfield's profit sharing status. Unfortunately, her paperwork doesn't include her ID number. All you know is that she works in the Boston office and spells her last name with a C instead of a K.

To find her record, you can use the information you have to narrow the search to all the records where the last name begins with the letter C and the Location field contains Boston. To limit your search in this way, open the data form for the Employee Data database, click the Criteria button, and then type C* in the text box for the Last Name field. Also enter Boston in the text box for the Location field.

When you enter search criteria for records in the blank text boxes of the data form, you can use the ? (for single) and * (for multiple) wild-card characters.

Now click the Find Next button. Excel displays in the data form the first record in the database where the last name begins with the letter C and the Location field contains Boston. The first record in this data list that meets these criteria is for William Cobb. To find Sherry's record, click the Find Next button again. Sherry Caulfield's record then shows up. Having located Caulfield's record, you can then edit her profit sharing status from No to Yes in the text box for the Profit Sharing field. When you click the Close button, Excel records her new profit sharing status in the data list.

When you use the Criteria button in the data form to find records, you can include the following operators in the search criteria you enter to locate a specific record in the database:

Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to

For example, to display only those records where an employee's salary is greater than or equal to $50,000, enter >=50000 in the text box for the Salary field and then click the Find Next button.

When specifying search criteria that fit a number of records, you may have to click the Find Next or Find Prev button several times to locate the record you want. If no record fits the search criteria you enter, the computer beeps at you when you click these buttons.

To change the search criteria, first clear the data form by clicking the Criteria button again and then clicking the Clear button.

To switch back to the current record without using the search criteria you enter, click the Form button. (This button replaces the Criteria button as soon as you click the Criteria button.)

About This Article

This article is from the book:

About the book author:

Greg Harvey, PhD is the President of Mind Over Media. Greg wrote his first computer book more than twenty years ago and since that time, he has amassed a long list of bestselling titles including Excel All-In-One For Dummies (all editions) and Excel Workbook For Dummies (all editions).

This article can be found in the category: