In a large data list, trying to find a particular record by moving from record to record in Excel 2013 — 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.)