Excel: Ditto The Formula Above

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. 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.

  2. 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.