Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

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:

  1. 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.

  2. 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.

  3. 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.
    Use the Criteria button to search records based on known data.
  4. 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.

  5. Click the Form button to return to the data form, or click Close to close the data form.

About This Article

This article is from the book:

About the book author:

Greg Harvey has authored tons of computer books, the most recent being Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One Desk Reference For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, Greg went on to teach semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.
His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience: the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.
Greg received his doctorate degree in Humanities in Philosophy and Religion with a concentration in Asian Studies and Comparative Religion last May. Everyone is glad that Greg was finally able to get out of school before he retired.

This article can be found in the category: