XL: Inconsistent Results Moving Chart Data Labels

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel, when you move a data label in a column chart from the top of a column to nearly the bottom of the column, and the value for that column is changed to a lower or a negative value, the data label disappears or changes position.

CAUSE

The calculation that Microsoft Excel uses to determine the position of a data label in a column chart is based on the distance from the top of the column. If a column becomes shorter due to a lower value, the data label shifts down accordingly. This shift can force the data label to be positioned outside of the plot area, where it is not visible. If the column value becomes negative, the data label is positioned at the same distance from the top of the column (which is inverted) on the positive side of the x-axis.

WORKAROUND

To work around this behavior in Microsoft Excel 97, use the following steps:

1. On the chart, click the label to select it.

2. On the Format menu, click Selected Data Labels.

3. In the Alignment tab, click Inside Base in the Label Position box, and click OK.

Steps to Reproduce Behavior

1. In a worksheet, type the following data:

        A1:  1
        A2:  2
        A3:  3
        A4:  4
   

2. Select the range A1:A4, click Chart on the Insert menu, and then click As New Sheet.

   Step 1 of 5 of the Chart Wizard appears.

3. Click Finish to create a standard column chart.

4. Click once on any one of the columns to select the entire series.

5. On the Format menu, click Selected Data Series.

6. Click the Data Labels tab, click Show Value, and then click OK to accept the change.

   This step places data labels at the top of each column in the chart.

7. Select the data label above the third column and drag it to the bottom, of the column between y values 0 and 0.5 (in the lower 6th area of the column).

8. On the spreadsheet, change the value of 3 in cell A3 to 2.

   The data label for the third column are no longer be visible.

9. On the spreadsheet, change the value of 2 in cell A3 to -2.

   The data label for the third column is displayed, but it is positioned above the column at approximately the y value of 2.

REFERENCES

"User's Guide," version 5.0, pages 277-278