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.
