- Excel Book Excerpt
Excel Ditto The Formula Above
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Ditto The Formula Above
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.
Figure 272 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.
Figure 273 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.
For more resources for Microsoft Excel: