Excel:...:Chart(3pm)

📦

version 1.15 · 7 versions

1.15 · ubuntu resolute, ubuntu stonking (latest)
1.14 · epel 9
1.14 · fedora 43
1.09 · ubuntu jammy
1.15 · fedora 44
1.10 · debian bookworm
1.11 · debian trixie, ubuntu noble, ubuntu questing

✨ NAME ✨

Chart — a whole class for writing Excel charts, bestie. 💖 (You might also see it as Excel::Writer::XLSX::Chart in the wild, no cap.)

🌸 SYNOPSIS 🌸

To create a simple Excel file with a chart using Excel::Writer::XLSX, you do this:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' );
my $worksheet = $workbook->add_worksheet();

# Add the worksheet data the chart refers to.
my $data = [
    [ 'Category', 2, 3, 4, 5, 6, 7 ],
    [ 'Value',    1, 4, 5, 2, 1, 5 ],

];

$worksheet->write( 'A1', $data );

# Add a worksheet chart.
my $chart = $workbook->add_chart( type => 'column' );

# Configure the chart.
$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

$workbook->close();

__END__

💅 DESCRIPTION 💅

So the Chart module is like the abstract base class for all the modules that implement charts in Excel::Writer::XLSX. Everything below applies to every available subclass, periodt. 💖

You don’t use Chart directly. You make a chart object with the Workbook add_chart() method, where you specify the chart type:

my $chart = $workbook->add_chart( type => 'column' );

Right now, the supported chart types are giving:

area — creates an Area (filled line) style chart. See Excel::Writer::XLSX::Chart::Area for the whole vibe. 🦋
bar — creates a Bar style (transposed histogram) chart. See Excel::Writer::XLSX::Chart::Bar. 💅
column — creates a column style (histogram) chart. See Excel::Writer::XLSX::Chart::Column. ✨
line — creates a Line style chart. See Excel::Writer::XLSX::Chart::Line. 🌸
pie — creates a Pie style chart. See Excel::Writer::XLSX::Chart::Pie. 🥧
doughnut — creates a Doughnut style chart. See Excel::Writer::XLSX::Chart::Doughnut. 🍩
scatter — creates a Scatter style chart. See Excel::Writer::XLSX::Chart::Scatter. ✨
stock — creates a Stock style chart. See Excel::Writer::XLSX::Chart::Stock. 📈
radar — creates a Radar style chart. See Excel::Writer::XLSX::Chart::Radar. 🎯

Chart subtypes are also supported in some cases:

$workbook->add_chart( type => 'bar', subtype => 'stacked' );

The currently available subtypes are:

area
    stacked
    percent_stacked

bar
    stacked
    percent_stacked

column
    stacked
    percent_stacked

scatter
    straight_with_markers
    straight
    smooth_with_markers
    smooth

line
    stacked
    percent_stacked

radar
    with_markers
    filled

🔥 CHART METHODS 🔥

Methods that are common to all chart types are documented below. For chart-specific info, check the documentation for each of the above chart modules. 💖

🦋 add_series() 🦋

In an Excel chart, a “series” is a collection of information like values, X axis labels, and the formatting that define which data is plotted. It’s giving data with a glow-up. ✨

With an Excel::Writer::XLSX chart object, you use the add_series() method to set the properties for a series:

$chart->add_series(
    categories => '=Sheet1!$A$2:$A$10', # Optional.
    values     => '=Sheet1!$B$2:$B$10', # Required.
    line       => { color => 'blue' },
);

The properties that can be set are:

values — This is the most important property of a series and must be set for every chart object. It links the chart with the worksheet data that it displays. A formula or array ref can be used for the data range, see below. 🔥
categories — This sets the chart category labels. The category is more or less the same as the X axis. In most chart types the categories property is optional and the chart will just assume a sequential series from 1 .. n. 💅
name — Set the name for the series. The name is displayed in the chart legend and in the formula bar. The name property is optional and if it isn’t supplied it will default to Series 1 .. n. 🎀
line — Set the properties of the series line type such as colour and width. See the “CHART FORMATTING” section below. 🌈
border — Set the border properties of the series such as colour and style. See the “CHART FORMATTING” section below. ✨
fill — Set the fill properties of the series such as colour. See the “CHART FORMATTING” section below. 💖
pattern — Set the pattern properties of the series. See the “CHART FORMATTING” section below. 🦋
gradient — Set the gradient properties of the series. See the “CHART FORMATTING” section below. 🌸
marker — Set the properties of the series marker such as style and colour. See the “SERIES OPTIONS” section below. 📍
trendline — Set the properties of the series trendline such as linear, polynomial and moving average types. See the “SERIES OPTIONS” section below. 📈
smooth — The smooth option is used to set the smooth property of a line series. See the “SERIES OPTIONS” section below. 🧈
y_error_bars — Set vertical error bounds for a chart series. See the “SERIES OPTIONS” section below. 📊
x_error_bars — Set horizontal error bounds for a chart series. See the “SERIES OPTIONS” section below. 📉
data_labels — Set data labels for the series. See the “SERIES OPTIONS” section below. 🏷️
points — Set properties for individual points in a series. See the “SERIES OPTIONS” section below. 🔴
invert_if_negative — Invert the fill colour for negative values. Usually only applicable to column and bar charts. 🎨
overlap — Set the overlap between series in a Bar/Column chart. The range is +/- 100. Default is 0.

overlap => 20,

Note, it is only necessary to apply this property to one series of the chart. 🤝

gap — Set the gap between series in a Bar/Column chart. The range is 0 to 500. Default is 150.

gap => 200,

Note, it is only necessary to apply this property to one series of the chart. 📏

The categories and values can take either a range formula such as =Sheet1!$A$2:$A$7 or, more usefully when generating the range programmatically, an array ref with zero indexed row/column values:

[ $sheetname, $row_start, $row_end, $col_start, $col_end ]

The following are equivalent:

$chart->add_series( categories => '=Sheet1!$A$2:$A$7'      ); # Same as ...
$chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.

You can add more than one series to a chart. In fact, some chart types such as stock require it. The series numbering and order in the Excel chart will be the same as the order in which they are added in Excel::Writer::XLSX. 💯

# Add the first series.
$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
    name       => 'Test data series 1',
);

# Add another series. Same categories. Different range values.
$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$C$2:$C$7',
    name       => 'Test data series 2',
);

It is also possible to specify non-contiguous ranges:

$chart->add_series(
    categories      => '=(Sheet1!$A$1:$A$9,Sheet1!$A$14:$A$25)',
    values          => '=(Sheet1!$B$1:$B$9,Sheet1!$B$14:$B$25)',
);

🎀 set_x_axis() 🎀

The set_x_axis() method is used to set properties of the X axis.

$chart->set_x_axis( name => 'Quarterly results' );

The properties that can be set are:

name
name_font
name_layout
name_line
name_fill
name_pattern
name_gradient
num_format
num_font
line
fill
pattern
gradient
min
max
minor_unit
major_unit
interval_unit
interval_tick
crossing
reverse
position_axis
log_base
label_position
major_gridlines
minor_gridlines
visible
date_axis
text_axis
minor_unit_type
major_unit_type
minor_tick_mark
major_tick_mark
display_units
display_units_visible

These are explained below. Some properties are only applicable to value or category axes, as indicated. See “Value and Category Axes” for an explanation of Excel’s distinction between the axis types. 🧐

