Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

Finding and Replacing Data in Excel 2007

Excel 2007's Find and Replace feature can be a powerful tool. Use Find and Replace to locate — and optionally replace — text or values in a worksheet. You can narrow the search results by specifying formatting to look for as well as other search options, including Match Case.

Finding data

Follow these steps to locate data in a worksheet:

  1. Choose Find & Select in the Editing group on the Home tab, and then select Find (or press Ctrl+F).

    The Find and Replace dialog box appears, with the Find tab on top.

  2. In the Find What box, enter the data you want to locate.

  3. (Optional) Click the Options button to expand the dialog box and specify any desired options.

    • Within: Search just the current worksheet or the entire workbook.

    • Search: Select whether to search first across the rows or down the columns.

    • Look In: Select whether you want to search through the values or formula results, through the actual formulas, or if you want to look in the comments.

    • Match Case: Check this box if you want your search to be case-specific.

    • Match Entire Cell Contents: Check this box if you want your search results to list only the items that exactly match your search criteria.

      Specify search options on the Find tab in the Find and Replace dialog box.
      Specify search options on the Find tab in the Find and Replace dialog box.
  4. Click Find Next.

    Excel jumps to the first occurrence of the match. If this isn't the entry you're looking for, click Find Next again. Excel advises you if it doesn't find the data you're searching for.

  5. Click Close when you've located the entry you want.

You can use wildcards to search for data in your worksheets. Use the ? (question mark) to indicate a single unknown character, or the * (asterisk) to indicate multiple unknown characters.

Replacing data

To find and replace data in a worksheet, follow these steps:

  1. Choose Find & Select in the Editing group on the Home tab, and then select Replace (or press Ctrl+H).

    The Find and Replace dialog box appears with the Replace tab on top.

  2. In the Find What box, enter the data you want to locate.

  3. In the Replace With box, enter the data with which you want to replace the found data.

    Use the Find and Replace feature to exchange data in Excel 2007.
    Use the Find and Replace feature to exchange data in Excel 2007.
  4. (Optional) Click the Options button and specify any desired options.

  5. Click Find Next to locate the first occurrence or click Find All to display a list of all occurrences.

    You can sort the results of a Find All search by clicking a column heading.

  6. If you want to use the replacement data, click Replace.

    Excel performs the replacement and locates the next occurrence.

  7. If you want to replace all occurrences at the same time, click Replace All.

    Excel displays an information box indicating the number of replacements made.

  8. Click OK in the alert box and then click Close.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.