Infinite opportunities?  Endless problems?  Limited resources?  Talk to us!
Operations
Consulting
Software
Consulting
Publishing and
Training
Applied
Solutions
Excel add-ins
and tutorials
Charts Excel and VBA
Case Studies
VBA
tutorials
PowerPoint
Add-Ins
Miscellenia
You are on the Home/Excel/Add-Ins/PivotChart Drilldown page
About this site

What's new

Google
Web
This Site
 

 

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.

 

 

 

 

Programs to extract files from a zip archive

There are a variety of programs available.  Search the ZDNet software library for the keyword winzip or unzip, or visit the WinZip web site.

 

 

For custom technology solutions, operations consulting, or training contact web-underscore-contact@tushar-hyphen-mehta-dot-cee-oh-em.
By accessing any page or link on this web site other than this page, you agree to the terms and conditions.

Ads from amazon.com

[Optional] Survey (current rating of site: 3.7 out of 4)  If you will take a moment to provide your comments, it will help improve the site both for you, and for other visitors.

On a scale of 4 (just what I need)
to zero (totally useless)

How do you rate the information

on this page?

  on this site?

[Optional]

Your name
Your email address
Other comments
 
Monitor page
for changes
    
   it's private  

by ChangeDetection
A comment selected at random:

 

 

Copyright © 2000-2008 Tushar Mehta.
Send comments and suggestions about the web site to webmaster@tushar-hyphen-mehta-dot-cee-oh-em
Last edited April 14, 2008