Microsoft Office Tutorials and References
In Depth Information
COPY AN EXACT FORMULA BY USING DITTO MARKS
Breaking It Down: There are two amazing features here. First, you can
successfully sort a subtotaled data set when it is in the collapsed state. Excel
actually rearranges groups of rows while doing the sort. (Each group contains
the hidden detail rows for one customer and the visible subtotal row.)
Second, in step 5, you take advantage of the fact that charts by default hide
data that is hidden in the worksheet. Although your selection might include
rows 1 through 21, the chart shows only the visible subtotals in rows 5, 9, 13,
17, and 21.
Summary: Excel properly sorts data when you’ve collapsed the view to show
only the subtotals. After you sort the data to ﬁ nd the ﬁ ve largest customers, you
can use Excel to create a chart based on the data.
COPY AN EXACT FORMULA
BY USING DITTO MARKS
Challenge: You’ve entered total formulas in row 23 of Figure 70. Immediately
below those formulas, you want to enter average formulas. However, if you
copied cell B23 to B24, the range referenced in the formula would automatically
Figure 70. You want to replicate the total formulas in row 23 as averages in row 24.
Solution: Do you remember back in school, when you used to use ditto marks
to mean “repeat the same value as above”?
Learn Excel 2007 from MrExcel
“ “ “ “