Pie Chart - Exclude Zero Values
A recent post to the Microsoft Excel Charting Discussion Group asks "how can I create a pie chart that references a fixed data source and excludes all zero values?" Below is an example of how to do so . . .
Step 1 - Creating the Worksheet
First, this example assumes that the workbook is called "PNZ.xls". Assume your original data set is in the range B4:C13.
The purpose of the formulas in column D is to slightly change the numbers in column C to allow them to continue to sort high-to-low if there are duplicates. The formula below is in cell C4. It is copied down the column to cover the range D4:D13.
=IF(C4=0,0,C4-ROW()/10^10)
A new range containing formulas that exclude zero values is created in cells E4:F13.
The formula in cell F4 is below. It is copied down the range F4:F13.
=IF(LARGE($D$4:$D$13,ROWS($B$4:B4))=0," ",LARGE($D$4:$D$13,ROWS($B$4:B4)))
The formula in cell E4 utilizes the Index and Match functions to perform a left-sided lookup on the data values returned in column F. It is copied down the range E4:E13.
=IF(ISERROR(INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0)))," ",INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0)))
Step 2 - Building the Chart
The chart uses defined names based on the data and data label ranges to automatically update. Two range names need to be created. In this example the first named range is called "Data". The formula that makes up the "Data" range appears in the screenshot below:
The second named range is called "DataLabels". The formula that makes up the "DataLabels" range appears in the screenshot below:
The source of the pie chart is based on the Data and DataLabels defined names. The chart source data dialog box containing series references to the two defined names (Data and DataLabels) is shown below.
Finally, the completed pie chart appears below.
In the screenshot below, the value 425 has been added to cell C6. Note how the chart source (columns E and F) has automatically updated to include the new entry in row 7:
The pie chart has automatically updated to include the new value:
- An explanation of the LARGE sorting array formula was obtained from John Walkenbach's book titled Excel 2003 Formulas.
- The INDEX and MATCH functions are also explained in Excel 2003 Formulas as well as at Debra Dalgleish's Contextures site.
- Jon Peltier provides a series of links to sites that show how to build dynamic charts.
- Andy Pope provides an example of a dynamic scrolling chart.
- An explanation of how to build dynamic charts is also included in John Walkenbach's book titled Excel Charts.