name — Set the name (title or caption) for the axis. The name is displayed below the X axis. The name property is optional. The default is to have no axis name. (Applicable to category and value axes).

$chart->set_x_axis( name => 'Quarterly results' );

The name can also be a formula such as =Sheet1!$A$1. ✨

name_font — Set the font properties for the axis title. (Applicable to category and value axes).

$chart->set_x_axis( name_font => { name => 'Arial', size => 10 } );

name_layout — Set the (x, y) position of the axis caption in chart relative units. (Applicable to category and value axes).

$chart->set_x_axis(
    name        => 'X axis',
    name_layout => {
        x => 0.34,
        y => 0.85,
    }
);

See the “CHART LAYOUT” section below. 📐

num_font — Set the font properties for the axis numbers. (Applicable to category and value axes).

$chart->set_x_axis( num_font => { bold => 1, italic => 1 } );

See the “CHART FONTS” section below. 🔢

num_format — Set the number format for the axis. (Applicable to category and value axes).

$chart->set_x_axis( num_format => '#,##0.00' );
$chart->set_y_axis( num_format => '0.00%'    );

The number format is similar to the Worksheet Cell Format num_format apart from the fact that a format index cannot be used. The explicit format string must be used as shown above. See set_num_format() in Excel::Writer::XLSX for more information. 💯

line — Set the properties of the axis line/border type such as colour and width. See the “CHART FORMATTING” section below. 🌈

okay so we were literally mid-set_x_axis() options and the line one was like, you can also just completely yeet the line:

$chart->set_x_axis( line => { none => 1 });

fill — sets the fill properties of the axis. peep the "CHART FORMATTING" section below for the deets ✨ but ⚠️ heads up bestie: in Excel the axis fill gets applied to the area of the numbers of the axis, not the area of the axis bounding box. that background? that's set from the chartarea fill. don't get it twisted 💅
pattern — sets the pattern properties of the axis. see the "CHART FORMATTING" section below.
gradient — sets the gradient properties of the axis. see the "CHART FORMATTING" section below.
name_line — sets the properties of the axis title/name line/border. see the "CHART FORMATTING" section below.
name_fill — sets the fill properties of the axis title/name. see the "CHART FORMATTING" section below.
name_pattern — sets the pattern properties of the axis title/name. see the "CHART FORMATTING" section below.
name_gradient — sets the gradient properties of the axis title/name. see the "CHART FORMATTING" section below.
min — sets the minimum value for the axis range. (this one's for value axes only, babes.)

$chart->set_x_axis( min => 20 );

max — sets the maximum value for the axis range. (value axes only.)

$chart->set_x_axis( max => 80 );

minor_unit — sets the increment of the minor units in the axis range. (value axes only.)

$chart->set_x_axis( minor_unit => 0.4 );

major_unit — sets the increment of the major units in the axis range. (value axes only.)

$chart->set_x_axis( major_unit => 2 );

interval_unit — sets the interval unit for a category axis. (category axes only.)

$chart->set_x_axis( interval_unit => 2 );

interval_tick — sets the tick interval for a category axis. (category axes only.)

$chart->set_x_axis( interval_tick => 4 );

crossing — sets the position where the y axis will cross the x axis. (applies to category and value axes.)

the crossing value can be a numeric value, or the strings 'max' or 'min' to set the crossing at the maximum/minimum axis value. 🎯

$chart->set_x_axis( crossing => 3 );
# or
$chart->set_x_axis( crossing => 'max' );

for category axes the numeric value must be an integer, representing the category number that the axis crosses at. for value axes it can have any value associated with the axis.

if crossing is omitted (that's the default) the crossing gets set automatically by Excel based on the chart data — no thoughts, Excel's got it ✨

position_axis — positions the axis on or between the axis tick marks. (category axes only.)

two allowable values: on_tick and between:

$chart->set_x_axis( position_axis => 'on_tick' );
$chart->set_x_axis( position_axis => 'between' );

reverse — reverses the order of the axis categories or values. (category and value axes.)

$chart->set_x_axis( reverse => 1 );

log_base — sets the log base of the axis range. (value axes only.)

$chart->set_x_axis( log_base => 10 );

label_position — sets the "Axis labels" position for the axis. the available positions are:

next_to (the default)
high
low
none

major_gridlines — configures the major gridlines for the axis. the available properties are:

visible
line

for example:

$chart->set_x_axis(
    major_gridlines => {
        visible => 1,
        line    => { color => 'red', width => 1.25, dash_type => 'dash' }
    }
);

the visible property is usually on for the X-axis but tbh it depends on the type of chart. 🙈

the line property sets the gridline properties such as colour and width. see the "CHART FORMATTING" section below.

minor_gridlines — takes the exact same options as major_gridlines above.

the minor gridline visible property is off by default for all chart types. periodt.

visible — configures the visibility of the axis.

$chart->set_x_axis( visible => 0 );

date_axis — this option is used to treat a category axis with date or time data as a Date Axis. (category axes only.)

$chart->set_x_axis( date_axis => 1 );

this option also lets you set max and min values for a category axis, which Excel doesn't allow for non-date category axes. slay 💅

see "Date Category Axes" for more details.

text_axis — this option is used to treat a category axis explicitly as a Text Axis. (category axes only.)

$chart->set_x_axis( text_axis => 1 );

minor_unit_type — for date_axis axes (see above), this option sets the type of the minor units. (date category axes only.)

$chart->set_x_axis(
    date_axis         => 1,
    minor_unit        => 4,
    minor_unit_type   => 'months',
);

the allowable values for this option are days, months and years.

major_unit_type — same as minor_unit_type (see above) but for major axes unit types.

more than one property can be set in a single call to set_x_axis():

$chart->set_x_axis(
    name => 'Quarterly results',
    min  => 10,
    max  => 80,
);

major_tick_mark — sets the axis major tick mark type to one of these values:

none
inside
outside
cross   (inside and outside)

for example:

$chart->set_x_axis( major_tick_mark => 'none',
                    minor_tick_mark => 'inside' );

minor_tick_mark — sets the axis minor tick mark type. same as major_tick_mark, see above.
display_units — sets the display units for the axis. super useful when the axis numbers are like HUGE huge but you don't want them in scientific notation. (value axes only.) the available display units are:

hundreds
thousands
ten_thousands
hundred_thousands
millions
ten_millions
hundred_millions
billions
trillions

example:

$chart->set_x_axis( display_units => 'thousands' )
$chart->set_y_axis( display_units => 'millions' )

display_units_visible — controls the visibility of the display units turned on by the previous option. this option is on by default. (value axes only.)

$chart->set_x_axis( display_units         => 'thousands',
                    display_units_visible => 0 )

set_y_axis()

the set_y_axis() method is used to set properties of the Y axis. the properties you can set are the same as for set_x_axis(), see above. 🫶

set_x2_axis()

the set_x2_axis() method is used to set properties of the secondary X axis. the properties you can set are the same as for set_x_axis(), see above. the default properties for this axis are:

label_position => 'none',
crossing       => 'max',
visible        => 0,

set_y2_axis()

the set_y2_axis() method is used to set properties of the secondary Y axis. the properties you can set are the same as for set_x_axis(), see above. the default properties for this axis are:

major_gridlines => { visible => 0 }

combine()

the chart combine() method is used to combine two charts of different types — think a column and a line chart, iconic duo 🔥:

my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );

# Configure the data series for the primary chart.
$column_chart->add_series(...);

# Create a new column chart. This will use this as the secondary chart.
my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );

