How to improve your Excel Chart

1. Improve your X Y Scatter Chart with custom data labels

The picture above shows a chart that has custom data labels, they are linked to specific cell values.

This means that you can build a dynamic chart and automatically change the labels depending on what is shown on the chart.

I have demonstrated how to build dynamic data labels in a previous article if you are interested in using those in a chart.

In a post from March 2013 I demonstrated how to create Custom data labels in a chart.

Unfortunately, that technique worked only for bar and column charts.

You can't apply the same technique for an x y scatter chart, as far as I know.

Luckily the people at Microsoft have heard our prayers.

They have implemented a feature into Excel 2013 that allows you to assign a cell to a chart data point label a, in an x y scatter chart.

I will demonstrate how to do this for Excel 2013 and later versions and a workaround for earlier versions in this article.

1.1 How to apply custom data labels in Excel 2013 and later versions

This example chart shows the distance between the planets in our solar system, in an x y scatter chart.

The first 3 steps tell you how to build a scatter chart.

1. Select cell range B3:C11
2. Go to tab "Insert"
3. Press with left mouse button on the "scatter" button
4. Press with right mouse button on on a chart dot and press with left mouse button on on "Add Data Labels"
5. Press with right mouse button on on any dot again and press with left mouse button on "Format Data Labels"
6. A new window appears to the right, deselect X and Y Value.
   
7. Enable "Value from cells"
   
8. Select cell range D3:D11
9. Press with left mouse button on OK

This is what the chart shows, as you can see you need to manually rearrange the data labels and add data label shapes.

Back to top

1.1 Video

The following video shows you how to add data labels in an X Y Scatter Chart [Excel 2013 and later versions].

Back to top

Learn more

Axis | Chart Area | Chart Title | Axis Titles | Axis lines | Chart Legend | Tick Marks | Plot Area | Data Series | Data Labels | Gridlines

1.2 How to add data label shapes

1. Press with right mouse button on on a data label.
2. Press with mouse on "Change data label shapes".
3. Select a shape.

Back to top

1.3 How to change data label locations

You can manually press with left mouse button on and drag data labels as needed. You can also let excel change the position of all data labels, choose between center, left, right, above and below.

1. Press with right mouse button on on a data label
2. Press with left mouse button on "Format Data Labels"
   
3. Select a new label position.

Back to top

Back to top

Learn more

Secondary Axis | Linear trendline | Logarithmic Trendline | Moving Average | Error Bars

2. Workaround for earlier Excel versions

This workaround is for Excel 2010 and 2007, it is best for a small number of chart data points.

1. Press with left mouse button on twice on a label to select it.
2. Press with left mouse button on in formula bar.
3. Type =
4. Use your mouse to press with left mouse button on a cell that contains the value you want to use.
5. The formula bar changes to perhaps =Sheet1!$D$3
   
6. Repeat step 1 to 5 with remaining data labels.

Change the value in cell D3 and see how the data label on the chart instantly changes.

The following animated picture demonstrates how to link a cell value to a specific chart data point.

If your chart has many data points this method becomes quickly tedious and time-consuming.

I have automated these steps for you in a macro that you can read about below, there is also an example workbook that you can get.

Learn more

Column | Bar | Line | Area | Pie | Doughnut | Scatter | Bubble |  Funnel | Stock | Candlestick | Surface | Radar | Map

Back to top

2.1 Apply custom data labels (VBA Macro)

This macro adds a cell reference to each data label, the value in the referenced cell is then linked to the label. If you change the value in the cell the label value changes as well.

'Name macro
Sub AddDataLabels()

'Enable error handling
On Error Resume Next

'Display an inputbox and ask the user for a cell range
Set Rng = Application.InputBox(prompt:="Select cells to link" _
, Title:="Select data label values", Default:=ActiveCell.Address, Type:=8)

'Disable error handling
On Error GoTo 0

