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

LE10000248.jpg 

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.

LE10000249.jpg 

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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:



Popular Pages

More Info