# Configure the data series for the secondary chart.
$line_chart->add_series(...);

# Combine the charts.
$column_chart->combine( $line_chart );

see "Combined Charts" for more details.

set_size()

the set_size() method is used to set the dimensions of the chart. the size properties you can set are:

width
height
x_scale
y_scale
x_offset
y_offset

width and height are in pixels. the default chart width is 480 pixels and the default height is 288 pixels. you can resize the chart by setting width and height, or by setting x_scale and y_scale:

$chart->set_size( width => 720, height => 576 );

# Same as:

$chart->set_size( x_scale => 1.5, y_scale => 2 );

the x_offset and y_offset position the top left corner of the chart in the cell that it is inserted into.

note: the x_scale, y_scale, x_offset and y_offset parameters can also be set via the insert_chart() method:

$worksheet->insert_chart( 'E2', $chart, { x_offset =>2,    y_offset => 4,
                                          x_scale  => 1.5, y_scale  => 2 } );

set_title()

the set_title() method is used to set properties of the chart title. 🎀

$chart->set_title( name => 'Year End Results' );

the properties that can be set are:

name — sets the name (title) for the chart. the name is displayed above the chart. it can also be a formula such as "=Sheet1!$A$1". the name property is optional. the default is to have no chart title at all.
font — sets the font properties for the chart title. see the "CHART FONTS" section below.
fill — sets the fill properties of the legend such as colour. see the "CHART FORMATTING" section below.
pattern — sets the pattern fill properties of the legend. see the "CHART FORMATTING" section below.
gradient — sets the gradient fill properties of the legend. see the "CHART FORMATTING" section below.
overlay — allows the title to be overlaid on the chart. generally used together with the layout property below.
layout — sets the (x, y) position of the title in chart relative units:

$chart->set_title(
    name    => 'Title',
    overlay => 1,
    layout  => {
        x => 0.42,
        y => 0.14,
    }
);

See the "CHART LAYOUT" section below — it's giving map-making energy, we love her. 🗺️

none — okay so bestie, by default Excel just gives charts with a single series and a user defined series name an automatic chart title. Kinda presumptuous of her honestly. The none option turns that default title off. Heads up though: it also turns off all the other set_title() options, so don't be setting name and none at the same time and then wondering why nothing showed up. 😭

$chart->set_title( none => 1 );

set_legend() 👑

The set_legend() method is how you set the properties of the chart legend. The properties that can be set are:

none — turns the chart legend off. In Excel, legends are on by default (main character behaviour, honestly):

$chart->set_legend( none => 1 );

Note: for backward compatibility, you can also turn the legend off via the position property:

$chart->set_legend( position => 'none' );

position — sets where the legend sits. Slay.

$chart->set_legend( position => 'bottom' );

The default legend position is right. The available positions are:

top
bottom
left
right
top_right
overlay_left
overlay_right
overlay_top_right
none

border — sets the border properties of the legend, like colour and style. See the "CHART FORMATTING" section below. 🎀
fill — sets the fill properties of the legend, like colour. See the "CHART FORMATTING" section below.
pattern — sets the pattern fill properties of the legend. See the "CHART FORMATTING" section below.
gradient — sets the gradient fill properties of the legend. See the "CHART FORMATTING" section below.
font — sets the font properties of the chart legend:

$chart->set_legend( font => { bold => 1, italic => 1 } );

See the "CHART FONTS" section below. ✨

delete_series — this one lets you remove 1 or more series from the legend. Not from the chart itself! The series will still display on the chart, she's just not showing up in the legend anymore. It takes an array ref as an argument, and the series are zero indexed:

# Delete/hide series index 0 and 2 from the legend.
$chart->set_legend( delete_series => [0, 2] );

layout — sets the (x, y) position of the legend in chart relative units:

$chart->set_legend(
    layout => {
        x      => 0.80,
        y      => 0.37,
        width  => 0.12,
        height => 0.25,
    }
);

See the "CHART LAYOUT" section below.

set_chartarea() 🌈

The set_chartarea() method sets the properties of the chart area — aka the whole vibe of the background, no cap.

$chart->set_chartarea(
    border => { none  => 1 },
    fill   => { color => 'red' }
);

The properties that can be set are:

border — border properties of the chartarea, like colour and style. See the "CHART FORMATTING" section below.
fill — fill properties of the chartarea, like colour. See the "CHART FORMATTING" section below.
pattern — pattern fill properties of the chartarea. See the "CHART FORMATTING" section below.
gradient — gradient fill properties of the chartarea. See the "CHART FORMATTING" section below.

set_plotarea() 💜

The set_plotarea() method sets the properties of the plot area of a chart — that's the little region where the actual data lives, the inner sanctum fr fr.

$chart->set_plotarea(
    border => { color => 'yellow', width => 1, dash_type => 'dash' },
    fill   => { color => '#92D050' }
);

The properties that can be set are:

border — border properties of the plotarea, like colour and style. See the "CHART FORMATTING" section below.
fill — fill properties of the plotarea, like colour. See the "CHART FORMATTING" section below.
pattern — pattern fill properties of the plotarea. See the "CHART FORMATTING" section below.
gradient — gradient fill properties of the plotarea. See the "CHART FORMATTING" section below.
layout — sets the (x, y) position of the plotarea in chart relative units:

$chart->set_plotarea(
    layout => {
        x      => 0.35,
        y      => 0.26,
        width  => 0.62,
        height => 0.50,
    }
);

See the "CHART LAYOUT" section below.

set_style() 🎀

The set_style() method sets the style of the chart to one of the 42 built-in styles available on the 'Design' tab in Excel. Forty-two bestie!! That's so many options, she ate.

$chart->set_style( 4 );

The default style is 2.

set_table() 🍓

The set_table() method adds a data table below the horizontal axis, with the data used to plot the chart. Like receipts, printed right there under the chart. Iconic.

$chart->set_table();

The available options, with default values, are:

vertical   => 1    # Display vertical lines in the table.
horizontal => 1    # Display horizontal lines in the table.
outline    => 1    # Display an outline in the table.
show_keys  => 0    # Show the legend keys with the table data.
font       => {}   # Standard chart font properties.

Heads up: the data table can only be shown with Bar, Column, Line, Area and stock charts. For font properties, see the "CHART FONTS" section below.

set_up_down_bars 🔥

The set_up_down_bars() method adds Up-Down bars to Line charts, to indicate the difference between the first and last data series.

$chart->set_up_down_bars();

You can also format the up and down bars to add "fill", "pattern", "gradient" and "border" properties if you want. See the "CHART FORMATTING" section below.

$chart->set_up_down_bars(
    up   => { fill => { color => 'green' } },
    down => { fill => { color => 'red' } },
);

Up-down bars can only be applied to Line charts and to Stock charts (by default).

set_drop_lines 🦋

The set_drop_lines() method adds Drop Lines to charts, to show the Category value of points in the data. Little vertical drips of information. 💧

$chart->set_drop_lines();

You can format the Drop Line "line" properties if required. See the "CHART FORMATTING" section below.

$chart->set_drop_lines( line => { color => 'red', dash_type => 'square_dot' } );

