I
was recently creating an Excel spreadsheet template for a friend and I needed
to know how to create collapsible rows in Excel. I had a worksheet that listed
student’s names and information. My friend wanted a +/- sign at the beginning
of each row representing different groups of students that could be clicked to
reveal grades for various classes. The Group function in Excel presented the
perfect solution to this situation.
One
method often used to collapse rows or columns in Excel is by using the Group
function.
Go to the Data tab, select the rows or columns you want to group,
then select the Group icon (located
in the Outline are). This will result in a button being placed to the left of
the row number column and allow you to instantly collapse or hide the grouped
rows. To ungroup the rows simply hit the Ungroup
button (also in the Outline area).
You can use keyboard shortcuts to improve your speed and
efficiency when applying this method. First, select the Row or Column range,
then;
To group:
<Shift> <Alt> <RightArrow>
To ungroup:
<Shift> <Alt> <LeftArrow>
To retain the Groups, but toggle hide/unhide the symbols:
<Ctrl> <8>
(Using the "8" that's under the function keys, *not* from the num keypad.)
To group:
<Shift> <Alt> <RightArrow>
To ungroup:
<Shift> <Alt> <LeftArrow>
To retain the Groups, but toggle hide/unhide the symbols:
<Ctrl> <8>
(Using the "8" that's under the function keys, *not* from the num keypad.)
Another option to group rows would be to apply Subtotals to your range. You'll get those outlining symbols and even a subtotal row between each group. The Subtotals
button is also in the Outline section of the Data tab.
I’ve created a short how to video to show you exactly how to
collapse rows in Excel. Check it out below:
So
that’s how you expand or collapse a group of cells in Excel. Pretty easy, huh?
-Nick
Easily
Grouping Columns and Rows in Excel
