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

change.

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”?

Title

Author

Learn Excel 2007 from MrExcel

Jelen, Bill

“ “ “ “

“ “