Microsoft Office Tutorials and References
In Depth Information
Ungrouping and grouping columns and rows
INSIDE OUT Selecting only the visible cells
When you collapse part of an outline, Excel hides the columns or rows you don’t want
to see. In Figure 8-75, for example, the detail columns are hidden for the first three
quarters of the year. Ordinarily, if you select a range that includes hidden cells, those
hidden cells are implicitly selected. Whatever you do with these cells also happens to
the hidden cells, so if you want to copy only the displayed totals, using copy and paste
won’t work. Here’s the solution: on the Home tab, click Find & Select, Go To Special,
and select the Visible Cells Only option. This is ideal for copying, charting, or
performing calculations on only those cells that occupy a particular level of your outline. This
feature works the same way in worksheets that have not been outlined; it excludes any
cells in hidden columns or rows from the current selection.
Ungrouping and grouping columns and rows
If the default automatic outline doesn’t give you the structure you expect, you can adjust it
by ungrouping or grouping particular columns or rows. You can easily change the hierarchy
of outlined columns and rows by clicking the Group and Ungroup buttons on the Data tab.
For example, you could select row 8 in the outlined worksheet shown in Figure 8-72 and
click Ungroup to change row 8 from level 2 to level 1. The outlining symbol to the left of
the row moves to the left under the row level symbol labeled 1. To restore the row to its
proper level, click Group.
Note
You cannot ungroup or group a nonadjacent selection, and you cannot ungroup a
selection that’s already at the highest hierarchical level. If you want to ungroup a
toplevel column or row to a higher level so that it appears to be separate from the
remainder of the outline, you have to group all the other levels of the outline instead.
Consolidating worksheets
You can use the Consolidate button on the Data tab to combine the values from a set of
worksheets in the same workbook or from different workbooks. The Consolidate command
lets you assemble information from as many as 255 supporting worksheets in a single
master worksheet and displays the Consolidate dialog box shown in Figure 8-76.
Search JabSto ::




Custom Search