At times I need to present financial models to Committees in a format that allows the group to interactively view the results of changes made to the model. The ability to name, hide, and show multiple charts via VBA is great for these types of presentations. Ideally, I like to stack all of the charts to be presented one on top of another. Stacking the charts prevents me from having to skip around the worksheet to find the chart as will as insures that any chart that I choose is positioned exactly where I want it to be on the overhead viewer. I then show the selected chart and hide the others via command buttons, option buttons, or combo boxes.

As a example, I first name all of the charts in the model using the following code (you must select the chart first and then run the macro to name it):

Sub NameChart()
    ActiveChart.Parent.Name = "Cht1"
End Sub

I then size one or more of the charts to the same outside dimensions and position on the worksheet. To size and position a single chart, I use this code:

Sub SizeAndPositionSingleChart()
    Dim Shp As Shape
    Set Shp = Worksheets("Sheet1").Shapes("Cht1")
    With Shp
        .Height = 168.75
        .Width = 286.5
        .Top = 18
        .Left = 50
    End With
End Sub

To size all of the charts on the worksheet to the same outside dimensions and position, I use the following code:

Sub SizeAndPositionAllCharts()
    Dim Cht As ChartObject
    For Each Cht In ActiveSheet.ChartObjects
        Cht.Height = 168.75
        Cht.Width = 286.5
        Cht.Top = 18
        Cht.Left = 10
    Next Cht
End Sub

In this example, assume the model contains two charts stacked one on top of the other. Using the NameChart macro above, one chart has been named "Cht1" and the second "Cht2". A macro loaded into a regular module similar to the one below will show Cht1 while hiding Cht2.

Sub ShowChart1()
    Worksheets("Sheet1").Shapes("Cht1").Visible = True
    Worksheets("Sheet1").Shapes("Cht2").Visible = False
End Sub

The following macro will show Cht2 and hide Cht1:

Sub ShowChart2()
    Worksheets("Sheet1").Shapes("Cht2").Visible = True
    Worksheets("Sheet1").Shapes("Cht1").Visible = False
End Sub

Assuming the charts are on "Sheet1", showing and hide the charts using command buttons can be accomplished by loading the following code into the sheet module for "Sheet1". To show Cht1 and hide Cht2, use the following:

Private Sub CommandButton1_Click()
    Shapes("Cht1").Visible = True
    Shapes("Cht2").Visible = False
End Sub

To show Cht2 and hide Cht1, use the following:

Private Sub CommandButton2_Click()
    Shapes("Cht1").Visible = False
    Shapes("Cht2").Visible = True
End Sub

A simple model showing Cht1 looks like this:

The same model showing Cht2 is below:

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:

The Microsoft Knowledge Base is a great resource for Excel support. Below are a couple of useful links:

• Microsoft Support
• Search the Knowledge Base

There are times when you need to plot data on both a primary axis and a secondary axis. Creating charts for correlation analysis is one example. Although Excel allows you to easily create these types of charts, unfortunately it does not offer an option to create proportionate axes. To do so you must perform the calculations yourself and then enter the scale values individually.

For selected primary and secondary axis presentations, it is important to make sure that each axis is proportionate to the other. If the axes are not proportionate, you run the risk of misrepresenting the data. The proportionate axes insures that the data is presented in the same plane.

Most of the time the calculations to create proportionate axes are fairly easy to do. This is especially true when the minimum and maximum values for both the primary and secondary axis are positive. However, the calculations get complex if negative values fall into the mix. Fortunately, I've seemed to have found a simple calculation that seems to work for all values positive and negative.

As an example and given the data set below, the task is to create a chart with a primary and secondary axis. Both axes must be proportional to each other and must have tick marks that line up with each other.

Before showing how to create the chart, below is the end result:

How do you know that the axes are proportional to each other? To test the results, divide the absolute value of the maximum axis value by the absolute value of the minimum axis value. Using the example above, the primary axis ratio is calculated as:

