DataWright Information Services

Consulting and Resources for Excel and Access




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.
the summary sheet with no detail displayed
   
This is the detail sheet
the detail sheet holds the data for the report
   
Here is the summary sheet after double-clicking to
insert detail columns

the summary report, showing 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.