Skip to content

excel chart histogram

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

Excel: Chart - histogram

A dedicated page for the histogram chart type. Histograms are part of Excel's cx:chart (extended chart) family and have a completely separate vocabulary from regular cChart types like column/bar/line/pie — they bin raw values on the fly, support outlier fencing, and share styling knobs with the other cx types (funnel/treemap/sunburst/boxWhisker).

Path: /{SheetName}/chart[N]

Add command shape:

officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Name:v1,v2,v3,..." \
  <binning + styling props>

See also: Chart - add, Chart - set, and the full runnable demo at examples/excel/charts-histogram.md (6 sheets × 29 charts covering every knob on this page).

What a histogram is

Unlike a column chart where each bar maps to one explicit category, a histogram takes a flat list of numeric samples and bins them automatically into ranges. The series value is therefore always name:raw_value,raw_value,... — you pass Excel the raw observations, not pre-computed bucket counts.

officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop title="Age distribution" \
  --prop series1="Age:18,19,20,21,22,22,23,24,25,27,29,32,35,40,48,55,62"

With no binning knobs at all, Excel auto-chooses the bin count. Everything below is about overriding that default or styling the result.

Binning knobs (histogram-unique)

These knobs exist only on histogram charts. They map to cx:layoutPr/cx:binning in the OOXML.

Property Default Notes
binCount (auto) Force exactly N equal-width bins. Positive integer. If both binCount and binSize are given, binCount wins.
binSize (auto) Force each bin to be exactly W wide. Positive number. Mutually exclusive with binCount.
intervalClosed r Which side of each bin is closed. r = (a,b] (default, right-closed), l = [a,b) (left-closed, useful when data has values exactly on a bin boundary).
underflowBin — Group every value < N into a single <N outlier bar at the left.
overflowBin — Group every value > M into a single >M outlier bar at the right.

Examples:

# Auto-binning (Excel picks the bin count — sqrt(n) rule)
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Samples:45,52,58,61,63,...,97,99"

# Exactly 10 bins
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Samples:..." \
  --prop binCount=10

# Fixed bin width of 50 (bins snap to multiples of 50)
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Sales:120,135,148,...,700" \
  --prop binSize=50

# Outlier fencing: anything <50 or >500 collapses into a single bar
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Latency:12,28,40,60,...,1200" \
  --prop underflowBin=50 \
  --prop overflowBin=500

# Left-closed intervals [a,b) — relevant when samples lie on bin edges
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="RT:10,15,20,...,100" \
  --prop binSize=10 \
  --prop intervalClosed=l

Layout knobs

Property Default Notes
x / y 0 Top-left cell position (column / row index)
width 8 Chart width in column units
height 15 Chart height in row units
gapWidth 0 Space between bars (0..500). Histograms default to 0 so bars touch, matching Excel's native output. Set gapWidth=30 or higher to make the chart look more like a regular column chart.

Series knobs

Histograms have exactly one series — the list of raw samples. series1 is the canonical form; data is also accepted but rarely used here since histograms don't have pre-binned categories.