With ActiveChart
    
    'Iterate through each series in chart
    For Each ChSer In .SeriesCollection

        'Save chart point to object SerPo
        Set SerPo = ChSer.Points

        'Save the number of points in chart to variable j
        j = SerPo.Count

        'Iterate though each point in current series
        For i = 1 To j

            'Enable data label for current chart point
            SerPo(i).ApplyDataLabels Type:=xlShowValue

            'Save cell reference to chart point
            SerPo(i).DataLabel.Formula = "=" & ActiveSheet.Name _
            & "!" & Rng.Cells(i).Address(ReferenceStyle:=xlR1C1)
        Next
    Next
End With
End Sub

Learn more

Waterfall | Treemap | Sunburst | Histogram | Pareto | Box & Whisker

Back to top

2.2 Where to put the code?

1. Copy macro (CTRL + c)
2. Go to the VB Editor (Alt + F11)
   
3. Press with left mouse button on "Insert" on the top menu.
4. Press with left mouse button on "Module" to insert a code module to your workbook.
5. Paste code to the module. (CTRL + v)
6. Return to Excel.
7. Save your workbook as a macro-enabled workbook (*.xlsm file).
   If you don't the macro will be gone the next time you open the same workbook.

Back to top

Learn more

Arrow | Normal distribution | Equation | Comparison | Heat map | Gantt

2.3 How to use macro

1. Select the x y scatter chart.
   
2. Press Alt+F8 to view a list of macros available.
   
3. Select "AddDataLabels".
4. Press with left mouse button on "Run" button.
5. Select the custom data labels you want to assign to your chart.
   Make sure you select as many cells as there are data points in your chart.
   
6. Press with left mouse button on OK button.

Back to top

Back to top

2. Label line chart series

The chart above contains no legend instead data labels are used to show what each line represents.

2.1. How to build

1. Insert a line chart.
   
2. To label each line we need a cell range with the same size as the chart source data. Simply copy the chart source data range and paste it to your worksheet, then delete all data.
3. All cells are now empty. Copy categories (Regions in this example) and paste to the last column (2018).
   Those correspond to the last data points in each series.
   
4. Press with right mouse button on on a data series and select "Add Data Labels".
   
5. Double press with left mouse button on with left mouse button on one of the data labels you just inserted to open the task pane window.
   
6. Select checkbox "Value from cells".
7. Select data label cell range we created earlier in step 3 and 4, that corresponds to the same line series. Use the legend to identify line series.
   In this example the data labels correspond to "South America", see image below.
   
8. The data labels now show both numerical values and the last text value.
   
   To hide the numerical values simply double press with left mouse button on on the data labels to open the task pane.
9. Deselect check box "Value".
   
10. All numerical values are now deleted from the data labels, only the last data point has a data label, see image below.
    
11. Position the data label to the right of the data point using the task pane.
    
12. Resize the plot area so the data label doesn't collide with the line.
    
13. Repeat step 4-12 with the remaining line series.

Back to top

2.2. How to wrap data label text

1. Double press with left mouse button on the cell that contains the data label.
2. Put the prompt between the words.
3. Press Alt + Enter.
4. Press Enter.

Back to top

2.3. Align data labels

1. If you want the labels to be aligned to the left simply select the data label.
2. Go to tab "Home" on the ribbon.
3. Press with left mouse button on the "Align Left" button.
   

Back to top

2.4 Get Excel *.xlsx file

Label series.xlsx

Back to top

3. Custom data labels in a column chart

You can easily change data labels in a chart. Select a single data label and enter a reference to a cell in the formula bar. You can also edit data labels, one by one, on the chart.

With many data labels, the task becomes quickly boring and time-consuming. But wait, there is a third option using a duplicate series on a secondary axis.

The animated image above shows you dynamic custom data labels. Here is how you build them.

3.1 Create a chart

1. Select a cell range
2. Go to "Insert" tab
3. Press with left mouse button on "Column" button
4. Select the first 2-D Column chart

3.2 Add another series to the chart

