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.