A column or line chart shows the trend, but readers usually need two more specific things: what each value actually is, and how reliable that number is. Data labels put the value right next to the mark; error bars draw a short line showing the range of variation. Neither changes the chart's data, they simply turn "read the shape" into "read the numbers".
In Excel these two settings sit under Add Data Labels and Error Bars in the chart menus. When a chart is generated by a script, both can be written into the code. This article uses Spire.XLS for Python to set data labels for chart series, replace the label of a single data point, and add percentage and standard deviation error bars.
Setting Up the Environment
Install Spire.XLS and import the modules:
pip install Spire.XLS
from spire.xls import *
from spire.xls.common import *
Creating the Base Chart
Start with a set of planned and actual monthly figures and build a clustered column chart. Data labels and error bars attach to series, so the chart has to exist first:
from spire.xls import *
from spire.xls.common import *
workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]
months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun"]
planned = [3.3, 2.5, 2.0, 3.7, 4.5, 4.0]
actual = [3.8, 3.2, 1.7, 3.5, 4.5, 4.3]
sheet.Range["A1"].Value = "Month"
sheet.Range["B1"].Value = "Planned"
sheet.Range["C1"].Value = "Actual"
for i in range(len(months)):
sheet.Range[i + 2, 1].Value = months[i]
sheet.Range[i + 2, 2].NumberValue = planned[i]
sheet.Range[i + 2, 3].NumberValue = actual[i]
chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
chart.DataRange = sheet.Range["B1:C7"]
chart.SeriesDataFromRange = False
chart.ChartTitle = "Monthly Results"
for series in chart.Series:
series.CategoryLabels = sheet.Range["A2:A7"]
# Place the chart below the data
chart.TopRow = 10
chart.BottomRow = 28
chart.LeftColumn = 1
chart.RightColumn = 10
workbook.SaveToFile("AnnotatedChart.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Chart.TopRow, BottomRow, LeftColumn, and RightColumn position and size the chart by row and column numbers, which keeps it from covering the data table. CategoryLabels binds the month labels to the category axis for each series.
Showing Value Labels
Data labels live on DataPoints.DefaultDataPoint.DataLabels of a series. DefaultDataPoint means "the default label for every point in this series", so one change applies to the whole series:
from spire.xls import *
from spire.xls.common import *
workbook = Workbook()
workbook.LoadFromFile("AnnotatedChart.xlsx")
sheet = workbook.Worksheets[0]
chart = sheet.Charts[0]
# Show the value on every data point
for series in chart.Series:
labels = series.DataPoints.DefaultDataPoint.DataLabels
labels.HasValue = True
labels.HasLegendKey = False
labels.Position = DataLabelPositionType.Outside
labels.Size = 9
workbook.SaveToFile("DataLabels.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
The key properties:
-
HasValuecontrols whether the number itself is shown, the most basic content of a label. -
HasLegendKeycontrols the legend-key swatch. Reports usually set it toFalseto keep labels free of the extra colour square. -
Positionplaces the label. Column charts commonly useOutside(above the bar), line charts useAbove(over the point). -
Sizesets the font size; the default is easy to crowd in dense charts.
Combining Label Content and Style
Besides the value, a label can include the series name and the category name, with Delimiter defining what separates them. The example below produces labels of the form "series. month. value" in small red bold type:
from spire.xls import *
from spire.xls.common import *
workbook = Workbook()
workbook.LoadFromFile("AnnotatedChart.xlsx")
sheet = workbook.Worksheets[0]
chart = sheet.Charts[0]
# Labels with series name, category name, and value
labels = chart.Series[0].DataPoints.DefaultDataPoint.DataLabels
labels.HasValue = True
labels.HasSeriesName = True
labels.HasCategoryName = True
labels.Delimiter = ". "
labels.Position = DataLabelPositionType.Outside
labels.Size = 8
labels.IsBold = True
labels.Color = Color.get_Red()
workbook.SaveToFile("DataLabelDetails.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
HasSeriesName and HasCategoryName turn on the series and category text; Delimiter joins the three parts, and . here produces a label such as "Planned. May. 4.5". Font size, weight, and colour come from Size, IsBold, and Color, applied to the default label of the whole series.
Customising a Single Data Point
Sometimes only one point needs a note — the first month of a new product, for example. The DataPoints collection is indexed, and its DataLabels.Text overrides the default label:
from spire.xls import *
from spire.xls.common import *
workbook = Workbook()
workbook.LoadFromFile("DataLabels.xlsx")
sheet = workbook.Worksheets[0]
chart = sheet.Charts[0]
# Replace the label of a single data point
point_label = chart.Series[0].DataPoints[0].DataLabels
point_label.Text = "Launch"
point_label.HasValue = True
point_label.IsBold = True
point_label.Color = Color.get_Red()
workbook.SaveToFile("CustomLabel.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
DataPoints[0] is the first point of the series. Setting Text makes that point show the custom wording while the rest of the series keeps the default value labels. Keeping HasValue on alongside Text also shows the original number after the custom word.
Adding Error Bars
Error bars are added with a series' ErrorBar() method. Its four arguments are: whether to show them, the direction (Plus / Minus / Both), the type, and the amount. The type decides how the error amount is calculated:
-
Percentage: a percentage of the value;20.0means plus or minus 20%. -
StandardDeviation: a multiple of the standard deviation;1.0means one standard deviation. -
StandardError: the standard error, where the amount argument is unused and0.0can be passed. -
Fixed: a fixed amount, useful for measurement data with a known error range.
The example adds a 20% one-sided error bar to the Planned series and a one-standard-deviation two-sided error bar to the Actual series:
from spire.xls import *
from spire.xls.common import *
workbook = Workbook()
workbook.LoadFromFile("DataLabels.xlsx")
sheet = workbook.Worksheets[0]
chart = sheet.Charts[0]
# Percentage error bars on the Planned series
chart.Series[0].ErrorBar(
True,
ErrorBarIncludeType.Plus,
ErrorBarType.Percentage,
20.0,
)
# Standard deviation error bars on the Actual series
chart.Series[1].ErrorBar(
True,
ErrorBarIncludeType.Both,
ErrorBarType.StandardDeviation,
1.0,
)
workbook.SaveToFile("ErrorBars.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
The direction carries meaning: Plus draws only upward, for "how much higher could it be"; Minus draws only downward; Both puts a segment on each side. Used together with data labels, the chart states both the value and the uncertainty around it.
Practical Tips
-
Build the series first: labels and error bars hang off
Series, so the chart and its data range must exist. -
One change covers the series:
DefaultDataPointapplies to every point; no per-point loop is needed. -
Match the position to the chart type:
Outsidefor columns,Abovefor lines,Centerfor pies. -
Turn off the legend key:
HasLegendKey = Falsekeeps labels clean; the legend already carries the series. -
Index points from zero:
DataPoints[0]is the first point in the data range. - The amount depends on the type: percentage and standard deviation take a number; standard error ignores it.
-
Release resources: call
Dispose()on each workbook when done.
Conclusion
This article showed how to annotate Excel charts from Python: set value labels, series names, and category names for a whole series through DataPoints.DefaultDataPoint.DataLabels, control content and style with Delimiter, Position, Size, IsBold, and Color; replace one point's text with DataPoints[i].DataLabels.Text; and add percentage, standard deviation, or standard error bars with ErrorBar(), using ErrorBarIncludeType to choose one or both sides. Written into a report script, these settings let a chart show the trend and state the exact values and their variation at the same time.



Top comments (0)