Ditto The Formula Above

January 06, 2022 - by Bill Jelen

Problem: I routinely have to sum and average the same range. The sum is easy enough with the AutoSum. But when I try to do the average, the formula above is in the way.

A different problem. Now there are temperatures in B5:B10. In B11, you want the total. In B12, you want the average. The AutoSum already provided the total =SUM(B5:B10) in cell B11. How do you get the AVERAGE in B11? — Figure 258. Add a total and an average.

Strategy: Go to cell B12. Hold Ctrl while you press the key with the ditto mark. (Remember the ditto mark from elementary school? It was a double quotation mark: “.) Technically, you are pressing Ctrl+Apostrophe, but think of it as Ctrl+Ditto.

Excel will make an exact copy of the formula above and show you the provisional formula. Why is this better than a copy and paste? A copied formula would change the B5:B10 range to be B6:B11. A dittoed range will keep the reference to B5:B10.

From cell B12, press Ctrl+Apostrophe. This brings the formula from above down without changing the cell references. You are left in edit mode, with the flashing insertion point at the end of the formula. From this point, press Home, Right Arrow, Delete, Delete, Delete and type AVERAGE. — Figure 259. Ctrl+Ditto copies the formula without changing the reference.

From the point in the figure above, you can press F2, Home, Right Arrow, AVERAGE, Delete, Delete, Delete, Enter.

This article is an excerpt from Power Excel With MrExcel

Title photo by Possessed Photography on Unsplash