Property Default Notes
series1 (required) "name:v1,v2,v3,..." — raw observations, not pre-binned bucket counts
fill (theme) Single-color shortcut for the one series: hex "4472C4" or with #. Internally stored without #.
colors (theme) Alias for fill on histograms (only first color is used since there's just one series).
series.shadow / seriesshadow — Outer drop shadow on every bar: "COLOR-BLUR-ANGLE-DIST-OPACITY" (e.g. "000000-6-45-3-35") or "none" to remove

Axis titles and tick labels

The cx category axis is the binned one (bottom), the cx value axis is the count (left). cx accepts the same xAxisTitle/yAxisTitle spelling as regular cChart's catTitle/axisTitle aliases.

Property Default Notes
xAxisTitle — Category (bottom) axis title
yAxisTitle — Value (left) axis title
axisTitle.color — Color for both axis titles (cx can't split per-axis)
axisTitle.size — Font size for both axis titles, in pt
axisTitle.bold — Bold both axis titles (true/false)
axisTitle.font — Font family for both axis titles
axisfont / axis.font — Compound tick-label styling for both axes: "size:color:fontname", all fields optional. E.g. "10:6B7280:Helvetica Neue"
tickLabels true Show the bin-range labels ([a, b]) on the x-axis. Set false for a minimalist "sparkline" look.

Axis scaling (value axis)

All four map to cx:valScaling attributes. Useful when you want two histograms in a dashboard to share a Y scale, or to cap auto-scaling when outliers blow up the axis.

Property Default Notes
axismin / min (auto) Value-axis lower bound (numeric)
axismax / max (auto) Value-axis upper bound (numeric)
majorunit (auto) Major gridline interval
minorunit (auto) Minor gridline interval 
# Lock Y axis to 0..25 with gridlines every 5
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Samples:..." \
  --prop axismin=0 --prop axismax=25 --prop majorunit=5

Axis visibility and spine styling

Property Default Notes
axis.visible / axisvisible true Hide both axes when false. axis.delete is the inverse alias.
cataxis.visible / cataxisvisible true Hide only the category (bottom) axis
valaxis.visible / valaxisvisible true Hide only the value (left) axis
axisline / axis.line — Spine outline shared by both axes: "color", "color:width", "color:width:dash", or "none"
cataxis.line / cataxisline — Category-axis-only spine (overrides axisline for cat)
valaxis.line / valaxisline — Value-axis-only spine (overrides axisline for val) 
# Minimal look: single thin gray spine, no value axis
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Events:..." \
  --prop binSize=10 \
  --prop "axisline=888888:1.25" \
  --prop valaxis.visible=false \
  --prop gridlines=false

Gridlines

Property Default Notes
gridlines / majorGridlines true Toggle value-axis major gridlines
gridlineColor / gridline.color — Value-axis gridline color (hex)
xGridlines false Toggle category-axis major gridlines (off by default to match Excel)
xGridlineColor / xGridline.color — Category-axis gridline color. Only applies when xGridlines=true.

Data labels

Property Default Notes
dataLabels false true draws the bin count above each bar
datalabels.numfmt / labelnumfmt — Excel format code for the label text: "0", "0.0", "0.00%", "#,##0", etc. 
# Count labels with thousands separator
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Samples:..." \
  --prop dataLabels=true \
  --prop "datalabels.numfmt=#,##0"

Plot area and chart area fills

The plot area is the rectangle the bars are drawn inside. The chart area is the outer frame that also contains the title, legend, and axis labels.

Property Default Notes
plotareafill / plotfill — Plot-area solid background: hex color
plotarea.border / plotborder — Plot-area outline: "color", "color:width", "color:width:dash", or "none"
chartareafill / chartfill — Chart-area (outer frame) solid background: hex color
chartarea.border / chartborder — Chart-area outline, same grammar as plotarea.border 
# "Card" look — light plot area with a subtle border
officecli add data.xlsx /Sheet1 --type chart \
  --prop chartType=histogram \
  --prop series1="Samples:..." \
  --prop plotareafill=FAFBFC \
  --prop "plotarea.border=D0D7DE:1" \
  --prop chartareafill=FFFFFF \
  --prop "chartarea.border=E5E5E5:0.75"

Title styling

Property Default Notes
title — Chart title text
title.color / titlecolor — Title font color (hex)
title.size / titlesize — Title font size (pt)
title.bold / titlebold — Title bold (true/false)
title.font / titlefont — Title font family
title.shadow / titleshadow — Title drop shadow: "COLOR-BLUR-ANGLE-DIST-OPACITY" or "none"

Legend

Property Default Notes
legend — top / bottom / left / right / none (omit legend)
legend.overlay / legendoverlay false Let the legend float on top of the plot area when true. Useful when chart space is tight.
legendfont / legend.font — Compound legend text styling: "size:color:fontname"

Full example: magazine hero chart

This is the full-bleed hero from examples/excel/charts-histogram.xlsx Sheet 0 — every knob above stacked into one presentation-grade chart:

officecli add charts-histogram.xlsx "/0-Hero" --type chart \
  --prop chartType=histogram \
  --prop title="The Shape of Data · 200-sample bell curve" \
  --prop title.color=F5F1E0 --prop title.size=22 --prop title.bold=true \
  --prop title.font="Helvetica Neue" \
  --prop "title.shadow=000000-8-45-4-70" \
  --prop series1="Samples:<200 gauss(75,12) values>" \
  --prop binCount=24 --prop intervalClosed=l \
  --prop fill=F0C96A --prop "series.shadow=000000-8-45-4-60" \
  --prop axismin=0 --prop axismax=28 --prop majorunit=4 \
  --prop xAxisTitle="Score" --prop yAxisTitle="Frequency" \
  --prop axisTitle.color=C9B87A --prop axisTitle.size=13 \
  --prop axisTitle.bold=true --prop axisTitle.font="Helvetica Neue" \
  --prop "axisfont=10:B8B090:Helvetica Neue" \
  --prop "axisline=6A6448:1.5" \
  --prop gridlineColor=2F3544 \
  --prop plotareafill=1A1F2C --prop "plotarea.border=3A3E4E:1.25" \
  --prop chartareafill=0B0F18 --prop "chartarea.border=2A2E3E:1" \
  --prop dataLabels=true --prop "datalabels.numfmt=0" \
  --prop legend=top --prop legend.overlay=false \
  --prop "legendfont=11:D4C994:Helvetica Neue" \
  --prop x=0 --prop y=0 --prop width=27 --prop height=38

Known quirks and limitations

  • binCount wins over binSize. If both are set, binCount takes precedence. This matches Excel's own behavior.
  • Default gapWidth=0. Histograms ship with zero gap so bars touch, matching real Excel output. Override with gapWidth=20..50 to emulate a column chart look.
  • axisTitle.* styles BOTH axis titles. cx doesn't split X/Y title styling into separate knob namespaces — set axisTitle.color=... and both X and Y titles inherit the same run properties. This is an Excel limitation, not an officecli one.
  • OOXML raw-element workaround for cx:binCount / cx:binSize. The Open XML SDK models these as leaf text elements (<cx:binCount>5</cx:binCount>), but real Excel writes the attribute form (<cx:binCount val="5"/>). officecli emits raw OpenXmlUnknownElement to produce the attribute form, otherwise Excel rejects the file with "We found a problem with some content" and deletes the drawing. You won't hit this unless you're hand-crafting OOXML with the SDK — officecli shields you from it.
  • Preview font fallback. The officecli HTML preview renders plot and chart area fills, custom gridline and axis line colors, tick label colors, data labels, locked axis scales, and gapWidth correctly, but does not reproduce custom axis-label font families — tick labels fall back to the preview's default sans font. Excel renders the full font family including Georgia / Courier New / etc. Use the preview for color + layout verification; use Excel for final typographic QA.

Inspect an existing histogram

# Count all charts across the workbook
officecli query charts-histogram.xlsx chart

# Introspect a single chart's bound properties
officecli get charts-histogram.xlsx "/0-Hero/chart[1]"

# Render the sheet to HTML preview
officecli view charts-histogram.xlsx html > preview.html

See Also

  • Chart — Overview of chart operations
  • Chart add — Generic chart add (all 17 types)
  • Chart set — Modify existing charts (incl. histogram)
  • Chart get — Read back chart properties
  • charts-histogram example — Runnable py/md/xlsx triad: 6 sheets × 29 histograms covering every knob on this page

Based on OfficeCLI v1.0.64

Clone this wiki locally