XL: Attaching Values Above Data Points in a Line Chart

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a

SUMMARY

When you attach text or values to points in a line chart, the text or values are placed to the right of the data point. If you want to position the text or values above the data point, you can create a combination chart (column and line) based on the same data.

MORE INFORMATION

Note that in Microsoft Excel versions 5.0 and 7.0, you can change the location of a single data label by dragging the label to the new location on your chart. However, if your chart has many data points, you can use the following procedure to create a line chart with data labels attached to the top of the data points by default to avoid positioning each data label manually.

Microsoft Excel versions 5.0 and 7.0

1. On a worksheet, select the data that you want to be represented in the chart, and click the ChartWizard tool. Use the ChartWizard tool to create a column chart.



2. Activate the chart (double-click it) and click New Data on the Insert menu. Select the same range you selected in Step 1, and then click OK.
   
   This creates two sets of columns based on the same data.



3. Select each of the duplicate column series and click Chart Type on the Format menu. Click the Line chart type and click OK.



4. Select each remaining column series, and click Selected Data Series on the Format menu. Click the Data Labels tab, and select the Show Value option (or the Show Label option). Click OK.
   
   The columns will have values above them.



5. Select each column series on the chart, click Selected Data Series on the Format menu, and click the Patterns tab. In the Patterns dialog box, click None under Border and Area. Click OK.
   
   This step should hide the Columns in the chart, leaving the lines with values displayed on top of each point.

Microsoft Excel versions 4.x and earlier

1. On a worksheet, select the data that you want to be represented in the chart, choose New from the File menu, and select the Chart option (or press F11).



2. Return to your original worksheet.



3. With your data selected, choose Copy from the Edit menu and switch to the chart.



4. From the Edit menu, choose Paste. This creates two sets of columns based on the same data.



5. From the Gallery menu, choose Column and select option 7 for a chart with attached data labels. Choose OK.
   
   All columns will have the values above the them.



6. From the Chart menu, choose Add Overlay.



7. From the Format Menu, choose Overlay. Change the overlay to a line chart, and choose OK.



8. Select one of the columns on the chart and choose Patterns from the Format menu. In the Patterns dialog box, choose None for both the Border and the Area options. Choose OK.
   
   This should hide the Columns in the chart, leaving the line and two sets of values on each point (with one at the top and one on the right).



9. Follow the appropriate procedure below to clear the values to the right of each data point (leaving the data points above the markers):
   
   - If you are using Microsoft Excel version 4.0, select the values to the right of each point, and press the DELETE key.
   
   - If you are using Microsoft Excel version 3.0, press the BACKSPACE key.

REFERENCES

"Getting Results with Microsoft Excel for Windows 95," version 7.0, page 97
"User's Guide," version 5.0, pages 283-284
"User's Guide 1," version 4.0, pages 428-430
"User's Guide," version 3.0, pages 422-426

Additional query words: 4.00a 5.00a 5.00c 7.00a howto label graph

Keywords          : xlchart 
Version           : WINDOWS:3.0,4.0,5.0,7.0,7.0a; MACINTOSH:3.0,4.0,5.0,5.0a
Platform          : MACINTOSH WINDOWS 
Issue type        : kbinfo 

Last Reviewed: April 6, 1999