Create an Excel report that can drill down to display detail

• Download the sample file here (21,768 bytes)

If you've ever had to build Excel reports that let you drill down to more detail, your options have probably been limited. You could create a pivot table, which lets you drill down to the detail behind any value. Problem is, the results are not formatted and they are displayed on another worksheet. You could create an outline, and show your users how to open and close the sections. That gives you control over the formatting but not all users like outlines. That led to the technique in this article which tries to combine the best features of both techniques:

• Double-click a cell to see more detail
• Control the formatting and keep everything on one sheet
• No need to create an outline
• Also, another double-click can hide the detail again

This example is based on a forecast where the summary shows totals for each half year and the full year, going out 4 years. A separate sheet contains the same divisions, along with all of the detail months. Formulas on the summary sheet pull through the data on the detail sheet. When a user double-clicks a half-year summary on the summary sheet, 6 columns are added to the summary sheet to show the detail. If the detail is already displayed, double-clicking removes the detail columns.

Here is the summary sheet, with no detail displayed.
This is the detail sheet
Here is the summary sheet after double-clicking to insert detail columns

Event code on the summary sheet controls execution

The BeforeDoubleClick event on the summary worksheet does most of the decision making for this technique. Based on the contents of row 3 in the target column, detail columns are added or deleted. If the target column is not a summary, nothing happens. The code is shown below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Cells(2, Target.Column) = "Summary" Then
        If Cells(3, Target.Column) = "Not expanded" Then
            Insert_Block
        ElseIf Cells(3, Target.Column) = "Expanded" Then
            Delete_Block
        End If
    End If
End Sub

Inserting columns drills down to show detail

The Insert_Block procedure adds 6 columns to the left of the current column, writes the appropriate headers to pull the data through from the detail sheet, colours the headers for the detail columns, and changes the status of the target column to Expanded. The header colours are defined as constants in the Declarations section of the code module.

By adding the 6 columns in a single step instead of a loop, the code executes faster. You won't see much difference in a relatively small workbook but, if you need to switch to manual calculation to get reasonable performance, avoiding loops can make a substantial difference.

The code is shown below. The first step is to insert 6 columns to the left of the target column. Because we are inserting entire columns, the insertion point must be in row 1.

Sub Insert_Block()
    Dim i As Integer
    Dim CurCol As Long
    Dim FirstDetailCol As Long
    
    CurCol = ActiveCell.Column
    
    Cells(1, CurCol).EntireColumn.Copy
    Cells(1, CurCol).Resize(1, 6).Insert shift:=xlToRight

Once that is done, the cursor is already in the column that will be the first detail column, so we select row 4 in this column (it contains the header names). Next we determine the location of the first detail header in the Forecast Detail sheet, using the Match function, and then copy the detail headers back to the summary sheet. Because the detail headers are formulas, we paste values and number formats into the summary sheet. 

    Cells(4, CurCol).Select
    FirstDetailCol = WorksheetFunction.Match(ActiveCell.Value, _
        Sheets("Forecast Detail").Range("1:1"), 0)
    Sheets("Forecast Detail").Cells(2, FirstDetailCol).Resize(1, 6).Copy
    ActiveCell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

The last four lines of code do some housekeeping. Row 2 of the detail columns is changed to read Detail, row 3 is cleared, the summary column's status is changed to Expanded, and the colour of the Detail headers is changed.

    Selection.Offset(-2, 0).Value = "Detail"
    Selection.Offset(-1, 0).ClearContents
    Selection.Interior.ColorIndex = DETAIL_COLOR
    ActiveCell.Offset(-1, 6).Value = "Expanded"
End Sub

To hide the detail, delete the detail columns

Deleting the detail columns is more straightforward because there is no need to reference the Forecast Detail sheet. The first step is capture the location of the target column and then to delete the 6 columns to the left.

Sub Delete_Block()
    Dim i As Integer
    Dim CurCol As Long
    Dim FirstDetailCol As Long
    
    CurCol = ActiveCell.Column
    
    Cells(1, CurCol - 6).Resize(1, 6).EntireColumn.Delete shift:=xlToLeft

Next we redefine the current column, go to row 4, and update the column header colour and the column's status.

    CurCol = CurCol - 6
    Cells(4, CurCol).Select
    Selection.Interior.ColorIndex = SUMMARY_COLOR
    ActiveCell.Offset(-1, 0).Value = "Not expanded"
End Sub

You can get the full code here if you want to avoid copying and pasting sections.

  Home | About | Links | Contact | Excel | Access