abs(-122.99268) / abs(59.47431) = 2.067997

The secondary axis ratio is calculated as:

abs(489,195) / abs(-236,555) = 2.067997

Since both ratios equal each other, the axes are proportionate.

Calculating the Proportionate Axes Values

To calculate the values for the proportional axes, three sets calculations are performed. The first two calculations provide data for the final calculation. The final calculation is then used as the source for the axes values. Based on the data above, the worksheet with all three calculations looks like this:

Axes Calculation 1

For Axes Calculation 1, one possible option for the primary axes is the maximum of the range C3:C12. Another possible option for the primary axes the minimum of the range C3:C12. The midpoint is the middle value between the maximum and the minimum as calculated with this formula:

=MAX($C$3:$C$12)-((MAX($C$3:$C$12)-MIN($C$3:$C$12))/2)

A possible option the secondary axes (cell D17) is calculated with this formula:

=C16/C17*D17

Another possible option for the secondary axes (cell D19) is calculated with this formula:

=C18/C17*D17

The midpoint for the secondary axes is calculated the same as shown above.

Axes Calculation 2

For Axes Calculation 2, a possible option for the secondary axes is the maximum of the range D3:D12. Another possible option for the secondary axes the minimum of the range D3:D12. The midpoint is the middle value between the maximum and the minimum as calculated with this formula:

=MAX($D$3:$D$12)-((MAX($D$3:$D$12)-MIN($D$3:$D$12))/2)

A possible option for the primary axes (cell E17) is calculated with this formula:

=F16/F17*E17

Another possible option for the primary axes (cell E19) is calculated with this formula:

=F18/F17*E17

The midpoint for the primary axes is calculated the same as shown above.

Final Axes Calculation

Once the first two calculations are complete, the maximum and minimum values for the proportional axes can be obtained. To get the maximum value for the primary axis, take the maximum of calculations 1 and 2. The formula in cell D24 is:

=MAX(C17,E17,C19,E19)

To get the minimum value for the primary axis, take the minimum of calculations 1 and 2. The formula in cell D25 is:

=MIN(C17,E17,C19,E19)

To get the maximum value for the secondary axis, take the maximum of calculations 1 and 2. The formula in cell E24 is:

=MAX(D17,F17,D19,F19)

To get the minimum value for the secondary axis, take the minimum of calculations 1 and 2. The formula in cell E25 is:

=MIN(D17,F17,D19,F19)

Enter the values obtained above as the maximum and minimum primary and secondary axis values. To align the tick marks, divide the difference between the maximum and minimum values for each axis by the same number. This example divides the differences by six to get the major units. For example, to get the primary major unit values, subtract the minimum value (-122.99268) from the maximum value (59.47431). The difference is 182.46699. Divide that difference by six and enter the result as the major unit. Do the same for the secondary axis and your axes are complete.

Please note that these calculations do not take into account the possibility that the midpoint of one, the other, or both axes falls at zero. This issue will be discussed at a later date.

Recently a project at work required that I do the following:

• Create two charts - one showing the top "X" percent of total revenue and the other showing the remaining "Y" percent of revenue. "X" and "Y" needed to be variable depending on the variability of the source data as well as how the user decided to present the data.
• In addition to the first chart showing the top "X" percent of revenue, it had to contain a bar at the end that showed the total "Y" percent of revenue that was to be detailed on the second chart.
• The data in both charts had to be sorted high-to-low with the exception "Y" bar of the first chart that needed to appear at the end.
• The data had to be set up in an static input format so that someone with little knowledge of Excel could update the charts.

Before explaining how to build, the final charts looked similar to these:

Chart 1

Chart 2

The first step in building the worksheet was to create a static range for data input. This range is reflected in cells C6:C25. This static data input range allows someone with very little spreadsheet knowledge could walk down each of the departments, which are in alphabetical order, and enter the data for that department.

