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