Copy An Exact Formula By Using Ditto Marks
April 16, 2021 - by Bill Jelen
Challenge: You’ve entered total formulas in row 23 of Figure 70. Immediately below those formulas, you want to enter average formulas. However, if you copied cell B23 to B24, the range referenced in the formula would automatically change.
Solution: Do you remember back in school, when you used to use ditto marks to mean “repeat the same value as above”?
| Title | Author | ||||
|---|---|---|---|---|---|
| Learn | Excel 2007 | from | MrExcel | Jelen, | Bill |
| “ | “ | “ | “ | “ | “ |
Well, the quotation mark key on your keyboard operates sort of like a ditto mark shortcut!
Follow these steps:
- Select cells B24:E24.
- Hold down the Ctrl key while pressing the apostrophe/quotation mark key. Excel copies the exact formula from cell B23 in cell B24. Excel leaves the formula in Enter mode, with the insertion point at the end of the formula.
- Press F2 to change the mode from Enter to Edit.
- Press Home to move to the beginning of the formula. Press the right arrow key. Type Average. Press the Delete key three times.
- Press Ctrl+Enter to enter similar formulas in B24:E24.
Alternate Strategy: Another solution to this problem is to copy the result of the formula as a value. If you go to cell B24 and press Ctrl+Shift+”, Excel copies the result from B23 as a value in cell B24. Unfortunately, you cannot use this method to fill an entire range, such as B24:E24. In that case, you could follow these steps:
- Select cells B23:E23
- Right-click the border of the selection. While holding down the right mouse button, drag down to row 24.
- Release the mouse button and choose Copy Here as Values Only.
Summary: Use the Ctrl+quotation mark to make an exact copy of a formula.
Title Photo: Faris Mohammed on Unsplash
This article is an excerpt from Excel Gurus Gone Wild.
