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.

Notes:

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

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar