Find and Replace Elements
With Find and Replace, you can:
- Find an element that is available on many worksheets in a workbook or on a single worksheet.
- Replace each instance of the element one at a time or all at once.
Use find and replace when you want to make global changes across of many sheets. You can find the following elements that are added to the worksheet. You cannot find labels and cannot find elements applied through a filter:
Find and Replace is available from the following areas:
- The OfficeConnect tab. From the Find drop-down, select Find or Replace. The Find and Replace dialog box opens to the selected action.
- The Elements tab or the Review tab in the Reporting pane. Right-click an element and select Find. The Find and Replace dialog box opens with the selected element pre-populated in the Find tree.
Find and Replace Abilities
- Auto-filter search: As you type the keyword, OfficeConnect filters the tree to show only the elements matching the text entry.
- Find and replace scope: Choose to find in selected sheets or entire workbook.
- Selected worksheets: Finds instances on selected worksheets. Ctrl-click each worksheet tab before finding the element.
- Workbook: Finds instances across all sheets in the workbook.
- Find and Replace options:
- Find All: Finds all instances.
- Find Next: Finds the next instance.
- Replace All: Replaces all found instances. When the replace is complete, a message appears indicating the number of instances replaced.
- Replace: Replaces the instance and goes to the next instance.
- Remove: Remove the element from the location. You can remove an element by finding it, selecting Remove the element instead of replacing it from the Replace tab, and clicking Replace.
Example: Global Find and Replace an Element in Workbook
Suppose that you are responsible for developing and maintaining a series of financial reports – based on the 2015 Draft Budget version. Organizational changes occurred halfway through fiscal year that require a refactoring of the draft budget. You want to replace the old version on all sheets in the workbook with a new version called 2015 Working Budget.
- On the Find tab, type a key word in the search text box. Select the element from the tree navigation to highlight the element to find.
Example: Type draft budget
- Select Workbook to find all instances across all sheets in the workbook.
- Click Find All. A list of sheets appears in the lower pane indicating where instances of the element is found.
- On the Replace tab, type a key word in the search box. Select the element from the tree navigation to highlight the element to replace.
Example: Type working budget
Click Replace All. When complete, a message appears indicating the number of instances that were replaced.