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 ...
Freeze or Lock Specific Rows and Columns when Scrolling in Excel
Prevent specific rows or columns from moving when you scroll through a spreadsheet in Excel. This allows you to kee ...
Print Specific Rows or Columns on Each Page in Excel
How to repeat specific rows or columns on every printed page so that you know what each column or row means when y ...

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

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!


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?


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 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?

If a user opens a workbook in read only becuase someone else is locking it would they still be able to manipulate a pivot chart in the workbook. Mainly just collapsing and expanding groups.


We have an SSRS report that gets exported to Excel. due to a bug in SSRS 2005, it is exporting the report with all the result groupings expanded. Is there a way to have Excel collapse everything all at once, or does the user have to collapse the results one grouping at a time?

Is it possible to use a loop thru a list of names in col A of a worksheet and create new folders in Windows Explorer with these names?

If in Col A was a list of states:

can a macro be run to create folders in C:\Test with these state names?


HI, I have a folder structure in excel. I want that there should be a Plus sign next to it, so that they can collapse OR expand.
A good example will be how we have in windows Explorer, If I click on folder it open more sub-folders.
Similaraly, If I click on cell (may be having a +sign OR - sign next to it) it should expand/collapse and show me the remaining rows.

Thanks for your help

I'm sure this is really easy, but I'm learning and not having luck in the search option for previous posts. Thanks for your patience in advance.

I want to open a windows explorer window by using a button in Excel.

Any ideas?

Thanks Turbo!

I have a 21000 line bill of material that is indented by level.
Column A has level number.

I am trying to use the group command to allow easy browsing by expanding and collapsing. This is straight forward to do manually but is now a weekly update so I don't want to go through multilpe levels and thousands of rows.

Can I somehow automate grouping rows through multiple levels


1 assy 1
2 subassy 1
3 part 1
3 part 2
2 subassy 2
3 part 3
2 part 4


Attached a view of desired output.

I know this is not excel specific, but does anyone know a good way to export the file names and properties of a folder in windows explorer to an excel file? For example, I want to create a CSV file that has in it's columns the file name, file size, type, date modified, etc. I would imagine that there would be a utility in windows explorer, but i have looked with no success. Is there an aftermarket product that will do that?

Hello, I thought I would try to write a macro to expand and collapse a pivot field. I recorded both expanding and collapsing the pivot field in the macro recorder and tried to combine the two with an if statement but it is failing on the first line of the macro with a "Run time erro 1004: application defined or object defined erro" message. Can someone tell me why as I have no idea.


Sub expandyeareco()
' expandyeareco Macro
' Expand the pivot field


    If ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ShowDetail = True Then
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ShowDetail = False
    If ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ShowDetail = False Then
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ShowDetail = True
    End If
    End If
End Sub

Hey all,

I know you can create a group of rows or columns manually and collapse them... but I wondered if there was a way to set up these groupings automatically, say based on a date range? So everything in "May 07" gets put into a "May 07" grouping?

And is there a way to label these groupings to make them more easy to identify? Thanks, I know I might be asking a toughy here... =)

Hello All,

I have a macro that has run and created multiple subtotals. At the end, I
would like the macro to collapse all of the subtotal ranges so that the user
will see just the summary subtotal data. Does anyone have any ideas on how
to accomplish this?



good afternoon, everyone!

I have been trying to solve a slight problem that has been bothering me for a while.

The problem is as follows: I usually use the white color for all cells on my worksheets (adds a bit of professional look) but when it comes to placing pivot tables on a worksheet I want the pivot table to fit the entire worksheet by being with the same stylistic look (I want all of the cells to be in white color). It looks okay if I preliminary paint all the cells into white and then start to add rows/columns. But the problem starts when I begin to collapse rows or columns. The area by which the pivot table was decreased gets regular formatting and the cell borders appear but I don't want that. So the entire worksheet is white with no borders but the area of the row/column that was just collapsed has a different formatting. I already tried conditional formatting and have set rules that stipulate painting all the cells that are (and are not) empty in white but anyway the problems remains.

Anyone knows what should be done to solve this?

PS I also tried formatting cells in pivot table options but the same result

many thanks in advance

I work in Excel 2013, under Windows 8, and when I access the Review - Protect Workbook, in the "Protect Structure and Windows" window the "Windows" option is grayed out. My workbook is not shared or protected in any other way. I tried creating a new workbook to see what happens: even if I have absolutely no content in the workbook, the "Windows" option is still grayed out. Can anyone please give me an idea as to why the option is not available? Thank you.

I am having difficulty sorting out columns in my 2007 Excel spreadsheet.
The option to sort under the editing header is greyed out. If I right click on an individual column the option is there but it does not sort it correctly. The usual option asking if I wish to expand the selecetion also does not appear.
If I copy the spreadsheet and then paste it into a new sheet then the sort works fine.
Any help would be much appreciated.

Hello Everybody,

The auto expand tables' new row format does not match the rest of the table.

Using VBA to autoexpand a table by finding the row below the table and making an entry. There is headers and a total row.

This happens also when manually turning off the total row, and then typing below the table to trigger the auto expand

20 466 3/7/11 419 Pam $ 2,076.35 3/15/11 (2,076.35) 4 466 21 467 3/11/11 420 Joe $ 1,825.00 3/19/11 (2,125.00) 9 467 22 456 Mar-4 123 sam 12.45 18-Mar 4-Mar 55.45 -20 not same not same 456

The last row above is of the wrong format.

Maybe I could use a line of code to fill a format down if anyone knows of such, but before that I was wondering if there was some kind of option I am missing.

This has not always happened.

Any advice is greatly appreciated.


Hi there.

I'm using Excel 2003 and Ive got 2 combo boxes that appear in rows 3 & 5 respectively of my worksheet. Rows 3-12 have been grouped using Data Grouping. My problem is on collapsing the grouping to hide rows 3 to 12 the rows are collapsed alright but both combo boxes are pushed down to row 13 instead of being hidden. The Object Positioning Property says "Move but don't size with cells".

Any ideas on how I sort this? Thanks in advance.

I have two option buttons on each of five spreadsheets in my Excel workbook.

Selecting the first option button enables an x to be written to a cell when that cell is clicked with the mouse.

Selecting the second option button disables the writing of the x when the cell is clicked with the mouse.

I have these working quite well.

My problem is that I want the second option button selected (disable writing of x) when I enter the sheet. I'm pretty certain that I need the code in Sub Worksheet_Activate() but I haven't been able to find the VBA statement to set the second option button on.

Also, unlike having the option buttons on a form, when I select the option button on the spreadsheet in Design mode I can see no property sheet indicating the name, value, etc of the option button.

Here's a basic question:
I'm using Shell "explorer", vbNormalFocus to open windows explorer, and when explorer opens it is pointing toward My Documents. I would like for it to point to My Computer instead, or possibly one of the other drives. What do I need to add?


I am trying to write a macro to collapse all groupings (rows and columns) on each worksheet within a workbook. The following is what I have, but it does not seem to be doing anything. When I run this macro my screen shows "Calculating: (16 Processor(s)): x%" in the bottom right hand corner. It runs through this several times from 1-100% but does not seem to do anything as far as collapsing the rows and columns goes. Any help would be greatly appreciated.

Sub CollapseGroupings()
'Collapses all rows and columns on each worksheet of a workbook
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Next wsSheet

End Sub