1. Press with right mouse button on on chart
2. Press with left mouse button on Select data
   
3. Press with left mouse button on "Add" button
4. Select a series name, cell C2
5. Select series values, C3:C8
   
6. Press with left mouse button on Ok
7. Press with left mouse button on Ok

3.3 Plot series on the secondary axis

1. Press with left mouse button on the second series on the chart
2. Press with right mouse button on on a "second series" column
   
3. Press with left mouse button on "Format Data Series..."
   
4. Select "Secondary Axis"
5. Press with left mouse button on Close

The following article shows you another trick using the secondary axis in a way it wasn't intended to do:

Recommended articles

3.4 Change the second series data source

1. Press with right mouse button on on the chart
2. Press with left mouse button on "Select Data"
   
3. Select the second series
   
4. Press with left mouse button on "Edit" button (Horizontal (Category) Axis Labels)
5. Select cell range D3:D8
6. Press with left mouse button on OK
7. Press with left mouse button on OK

3.5 Add data labels

1. Press with right mouse button on on a column
   
2. Press with left mouse button on "Add Data Labels"
3. Double press with left mouse button on a data label
   
4. Deselect Value
5. Select Category name
6. Press with left mouse button on Close

6. How to add a secondary axis to an Excel Chart

It might be impossible to read the smaller series values on the y-axis if you have two series of data plotted on a chart and one of the series has much smaller values, see the image above.

Data in series A on the Excel chart above is really hard to compare and analyze. You can solve this problem by moving series B to a secondary axis.

Follow these steps to add a secondary axis to an Excel Chart

1. Press with right mouse button on on series B.
2. Press with left mouse button on "Format Data Series...".
   
3. Select "Secondary Axis".
4. Press with left mouse button on Close button.

If you are unlucky, one of the data series is hidden. In this case, series A is totally hidden behind series B, see chart above. You can change the transparency for series B but in my opinion, it makes the chart better but not great.

Yes, you can see both data series but it is still a mess and the colors in the legend and the columns don't match, see chart above. Don't add transparency, try separating the two series instead. Instructions, below.

Back to top

6.1 Add two blank series

This is the data I am working with.

Here is how to add a blank series.

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select Data...".
3. Press with left mouse button on the "Add" button, see the image below.
   
4. Select a blank cell range, in this case D2:D7.
   
5. Press with left mouse button on OK button.

Repeat steps 3 to 6 to add a fourth blank series, you can use the same cell range. Then press with left mouse button on the OK button. The chart now looks like this:

Back to top

6.2 Add spaces between columns series

1. Press with right mouse button on on series A on the chart.
2. Press with left mouse button on "Format Data Series...".
3. Add "Gap width" to 400%.
4. Press with left mouse button on OK.

6.3 How to customize the chart legend

The steps below describe how to edit the legend and tell the user which chart series belong to which y-axis.

1. Press with mouse on the empty legend entry and delete.
2. Do the same with Series4.
3. Move the legend below.
4. Add more descriptive text.
5. Delete grid lines.

Back to top

7. How to add data labels to chart series

One way to make the second smaller series easier to read is to add labels to each column/bar, however, the size of the columns still makes it hard for a quick comparison.

1. Press with right mouse button on on one of the second series' columns/bars.
   
2. Press with mouse on "Add Data Labels".

This article explains how to customize data labels: How to add and customize chart data labels

Back to top

8. Adjust axis min and max value

The image above displays a chart that has both the min and max values of the y-axis changed. This makes it easier to compare chart column sizes, however, it also may make the increase or decrease more pronounced and more serious than it really is.

1. Press with right mouse button on on one y-axis.
2. Press with mouse on "Format Axis...".
3. A dialog box appears or a settings pane if you use a more recent Excel version.
   
4. Change the minimum and the maximum values. Choose values that are closer to the largest and smallest series values.
5. Exit the dialog box or settings pane.

Back to top

Charts category

Scatter x y chart category

More than 1300 Excel formulas

Excel categories

Comments (27)