Skip to content

excel chart column

Jump to bottom
zmworm edited this page Apr 29, 2026 · 17 revisions

Excel: Chart - column

A dedicated page for the column chart type. Column charts display data as vertical bars, ideal for comparing values across categories. Column charts support gap width, overlap, bar shapes, data labels, reference lines, dual axes, and all the shared chart styling properties.

Path: /{SheetName}/chart[N]

Add command shape:

officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop title="Quarterly Sales" \
  --prop series1="Revenue:100,200,300,400" \
  --prop categories="Q1,Q2,Q3,Q4" \
  <styling props>

See also: Chart - add, Chart - set, bar for the horizontal variant.

Chart type variants

Type string Grouping 3D
column Clustered No
columnStacked Stacked No
columnPercentStacked PercentStacked No
column3d Clustered Yes

Column-specific properties

Gap width

Property Default Notes
gapWidth / gap 150 Space between bar groups (0-500, as % of bar width) 
# Tight bars (no gap)
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column --prop gapWidth=0 \
  --prop series1="Sales:10,20,30" --prop categories="A,B,C"

# Wide gap
officecli set data.xlsx /Sheet1/chart[1] --prop gapWidth=300

Overlap

Property Default Notes
overlap 0 (clustered), 100 (stacked) Bar overlap (-100 to 100, as % of bar width)

Positive values overlap bars; negative values add space between bars within the same category group.

officecli set data.xlsx /Sheet1/chart[1] --prop overlap=50

Invert if negative

Property Default Notes
invertIfNeg / invertIfNegative false Reverse bar color/direction for negative values 
officecli set data.xlsx /Sheet1/chart[1] --prop invertIfNeg=true

Bar shape (3D only)

Property Default Notes
shape / barShape box 3D bar shape: box, cone, cylinder, pyramid 
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column3d --prop shape=cylinder \
  --prop series1="Q:10,20,30,40" --prop categories="Q1,Q2,Q3,Q4"

Title styling

Property Default Notes
title — Chart title text
title.font / titleFont — Font family
title.size / titleSize — Font size (pt)
title.color / titleColor — Font color (hex)
title.bold / titleBold — Bold (true/false)
title.glow / titleGlow — Glow: "COLOR-RADIUS-OPACITY" or "none"
title.shadow / titleShadow — Shadow: "COLOR-BLUR-ANGLE-DIST-OPACITY" or "none" 
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop title="Sales Overview" \
  --prop title.size=16 --prop title.bold=true \
  --prop title.font="Arial" --prop title.color=1F2937 \
  --prop series1="Revenue:100,200,300" --prop categories="Q1,Q2,Q3"

Legend

Property Default Notes
legend true Position: top, bottom, left, right, none
legend.overlay false Float legend on top of the chart
legendFont / legend.font — "size:color:fontname" 
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop series1="East:120,180,210" --prop series2="West:90,140,160" \
  --prop categories="Q1,Q2,Q3" \
  --prop legend=bottom --prop "legendfont=9:6B7280:Arial"

Axis features

Scale control

Property Default Notes
axisMin / min (auto) Value axis lower bound
axisMax / max (auto) Value axis upper bound
majorUnit (auto) Major gridline / tick interval
minorUnit (auto) Minor gridline / tick interval
axisNumFmt / axisNumberFormat General Number format for tick labels (e.g. "$#,##0", "0%") 
# Lock Y axis to 0-300, gridlines every 50
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop series1="Data:80,150,210,275" \
  --prop categories="Q1,Q2,Q3,Q4" \
  --prop axisMin=0 --prop axisMax=300 --prop majorUnit=50

Dual axis (secondary axis)

Property Default Notes
secondaryAxis / secondary — Comma-separated 1-based series indices for secondary Y axis 
# Revenue on left axis, margin % on right axis
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop series1="Revenue:100,200,300,400" \
  --prop series2="Margin %:15,18,22,25" \
  --prop categories="Q1,Q2,Q3,Q4" \
  --prop secondaryAxis=2

Display units

Property Default Notes
dispUnits / displayUnits — thousands, millions, billions, etc.

Axis visibility and styling

Property Default Notes
axisVisible / axis.visible true Show/hide both axes
axisLine / axis.line — Value axis line: "color:width:dash"
catAxisLine — Category axis line: "color:width:dash"
majorTickMark / majorTick — out, in, cross, none
minorTickMark / minorTick — Same as major tick
tickLabelPos nextTo nextTo, high, low, none 
# Hide tick marks and move labels
officecli set data.xlsx /Sheet1/chart[1] \
  --prop majorTickMark=none --prop tickLabelPos=low

Gridlines

Property Default Notes
gridlines / majorGridlines true Toggle or configure: true, false/none, or "color:widthPt:dash"
minorGridlines false Same format as gridlines 
# Custom gridlines: light gray, thin, dotted
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop series1="Data:100,200,300" --prop categories="A,B,C" \
  --prop "gridlines=D0D0D0:0.5:sysDot" \
  --prop "minorGridlines=EEEEEE:0.3:sysDot"

Reference lines (target / threshold overlays)

Property Default Notes
referenceLine / refLine — value or "value:color:width:dash"

Reference lines are horizontal overlays drawn at a constant value. They appear in the legend and are rendered in the HTML preview with the correct dash pattern.

# Target line at 200 — red, dashed
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop series1="Actual:120,180,210,250" \
  --prop categories="Q1,Q2,Q3,Q4" \
  --prop "referenceLine=200:FF0000:1.5:dash"

