Microsoft's macrofun.hlp file documents the XLM function GET.CHART.ITEM, which is useful for finding the vertical and horizontal positions for selected chart items. This function is further explained in Professional Excel Development.

From Microsoft's macrofun.hlp, below is the documentation for GET.CHART.ITEM:

GET.CHART.ITEM returns the vertical or horizontal position of a point on a chart item. Use these position numbers with FORMAT.MOVE and FORMAT.SIZE to change the position and size of chart items. Position is measured in points; a point is 1/72nd of an inch.

Syntax

GET.CHART.ITEM(x_y_index, point_index, item_text)

X_Y_index is a number specifying which of the coordinates you want returned.

X_Y_index Coordinate returned:

• 1 - Horizontal coordinate.
• 2 - Vertical coordinate.

Point_index is a number specifying the point on the chart item. These indexes are described later. If point_index is omitted, it is assumed to be 1. If the specified item is a point, point_index must be 1. If the specified item is any line other than a data line, use the following values for point_index.

Point_index and Chart item position:

• 1 - Lower or left.
• 2 - Upper or right.

If the selected item is a legend, plot area, chart area, or an area in an area chart, use the following values for point_index.

Point_index and Chart item position:

• 1 - Upper left.
• 2 - Upper middle.
• 3 - Upper right.
• 4 - Right middle.
• 5 - Lower right.
• 6 - Lower middle.
• 7 - Lower left.
• 8 - Left middle.

If the selected item is an arrow in Microsoft Excel 4.0, use the following values for point_index. In Microsoft Excel 5.0, arrows are named lines, and the arrowhead position returned is equivalent to the end of a line where the arrowhead begins.

Point_index and Chart item position:

• 1 - Arrow shaft.
• 2 - Arrowhead.

If the selected item is a pie slice, use the following values for point_index.

Point_index and Chart item position:

• 1 - Outermost counterclockwise point.
• 2 - Outer center point.
• 3 - Outermost clockwise point.
• 4 - Midpoint of the most clockwise radius.
• 5 - Center point.
• 6 - Midpoint of the most counterclockwise radius.

Item_text is a selection code that specifies which item of a chart to select. See the chart form of SELECT for the item_text codes to use for each item of a chart.

If item_text is omitted, it is assumed to be the currently selected item.

If item_text is omitted and no item is selected, GET.CHART.ITEM returns the #VALUE! error value.

Remarks

If the specified item does not exist, or if a chart is not active when the function is carried out, the #VALUE! error value is returned.

Examples

The following macro formulas return the horizontal and vertical locations, respectively, of the top of the main-chart value axis:

GET.CHART.ITEM(1, 2, "Axis 1")

GET.CHART.ITEM(2, 2, "Axis 1")

You could then use FORMAT.MOVE to move a floating text item to the position returned by these two formulas.

In the previous post, I presented a macro that aligned embedded charts to a worksheet cell grid given certain criteria. The macro works OK if you're not concerned about the order in which any chart is placed on the worksheet. Andy Pope offered a more efficient version via comment. However, after more review I found that the macro would rearrange the original placement of one or more of the charts if one or more of the original charts were deleted and replaced. The root of the problem is that embedded charts are really shapes that are stored with an index number in the shapes collection. If a shape is deleted and a new shape created, the new shape is given the last index number in the collection. To place individual charts where you want, you need to somehow specify what index number of the shape you want to where.

The only way I could think of to get around this problem was to name each chart individually and the order the charts by name. Depending on how I wanted the charts to fall on the worksheet, I named each chart "Cht1", "Cht2", "Cht3", etc. I then modified Andy's macro to order the charts by the chart names:

Sub AlignCharts()

    Dim Cht As ChartObject
    Dim ChartsAcross As Integer
    Dim ColumnsAcross As Integer
    Dim RowsDown As Integer
    Dim Rng1 As Range
    Dim Rng2 As Range

    Set Rng1 = Range("B2")
    ChartsAcross = 3
    ColumnsAcross = 4
    RowsDown = 10

    Set Rng2 = Rng1
    
    TotalCharts = 0
    
    For Each Cht In ActiveSheet.ChartObjects
        TotalCharts = TotalCharts + 1
    Next Cht
    
    For Cnt = 1 To TotalCharts
    
    Set Cht = ActiveSheet.ChartObjects("Cht" & Cnt)
        With Cht
            .Top = Rng1.Top
            .Left = Rng1.Left
            .Height = 94.5
            .Width = 144
        End With
        If Cnt Mod ChartsAcross = 0 Then
            Set Rng1 = Rng2.Offset(RowsDown, 0)
            Set Rng2 = Rng1
        Else
            Set Rng1 = Rng1.Offset(0, ColumnsAcross)
        End If
    
    Next

End Sub

When a series of embedded charts are printed on a page and assuming all of the charts are the same height and width, for a more professional presentation consider aligning the charts horizontally and vertically with an even amount of spacing between. Aligning charts via worksheet cell ranges can help to do so. The code below automates that process and is useful for aligning many embedded charts at one time. There are six inputs:

• Rng1 - the starting point for the chart at the top left side of the worksheet.
• ChartsAcross - the number of charts that you want across the worksheet.
• ColumnsAcross - increments the number of columns between Rng1.
• RowsDown - increments the number of rows between Rng1.
• Cht.Height - the height for each chart on the worksheet.
• Cht.Width - the width for each chart on the worksheet.

Sub AlignCharts()

    Dim Cht As ChartObject
    Dim ChartsAcross As Integer
    Dim ColumnsAcross As Integer
    Dim RowsDown As Integer
    Dim Rng1 As Range
    Dim Rng2 As Range

    Set Rng1 = Range("B2")
    ChartsAcross = 2
    ColumnsAcross = 4
    RowsDown = 10
       
    For Each Cht In ActiveSheet.ChartObjects
        Cht.Height = 94.5
        Cht.Width = 144
    Next Cht

    Set Rng2 = Rng1

    For Each Cht In ActiveSheet.ChartObjects
        Cnt = Cnt + 1
    Next Cht

    For A = 1 To Cnt

        Set Shp = ActiveSheet.Shapes(A)
        Shp.Top = Rng1.Top
        Shp.Left = Rng1.Left

        If Application.WorksheetFunction.Round(A / ChartsAcross, 0) _
        - (A / ChartsAcross) = 0 Then
            Set Rng1 = Rng2.Offset(RowsDown, 0)
            Set Rng2 = Rng1
        Else
            Set Rng1 = Rng1.Offset(0, ColumnsAcross)
        End If

    Next A

End Sub

Given the assumptions above, the code works by first counting the number of charts on the worksheet. The first chart is placed at the top / left of cell B2. An If-Then-Else statement is used to place the following charts to the right of the chart before or to increment to the row range. The statement works by comparing the rounded (integer) value of the chart count divided by the total number of charts against the real number of the chart count divided by the total number of charts. If the divisor has a remainder, the chart is placed to the right of the chart before it. If the divisor does not have a remainder, the range increments to the next row.

The end result given the assumptions above returns the following:

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:

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.