Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Expand & Collapsing Rows/columns

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have seen on other Spreadsheets a expanding and collapsing option for excessive amounts of data.

How do you do this ?

To be more informative. It is tha same type of option you have on folders in Windows Explorer.


View Answers     

Similar Excel Tutorials

Group Data Together for Increased Readability in Excel
How to group data together or collapse it in order to focus only on the important data in Excel. This allows you t ...
Quickly Resize Multiple Columns or Rows at Once in Excel
How to quickly resize multiple columns and rows at once in Excel.  This avoids having to individually resize rows ...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
How to Resize Rows and Columns in Excel Quickly
Resizing rows and columns in Excel is an easy process. Simply left click in between the columns and drag the mouse ...

Helpful Excel Macros

Disables the "Save As" Feature in Excel
- This macro will disable the Save As feature in excel. This means that a user will not be able to resave the workbook un
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Delete Rows in Excel if Completely Empty
- This macro will delete only completely blank rows in an excel spreadsheet. It allows you to make a selection of rows, ru
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics

I attached a sample spreadsheet. I want the results to remain in either currency or accounting but they always return to general when collapsing and expanding.



I am using Excel 2010 and want to collapse some items in a list under a header essentially.

I know I can do this using the group cells option and produce an expand icon on the far left but I was wodnering if there is a way to have the expand icon next to the text in the header cell. i.e:

Heading +
Sub 1
Sub 2
Sub 3

so that when collapsed just see 'Heading'.

Some background on what this is for: I am compiling a database of who (of a group of employees) has access to certain folders on our company cloud server. To do this I have created a matrix of employees against folders formatted to show green if someone has access and red if they do not. Some of the folders have sub folders with further restrictions which I also need to include. Although I could include these as rows of equal importance to the root folder ideally I would like to work it so that the root folders are displayed and if access to a sub-folder needs to be viewed the root folder row can have the ability to expand and reveal the cells for the sub folder (as displayed above).

Any help greatly appreciated- I think I have seen this done in an excel sheet before but I cannot remember where I saw it!


Is there a shortcut to expand and collapse groups of rows or columns (the +/-
grouping sets that can be made)?


Hi guys, I am new to this so will try and explain my problem as simply as possible.

I am trying to basically show a visual representation of a calendar of events in the gantt chart form (attached example), so would love to be able to change the view so I can see by events by day, by week, by month and by quarter etc by expanding/collapsing the relevant cells. I have been able to work out how to expand all cells so that I can see the chart by day (although as you can imagine, this is a tad excessive considering it goes on for three years!). However I can't work out the following:

a) I don't know how to get it to revert to the overview you currently see.

b) I don't know how to expand for week, month and quarter (or if this is even possible) - and how to expand only a certain selection (e.g. January or Qtr 1).

Does anyone have any ideas? I have been searching a number of forums and posts and cannot find anything that helps! Please bear in mind that I am not an excel expert as well.

Thanks for any help!



Is there any way to get the [+] and [-] collapsing menu system on across columns, as well as on rows. I basically want a user friendly way to show/hide detailed workings, without using macros or the row/column hide/unhide system.


I have rows of data that I want to group to enable expanding/collapsing for easier viewing.
I'd also like to be able to protect the sheet so some of the cells cant be changed.
However when I protect the sheet and select the +/- to expand/collapse Excel returns a message that you cannot use this command on a protected sheet.

Any way that I can actually do this?


I have a file with over 600 tabs. Of the 600 tabs, there should be 12 groupings of them. I don't believe I can group them the way you can rows/columns with the plus and minus expand/collapse buttons. What code do I need to use to group specific tabs together? Hopefully I just need to add the tab names that need to be grouped together in the code. Any help would be much appreciated!

Thank you

Is there any way to protect the contents of the cells while still allowing the expanding and collapsing of the grouped rows?

Example Attached

Happy Masters to all,

I need to find a way to expand all groupings (rows) so that my routine that enters the data from a form to the sheet will work.
The problem is the data entry code barfs if the groupings are in their grouped state. I need to expand all groups, write the data to the sheet, then return the grouping to their original state.

Other than adding grouping via code, I have no idea how else to manipulate the groupings.


i am doing a subtotal on my report and for some reason, i am not seeing the little 1, 2, 3, and the pluses and minuses on the left size of the spreadsheet. the plus will expand the data to show the detail for each category's subtotal and the minus will collapse the data to only show the subtotal per category.

the subtotal is working, but i'm not sure why i can't expand and collapse the subtotals.

Hi there,

I have grouped a number of rows in my excel sheet so I can collapse the rows
into 1 row.

I then want to protect the worksheet from end users however I also want them
to be able to expand and collapse the group with the small grey plus/minus
box that appears next to the grouped column rows.

Currently when i put protection on the worksheet it protects the sheet but
it also restricts users from expanding/collapsing the grouped rows. Is there
a way around this?

Thanks in advance.

I am working on a document where I want people to input text into a text box. I want to give them enough room to write, but would like the text boxes to automatically expand so that people can type in any amount of text. If you manually expand the box, this hides data that is below the text box (the 'automatic size' option in the text box properties only makes the text box expands sideways - I want it to expand downwards).

The text box option in the control toolbar does the same - it hides the data below when expanding (plus the auto size function makes the box almost dissapear if you delete its content)

Is there a macro I could use to make a regular text box expand without hiding the data below? Alternatively, I thought a macro (linked to a button dubbed "Click here to expand the text box") could be used to expand the row height (when the "move and size with cells" option is on), but it would need to expand every time the button is clicked...

