PivotChart Drill-down
Written in response to various
requests for an utility of this nature, the PivotChart
Drill-down add-in does just what the name implies.
For a PivotChart corresponding to a PivotTable with
multiple row fields, it allows one to start with a
summary of the leftmost field (maximum zoom so to say)
and with each click drill down to the next more detailed
level.
This add-in works in the limited
testing performed using Excel 2003 before making it available on the
website. However, it should be treated as alpha
software. You are strongly urged to not rely on it
for any business decision but to test the add-in to
ensure it works as intended. Your comments on
problems as well as suggestions for improvements are
indeed solicited.
By downloading the add-in you
agree that you understand the add-in is copyright Tushar
Mehta and that you will make no attempt nor will you ask
anyone to access the source code.
Click here for common
installation instructions that apply to all the Excel
add-ins.
Help and documentation
Currently, the only available help
is what's on this page.
Once the add-in is loaded into
Excel, select any chart. You will see the TM |
PivotChart Drill-down sub-menu
 |
Figure 1
This enables the drill down
capability for a chart that meets the following
criteria: It must be a PivotChart, it must be on a sheet
by itself, and it must plot a single data series.
For the rest of the information
below, we will use the data source (below left) to
create a PivotTable shown fully expanded (below right).
The PivotTable has four row fields (the columns x, y, z,
and w) and one data field (Sum of column w).
| x |
y |
z |
w |
| a |
1 |
11 |
1 |
| a |
1 |
11 |
2 |
| a |
1 |
12 |
3 |
| a |
1 |
12 |
4 |
| a |
2 |
11 |
5 |
| a |
2 |
11 |
6 |
| a |
2 |
12 |
7 |
| a |
2 |
12 |
8 |
| a |
2 |
13 |
17 |
| a |
2 |
13 |
18 |
| b |
1 |
23 |
9 |
| b |
1 |
23 |
10 |
| b |
1 |
24 |
11 |
| b |
1 |
24 |
12 |
| b |
2 |
21 |
13 |
| b |
2 |
21 |
14 |
| b |
2 |
22 |
15 |
| b |
2 |
22 |
16 |
Table 1
|
 |
Figure 2
|
The example workbook contains two
PivotCharts based on this table. In addition, the
workbook contains a second PivotTable and one PivotChart
based on this table.
When one selects a PivotChart's
sheet, the chart is reset to show the highest level of
the data. So, in the above example, it would show
information broken down by the x column,
i.e., show totals for the values a and
b.
 |
Figure 3
|
 |
Figure 4
|
Clicking on any of the segments
drills down to the next level for that segment.
So, clicking on the a segment would show
the totals for y values corresponding to
the x value equal to a.
 |
Figure 5
|
 |
Figure 6
|
One can continue drilling down all
the way to the most detailed segment, which would
correspond to x=a, y=2, and z=13
 |
Figure 7
|
 |
Figure 8
|
Clicking on any segment when the
chart is fully drilled down resets the chart to the
beginning (Figure 3).
Additional comments
The software automatically adds a
datalabel to each shown segment. This includes a
name and the value of the point. In addition, for
a pie PivotChart is shows the percentage represented by
each segment.
The color choices are simply those
available in Excel limited color palette. Each new
segment shown uses another color. A future version
of the software may improve the color choices but given
the limit of 56 imposed by Excel it is not clear how
sophisticated the color choices can be.
