Find and Replace for Series Formulas
The Edit Series Formulas feature works like Find and Replace, but for your charts’ series formulas. For example, you could edit the addresses in these formulas to change the sheet name or the first or last row.
This feature is based on routines described in Change Series Formula – Improved Routines, a tutorial on the Peltier Tech Blog. These routines have been improved and made much more reliable in Peltier Tech Charts for Excel.
Edit Series Formulas in the Peltier Tech Ribbon
Edit Series Formulas is the first button in the Chart Data section of the Peltier Tech custom ribbon tab. You may select one or more charts, or no charts at all, prior to clicking the button.
Edit Series Formulas Dialog
The dialog, shown below, contains a textbox to enter the text you want to search for and replace, and a textbox for the text you want to replace the found text with. There are also one or more buttons, depending on the context of your search. For example, if you select the only chart on the only sheet of the active workbook, the only button will say Change Active Chart Only.
How the Feature Works
The program will cycle through each series of the indicated charts. It will find each instance of the ‘Find’ text and replace it with the ‘Replace with’ text. If Excel disallows a given formula, the replacement will not be made and you will get an error message.
There are several tricks that will make the feature more reliable and easier to use.
- ‘Find old text’ is case sensitive. ‘C’ will find row C, but ‘c’ will not.
- ‘Replace with new text’ is not case sensitive. Excel will accept ‘a’ as a new column letter, and automatically capitalize it in the new formula.
- To ensure that ‘C’ only searches for a column letter and not a character in a sheet name, search for ‘$C$’ (with leading and trailing dollar signs).
- To ensure that ‘1’ only searches for a row number and not a character in a sheet name, search for ‘$1’ (with a leading dollar sign).
- To search for the first row number in an address, search for ‘$1:’ (with a trailing colon).
- To search for the last row number in an address, search for ‘$10,’ (with a trailing comma).
- ‘Replace with new text’ does not need dollar signs matching those in ‘Find old text’. Excel will accept ‘a1’ as a replacement for ‘$B$2’, and automatically convert it to ‘$A$1’ in the new formula.
- Entries in ‘Find old text’ and ‘Replace with new text’ will appear in the dialog when called again from the same active workbook.