Drilling down in an Excel Income Statement

[GTretick]

I am designing an income statement in excel. One of the features I would like it to do is to be able to drill down into the detail of the category.

I have attached a simple dummy partial income statement for discussion and perhaps modification by a replier to get me started.

In my example I have two expense categories: Advertising and Office. Each of these can be expanded into three sub-categories and these in turn can be expanded into two or three "micro-categories".

I would like to be able to start with a rolled up statement for a clean look. As I need to look into an anomaly, I would like to expand that category to the sub-category and again to the micro-category.

Opening one sub or micro-category should not automatically open all of the other sub or micro-categories. They should be able to collapse nicely as well.

Using the hide and unhide command helps but it is both clunky and when unhiding from the collapsed view, gives ALL the detail rather than a true drill down.

The grouping command is also an alternative I have explored but nesting them was frustrating with how thing were spaced on the sheet as well as it starts to eat into my screen space as the left hand sidebar keeps expanding with each nest. (my finished product potentially could contain more than two nests).

I believe my answer lies in a macro that hides rows based on certain conditions. I am not very good in VBA however so I would need a kick start. I could probably figure out how to amend the code for wider application after that.

If anybody can come up with a some code or another eloquent solution I (and probably several other accountants) would be grateful to see your solution. I am flexible to change layout a bit if that is necessary.

Thanks.

 

[Ken Puls]

Honestly, I think what you need is a PivotTable. Much of the functionality you're looking for is built in, and it's going to be a HUGE amount easier to build and maintain than VBA.

Have a look at the attached and click the +/- buttons to see what I'm getting at. This is built off the data you provided, I just modified the table to put the data into good source for the Pivot.

 

[GTretick]

Thanks for the reply.

Yes this does seem to do what I was asking for.

The downside to using a pivot table is that it does not look at sleek as I'd like for a financial report. My own personal talent around pivot tables is fairly mediocre so it is not my first choice as to how I would build an income statement.

I will mull this option some more to see if I can successfully incorporate it into my solution.

 

[Ken Puls]

I hear you on this, but... it does have several advantages over trying to code things from scratch.

The PivotTable refreshes from the data set. Providing you have that in a table, then you'll never miss a row. Take it from someone who built financials with over 26,500 formulas that had to be maintained... every time we added a new account, the entire set of statements had to be updated. Had we been using Pivots, it would have been a right click and done with new accounts automatically pulled in.

The formatting is a challenge, but keep in mind that you can do these things:
-Hide the header row... this allows you to build a nicer one in the row above the Pivot
-Change the Pivot table layout to get more spacing where you need it
-Use slicers to filter

What is hard though, is getting the number formats to show nicely for an income statement. (Having revenue & expenses both positive, yet still add up.) For this, there is Power Pivot... http://www.excelguru.ca/content.php?268-Using-HASONEVALUE-in-a-DAX-IF-statement

 

[GTretick]

I have played with various solutions with different degrees of success. Here are 5 example files

1) Dummy FS 1 (Grouping) : This uses simple grouping layouts without macros. It works as intended except limitations include
a) Expand/collapse button is offset one row lower than the row it controls.
b) I need to add extra rows in the drill down data for proper display.
c) The expansion field on the left encroaches on screen room.
2) Dummy FS 2 (Macro Multi Button-Move&Size) : This uses tick boxes combined with a macro I lifted off the internet and modified. It works exactly as I
intended except that when I save and close the file with the rows expanded, it reopens with the tick boxes hidden between consecutive rows. This is
not a workable solution.
3) Dummy FS 3 (Macro Multi Button-Move&Size Test) : An example of the problem I described in (2) above with the first category "Advertising" saved in
a collapsed mode. Expanding that category will have the sub-tick boxes messed up. The tick boxes are set to Move & Size with cells which from what
I can tell is the correct setting.
4) Dummy FS 4 (Macro Drop Down Auto) : With the failure of the tick boxes I decided to use a data validation drop down box as my toggle. My hope was
that I could enable a macro upon changing the drop down. I lifted a macro from a help site and went ahead modifying it for my own use. The
weird thing was that when I modified it the original code was still being read. After some trial I actually erased all the code and it was still picking up
the original code. I included it here as a point of academic interest for those who might be curious. I can't figure out why it is doing this.
5) Dummy FS 5 (Macro Drop Down Single Button) : After the failure in (4) above I decided to trigger the macro with the push of a single button rather
than rely on an auto trigger upon use of the drop down. It is more cumbersome but it works.

In summary 1 and 5 work as intended each with their own drawbacks.

2/3 and 4 are closer to what I was hoping to achieve but had major flaws and as such are incomplete.

I consider my problem solved but if anyone wants to tinker with anything I would be curious to see if these could be improved upon.

 

[Ken Puls]

Provided below is an alternate version of your macro for version 5. Biggest difference is that it will dynamically expand to as many rows as you need, rather than get capped at 50:

Sub DrillDown()
    Dim cl As Range
    Dim rngExamine As Range
    
    'Turn off screen updates for speed
    Application.ScreenUpdating = False
    
    With Worksheets("Single Button")
        'Set range to examine from E2 to last used cell in column E
        Set rngExamine = .Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row)


        'Unhide all rows so that loop examines every row
        rngExamine.EntireRow.Hidden = False


        'Set rows visible/hidden as required
        For Each cl In rngExamine
            If cl.Value = 1 Then
                cl.EntireRow.Hidden = True
            Else
                cl.EntireRow.Hidden = False
            End If
        Next cl
    End With
    
    'Resume screen updates
    Application.ScreenUpdating = True
End Sub

(FYI, I'm not stoked with the performance of this, but I'm more concerned about your statement missing the last row(s) due to a hard coded value in code that no one can see.)

 

[GTretick]

Thanks Ken. I didn't know how to set a variable range so I went with a hard range. Appreciate the feedback.

 

[GTretick]

I want to use this same concept you posted April 4th except to hide columns rather than rows based on the numeric value of 1.

I've tried to "flip" the coding but haven't had any success.

Can you show me the code with row 5 as the examination row rather than Column E?

Thanks.

 

[Ken Puls]

Something like this:

Sub hidethem()    Dim cl As Range
    Dim rngSearch As Range
    
    With ActiveSheet
        Set rngSearch = .Range("A5:" & .Cells(5, .Columns.Count).End(xlToLeft).Address)
    End With
        
    
    For Each cl In rngSearch
        If cl.Value = 1 Then
            cl.EntireColumn.Hidden = True
        Else
            cl.EntireColumn.Hidden = False
        End If
        
    Next cl
End Sub

 

[GTretick]

Yes this works!

Just a side note: With both the vertical and horizontal hide routines, I noticed that when the end cells are hidden and you want to unhide them the routine doesn't work. I reasoned that because the cells are hidden that the search doesn't find them. To fix this I placed a character past the end of the examination row/column for it to "find". This did the trick in each case.

 

[Ken Puls]

Excellent. I can see where the issue comes up yes, but glad you've got a way to sort it out.