Custom data labels in a 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.
Note! Before you continue reading. If you own Excel 2013 or a later version you don't have to do the work-around presented below this yellow box.
- Right-click on any data series displayed in the chart.
- Click on "Add Data Labels".
- Click on Add Data Labels".
- Double click on any data label to expand the "Format Data Series" pane.
- Enable checkbox "Value from cells".
A small dialog box prompts for a cell range containing the values you want to use a s data labels. - Select the cell range and click OK button.
The chart shows the values you selected as data labels.
Create a chart
- Select a cell range
- Go to "Insert" tab
- Click "Column" button
- Select the first 2-D Column chart
Add another series to the chart
- Right click on chart
- Click Select data
- Click "Add" button
- Select a series name, cell C2
- Select series values, C3:C8
- Click Ok
- Click Ok
Plot series on the secondary axis
- Click the second series on the chart
- Right click on a "second series" column
- Click "Format Data Series..."
- Select "Secondary Axis"
- Click Close
The following article shows you another trick using the secondary axis in a way it wasn't intended to do:
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
Change the second series data source
- Right click on the chart
- Click "Select Data"
- Select the second series
- Click "Edit" button (Horizontal (Category) Axis Labels)
- Select cell range D3:D8
- Click OK
- Click OK
Add data labels
- Right click on a column
- Click "Add Data Labels"
- Double click a data label
- Deselect Value
- Select Category name
- Click Close
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Rearrange data source in order to create a dynamic chart
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
Change chart series by clicking on data [VBA]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
13 Responses to “Custom data labels in a chart”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Or for the slothful there's the beautiful free addin from Rob Covey called "XY Chart Labeler" and in it does just this very thing in couple of steps!
I have to try it!
Here is a link: https://www.appspro.com/Utilities/ChartLabeler.htm
Thank you for commenting!
I tried the solution above and it worked!
Tom,
I am happy you got it working!
I'm sure that's great for bar charts, but it doesn't work for x-y scatter plots. No matter what, the option to edit 'Horizontal (category) axis labels'(step 3 above) is always greyed out and thus unavailable. Any other options? It does seem daft that you can label x-y points on a 'map' with either x or y value, but not something useful....
After you have added the second series on the secondary axis (the copy of the first). Right click and change the series chart type.
Select the clustered bar. Now it's changed to a bar chart that you will be able to change the horizontal axis label on it.
Once you have the labels in place then right click on the bar series and select 'format data series'. Now change the Fill to Solid Fill and make it the same color as the background. Now make the transparency 100%.
This should make your bar chart series 'disappear' but retain the labels.
Sean Foster,
Very interesting but I can't get that to work?
My workbook: https://www.get-digital-help.com/wp-content/uploads/2013/03/xy-scatter-chart-custom-labels1.xlsx
What am I doing wrong?
[…] demonstrated in a post from March 2013 how to create Custom data labels in a chart. Unfortunately that technique worked only for bar and column charts, there was no way you could […]
great
thanks
Oscar
Thanks a lot. It's very helpful hint.
Unfortunately it doesn't work with scatter plots.
Though i bet MS Office 2013 has some additional options for that.
Best regards
DP
Denis Pro
Thank you.
If you have excel 2013 you can use custom data labels on a scatter chart.
1. Right click on a series
2. Click "Add Data Labels"
3. Right click again on a series
4. Click "Format Data Labels"
5. Enable check box "Value from cells"
6. Select a cell range
7. Disable check box "Y Value"
My problem is I need to have data labels added automatically as data is added; however, I have multiple series feeding the scatter plot so the data labels are specific per series. Series 1: Column 1 = X Values; Column 2 = Y value; Column 3 = Bubble Size; Column 4 = Bubble (Data) Label and repeat this for an additional 56 series. Some cells will be blank as well so count 1 does not work for me (or at least not the code I was using).
I can rearrange my columns; however, I need to accomplish the automatic population of data labels.
How to show a connector indicating which data value is allocated to which data level??