A recent post to the Microsoft Excel Charting newsgroup asks "I have a chart where two lines intersect at the breakeven point. How can I show a dynamic label at the point of intersection that describes the breakeven point?"

A simple solution is to add a text box in the chart. Make the source of that text box a worksheet cell that contains the data that you want to show.

For example, the model below provides the data needed to create a breakeven chart.

Based on the information above, a combination Line - XY Scatter Chart can be created to show the breakeven point.

When changes are made to the model, the label at the top of the chart will always reflect those changes. To create the label, first decide what worksheet cell you want the label to tie to. In this case the label ties to cell C25. Within that cell you can reference what is already there or create a phase that you want to appear. In this example the phrase "BE Units = 152.16 and BE Cost = $4,868" is returned by using the following formula in cell C25:

="BE Units = "&TEXT(F5,"##.##")&" and "&"BE Cost = "&TEXT(G5,"$#,###")

Finally, add the label to the chart. To do so, select the chart (select any element except a text item). Click in the formula bar, then type the number if it's static or type = and click on the cell with the value (no need to create the textbox first). Press enter and a textbox should appear in the middle of the chart. This will put a textbox in the chart.

Note: Instructions for adding the text box to the chart was provided by Jon Peltier of Peltier Technical Services via a past post to the Microsoft Excel Charting Newsgroup.

The following example uses a combination of defined names and array formulas to dynamically chart a range between a high and low value. In the screenshot below, data is entered into the yellow shaded cells. The chart source is within the white grid range E3:F22.

The first step to creating the chart is to build four named ranges. Three of the four named ranges will act as components of the array formulas that make up the chart source. Each of the formulas can be viewed in the screenshots of the named range dialog boxes below:

The first named range is called "Data":

The second named range is called "Labels":

The third named range is called "List":

The fourth and final named range is called "Values":

Two array formulas make up the chart source. To enter an array formula, select the entire range, enter the formula into the formula bar, and then hit the CONTROL - SHIFT - ENTER keys at the same time.

The first array formula below covers the range E3:E22.

