How to Hide Column or Rows by Group Function in Excel

We already know hide and unhide row or columns by right click ‘Hide\Unhide’ in Excel. And there is another way to hide row or columns by Group function, and on the other side unhide row or columns by it as well.

Prepare a table contains ID, Name and Score information. By ID classification we can think that these students can be divided into two groups, ID started from 0610… belongs to group1, and ID started from 0620… belongs to group2.

How to Hide 1

If we want to high group1 or group2, we all know that we can select the rows we want to hide and directly right click and select Hide to hide rows for group. Is there another way to hide them?

Hide Row or Columns by Group Function in Excel

Step 1: Select entire rows you want to hide. In this case we select row5 to row7.

How to Hide 2

Step 2: Click on Data->Group to group rows.

How to Hide 3

You can find that after clicking on Group icon, outline area is displayed, and a minus sign is auto displayed in front of the selected rows, actually it displays just before row8. Obviously user can click on the minus sign.

How to Hide 4

Step 3: Click on the minus sign. Verify that rows are hidden now. And minus sign is change to plus sign.

How to Hide 5

Step 4: Click on plus sign again, verify that all hidden rows are expanded again.


If you have only one single level of group, number 1 and 2 are displayed in outline; if you add a new group within the created group, it will show number 1, 2 and 3 in outline.

How to Hide 6

In below screenshot we create a new group only contains two students. Now there two levels of groups.

How to Hide 7

Step 5: If user want to ungroup the group, select the rows you want to ungroup, and click on Data->Ungroup.

How to Hide 8

For example, after step#4, only select row5 and row6, click Ungroup. Then you can find only row7 is included in group.

How to Hide 9

Step 6: Select row7, click on Data->Ungroup->Clear Outline.

How to Hide 10

Verify that group is removed now. Minus sign or plus sign is cleared.

How to Hide 11

Related Posts

Match Single Criteria in Excel

No repeat statistic based on a single condition is used wildly in daily work, for example, count type of products, the duplicate types are not encountered. This article describes the way of no repeats statistics for data in a single ...

Excel Array Construction

This article will talk about how to create one-dimensional array or two-dimensional array by using some functions in Excel. When using array formulas in Excel, we often use functions to construct arrays. Generate Array with ROW or COLUMN Functions Array ...

Excel Array Operation

We have all heard of arrays. Depending on the dimensionality, they are divided into one-dimensional arrays and two-dimensional arrays. Depending on the data type, they can also be divided into numeric arrays, logical arrays, and so on. The concatenation and ...

Excel Array Matrix Operation

In Excel, we have a built-in function used to perform matrix operations. It is the MMULT function. This function accepts two arrays as parameters and returns the product of the two arrays. However, this function has a prerequisite that the ...

A1 Reference Style and R1C2 Reference in Excel

In Excel, there are two reference styles. A1 reference style and R1C2 reference style. These two reference styles can help us find the cell by the cell address. Some functions return these two addresses through the argument settings. A1 Reference ...