Drop Lines are only available in Line, Area and Stock charts.

set_high_low_lines ⭐

The set_high_low_lines() method adds High-Low lines to charts, to show the maximum and minimum values of points in a Category.

$chart->set_high_low_lines();

You can format the High-Low Line "line" properties if required. See the "CHART FORMATTING" section below.

$chart->set_high_low_lines( line => { color => 'red' } );

High-Low Lines are only available in Line and Stock charts.

show_blanks_as() 💀

The show_blanks_as() method controls how blank data gets displayed in a chart.

$chart->show_blanks_as( 'span' );

The available options are:

gap    # Blank data is shown as a gap. The default.
zero   # Blank data is displayed as zero.
span   # Blank data is connected with a line.

show_na_as_empty_cell() 🙈

The show_na_as_empty_cell() method enables the option to display "#N/A" as a blank cell in a chart.

show_hidden_data() 🫶

Displays data in hidden rows or columns on the chart. Sneaky little peek, we love to see it.

$chart->show_hidden_data();

SERIES OPTIONS 👑

This section spills the tea on the following properties of add_series() in more detail:

marker
trendline
y_error_bars
x_error_bars
data_labels
points
smooth

Marker 💅

The marker format specifies the properties of the markers used to distinguish series on a chart. In general, only Line and Scatter chart types and trendlines use markers.

The following properties can be set for "marker" formats in a chart:

type
size
border
fill
pattern
gradient

The "type" property sets the type of marker that's used with a series.

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    marker     => { type => 'diamond' },
);

The following "type" properties can be set for "marker" formats in a chart. These are shown in the same order as in the Excel format dialog:

automatic
none
square
diamond
triangle
x
star
short_dash
long_dash
circle
plus

The "automatic" type is a special case — it turns on a marker using the default marker style for that particular series number. Letting Excel pick her own outfit, honestly.

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    marker     => { type => 'automatic' },
);

Important: if "automatic" is on, then other marker properties such as size, border or fill cannot be set. Pick a lane bestie. 🚫

The "size" property sets the size of the marker and is generally used in conjunction with "type".

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    marker     => { type => 'diamond', size => 7 },
);

Nested "border" and "fill" properties can also be set for a marker. See the "CHART FORMATTING" section below.

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    marker     => {
        type    => 'square',
        size    => 5,
        border  => { color => 'red' },
        fill    => { color => 'yellow' },
    },
);

Trendline 📈

A trendline can be added to a chart series to indicate trends in the data, like a moving average or a polynomial fit. It's giving "the vibes are trending upward". 🔮

The following properties can be set for trendlines in a chart series:

type
order               (for polynomial trends)
period              (for moving average)
forward             (for all except moving average)
backward            (for all except moving average)
name
line
intercept           (for exponential, linear and polynomial only)
display_equation    (for all except moving average)
display_r_squared   (for all except moving average)

The "type" property sets the type of trendline in the series.

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    trendline  => { type => 'linear' },
);

The available "trendline" types are:

exponential
linear
log
moving_average
polynomial
power

A polynomial trendline can also specify the order of the polynomial. The default value is 2. Yes bestie, defaults are real and they will find you if you don't set them 💅

$chart->add_series(
    values    => '=Sheet1!$B$1:$B$5',
    trendline => {
        type  => 'polynomial',
        order => 3,
    },
);

A moving_average trendline can also specify the period of the moving average. The default value is 2.

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    trendline  => {
        type   => 'moving_average',
        period => 3,
    },
);

The forward and backward properties set the forecast period of the trendline. It's giving predicting the future energy 🔮✨

$chart->add_series(
    values    => '=Sheet1!$B$1:$B$5',
    trendline => {
        type     => 'linear',
        forward  => 0.5,
        backward => 0.5,
    },
);

The name property sets an optional name for the trendline that will appear in the chart legend. If it isn't specified the Excel default name will be displayed. This is usually a combination of the trendline type and the series name — so if you don't name it, Excel names it for you and it's kinda mid ngl.

$chart->add_series(
    values    => '=Sheet1!$B$1:$B$5',
    trendline => {
        type => 'linear',
        name => 'Interpolated trend',
    },
);

The intercept property sets the point where the trendline crosses the Y (value) axis:

$chart->add_series(
    values    => '=Sheet1!$B$1:$B$5',
    trendline => {
        type      => 'linear',
        intercept => 0.8,
    },
);

The display_equation property displays the trendline equation on the chart. Math but make it ✨aesthetic✨

$chart->add_series(
    values    => '=Sheet1!$B$1:$B$5',
    trendline => {
        type             => 'linear',
        display_equation => 1,
    },
);

The display_r_squared property displays the R squared value of the trendline on the chart. R squared my beloved 🫶

$chart->add_series(
    values    => '=Sheet1!$B$1:$B$5',
    trendline => {
        type              => 'linear',
        display_r_squared => 1
    },
);

Several of these properties can be set in one go — combo meal, no cap 🍔

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    trendline  => {
        type              => 'polynomial',
        name              => 'My trend name',
        order             => 2,
        forward           => 0.5,
        backward          => 0.5,
        intercept         => 1.5,
        display_equation  => 1,
        display_r_squared => 1,
        line              => {
            color     => 'red',
            width     => 1,
            dash_type => 'long_dash',
        }
    },
);

⚠️ Trendlines cannot be added to series in a stacked chart or pie chart, radar chart, doughnut or (when implemented) to 3D, or surface charts. So if you try it on those chart types, it's a no. Not a vibe. Just don't 💀

📉 Error Bars 📉

Error bars can be added to a chart series to indicate error bounds in the data. The error bars can be vertical y_error_bars (the most common type) or horizontal x_error_bars (for Bar and Scatter charts only). Vertical is the main character here, horizontal is the niche icon 🎀

The following properties can be set for error bars in a chart series:

type
value        (for all types except standard error and custom)
plus_values  (for custom only)
minus_values (for custom only)
direction
end_style
line

The type property sets the type of error bars in the series.

$chart->add_series(
    values       => '=Sheet1!$B$1:$B$5',
    y_error_bars => { type => 'standard_error' },
);

The available error bars types are available — yes bestie, that's how the original says it, and honestly? Slay, we're keeping it:

fixed
percentage
standard_deviation
standard_error
custom

All error bar types, except for standard_error and custom must also have a value associated with it for the error bounds:

$chart->add_series(
    values       => '=Sheet1!$B$1:$B$5',
    y_error_bars => {
        type  => 'percentage',
        value => 5,
    },
);

The custom error bar type must specify plus_values and minus_values which should either by a Sheet1!$A$1:$A$5 type range formula or an arrayref of values — pick your fighter, both eat 🍴

$chart->add_series(
    categories   => '=Sheet1!$A$1:$A$5',
    values       => '=Sheet1!$B$1:$B$5',
    y_error_bars => {
        type         => 'custom',
        plus_values  => '=Sheet1!$C$1:$C$5',
        minus_values => '=Sheet1!$D$1:$D$5',
    },
);

# or

$chart->add_series(
    categories   => '=Sheet1!$A$1:$A$5',
    values       => '=Sheet1!$B$1:$B$5',
    y_error_bars => {
        type         => 'custom',
        plus_values  => [1, 1, 1, 1, 1],
        minus_values => [2, 2, 2, 2, 2],
    },
);