=IF(ISERR(SMALL(IF(Data=F3,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($F$3:F3,F3)))," ",
INDEX(List,SMALL(IF(Data=F3,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($F$3:F3,F3))))

The second array formula below covers the range F3:F22.

=IF(ISERR(SMALL(IF(Data>=$H$3,IF(Data<=$H$2,ROW(INDIRECT("1:"&ROWS(Data))))),
ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data>=$H$3,
IF(Data<=$H$2,ROW(INDIRECT("1:"&ROWS(Data))))),ROW(INDIRECT("1:"&ROWS(Data))))))

Finally, it's time to create the chart. Assuming that the file name is "RangeBetween.xls", the chart source dialog box looks like this:

I try to spend a few minutes each day in the Microsoft Excel Charting forum and it seems like each day someone has a question about pie charts. Today a question was brought up about overlapping data labels in a pie chart with 13 data points. Who cares about the labels . . . what can you see in a pie chart with 13 points? Why do people use pie charts?

This quote is brought to us courtesy of Juice Analytics. Coda Hale writes:

Piecharts are for middle management. Piecharts are the information visualization equivalent of a roofing hammer to the frontal lobe. They have no place in the world of grownups, and occupy the same semiotic space as short pants, a runny nose, and chocolate smeared on one’s face. They are as professional as a pair of assless chaps. Anyone who suggests their use should be instinctively slapped.

For the record, I avoid pie charts like the plague. I've never seen a case where a pie chart performed better than a bar chart or a simple data table.

Below are links to sources that explain how pie charts should and shouldn't be used. They also describe the data visualization problems associated with pie charts. Additional links would be appreciated.

• Making Charts in Microsoft Excel
• The Problem with Pie Charts
• The Chart Doctor from Process Trends

For the purposes of this post, "In-Cell" charts can be defined as very small charts, or chart pictures, that cover the height and width of one worksheet cell. Because In-Cell charts are so small, they can be very useful for dashboard reporting.

I've found that it's extremely difficult if not impossible to scale-down and manage one or more Excel charts to the size of a worksheet cell. However, it's not that difficult if the chart is converted to a picture, scaled down, and then positioned into the cell.

The simple report below provides an example of a series of charts scaled down to the size of worksheet cells. The objective of this report is to provide the current month's statistic and a high-level graphic showing the six month trend.

To create the report, I set up the data to be included in each In-Cell chart on the right side of the report outside of the page view. I then calculated a minimum and maximum for each row of data. The data set looks like this:

A single chart is created that uses one row of data as the six-month trend source. I named the chart "Cht1". A macro is used to loop through each row of data, update the chart, copy the chart as a picture, scale the picture down to the size of one worksheet cell, and then place the picture into the appropriate cell. The macro is below:

Sub BuildMicroCharts()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim ChtRng As Range
    Dim ChtMax As Range
    Dim ChtMin As Range
    Dim Cht As ChartObject

    Set ChtRng = ActiveSheet.Range("I4:N4")
    Set ChtMax = Range("O4")
    Set ChtMin = Range("P4")
    Set Cht = ActiveSheet.ChartObjects("Cht1")

    For Each Rng In Range("F4:F11")

        ActiveSheet.ChartObjects("Cht1").Activate
        ActiveChart.SetSourceData Source:=ChtRng, PlotBy:=xlRows
        ActiveChart.Axes(xlValue).Select
        With ActiveChart.Axes(xlValue)
            .MaximumScale = ChtMax
            .MinimumScale = ChtMin
            .MajorUnit = (.MaximumScale - .MinimumScale) / 6
            .MinorUnit = (.MaximumScale - .MinimumScale) / 12
        End With

        Cht.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
        Rng.Select
        ActiveSheet.Paste
        
        Selection.ShapeRange.LockAspectRatio = msoFalse
        Selection.ShapeRange.Height = 15
        Selection.ShapeRange.Width = 48

        Set Rng = Rng.Offset(1, 0)
        Set ChtRng = ChtRng.Offset(1, 0)
        Set ChtMax = ChtMax.Offset(1, 0)
        Set ChtMin = ChtMin.Offset(1, 0)

    Next Rng

    Range("A1").Select

    Application.ScreenUpdating = True

End Sub

To delete the series of In-Cell charts, I refered to Ron de Bruin's site which contains many examples of code showing how to delete shapes from a worksheet. The following code deletes each In-Cell chart from the worksheet (the shape type for each In-Cell chart is 13) while preserving the actual chart that serves as the picture source (the shape type for the picture source chart is 3).

Sub DeleteShapes()
    Dim Shp As Shape
    For Each Shp In ActiveSheet.Shapes
        If Shp.Type = 13 Then Shp.Delete
    Next Shp
End Sub

Finally, Ron also provides code that shows the Type numbers of all controls on your worksheet. That code allows you to differentiate between the Type numbers for chart pictures that should be deleted and the actual chart source which you don't want to delete. That code is below:

Sub ListAllObjectsActiveSheet()
    Dim NewSheet As Worksheet
    Dim MySheet As Worksheet
    Dim myshape As Shape
    Dim I As Long

    Set MySheet = ActiveSheet
    Set NewSheet = Worksheets.Add

    With NewSheet
        .Range("A1").Value = "Name"
        .Range("B1").Value = "Visible(-1) or Not Visible(0)"
        .Range("C1").Value = "Shape type"
        I = 2

        For Each myshape In MySheet.Shapes
            .Cells(I, 1).Value = myshape.Name
            .Cells(I, 2).Value = myshape.Visible
            .Cells(I, 3).Value = myshape.Type
            I = I + 1
        Next myshape

        .Range("A1:C1").Font.Bold = True
        .Columns.AutoFit
        .Range("A1:C" & Rows.Count).Sort Key1:=Range("C1"), _
                        Order1:=xlAscending, Header:=xlYes
    End With

End Sub

Using the VBA techniques above, you can quickly scale and position many In-Cell charts in a very short period of time.

A recent post to the Excel Charting Newsgroup asks "I would like the color of my data points to vary based not on their x or y value, but rather on a third column running alongside them. How can I do it?" A worksheet solution would be to create an XY series for each option that appears in the third column. For example, the screenshot below shows a worksheet with the original XY values in columns A and B. The colors of each point depends on the name entered into column C. Columns E through J contain formulas that determine the series that the data point falls into. If the value in column C is "Jim", the data corresponding to Jim appears in columns E and F. If the value in column C is "Frank", the data corresponding to Frank appears in columns G and H. Finally, if the value in column C is "Kim", the data corresponding to Kim appears in columns I and J.

The formulas are as follows:

The formula in cell E3 is:

=IF($C3="Jim",$A3,NA())

The formula in cell F3 is:

=IF($C3="Jim",$B3,NA())

The formula in cell G3 is:

=IF($C3="Frank",$A3,NA())

The formula in cell H3 is:

=IF($C3="Frank",$B3,NA())

The formula in cell I3 is:

=IF($C3="Kim",$A3,NA())

Finally, the formula in cell J3 is:

=IF($C3="Kim",$B3,NA())

Each of these formulas is copied through their respective ranges. For example, the formula in cell E3 is copied through the range E3 to E11.

Conditional formatting of the range E3:J11 is used to mask the errors. To do so, go to Format -> Conditional Formatting. Enter the formula in the Conditional Formatting dialog box as shown below:

The formatted color is set to white to mask the error messages. Copy the format through the range E3:J11.

The three series that act as the source for the XY Scatter Chart are entered into the Chart Source Data dialog box. An example showing the first series "Jim" is shown below:

The example showing the second series "Frank" is below:

Finally, the last example showing the third series "Kim" is below:

A second option would be to use VBA to color each point. An example of a VBA routine to do so is below:

Sub ChangeXYColors()
    Application.ScreenUpdating = False
    Dim Rng As Range
    Cnt = 1
    For Each Rng In Range("C3:C11")
    ActiveSheet.ChartObjects("Chart1").Activate
    ActiveChart.SeriesCollection(1).Points(Cnt).Select
        If Rng.Value = "Jim" Then
            With Selection
                .MarkerBackgroundColorIndex = 5
                .MarkerForegroundColorIndex = 5
            End With
        ElseIf Rng.Value = "Frank" Then
            With Selection
                .MarkerBackgroundColorIndex = 3
                .MarkerForegroundColorIndex = 3
            End With
        ElseIf Rng.Value = "Kim" Then
            With Selection
                .MarkerBackgroundColorIndex = 10
                .MarkerForegroundColorIndex = 10
            End With
        End If
    Cnt = Cnt + 1
    Next Rng
ActiveChart.Deselect
End Sub

The worksheet option and the VBA option both return a chart that looks like this:

One of my responsibilities at work is to produce a monthly statistics package and top-level commentary. A single spreadsheet serves as a data repository and there are approximately 50 charts based on differing data that we use to visually present our monthly results. All of the charts are designed to be dynamic. By dynamic I mean that they automatically update as statistics are entered into the spreadsheet. The "upgrade" to dynamic charting vs. manual updates has probably saved at least one to two days of work as well as decreased the chance of error.

One of the easiest charts to automate is an actual vs. plan chart. The chart below is an example of a simple actual vs. plan dynamic chart:

The data source for the chart is below:

The monthly data is entered into the yellow shaded range C4:C15.

The chart is composed of three different chart types - a simple Line chart, an Area chart, and an XY Scatter chart. The actual data in the range F4:F15 is the base of the Line chart. The formula in cell F4 is below:

=IF(C4,C4,NA())

This formula is copied into each cell in the range F4:F15. The chart source data - series dialog box for the actual numbers looks like this (note that the example is on Sheet2 of the spreadsheet):

The plan numbers in the range G4:G15 simply refer to the numbers in the range D4:D15. The plan data uses the Area chart type. The chart source data - series dialog box for the plan numbers looks like this:

The next task is to automate the positioning of the data labels. In this case, we want the data labels to reflect the actual and plan numbers for the current month. The XY Scatter chart is used for data labeling positioning. Two XY Scatter points are used to track the actual and plan data points for the current month. The first XY data point reflects the actual month number and is based on the range J5:J6. Formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J5 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J6 (Y Axis point) is:

=OFFSET($C$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the actual XY Scatter point numbers looks like this:

Finally, the second XY data point reflects the plan month number and is based on the range J10:J11. As with the actual data labels, formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J10 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J11 (Y Axis point) is:

=OFFSET($D$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the plan XY Scatter point numbers looks like this:

In another variation of the chart above, the plan is shown going into the future only. The chart is made up of a Line chart with two series and an XY Scatter chart with two data label points. Below is an example:

The data source for the chart is below:

In this example, all formulas and chart sources are the same except for the formulas in range G4:G15. The formula in cell G4 is:

=IF(COUNT($C$4:C4)=COUNT($C$4:$C$15),D4,IF(C4,NA(),D4))

This formula is copied into each cell in the range G4:G15.

What's nice about using dynamic techniques such as these is that so many people don't. If you're lucky enough to work for one of these people, they'll still think that it takes more time than it really does to complete the work. As a result, set your email to send something to him or her every 1/2 hour and take the rest of the afternoon off. Unfortunately, once the secret's out they'll want everything now.