How to Find Records in an Excel 2007 Table
3 of 12 in Series: The Essentials of Creating and Working with Tables in Excel 2007
When you work with Excel 2007 tables, you can use keystrokes or a data form to move through table records until you find the one you want to edit or delete. In larger tables, you can use search criteria in the data form to look up a record. These methods also work with a normal range of data — one that has not been converted to a table using the Table button on the Insert tab.
The Form button that displays a data form isn’t included on the Excel 2007 Ribbon, but you can add this button to the Quick Access toolbar by using the Customize settings in the Excel Options dialog box.
How to find records in a table manually
Click the Form button on the Quick Access toolbar and then use the following techniques to navigate records in the data form:
Next record: Press the down-arrow key, press Enter, click the Find Next button, or click the down scroll arrow at the bottom of the scroll bar.
Previous record: Press the up-arrow key, press Shift+Enter, click the Find Prev button, or click the up scroll arrow at the top of the scroll bar.
First record: Press Ctrl+up-arrow key, press Ctrl+PgUp, or drag the scroll box to the very top of the scroll bar.
New, blank data form: Press Ctrl+down-arrow key, press Ctrl+PgDn, or drag the scroll box to the very bottom of the scroll bar.
How to use search criteria to find table records
For larger Excel tables, use the Criteria button in the data form to find records. Follow these steps:
Click the Form button in the Quick Access toolbar to open the data form.
Remember that you must add this button to the Quick Access toolbar.
Click the Criteria button in the data form.
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.
Enter the criteria in one or more fields of the data form.
You can use wildcards (such as a question mark for a single character or an asterisk for multiple characters) and comparison operators (such as < or >=), as well as text and values.
For example, if you are searching for a record in an employee table and you know that the person’s last name begins with C and they are located in Boston, you would type C* in the Last Name field and Boston in the Location field to locate the record.Use the Criteria button to search records based on known data.
Click the Find Next button or the Find Prev button.
Excel finds the first record that matches the specified criteria. Repeat this step as needed until you find the desired record.
Click the Form button to return to the data form, or click Close to close the data form.