17

I would like to create indented collapsible row hierarchies in Excel for my spreadsheet.

I have used group function but that becomes hard to manage for me.

Here is an example of what I am trying to create:

https://www.youtube.com/watch?v=CBJY83PTiXs

Cœur
  • 37,241
  • 25
  • 195
  • 267
user2533460
  • 349
  • 3
  • 7
  • 20

2 Answers2

27

A much easier way is to go to Data and select Group or Subtotal. Instant collapsible rows without messing with pivot tables or VBA.

Athlwulf
  • 271
  • 2
  • 3
1

Create a Pivot Table. It has these features and many more.

If you are dead-set on doing this yourself then you could add shapes to the worksheet and use VBA to hide and unhide rows and columns on clicking the shapes.

Andy G
  • 19,232
  • 5
  • 47
  • 69
  • Hi Andrew, I know how to use VBA code to hide rows, so I should use a shape button to create the effect. – user2533460 Jun 29 '13 at 00:45
  • Do you know if that effect is created in a pivot table - the hierarchy structure? ~ shana – user2533460 Jun 29 '13 at 00:46
  • @user2533460 Yes. There are different Report Layouts to choose between, the report automatically appears with little +/- signs, you can Group, Filter, items, etc.. The Pivot Table is based on a regular table though, with column headings. – Andy G Jun 29 '13 at 00:53
  • Andy, do you know how I could use an image to hide and un hide rows? – user2533460 Jun 29 '13 at 21:23
  • `Columns(3).Hidden = True`, `Columns("D:E").Hidden = False`. You then assign a macro containing such code, by right-clicking the image. You need to learn how to use the **Macro Recorder** in Excel to generate initial code. Good luck. – Andy G Jun 29 '13 at 21:32