The second step in building the worksheet was to create three helper columns. The first two helper columns covering the range D6:E25 contained formulas that sorted the data in the input range C6:C25. The screenshot below shows the worksheet.

The formula in cell E6 is below. This formula sorts the data in the input range C6:C25 from high-to-low. It is copied through the range E6:E25.

=LARGE($C$6:$C$25,ROWS($C$6:C6))

The formula in cell D6 is below. This formula looks up the department values based on the sorted data in the range E6:E25. It is copied through the range D6:D25.

=INDEX($B$6:$B$25,MATCH($E6,$C$6:$C$25,0))

The reminder of the revenue that makes up the last bar in the first chart is reflected in cell E27. The formula in that cell is:

=SUMPRODUCT(($F$6:$F$25=2)*($E$6:$E$25))

The third helper column covers the range F6:F25. The purpose of this range is to assign the data to charts one or two depending on where each department falls as a percentage of total revenue. The formula in cell F6 is below. It is copied through the range F6:F25. It works by building a cummulative sum of the data and comparing that sum to the total sum of the data. If the percent of revenue is less than the value in cell D2, the revenue is assigned to chart one. If it is more than the value in cell D2, it is assigned to the second chart.

=IF(SUM($E$6:E6)/SUM($E$6:$E$25)<$D$2,1,2)

Once the worksheet was built, the key to building the charts is utilizing a series of dynamic range names as the chart source. Because the first chart had to reflect the top "X" percent of revenue and a summary bar showing the remaining revenue, the chart had to be based on two non-contiguous data sources.

To create the first chart four named ranges needed to be created. The first named range, called "Cht1Data", makes up the first non-contiguous range. It is a dynamic formula (the range changes depending on what is entered into cell D2, and consists of the formula:

=OFFSET(Sheet1!$E$6,0,0,COUNTIF(Sheet1!$F$6:$F$25,1),1)

The formula appears in the named range dialog box as:

The second named range, called "Cht1Remainder", makes up the second non-contiguous range. It picks up the remainder of the revenue shown in cell E27. It consists of the formula:

=Sheet1!$E$27

The formula appears in the named range dialog box as:

Both of these named ranges will need to have corresponding X-Axis values assigned. To do so, two more named ranges are created. The first named range assigns the X-Axis values to the "Cht1Data" range. It is called "Cht1XAxis" and consists of the formula:

=OFFSET(Sheet1!$E$6,0,-1,COUNTIF(Sheet1!$F$6:$F$25,1),1)

The formula appears in the named range dialog box as:

The second named range assigns the X-Axis values to the "Cht1Remainder" range. It is called "Cht1XRemainder" and consists of the formula:

=Sheet1!$B$27

The formula appears in the named range dialog box as:

Now that the range names are created for the first chart, it's time to create the chart. To do so, the range names need to be added to the series and X-Axis in a non-contiguous format. Below are the source values for the first chart.

Assuming the Excel file is called "NCR.xls", the source for the first series is:

=(NCR.xls!Cht1Data,NCR.xls!Cht1Remainder)

Note that a comma separates the two range names and that the source is surrounded by parenthesis.

The source for the X-Axis is:

=(NCR.xls!Cht1XAxis,NCR.xls!Cht1XRemainder)

The process of building the second chart is exactly the same as the first chart - just a little easier. This chart only requires two named ranges. The first named range, called "Cht2Data", consists of the formula (enter the formula into the named range dialog box as shown in the first examples above):

=OFFSET(Sheet1!$E$6,COUNTIF(Sheet1!$F$6:$F$25,1),0,COUNTIF(Sheet1!$F$6:$F$25,2),1)

The second named range, called "Cht2XAxis", consists of the formula (enter the formula into the named range dialog box as shown in the first examples above):

=OFFSET(Sheet1!$E$6,COUNTIF(Sheet1!$F$6:$F$25,1),-1,COUNTIF(Sheet1!$F$6:$F$25,2),1)

Below are the source values for the second chart.

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: