Macro to Add Data Point Labels to a Chart in Excel

• Microsoft Excel for Windows, versions 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for the Macintosh, versions 3.x, 4.x, 5.0

SUMMARY

In Microsoft Excel, you can place text labels on individual data points in a chart. You can replace existing labels or create new labels with text values from a worksheet either by manually editing the label in the formula bar in the chart, or you can write a macro. This article shows how to do this from a macro, which is the desired method if the chart contains many data points or if you update charts in this fashion often.

MORE INFORMATION

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

Example

The following example creates new, or replaces existing, data labels on a chart for a single data series. Before following the steps below, you should have an existing chart, and you should have a range of cells on your worksheet containing the labels that you wish to have placed in your chart. The number of labels on your worksheet should be equal to the number of points per data series in your chart.

1. Select the cells containing the labels in your worksheet. (For this example, the worksheet will be called "SHEET1.XLS".)

2. From the Formula menu, choose Define Name. Type "Labels" (without the quotation marks) in the Name box and choose the OK button.

3. Enter the following into a macro sheet:

         A1: i=1
         A2: =FOR.CELL("CurrentCell",Sheet1.XLS!Labels)
         A3: =ATTACH.TEXT(4,1,i)
         A4: =FORMULA(CurrentCell)
         A5: i=i+1
         A6: =NEXT()
         A7: =RETURN()
   

   This macro uses a FOR.CELL loop. Each pass through the loop takes the text from the current cell in the range called "Labels" and places that text on the next data point. Incrementing the counter "i" allows the ATTACH.TEXT statement to refer to the next data point.

4. Select cell A1. From the Formula menu, choose Define Name. Type "Change" (without the quotation marks) in the Name box. Choose Command and choose the OK button.

5. Activate your chart and run the macro by choosing Run from the Macro menu, selecting Change from the list box, and then choosing the OK button.

NOTE: This works differently under 3-D charts.

REFERENCES

"User's Guide 1," version 4.0, pages 455-456 "Function Reference," version 4.0, pages 29-30, 155-156 "Microsoft Excel User's Guide" for Windows, version 3.0, pages 475-476 "Microsoft Excel Function Reference" for Windows, version 3.0, pages 18-19, 80