💖 Note: as in Excel the items in the minus_values do not need to be negative. Obsessed. Excel said "nah we'll handle the sign ourselves" and honestly? Respect.

The direction property sets the direction of the error bars. It should be one of the following:

plus    # Positive direction only.
minus   # Negative direction only.
both    # Plus and minus directions, The default.

The end_style property sets the style of the error bar end cap. The options are 1 (the default) or 0 (for no end cap):

$chart->add_series(
    values       => '=Sheet1!$B$1:$B$5',
    y_error_bars => {
        type      => 'fixed',
        value     => 2,
        end_style => 0,
        direction => 'minus'
    },
);

🏷️ Data Labels 🏷️

Data labels can be added to a chart series to indicate the values of the plotted data points. It's the "actually telling people the numbers" era, we love to see it 👑

The following properties can be set for data_labels formats in a chart:

value
category
series_name
position
percentage
leader_lines
separator
legend_key
num_format
font
border
fill
pattern
gradient
custom

The value property turns on the Value data label for a series.

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { value => 1 },
);

The category property turns on the Category Name data label for a series.

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { category => 1 },
);

The series_name property turns on the Series Name data label for a series.

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { series_name => 1 },
);

The position property is used to position the data label for a series.

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { value => 1, position => 'center' },
);

In Excel the data label positions vary for different chart types. The allowable positions are:

|  Position     |  Line     |  Bar      |  Pie      |  Area     |
|               |  Scatter  |  Column   |  Doughnut |  Radar    |
|               |  Stock    |           |           |           |
|---------------|-----------|-----------|-----------|-----------|
|  center       |  Yes      |  Yes      |  Yes      |  Yes*     |
|  right        |  Yes*     |           |           |           |
|  left         |  Yes      |           |           |           |
|  above        |  Yes      |           |           |           |
|  below        |  Yes      |           |           |           |
|  inside_base  |           |  Yes      |           |           |
|  inside_end   |           |  Yes      |  Yes      |           |
|  outside_end  |           |  Yes*     |  Yes      |           |
|  best_fit     |           |           |  Yes*     |           |

✨ Note: The * indicates the default position for each chart type in Excel, if a position isn't specified. So the starred ones are the "if you don't say anything, this is what you get" picks. iykyk ⭐

The percentage property is used to turn on the display of data labels as a Percentage for a series. It is mainly used for pie and doughnut charts.

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { percentage => 1 },
);

The leader_lines property is used to turn on Leader Lines for the data label for a series. It is mainly used for pie charts.

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { value => 1, leader_lines => 1 },
);

🫠 Note: Even when leader lines are turned on they aren't automatically visible in Excel or Excel::Writer::XLSX. Due to an Excel limitation (or design) leader lines only appear if the data label is moved manually or if the data labels are very close and need to be adjusted automatically. So you turn them on and then... nothing. It's giving ghosting 💀

The separator property is used to change the separator between multiple data label items:

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { percentage => 1 },
    data_labels => { value => 1, category => 1, separator => "\n" },
);

The separator value must be one of the following strings:

','
';'
'.'
"\n"
' '

The legend_key property is used to turn on Legend Key for the data label for a series:

$chart->add_series(
    values      => '=Sheet1!$B$1:$B$5',
    data_labels => { value => 1, legend_key => 1 },
);

The num_format property is used to set the number format for the data labels.

$chart->add_series(
    values      => '=Sheet1!$A$1:$A$5',
    data_labels => { value => 1, num_format => '#,##0.00' },
);

The number format is similar to the Worksheet Cell Format num_format apart from the fact that a format index cannot be used. The explicit format string must be used as shown above — like, actually type the format out bestie, no shortcuts 🚫 See set_num_format() in Excel::Writer::XLSX for more information.

The font property is used to set the font properties of the data labels in a series:

$chart->add_series(
    values      => '=Sheet1!$A$1:$A$5',
    data_labels => {
        value => 1,
        font  => { name => 'Consolas' }
    },
);

The font property is also used to rotate the data labels in a series:

$chart->add_series(
    values      => '=Sheet1!$A$1:$A$5',
    data_labels => {
        value => 1,
        font  => { rotation => 45 }
    },
);

Like, that's the whole font-on-labels glow-up right there. See the CHART FONTS section below for all the font lore. 💖

