Bar Chart - Exclude Zero Values
In a previous post titled Pie Chart - Exclude Zero Values, formulas were provided that sort data while excluding zero values. This post explains how to achieve the same functionality without sorting the data.
Begin with the simple two-column data set below.
Add a set of helper columns similar to what is shown below. These helper columns will serve as the chart source.
Next, create two named ranges. The first named range covers the range C4:C12. In this example I've named that range "Data".
The second named range covers the range B4:B12. I've named that range "DataX".
At this point it's time to add two array formulas to the helper columns to extract the data for the chart. Array formula are entered into a range using the Contol - Alt - Enter keystokes.
The first formula is a multi-cell array and covers the range G4:G12. The formula is:
{=INDEX(Data,SMALL(IF(Data<>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data)))))}
The second formula is a single-cell array that is entered into cell F4 and copied down the range F4:F12. The formula in cell F4 is:
{=INDEX(DataX,SMALL(IF(Data=G4,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($G$4:G4,G4)))}
As stated above, because this is a single cell array it must be copied through the range F4:F12.
With both formulas properly entered, the helper columns now reflect the original data excluding zeros as shown below.
Conditional formatting can be applied to the helper columns to get rid of the errors (#NUM!). In the conditional formatting dialog box below, I've set the font color to match the background of the spreadsheet (white) if the formula returns an error. The result is to hide the error value.
At this point a simple bar chart can be created using dynamic formulas based on the helper columns. Start with creating two more named ranges. In this example a named range called "ChartData" is created that contains the dynamic formula which serves as the data source for the chart. That formula is:
=OFFSET(Sheet1!$G$4,,,COUNT(Sheet1!$G$4:$G$12),1)
The defined name dialog box now looks like this:
The second named range is called called "ChartDataX". It contains a dynamic formula which serves as the X-Axis source for the chart. That formula is:
=OFFSET(Sheet1!$F$4,,,COUNT(Sheet1!$G$4:$G$12),1)
The defined name dialog box now looks like this:
In this example the workbook is called CNZ.xls. The source for the bar chart references the named ranges "ChartData" and "ChartDataX" as shown in the Chart Source Data dialog box below:
- Explanations of how to create array formulas as well as the ROW and INDIRECT functions can be found in John Walkenbach's book titled Excel 2003 Formulas.
- 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.
