Problem: When I need to build a complex calculation, I sometimes need to build several intermediate formulas to help figure out the problem. When these formulas are all working, can I combine the logic from the intermediate formulas into a single formula?
Strategy: Using temporary sub-formulas is a great way to figure out a somewhat complex formula. There is a relatively easy way to get all of these formulas back into a single formula.
Consider the previous example, a formula in J7 =INDEX(B2:G20,J5,J6) references two other cells J5 and J6. Each of those cells contain a formula.
Strategy: You can select characters in the formula bar and copy them to the Clipboard with Ctrl+C. When you copy an entire cell, you introduce many complexities, including the problem that you cannot paste this cell into the middle of a formula or into the Replace dialog. Instead, by copying characters from the formula bar, you have regular text on the Clipboard and can either paste into another formula or in the Replace dialog. Here's what you do:
- Select cell J5. In the formula bar, click the mouse after the equal sign and drag to the end of the formula. Press Ctrl+C to copy these characters to the Clipboard. Exit Edit mode by pressing the Esc key.
- Select cell J7. In the formula bar, highlight the reference to J5
- Press Ctrl+V to paste the formula from J5 to replace the reference to J5.
- Select cell J6. Press F2, Ctrl+Shift+Home, Shift+Right Arrow, Ctrl+C, Esc. These keyboard shortcuts edit the cell, then select everything but the equals sign.
- Select cell J7. Select the reference to J6
- Press Ctrl+V to paste the formula from J6 to replace the reference to J6.
The result is a single formula that replaces the three formulas.
- This is an intimidating formula once it is all combined.
Result: Your coworkers will be amazed at your ability to create massive formulas.
Alternate Strategy: Instead of following the steps just outlined, you can use the Replace dialog to combine the intermediate formulas into mega-formulas. Follow these steps:
- Select cell J5. In the formula bar, use the mouse to select everything from immediately after the equals sign to the end of the formula. Press Ctrl+C to copy those characters to the Clipboard. Press the Esc key to exit the formula bar.
- Select cells J7:J8. Gotcha: Make sure this selection contains two cells, even if you are only working on a single formula! If you select two or more cells, the Replace All command will work only within the selection. If you select only one cell, the Replace All command will extend to all 17 billion cells in the worksheet.
- Select Home, Find & Select, Replace or Ctrl+H.
- In the Find What box, type J5.
- Tab to the Replace With box. Press Ctrl+V. Excel will copy the characters from the J5 formula into the dialog.
- Click the Options button.
- Make sure the Look In dropdown is set to Formulas. Make sure that Match Entire Cell Contents is unchecked. (If you start a new Excel session, both of these settings will be correct. However, the dialog remembers the settings from the last find and replace you did earlier in the current session, so it is always worth your time to click the Options button to make sure these settings are correct.)
- Click Replace All. Excel will remove the reference to J5 from the selected cells and replace it with the characters from J5.
- Repeat step 1 for cell J6.
- Repeat steps 2"8.
Depending on how many times the intermediate formulas are referenced in the final formula, using Find and Replace might be faster than using the copy and paste method.
Gotcha: Be careful that your target formulas don't contain references that contain some other form of B2 and C2, such as B20 or C210909. If your formulas do contain such references, when you replace B2, Excel will blindly put the B2 formula where the characters B2 appear in B20.