Excel Create Collapsible Indented Row Hierarchies

Question

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

Torchbearer

Answer 1

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

Furman

Answer 2

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.

Cyprian

Hi Andrew, I know how to use VBA code to hide rows, so I should use a shape button to create the effect. – Torchbearer

Do you know if that effect is created in a pivot table - the hierarchy structure? ~ shana – Torchbearer

@Torchbearer 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. – Cyprian

Andy, do you know how I could use an image to hide and un hide rows? – Torchbearer

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. – Cyprian