4. 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.
5. Select cell J7. Select the reference to J6
6. 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.
Figure 445 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:
1. 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.
2. 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.
3. Select Home, Find & Select, Replace or Ctrl+H.
4. In the Find What box, type J5.
5. Tab to the Replace With box. Press Ctrl+V. Excel will copy the characters from the J5 formula into the dialog.
6. Click the Options button.
7. 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.)
8. Click Replace All. Excel will remove the reference to J5 from the selected cells and replace it with the characters from J5.
9. Repeat step 1 for cell J6.
10. 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.