Fills and borders

Property Default Notes
plotFill / plotAreaFill — Plot area background: hex, gradient "C1-C2[:angle]", or "none"
chartFill / chartAreaFill — Chart area background
plotArea.border / plotBorder — Plot area outline: "color:width:dash" or "none"
chartArea.border / chartBorder — Chart area outline 
# Light background with border
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop series1="Data:100,200,300" --prop categories="A,B,C" \
  --prop plotFill=F8F9FA --prop "plotArea.border=E5E7EB:0.5:solid" \
  --prop chartFill=FFFFFF --prop "chartArea.border=D1D5DB:1:solid"

Series shadow and outline

Property Default Notes
series.shadow / seriesShadow — "COLOR-BLUR-ANGLE-DIST-OPACITY" or "none"
series.outline / seriesOutline — "COLOR-WIDTH" e.g. "FFFFFF-0.5" or "none" 
# Add shadow to column bars
officecli set data.xlsx /Sheet1/chart[1] \
  --prop "series.shadow=000000-4-315-3-40"

Data labels

Property Default Notes
dataLabels false Toggle data labels
labelPos / labelPosition — Position: center, insideEnd, insideBase, outsideEnd, top, bottom, left, right, bestFit
labelFont — "size:color:bold"
dataLabel{N}.text — Custom text for individual label
dataLabel{N}.x/y/w/h — Manual layout of individual label
dataLabel{N}.delete — Hide individual label
dataLabels.separator — Separator between label parts
dataLabels.numFmt — Number format for labels 
# Show data labels outside the top of each bar
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop series1="Sales:120,180,210" --prop categories="Q1,Q2,Q3" \
  --prop dataLabels=true --prop labelPos=outsideEnd \
  --prop "labelFont=9:333333:true"

Transparency and opacity

Property Default Notes
transparency 0 Series transparency (0-100%)
opacity / alpha 100 Series opacity (0-100%) 
# Translucent columns for background context
officecli set data.xlsx /Sheet1/chart[1] --prop transparency=40

Conditional coloring

Property Default Notes
colorRule / conditionalColor — Color data points conditionally
point{N}.color — Individual data point color (hex) 
# Highlight Q4 bar in green
officecli set data.xlsx /Sheet1/chart[1] --prop point4.color=22C55E

Layout control

Property Default Notes
x, y 0 Chart position (column/row index)
width 8 Chart width (column units)
height 15 Chart height (row units)
plotArea.x/y/w/h — Manual plot area layout (0-1 decimal)
title.x/y/w/h — Manual title layout
legend.x/y/w/h — Manual legend layout

3D settings

Property Default Notes
view3d / camera / perspective — "rotX,rotY,perspective" e.g. "15,20,30"

Applies only to column3d chart type.

Full example: presentation-grade column chart

officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=column \
  --prop title="Regional Sales Comparison" \
  --prop title.size=16 --prop title.bold=true \
  --prop title.font="Arial" --prop title.color=1F2937 \
  --prop series1.name=East --prop series1.values="Sheet1!B2:B5" \
  --prop series2.name=South --prop series2.values="Sheet1!C2:C5" \
  --prop series3.name=North --prop series3.values="Sheet1!D2:D5" \
  --prop series1.categories="Sheet1!A2:A5" \
  --prop colors="4472C4,ED7D31,70AD47" \
  --prop gapWidth=100 --prop overlap=0 \
  --prop dataLabels=true --prop labelPos=outsideEnd \
  --prop "gridlines=E5E7EB:0.5:solid" \
  --prop "minorGridlines=F3F4F6:0.3:sysDot" \
  --prop axisMin=0 --prop majorUnit=50 \
  --prop "axisfont=10:6B7280:Arial" \
  --prop plotFill=FAFBFC \
  --prop "referenceLine=200:FF0000:1:dash" \
  --prop legend=bottom \
  --prop "legendfont=9:6B7280:Arial" \
  --prop x=0 --prop y=0 --prop width=14 --prop height=20

HTML preview support

Feature Preview Notes
Vertical columns Yes
Stacked / percent stacked Yes
Gap width Yes
Overlap Yes
Data labels Yes Positioned at bar center or outside end
Reference lines Yes Horizontal overlays
3D columns Yes Isometric projection
Custom axis scale Yes axisMin/axisMax/majorUnit
Gridlines Yes Color, width, dash from OOXML
Dual axis Yes Secondary Y axis
Conditional coloring Yes Per-point color
Shadow effects No Decorative; not rendered
Glow effects No Decorative; not rendered

Known quirks

  • Stacked type is set at creation. You cannot change a column to columnStacked via set — recreate the chart with the desired type.
  • 3D column charts use isometric projection in preview. column3d is rendered with a simple isometric projection in the HTML preview. Use Excel for full 3D QA.
  • Gap width and overlap interact. Large overlap values with small gap widths can cause bars to fully merge visually.

Inspect an existing column chart

# List all charts
officecli query data.xlsx chart

# Inspect a specific chart (shows all series, gapWidth, overlap, etc.)
officecli get data.xlsx "/Sheet1/chart[1]"

# Render to HTML preview
officecli view data.xlsx html > preview.html

See Also

  • Chart - Overview of chart operations
  • Chart add - Generic chart add (all types)
  • Chart set - Modify existing charts
  • Chart get - Read back chart properties
  • bar - Horizontal variant of column charts

Based on OfficeCLI v1.0.64

Clone this wiki locally