I'm no expert in VB but can make a macro work if it is ready to use

Can anyone help?



I am using command buttons to link to graphs. The command buttons are located within columns that I am making collapsable/expandable. Whenever I expand the columns, the command buttons do not maintain their original position and stack on top of each other. How can I manipulate them so they stay in the original position and remain viewable after the columns have been expanded?



When clicking on the grouping numbers (to expand / collapse all groupings in that level) the sheet (heavily calculated) can hang for a long period before collapsing / expanding all groups.

Is there a way to programmatically catch the

ActiveSheet.Outline.ShowLevels RowLevels:=4

Envet and stop calculation before it starts?


This may be something simple, but we're a little frustrated by this. We have a sheet that is in the form of a calendar. Each day uses 2 columns, so we're trying to merge and center these two columns. So that part's easy. We want to use Text wrap, which again is easy.... Then when we double click on the row to automatically resize the row, it doesn't do what it's supposed to. It leaves the row at the one row height instead of resizing it to 2 or 3 rows height... Is there a way to do this, or do I need some sort of macro to allow this to happen?? Thanks...


I need to figure out how to have a column collapse. NOT HIDE it, though. So that there's an icon and I can just click it to expand it or collapse it. Thanks.

Hi everyone,

So I spent a couple of days searching for an answer, but I've had no luck so far. I am working on a dashboard and have a few pivot tables one right on top of the other. I have enough rows hidden between them so that I don't get the pivot tables cannot overlap another error, but the code I'm using seems inefficient. I want the rows to unhide when they become populated with data, ie after I expand a field, and hide the rows when they are empty.

Here is the code I am currently using.

Please Login or Register  to view this content.

The problem is that every time I expand or collapse a field the for loop checks through all of the cells and can take a couple seconds, which does not seem user friendly for the people who read the report. I am trying to figure out how to exit out of the for loop when it unhides the necessary amount of rows, ie. if I expand a field and it populates 10 cells with data, then I want the macro to unhide the 10 cells and then stop. When I collapse a field I want the macro to hide the cells that no longer have any data in them and then stop the for loop there without looping through all 84 rows.

I hope this is enough information. Please let me know if other details are required.

Thank you!

I suspect I need a class module for this...

How do I trap the event when a user chooses to expand / collapse outlines (columns in this instance) on a worksheet?

Basicallly, when attempting to expand I want to prompt the user to enter a password and then unprotect the sheet. And if collapsing to prompt again and then protect the sheet. I have this bit covered, just not sure how to trap the event.


In Excel 2003, how do you group cell rows into collapsable regions that can be expanded and contracted with the little plus and minus signs? Similar to the Folders view in Windows Explorer.

Hi there,

I've got a problem with grouping and collapsing data.
I've got a schedule of more than 3000 items and I wanted to group the data into the susections. That part was not a biggie.
However, I encountered a problem when I was trying to collapse the group. Please have a look at the attachment. Say I want to collapse section 2.1 and that is not a problem. But then I cannot collapse section 2.2. although it has been groupes.

Can someone advise?

Thanks a lot,


Greetings. I posted this issue on another forum a couple of days ago but got no replies, so I thought I would see if anyone here could help.

I'm developing a userform with a multipage object on it with four tabs. Three of them have treeview (tvw) objects. I noticed a delay in switching from one tvw tab to the other, but didn't think much of it until I switched to the 4th tab, and then back to one of the others. In that case, all of the nodes in the tvw showed up as expanded, though they hadn't been before.

To find what was triggering that, I put debug stops on the tvw Expand and Collapse events. I found that when I switch from one tvw to another, for each node that was collapsed it fires the Expand event and then the Collapse event. For each node that was expanded, it fires the Expand event twice. This explains the delay I had noticed, and happens when I go from one tvw tab to another. When I go from the non-tvw tab to a tvw tab, it does the same thing, except instead of expanding and then collapsing the collapsed nodes, it expands them twice also, so they end up expanded.

I can't find anything in my code that would trigger these events, and can't account for the difference in which tab I'm coming from. I looked at the call stack during the events, and they show [<Non-Basic Code>] below the event. I suppose I could live with the delay, but I don't want the nodes expanding themselves.

All of this happens before the Multipage Change event fires.

Any ideas? Thanks in advance!


I like to color subtotal rows and columns in a distinct color.
Of course, when a field is collapsed, the corresponding subtotals are still displayed and keep the same values, only the details are not shown.

I am a little bit disturbed that when collapsing a field, the color that I have chosen for "totals" is not used anymore to format the corresponding subtotal row or column. Since the numbers and their meaning remain the same as before collapsing, I would greatly prefer that they also keep the same format.

In other words, concerning expanded or collapsed subtotals, I would like to keep their format in relation to their meaning, which is the same with the detail being displayed or without the details being displayed.

Would you know an easy way to do that?
Eventually, would it be possible to define that as a re-usable style?
I would re-use that at least 100 times !

Thanks for your suggestions,


I have created a userform with Command Buttons to open various spreadsheets that we use very often. What I would like to do is add some additional buttons that would open Windows Explorer in a specific folder. Even better if it would default to the "Search" option. Can this be done?

Does anyone know a way to trigger an event by expanding or collapsing the "group and outline" buttons? I want to fire off a module that changes the size and color of particular cells when the grouping button is clicked on.