MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy An Exact Formula By Using Ditto Marks


April 16, 2021 - by Bill Jelen

Copy An Exact Formula By Using Ditto Marks

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.

Figure 70. You want to replicate the total formulas in row 23 as averages in row 24.
Figure 70. You want to replicate the total formulas in row 23 as averages in row 24.

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:

  1. Select cells B24:E24.
  2. 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.
  3. Press F2 to change the mode from Enter to Edit.
  4. Press Home to move to the beginning of the formula. Press the right arrow key. Type Average. Press the Delete key three times.
  5. 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:

  1. Select cells B23:E23
  2. Right-click the border of the selection. While holding down the right mouse button, drag down to row 24.
  3. 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.



Bill Jelen is the author / co-author of:

MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.