Custom data labels in a chart
Author: Oscar Cronquist Article last updated on January 21, 2020
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.
- Press with right mouse button on on any data series displayed in the chart.
- Press with mouse on "Add Data Labels".
- Press with mouse on Add Data Labels".
- Double press with left mouse button 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 press with left mouse button on OK button.
The chart shows the values you selected as data labels.
Create a chart
- Select a cell range
- Go to "Insert" tab
- Press with left mouse button on "Column" button
- Select the first 2-D Column chart
Add another series to the chart
- Press with right mouse button on on chart
- Press with left mouse button on Select data
- Press with left mouse button on "Add" button
- Select a series name, cell C2
- Select series values, C3:C8
- Press with left mouse button on Ok
- Press with left mouse button on Ok
Plot series on the secondary axis
- Press with left mouse button on the second series on the chart
- Press with right mouse button on on a "second series" column
- Press with left mouse button on "Format Data Series..."
- Select "Secondary Axis"
- 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:
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
- Press with right mouse button on on the chart
- Press with left mouse button on "Select Data"
- Select the second series
- Press with left mouse button on "Edit" button (Horizontal (Category) Axis Labels)
- Select cell range D3:D8
- Press with left mouse button on OK
- Press with left mouse button on OK
Add data labels
- Press with right mouse button on on a column
- Press with left mouse button on "Add Data Labels"
- Double press with left mouse button on a data label
- Deselect Value
- Select Category name
- Press with left mouse button on Close
Related articles
How to customize the chart legend
You can easily customize the font, font size, font color etc of the chart legend: Select legend. Go to tab […]
You can easily customize the font, font size, font color etc of the chart legend: Select legend. Go to tab […]
Add pictures to a chart axis
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
Advanced Gantt Chart Template
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are […]
Build a comparison table/chart
I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, […]
I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, […]
Build a stock chart with two series
This article demonstrates how to create a stock chart with two series. Related articles Improve worksheet readabilityMaking your sheets easy […]
This article demonstrates how to create a stock chart with two series. Related articles Improve worksheet readabilityMaking your sheets easy […]
Change chart axis range programmatically
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
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 […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
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 […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Change column/bar color in charts
This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color […]
This article demonstrates how to set up a chart so it shows one color for increasing bars/columns and another color […]
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 […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
Compare data in an Excel chart using drop down lists
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Dynamic Gantt charts
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
Dynamic stock chart
This stock chart built in Excel allows you to change the date range and the chart is instantly updated. What's […]
This stock chart built in Excel allows you to change the date range and the chart is instantly updated. What's […]
Excel chart components
Charts in Microsoft Excel lets you visualize, analyze and explain data. Charting in Excel is very easy and you […]
Charts in Microsoft Excel lets you visualize, analyze and explain data. Charting in Excel is very easy and you […]
Excel chart problem: Hard to read series values
It might be impossible to read the smaller series values on the y-axis if you have two series of data […]
It might be impossible to read the smaller series values on the y-axis if you have two series of data […]
Four ways to resize a chart
To be able to resize a chart you must first select it, you do that by press with left mouse button […]
To be able to resize a chart you must first select it, you do that by press with left mouse button […]
Group chart categories
The image above shows you categories (countries) grouped into regions making this chart a lot cleaner and easier to read. How […]
The image above shows you categories (countries) grouped into regions making this chart a lot cleaner and easier to read. How […]
Heat map using pictures
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
Heat map yearly calendar
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Hide specific columns programmatically
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
Highlight a bar in a chart
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
Highlight a column in a stacked column chart
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
Highlight a column in a stacked column chart
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
Highlight a data series in a line chart
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Highlight a group of chart bars
This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine […]
This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine […]
Highlight group of values in an x y scatter chart programmatically
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
Hover with mouse cursor to change stock in a candlestick chart
This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]
This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]
How to add a linear trendline to a chart
Excel allows you to insert a linear chart trendline that displays a straight line calculated based on the method of […]
Excel allows you to insert a linear chart trendline that displays a straight line calculated based on the method of […]
How to add a logarithmic trendline in a chart
Excel lets you easily add a best-fit curved logarithmic trendline calculated based on the method of least squares. How to […]
Excel lets you easily add a best-fit curved logarithmic trendline calculated based on the method of least squares. How to […]
How to add a moving average to a chart
A moving average smooths out short-term variations to show a long-term trend or cycle. The chart above shows random values […]
A moving average smooths out short-term variations to show a long-term trend or cycle. The chart above shows random values […]
How to add and customize chart data labels
The image above demonstrates data labels in a line chart, each data point in the chart series has a visible […]
The image above demonstrates data labels in a line chart, each data point in the chart series has a visible […]
How to add chart gridlines
Chart gridlines are great for making the chart data more readable and detailed, Excel allows you to add major and […]
Chart gridlines are great for making the chart data more readable and detailed, Excel allows you to add major and […]
How to add Error Bars in a chart
Enable error bars when you want to show, for example, standard deviations in a chart, Excel lets you insert error […]
Enable error bars when you want to show, for example, standard deviations in a chart, Excel lets you insert error […]
How to add horizontal line to chart
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
This tutorial shows you how to add a horizontal/vertical line to a chart. Excel allows you to combine two types […]
How to add lines between stacked columns/bars [Excel charts]
The image above shows lines between each colored column, here is how to add them automatically to your chart. Select […]
The image above shows lines between each colored column, here is how to add them automatically to your chart. Select […]
How to align chart with cell grid
This trick is so simple and also an incredible time-saver if you want to build beautiful worksheets or dashboards where […]
This trick is so simple and also an incredible time-saver if you want to build beautiful worksheets or dashboards where […]
How to animate a line chart
This article demonstrates how to create an animation using a line chart in Excel. The user selects a series in […]
This article demonstrates how to create an animation using a line chart in Excel. The user selects a series in […]
How to animate an Excel Bar Chart
This article demonstrates macros that animate bars in a chart bar. The image above shows a bar chart that animates […]
This article demonstrates macros that animate bars in a chart bar. The image above shows a bar chart that animates […]
How to animate an Excel chart
This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]
This article demonstrates how to create a chart that animates the columns when filtering chart data. The columns change incrementally […]
How to build an arrow chart
This chart is an arrow chart that has horizontal and vertical lines, positive arrows are green and negative arrows are […]
This chart is an arrow chart that has horizontal and vertical lines, positive arrows are green and negative arrows are […]
How to build an interactive map in Excel
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
How to color chart bars based on their values
(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]
(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]
How to create a 100% stacked column chart
The 100% stacked column chart lets you graph values in a group. Each value in a group is a stacked […]
The 100% stacked column chart lets you graph values in a group. Each value in a group is a stacked […]
How to create a bar chart
The bar chart is simply a column chart rotated 90 degrees right, this makes it great if you have long […]
The bar chart is simply a column chart rotated 90 degrees right, this makes it great if you have long […]
How to create a Box and Whisker chart
The Box and Whisker chart allows you to plot distributions in statistics, it also displays local minimum, first quartile, median, […]
The Box and Whisker chart allows you to plot distributions in statistics, it also displays local minimum, first quartile, median, […]
How to create a bubble chart
The bubble chart allows you to plot data just like the scatter chart but also the size of the bubbles. […]
The bubble chart allows you to plot data just like the scatter chart but also the size of the bubbles. […]
How to create a candlestick chart
The candlestick chart allows you to plot price movements of stocks, derivates, bonds and currencies. A black candlestick in Excel […]
The candlestick chart allows you to plot price movements of stocks, derivates, bonds and currencies. A black candlestick in Excel […]
How to create a column chart
The clustered column chart allows you to graph data in vertical bars, this layout makes it easy to compare values […]
The clustered column chart allows you to graph data in vertical bars, this layout makes it easy to compare values […]
How to create a combined chart – Column and Line on secondary axis
The combination chart category lets you build a chart that has columns on the primary axis and the line on […]
The combination chart category lets you build a chart that has columns on the primary axis and the line on […]
How to create a combined Column and Line chart
The Column and Line combo chart is a great choice if you want to differentiate two data series and make […]
The Column and Line combo chart is a great choice if you want to differentiate two data series and make […]
How to create a combined stacked area and a clustered column chart
Combine the stacked area chart and the clustered column chart if you want to differentiate at least two data series to […]
Combine the stacked area chart and the clustered column chart if you want to differentiate at least two data series to […]
How to create a doughnut chart
A doughnut chart is just like a pie chart showing the relationships of segments to a total except that the it […]
A doughnut chart is just like a pie chart showing the relationships of segments to a total except that the it […]
How to create a dynamic chart
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to create a funnel chart
A funnel chart is mostly used to display a series of steps within a process, each step shows a proportion […]
A funnel chart is mostly used to display a series of steps within a process, each step shows a proportion […]
How to create a histogram chart
The histogram chart lets you automatically group data values in intervals you specify, it is often used in statistics to show […]
The histogram chart lets you automatically group data values in intervals you specify, it is often used in statistics to show […]
How to create a line chart
The line chart lets you chart data points as a line, this chart type is useful if you have many […]
The line chart lets you chart data points as a line, this chart type is useful if you have many […]
How to create a map chart
Excel 2016 owners with an office 365 subscription can now easily build beautiful map charts. Excel uses maps from Bing […]
Excel 2016 owners with an office 365 subscription can now easily build beautiful map charts. Excel uses maps from Bing […]
How to create a Pareto chart
A Pareto chart is often used in statistics to demonstrate how sample values are distributed across given ranges, the values are […]
A Pareto chart is often used in statistics to demonstrate how sample values are distributed across given ranges, the values are […]
How to create a pie chart
The pie chart is used to graph parts of a whole, the total of our numbers must be 100%. The […]
The pie chart is used to graph parts of a whole, the total of our numbers must be 100%. The […]
How to create a radar chart
The radar chart allows you to plot values based on a center point and outwards to categories you specify. Each […]
The radar chart allows you to plot values based on a center point and outwards to categories you specify. Each […]
How to create a scatter chart
The scatter chart is great for charting numeric values in pairs, for example, coordinates. It lets you compare multiple data […]
The scatter chart is great for charting numeric values in pairs, for example, coordinates. It lets you compare multiple data […]
How to create a sparkline – Column
A sparkline is a visual representation of a data series in only one cell, the image above shows sparklines next […]
A sparkline is a visual representation of a data series in only one cell, the image above shows sparklines next […]
How to create a sparkline – Line
A sparkline is a visual representation of a data series in only one cell, the image above shows sparklines next […]
A sparkline is a visual representation of a data series in only one cell, the image above shows sparklines next […]
How to create a sparkline – Win/Loss
A win/loss sparkline is a visual representation of a data series in only one cell, the image above shows a […]
A win/loss sparkline is a visual representation of a data series in only one cell, the image above shows a […]
How to create a stacked column chart
The stacked column chart is great for comparing parts of a whole and how they change over time or categories. […]
The stacked column chart is great for comparing parts of a whole and how they change over time or categories. […]
How to create a stock chart
The high-low-close stock chart displays the high, low and closing price for a given date range. Each line represents a […]
The high-low-close stock chart displays the high, low and closing price for a given date range. Each line represents a […]
How to create a sunburst chart
(Press with left mouse button on to expand image) The sunburst chart is great for showing relationships of hierarchical data and […]
(Press with left mouse button on to expand image) The sunburst chart is great for showing relationships of hierarchical data and […]
How to create a surface chart
The surface chart allows you to graph multiple data series in a 3D chart, color-coded to make the chart easier […]
The surface chart allows you to graph multiple data series in a 3D chart, color-coded to make the chart easier […]
How to create a treemap chart
Use the treemap chart when you want to compare segments to a total and you have categories in a hierarchy. […]
Use the treemap chart when you want to compare segments to a total and you have categories in a hierarchy. […]
How to create a waterfall chart
The waterfall chart allows you to plot values as bars, however, depending on whether they are negative or positive […]
The waterfall chart allows you to plot values as bars, however, depending on whether they are negative or positive […]
How to create an area chart
The area chart is similar to the line chart except that the area below the line is filled, use this […]
The area chart is similar to the line chart except that the area below the line is filled, use this […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
How to customize a chart data series
To change the looks of a data series simply double-press with left mouse button on with left mouse button on […]
To change the looks of a data series simply double-press with left mouse button on with left mouse button on […]
How to customize chart axis
Most Excel charts consist of an x-axis and a y-axis, Excel allows you to easily change the looks of […]
Most Excel charts consist of an x-axis and a y-axis, Excel allows you to easily change the looks of […]
How to customize chart axis titles
The x-axis and y-axis titles are just as easy to format and customize as the other chart elements. How to […]
The x-axis and y-axis titles are just as easy to format and customize as the other chart elements. How to […]
How to customize chart tick marks
The image above shows tick mars in a line chart, there are two types of tick marks, major and minor […]
The image above shows tick mars in a line chart, there are two types of tick marks, major and minor […]
How to customize the chart area
The chart area contains all components. If you choose to format the chart area you can change things like border, […]
The chart area contains all components. If you choose to format the chart area you can change things like border, […]
How to customize the chart title
I recommend that the chart title is short and easy to understand. How to add a chart title Follow these […]
I recommend that the chart title is short and easy to understand. How to add a chart title Follow these […]
How to customize the plot area
The plot area refers to the location of the chart that displays the actual data represented by lines, bars, columns, […]
The plot area refers to the location of the chart that displays the actual data represented by lines, bars, columns, […]
How to filter chart data
What if you want to show a selection of a data set on a chart and easily change that selection? […]
What if you want to show a selection of a data set on a chart and easily change that selection? […]
How to graph a Normal Distribution
The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]
The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]
How to graph an equation
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
How to position month and year between chart tick marks
The picture above shows a line chart with month and year labels between tick marks instead of date values below […]
The picture above shows a line chart with month and year labels between tick marks instead of date values below […]
How to replace columns with pictures in a column chart
I found an interesting chart on CNN's website: Rise of the supersize rugby player It shows the average height of athletes […]
I found an interesting chart on CNN's website: Rise of the supersize rugby player It shows the average height of athletes […]
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 […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
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 […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
Label line chart series
The chart above contains no legend instead data labels are used to show what each line represent. How to build […]
The chart above contains no legend instead data labels are used to show what each line represent. How to build […]
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. […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
Two y-axes in one chart
The image above demonstrates a line chart containing two data series and two y-axes, one for each data series. I […]
The image above demonstrates a line chart containing two data series and two y-axes, one for each data series. I […]
Use drop down lists and named ranges to filter chart values
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
Use slicers to quickly filter chart data
Slicers let you control data displayed in a chart, simply press with left mouse button on a button to quickly […]
Slicers let you control data displayed in a chart, simply press with left mouse button on a button to quickly […]
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). Press with right mouse button 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 press with mouse 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 press with mouse on a series
2. Press with left mouse button on "Add Data Labels"
3. Right press with mouse again on a series
4. Press with left mouse button on "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??