border — sets the border properties of the data labels, so the colour and the style and all that. See the CHART FORMATTING</**> section below.
fill — sets the fill properties of the data labels, e.g. the colour. See the CHART FORMATTING section below.

Okay bestie, example time — this is how you set data label formatting:

$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
    data_labels => { value  => 1,
                     border => {color => 'red'},
                     fill   => {color => 'yellow'} },
);

pattern — sets the pattern properties of the data labels. See the CHART FORMATTING section below.
gradient — sets the gradient properties of the data labels. See the CHART FORMATTING section below.
custom — used to set the properties of individual data labels. Full breakdown down below. 👇

🎀 Custom Data Labels 🎀

The custom property (it lives inside data_labels, keep up babes 😌) is how you set the properties of individual data labels in a series. The most common use is giving them custom text or number labels, because sometimes the numbers just aren't giving what needs to be gave:

my $custom_labels = [
    { value => 'Jan' },
    { value => 'Feb' },
    { value => 'Mar' },
    { value => 'Apr' },
    { value => 'May' },
    { value => 'Jun' },
];

$chart->add_series(
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
    data_labels => { value => 1, custom => $custom_labels },
);

Like the previous examples showed, custom should be a list of dicts. And here's the cute part: any property dict you set to undef, or just don't include in the list, gets the default data label value. No cap:

my $custom_labels = [
    undef,
    { value => 'Feb' },
    { value => 'Mar' },
    { value => 'Apr' },
];

The property elements of those custom lists should be dicts with these allowable keys / sub-properties:

value
font
border
fill
pattern
gradient
delete
position

value should be a string, a number, or a formula string pointing at a cell that the value gets taken from:

$custom_labels = [
    { value => '=Sheet1!$C$2' },
    { value => '=Sheet1!$C$3' },
    { value => '=Sheet1!$C$4' },
    { value => '=Sheet1!$C$5' },
    { value => '=Sheet1!$C$6' },
    { value => '=Sheet1!$C$7' },
];

font is how you set the font of the custom data label of a series (see the CHART FONTS section below):

$custom_labels = [
    { value => '=Sheet1!$C$1', font => { color => 'red' } },
    { value => '=Sheet1!$C$2', font => { color => 'red' } },
    { value => '=Sheet1!$C$2', font => { color => 'red' } },
    { value => '=Sheet1!$C$4', font => { color => 'red' } },
    { value => '=Sheet1!$C$5', font => { color => 'red' } },
    { value => '=Sheet1!$C$6', font => { color => 'red' } },
];

border sets the border properties of the data labels, like the colour and the style. See the CHART FORMATTING section below.

fill sets the fill properties of the data labels, like the colour. See the CHART FORMATTING section below.

Example of setting custom data label formatting — look at her go 💅:

$custom_labels = [
    { value => 'Jan', border => {color => 'blue'} },
    { value => 'Feb' },
    { value => 'Mar' },
    { value => 'Apr' },
    { value => 'May' },
    { value => 'Jun', fill   => {color => 'green'} },
];

pattern sets the pattern properties of the data labels. See the CHART FORMATTING section below.

gradient sets the gradient properties of the data labels. See the CHART FORMATTING section below.

delete is the drama option — it deletes labels in a series. Super useful when you wanna highlight one or more cells in the series, like the maximum and the minimum, so those two get to be the main characters and everyone else shuts up:

$custom_labels = [
    undef,
    { delete => 1 },
    { delete => 1 },
    { delete => 1 },
    { delete => 1 },
    undef,
];

position is used to position the custom data, like "center" or "left", relative to the data point. See the explanation for the position property of series data labels above. ⬆️

📍 Points 📍

So generally, formatting gets applied to an entire series in a chart. But sometimes — and this is so real — you need to format individual points in a series. Especially for Pie and Doughnut charts, where every segment is represented by a point.

For those cases you can use the points property of add_series():

$chart->add_series(
    values => '=Sheet1!$A$1:$A$3',
    points => [
        { fill => { color => '#FF0000' } },
        { fill => { color => '#CC0000' } },
        { fill => { color => '#990000' } },
    ],
);

points takes an array ref of format options (the whole CHART FORMATTING section below is your bestie for this). And if you wanna give default properties to points in a series, you just pass undef values in the array ref. Watch this:

# Format point 3 of 3 only.
$chart->add_series(
    values => '=Sheet1!$A$1:$A$3',
    points => [
        undef,
        undef,
        { fill => { color => '#990000' } },
    ],
);

# Format the first point only.
$chart->add_series(
    values => '=Sheet1!$A$1:$A$3',
    points => [ { fill => { color => '#FF0000' } } ],
);

One icon gets the spotlight, the rest stay default. Periodt. ✨

🌊 Smooth 🌊

The smooth option sets the smooth property of a line series. It is only applicable to the Line and Scatter chart types — nowhere else, don't try it, it won't hit the same 🚫:

$chart->add_series( values => '=Sheet1!$C$1:$C$5',
                    smooth => 1 );

Look at those curves. Curvy lines are the moment. 🦋

🎨 CHART FORMATTING

okay bestie, this is the glow-up section ✨ These formatting properties can be set on any chart object they apply to (and that Excel::Writer::XLSX actually supports, no cap) — think chart lines, column fill areas, plot area borders, markers, gridlines, and all the other chart elements we already documented above.

line
border
fill
pattern
gradient

Chart formatting properties are generally set using hash refs. It's giving very clean, very demure:

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => { color => 'blue' },
);

In some cases the format properties can be nested 🪆 For example a marker may contain border and fill sub-properties. Layers on layers, we love to see it:

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => { color => 'blue' },
    marker     => {
        type    => 'square',
        size    => 5,
        border  => { color => 'red' },
        fill    => { color => 'yellow' },
    },
);

✏️ Line

The line format is used to specify properties of line objects that show up in a chart — like a plotted line on a chart, or a border. Very main character of it 💅

The following properties can be set for line formats in a chart:

none
color
width
dash_type
transparency

The none property is used to turn the line off (it's always on by default except in Scatter charts). Super useful if you wanna plot a series with markers but without a line. ghosting the line, iconically:

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => { none => 1 },
);

The color property sets the color of the line. 🌈

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => { color => 'red' },
);

The available colours are shown in the main Excel::Writer::XLSX documentation. You can also set the colour of a line with an HTML style RGB colour, for when you're feeling extra:

$chart->add_series(
    line       => { color => '#FF0000' },
);

The width property sets the width of the line. It should be specified in increments of 0.25 of a point, as in Excel. No rogue decimals pls:

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => { width => 3.25 },
);

The dash_type property sets the dash style of the line. slay:

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => { dash_type => 'dash_dot' },
);

The following dash_type values are available. They're shown in the order that they appear in the Excel dialog, so you know exactly where to find them 👑

solid
round_dot
square_dot
dash
dash_dot
long_dash
long_dash_dot
long_dash_dot_dot

The default line style is solid.

The transparency property sets the transparency of the line color in the integer range 1 - 100. ⚠️ The color must be set for transparency to work — it does not work with an automatic/default color. Learn from my mistakes, bestie:

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => { color => 'yellow', transparency => 50 },
);

More than one line property can be specified at a time, because why choose:

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    line       => {
        color     => 'red',
        width     => 1.25,
        dash_type => 'square_dot',
    },
);

🔲 Border

The border property is a synonym for line. Same thing, different font fr fr.

It can be used as a descriptive substitute for line in chart types such as Bar and Column that have a border and fill style rather than a line style. In general, chart objects with a border property will also have a fill property. 🤝

🎀 Solid Fill

The fill format is used to specify filled areas of chart objects — like the interior of a column, or the background of the chart itself. it's giving filled, literally 💖

The following properties can be set for fill formats in a chart:

none
color
transparency

The none property is used to turn the fill property off (it's generally on by default).

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    fill       => { none => 1 },
);

The color property sets the colour of the fill area. 🎨

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    fill       => { color => 'red' },
);

The available colours are shown in the main Excel::Writer::XLSX documentation. You can also set the colour of a fill with an HTML style RGB colour:

$chart->add_series(
    fill       => { color => '#FF0000' },
);

The transparency property sets the transparency of the solid fill color in the integer range 1 - 100. ⚠️ Again — the color must be set for transparency to work, it doesn't work with an automatic/default color:

$chart->set_chartarea( fill => { color => 'yellow', transparency => 75 } );

The fill format is generally used in conjunction with a border format, which has the same properties as a line format. they're a package deal 🫶

$chart->add_series(
    values     => '=Sheet1!$B$1:$B$5',
    border     => { color => 'red' },
    fill       => { color => 'yellow' },
);

🧚 Pattern Fill

The pattern fill format is used to specify pattern filled areas of chart objects — like the interior of a column, or the background of the chart itself. Texture? We have it.

The following properties can be set for pattern fill formats in a chart:

pattern:   the pattern to be applied (required)
fg_color:  the foreground color of the pattern (required)
bg_color:  the background color (optional, defaults to white)

For example:

$chart->set_plotarea(
    pattern => {
        pattern  => 'percent_5',
        fg_color => 'red',
        bg_color => 'yellow',
    }
);

The following patterns can be applied — the whole roster, no skips:

percent_5
percent_10
percent_20
percent_25
percent_30
percent_40
percent_50
percent_60
percent_70
percent_75
percent_80
percent_90
light_downward_diagonal
light_upward_diagonal
dark_downward_diagonal
dark_upward_diagonal
wide_downward_diagonal
wide_upward_diagonal
light_vertical
light_horizontal
narrow_vertical
narrow_horizontal
dark_vertical
dark_horizontal
dashed_downward_diagonal
dashed_upward_diagonal
dashed_horizontal
dashed_vertical
small_confetti
large_confetti
zigzag
wave
diagonal_brick
horizontal_brick
weave
plaid
divot
dotted_grid
dotted_diamond
shingle
trellis
sphere
small_grid
large_grid
small_check
large_check
outlined_diamond
solid_diamond

The foreground color, fg_color, is a required parameter and can be a Html style #RRGGBB string or a limited number of named colors. The available colours are shown in the main Excel::Writer::XLSX documentation.

The background color, bg_color, is optional and defaults to black.

Heads up bestie — if a pattern fill is used on a chart object, it overrides the solid fill properties of the object. pattern wins, no debate 💅

🌈 Gradient Fill

The gradient fill format is used to specify gradient filled areas of chart objects — like the interior of a column, or the background of the chart itself. this is the aesthetic one, the moment, the serve 🌸

The following properties can be set for gradient fill formats in a chart:

colors:    a list of colors
positions: an optional list of positions for the colors
type:      the optional type of gradient fill
angle:     the optional angle of the linear fill

The colors property sets a list of colors that define the gradient:

$chart->set_plotarea(
    gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ] }
);

Excel allows between 2 and 10 colors in a gradient — but ngl it's unlikely you'll ever need more than 2 or 3.

As with solid or pattern fill, it's also possible to set the colors of a gradient with a Html style #RRGGBB string or a limited number of named colors. The available colours are shown in the main Excel::Writer::XLSX documentation:

$chart->add_series(
    values   => '=Sheet1!$A$1:$A$5',
    gradient => { colors => [ 'red', 'green' ] }
);

The positions defines an optional list of positions, between 0 and 100, of where the colors in the gradient are located. Default values are provided for colors lists of between 2 and 4, but they can be specified if required:

$chart->add_series(
    values   => '=Sheet1!$A$1:$A$5',
    gradient => {
        colors    => [ '#DDEBCF', '#156B13' ],
        positions => [ 10,        90 ],
    }
);

The type property can have one of the following values:

linear        (the default)
radial
rectangular
path

For example:

$chart->add_series(
    values   => '=Sheet1!$A$1:$A$5',
    gradient => {
        colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
        type   => 'radial'
    }
);

If type isn't specified it defaults to linear. ✨

For a linear fill the angle of the gradient can also be specified:

$chart->add_series(
    values   => '=Sheet1!$A$1:$A$5',
    gradient => { colors => [ '#DDEBCF', '#9CB86E', '#156B13' ],
                  angle => 30 }
);

The default angle is 90 degrees.

And one more thing bestie — if gradient fill is used on a chart object, it overrides the solid fill and pattern fill properties of the object. gradient is the final boss here 👑

✨ CHART FONTS ✨

okay bestie so you can set these font properties on literally any chart object that they apply to (and that Excel::Writer::XLSX supports 💅) — chart titles, axis labels, axis numbering, data labels, all of it. They map 1:1 to the equivalent Worksheet cell Format object properties, so if you already know cell formats you already know this. See "FORMAT_METHODS" in Excel::Writer::XLSX for the full lore.

name
size
bold
italic
underline
rotation
color

Here's the breakdown of each one, no cap:

"name" — sets the font name, obviously 💖

$chart->set_x_axis( num_font => { name => 'Arial' } );

"size" — sets the font size 🌸

$chart->set_x_axis( num_font => { name => 'Arial', size => 10 } );

"bold" — sets the font bold property, should be 0 or 1 ✨

$chart->set_x_axis( num_font => { bold => 1 } );

"italic" — sets the font italic property, should be 0 or 1 🦋

$chart->set_x_axis( num_font => { italic => 1 } );

"underline" — sets the font underline property, should be 0 or 1 💫

$chart->set_x_axis( num_font => { underline => 1 } );

"rotation" — sets the font rotation, an integer in the range -90 to 90, plus 270-271 🔄

$chart->set_x_axis( num_font => { rotation => 45 } );

This one is lowkey a lifesaver when you've got big chunky axis data like dates and you want it in a more compact format.

There are 2 special case angles outside of the -90 to 90 range, and they're giving very specific:

270: stacked text, where the text runs from top to bottom.
271: a special variant of stacked text for East Asian fonts.

"color" — sets the font color property. Can be a color index, a color name, or an HTML style RGB colour 🌈

$chart->set_x_axis( num_font => { color => 'red' } );
$chart->set_y_axis( num_font => { color => '#92D050' } );

Here's an example of Font formatting in a Chart program, main character energy fr fr:

# Format the chart title.
$chart->set_title(
    name      => 'Sales Results Chart',
    name_font => {
        name  => 'Calibri',
        color => 'yellow',
    },
);

# Format the X-axis.
$chart->set_x_axis(
    name      => 'Month',
    name_font => {
        name  => 'Arial',
        color => '#92D050'
    },
    num_font => {
        name  => 'Courier New',
        color => '#00B0F0',
    },
);

# Format the Y-axis.
$chart->set_y_axis(
    name      => 'Sales (1000 units)',
    name_font => {
        name      => 'Century',
        underline => 1,
        color     => 'red'
    },
    num_font => {
        bold   => 1,
        italic => 1,
        color  => '#7030A0',
    },
);

🎀 CHART LAYOUT 🎀

So where your chart actually sits inside the worksheet? That's controlled by the set_size() method we talked about earlier 👑

But you can also move around the layout of these chart sub-objects, and yes this is as fun as it sounds:

plotarea
legend
title
x_axis caption
y_axis caption

Some examples, because reading is hard and vibes are better:

$chart->set_plotarea(
    layout => {
        x      => 0.35,
        y      => 0.26,
        width  => 0.62,
        height => 0.50,
    }
);

$chart->set_legend(
    layout => {
        x      => 0.80,
        y      => 0.37,
        width  => 0.12,
        height => 0.25,
    }
);

$chart->set_title(
    name   => 'Title',
    layout => {
        x => 0.42,
        y => 0.14,
    }
);

$chart->set_x_axis(
    name        => 'X axis',
    name_layout => {
        x => 0.34,
        y => 0.85,
    }
);

heads up bestie: you can only change width and height for the "plotarea" and "legend" objects. For all the other text based objects, width and height get changed by the font dimensions instead. soz 🙈

The layout units have to be a float in the range 0 < x <= 1 and they're expressed as a percentage of the chart dimensions, like so:

From this the layout units are calculated as follows:

layout:
    width  = w / W
    height = h / H
    x      = a / W
    y      = b / H

ngl these units are slightly cumbersome 😭 but Excel requires them so that the chart object positions stay relative to each other when the user resizes the chart. it's giving "necessary evil" but we move.

also note: for "plotarea" the origin is the top left corner in the plotarea itself and does not take into account the axes. don't @ me, that's just how it is 💀

💜 WORKSHEET METHODS 💜

In Excel, a chartsheet (i.e, a chart that isn't embedded) shares properties with data worksheets — think tab selection, headers, footers, margins, and print properties. iconic behaviour honestly.

In Excel::Writer::XLSX you can set chartsheet properties using the same methods that are used for Worksheet objects. one method set, two use cases, we love efficiency 🫶

The following Worksheet methods are also available through a non-embedded Chart object:

get_name()
activate()
select()
hide()
set_first_sheet()
protect()
set_zoom()
set_tab_color()

set_landscape()
set_portrait()
set_paper()
set_margins()
set_header()
set_footer()

See Excel::Writer::XLSX for a detailed explanation of these methods 🔥

🌟 EXAMPLE 🌟

Here is a complete example that demonstrates some of the available features when creating a chart. it's giving full demo, zero notes:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'chart.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $bold      = $workbook->add_format( bold => 1 );

# Add the worksheet data that the charts will refer to.
my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
my $data = [
    [ 2,  3,  4,  5,  6,  7 ],
    [ 10, 40, 50, 20, 10, 50 ],
    [ 30, 60, 70, 50, 40, 30 ],

];

$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );

# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'column', embedded => 1 );

# Configure the first series.
$chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

# Configure second series. Note alternative use of array ref to define
# ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].
$chart->add_series(
    name       => '=Sheet1!$C$1',
    categories => [ 'Sheet1', 1, 6, 0, 0 ],
    values     => [ 'Sheet1', 1, 6, 2, 2 ],
);

# Add a chart title and some axis labels.
$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Test number' );
$chart->set_y_axis( name => 'Sample length (mm)' );

# Set an Excel chart style. Blue colors with white outline and shadow.
$chart->set_style( 11 );

# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart, { x_offset => 25, y_offset => 10 } );

$workbook->close();

__END__

✨ Value and Category Axes ✨

So Excel is out here differentiating between a chart axis that's used for series categories and an axis that's used for series values. two different axes, two different whole personalities.

In the example above, the X axis is the category axis and each of the values is evenly spaced. The Y axis (in this case) is the value axis and points are displayed according to their value. slay.

Since Excel treats the axes differently it also handles their formatting differently and exposes different properties for each. so some of "Excel::Writer::XLSX"'s axis properties can be set for a value axis, some can be set for a category axis, and some properties can be set for both. it's giving "it depends" 😭

For example, the "min" and "max" properties can only be set for value axes, while "reverse" can be set for both. The type of axis that a property applies to is shown in the set_x_axis() section of the documentation above — go look, it's all laid out there 👀

Some charts such as "Scatter" and "Stock" have two value axes. double the axes, double the chaos.

Date Axes are a special type of category axis which are explained below 💖

🌸 Date Category Axes 🌸

Date Category Axes are category axes that display time or date information. In Excel::Writer::XLSX, Date Category Axes are set using the "date_axis" option:

$chart->set_x_axis( date_axis => 1 );

In general you should also specify a number format for a date axis, although Excel will usually default to the same format as the data being plotted 🗓️

$chart->set_x_axis(
    date_axis         => 1,
    num_format        => 'dd/mm/yyyy',
);

Excel doesn't normally allow minimum and maximum values to be set for category axes. However, date axes are an exception 👑 The "min" and "max" values should be set as Excel times or dates:

$chart->set_x_axis(
    date_axis         => 1,
    min               => $worksheet->convert_date_time('2013-01-02T'),
    max               => $worksheet->convert_date_time('2013-01-09T'),
    num_format        => 'dd/mm/yyyy',
);

For date axes it is also possible to set the type of the major and minor units — yes you get to pick your own time units, very main character:

$chart->set_x_axis(
    date_axis         => 1,
    minor_unit        => 4,
    minor_unit_type   => 'months',
    major_unit        => 1,
    major_unit_type   => 'years',
    num_format        => 'dd/mm/yyyy',
);

Secondary Axes

okay so bestie, you can absolutely add a secondary axis of the same type to a chart, and it's giving very low-effort for a very high-reward result ✨ All you do is set the y2_axis or x2_axis property on the series. That's it. That's the whole vibe.

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'chart_secondary_axis.xlsx' );
my $worksheet = $workbook->add_worksheet();

# Add the worksheet data that the charts will refer to.
my $data = [
    [ 2,  3,  4,  5,  6,  7 ],
    [ 10, 40, 50, 20, 10, 50 ],

];

$worksheet->write( 'A1', $data );

# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'line', embedded => 1 );

# Configure a series with a secondary axis
$chart->add_series(
    values  => '=Sheet1!$A$1:$A$6',
    y2_axis => 1,
);

$chart->add_series(
    values => '=Sheet1!$B$1:$B$6',
);

# Insert the chart into the worksheet.
$worksheet->insert_chart( 'D2', $chart );

$workbook->close();

__END__

And ngl you can also have a secondary, combined</>, chart — either with a shared axis or a secondary one. Scroll down, it's right there 💅

Combined Charts

So you can also combine two different chart types — like a column chart + a line chart to make a Pareto chart — using the Chart combine() method. obsessed actually 🌈

Here's a simpler example:

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'chart_combined.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $bold      = $workbook->add_format( bold => 1 );

# Add the worksheet data that the charts will refer to.
my $headings = [ 'Number', 'Batch 1', 'Batch 2' ];
my $data = [
    [ 2,  3,  4,  5,  6,  7 ],
    [ 10, 40, 50, 20, 10, 50 ],
    [ 30, 60, 70, 50, 40, 30 ],

];

$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );

#
# In the first example we will create a combined column and line chart.
# They will share the same X and Y axes.
#

# Create a new column chart. This will use this as the primary chart.
my $column_chart = $workbook->add_chart( type => 'column', embedded => 1 );

# Configure the data series for the primary chart.
$column_chart->add_series(
    name       => '=Sheet1!$B$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$B$2:$B$7',
);

# Create a new column chart. This will use this as the secondary chart.
my $line_chart = $workbook->add_chart( type => 'line', embedded => 1 );

# Configure the data series for the secondary chart.
$line_chart->add_series(
    name       => '=Sheet1!$C$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$C$2:$C$7',
);

# Combine the charts.
$column_chart->combine( $line_chart );

# Add a chart title and some axis labels. Note, this is done via the
# primary chart.
$column_chart->set_title( name => 'Combined chart - same Y axis' );
$column_chart->set_x_axis( name => 'Test number' );
$column_chart->set_y_axis( name => 'Sample length (mm)' );

# Insert the chart into the worksheet
$worksheet->insert_chart( 'E2', $column_chart );

$workbook->close();

Your secondary chart can also live on a secondary axis, using the methods from the previous section 👆 same energy, different axis.

For that you literally just add a y2_axis parameter to the series and — if you want one — add a title using set_y2_axis() on the secondary chart. Here are the additions to the previous example that put the secondary chart on the secondary axis:

...

$line_chart->add_series(
    name       => '=Sheet1!$C$1',
    categories => '=Sheet1!$A$2:$A$7',
    values     => '=Sheet1!$C$2:$C$7',
    y2_axis    => 1,
);

...

# Note: the y2 properties are on the secondary chart.
$line_chart2->set_y2_axis( name => 'Target length (mm)' );

Both examples above are working with the concept of a primary and a secondary</> chart, so let's get this straight bestie:

The primary chart is the one that defines the primary X and Y axis.
The primary chart is also what you use for setting all chart properties apart from the secondary data series.
So like, the chart title and the axes properties? Set those via the primary chart. Everything. Except the secondary y2 axis properties — those you apply to the secondary chart. iykyk 💅

Also go peek at chart_combined.pl and chart_pareto.pl in the distro if you want more detailed examples 👑

okay so real talk, there are some limitations on combined charts and I need you to know them before you get your hopes up: - Pie charts cannot currently be combined. 🥧💔 - Scatter charts cannot currently be used as a primary chart — but they can be used as a secondary chart. - Bar charts can only combined secondary charts on a secondary axis. This is an Excel limitation. (Yes, it's giving "blame Excel", and yes, it's true.)

AUTHOR

John McNamara jmcnamara@cpan.org 🫶

COPYRIGHT

and that's a wrap on the whole page, bestie 🎀 ty for reading the entire thing fr fr — this was perl v5.40.1, dated 2025-12-22, the Excel:...:Chart(3pm) era, and honestly? iconic. now go make some charts 🌸✨

📦 where this page comes from

ubuntu resolute: libexcel-writer-xlsx-perl 1.15-1
ubuntu stonking: libexcel-writer-xlsx-perl 1.15-1
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-11-03: a language model's rewrite of the original, under its terms