Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create a Histogram in Excel with Python

Show how values are distributed — order sizes, delivery times, invoice amounts — as a histogram in an Excel report. Choose bin edges with numpy, count values per bin, label bins the way readers expect, draw them as touching columns with openpyxl or xlsxwriter, and keep a live FREQUENCY version for data that changes.
Charts & imagesPivots & summaries

Averages hide most of what matters about a set of values: whether they bunch together tightly or spread widely, whether there are two groups instead of one, whether a handful of huge orders drag the mean up. A histogram shows all of that at a glance — how many values fall in each range. Excel has a built-in histogram chart, but it belongs to a newer chart format that neither openpyxl nor xlsxwriter can write. The reliable approach is to count the values into bins in Python and chart the counts as touching columns. This page, part of Creating Charts in Excel with openpyxl, builds a histogram of order values that way.

From raw values to a histogram chart Four steps. Choose bin edges with a round width. Count how many values fall in each bin. Write a table of bin labels and counts to the sheet. Chart the counts as a column chart with no gaps between the columns. Choose bins round width Count np.histogram Write table label and count Chart columns, gap 0

Prerequisites

Bash
pip install numpy pandas openpyxl xlsxwriter

orders.xlsx holds 480 order values. Most are between about 40 and 250; a handful are far larger.

Step 1 — Choose readable bins

numpy.histogram_bin_edges with bins="auto" picks a bin count suited to the amount of data. Its edges are rarely round numbers, though, and bin labels such as "47.3–61.9" are hard to read. Use the suggested width as a guide and round it to a friendly step:

Python
import numpy as np
import pandas as pd

orders = pd.read_excel("orders.xlsx")
values = orders["Value"].to_numpy()
auto = np.histogram_bin_edges(values, bins="auto")
print(len(auto) - 1, "auto bins of width", round(auto[1] - auto[0], 1))

def nice_width(raw):
    for step in [1, 2, 2.5, 5, 10, 20, 25, 50, 100, 200, 250, 500, 1000]:
        if step >= raw:
            return step
    return raw

cap = 400
width = nice_width((np.percentile(values, 99) - values.min()) / 14)
edges = np.arange(0, cap + width, width)
print("width", width, "edges", edges[:4], "…", edges[-2:], "| values over", cap, ":", int((values >= cap).sum()))

The automatic choice gives 44 bins of about 28 each — far too many for a chart, and most of them empty stretches reaching out to the few very large orders. Basing the width on the 99th percentile instead, and aiming for about fourteen bins, gives a width of 25 — labels like "75–100" that anyone can read. Values above a cap of 400 will go in a single overflow bin rather than forcing a dozen empty bins between 400 and 1,250.

How the bin width changes the picture A table of bin widths for the 480 orders up to 400. A width of 10 gives 40 bins, too noisy to read. A width of 25 gives 16 bins, which shows the shape clearly. A width of 50 gives 8 bins, which hides the peak between 75 and 100 inside a wider 50 to 100 bin. Bins up to 400 Reads as Width 10 40 noisy — every bump looks important Width 25 16 clear peak and fall-off Width 50 8 smooth but hides the 75–100 peak

There is no single correct width; try two or three and keep the one where the shape is clear without every small bump looking meaningful. Once chosen, keep the same edges from one report to the next, so readers compare like with like.

Step 2 — Count values into the bins

np.histogram counts values per bin for a given set of edges. Add the overflow bin by counting separately, and build human-readable labels:

Python
counts, _ = np.histogram(values[values < cap], bins=edges)
labels = [f"{lo:.0f}–{hi:.0f}" for lo, hi in zip(edges[:-1], edges[1:])]
labels.append(f"{cap} and over")
counts = np.append(counts, (values >= cap).sum())

hist = pd.DataFrame({"Order value": labels, "Orders": counts})
print(hist.to_string(index=False))
assert hist["Orders"].sum() == len(values)

Every bin includes its lower edge and excludes the upper one, except that np.histogram includes the upper edge in the last bin — which is why the overflow values are separated first with values < cap. The assertion confirms every order is counted exactly once. Bin Values into Ranges with pandas cut does the same with pd.cut when each row needs its bin label.

Step 3 — Chart the counts with openpyxl

Write the table and add a column chart. Three settings make it a histogram rather than a bar chart: gapWidth = 0 so the columns touch, no legend (there is one series), and visible axes:

Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook(); ws = wb.active; ws.title = "Distribution"
ws.append(["Order value", "Orders"])
for row in hist.itertuples(index=False):
    ws.append([row[0], int(row[1])])
ws.column_dimensions["A"].width = 14

chart = BarChart()
chart.type = "col"
chart.gapWidth = 0
chart.title = f"Order values, September 2026 (n = {len(values)})"
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=len(hist) + 1), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=len(hist) + 1))
chart.legend = None
chart.x_axis.title = "Order value"
chart.y_axis.title = "Number of orders"
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.series[0].graphicalProperties.line.solidFill = "FFFFFF"
chart.width, chart.height = 20, 9
ws.add_chart(chart, "D2")
wb.save("order_histogram.xlsx")
print(hist.loc[hist["Orders"].idxmax()].tolist())

A thin white outline on each column keeps the bins distinguishable while they touch. The delete = False lines make Excel show the axes, which it otherwise hides for openpyxl 3.1 charts. The tallest bin is 75–100, with the gradual fall-off to the right typical of order values: many modest orders, a few large ones.

Order values by 25-wide bin Histogram of 480 order values in bins 25 wide from 0 to 400, plus an overflow bin for 400 and over. Counts rise to a peak of 121 in the 75 to 100 bin and fall away gradually to the right, with 4 orders of 400 and over. Orders per bin 0–25 1 25–50 31 50–75 89 75–100 121 100–125 89 125–150 53 150–175 33 175–200 25 200–225 16 225–250 8 250–275 4 275–300 4 300–325 2 325–350 0 350–375 0 375–400 0 400+ 4

Step 4 — The same chart with xlsxwriter

In xlsxwriter the gap is a series option, and pandas can write the table in the same ExcelWriter:

Python
with pd.ExcelWriter("order_histogram_xw.xlsx", engine="xlsxwriter") as writer:
    hist.to_excel(writer, sheet_name="Distribution", index=False)
    book, sheet = writer.book, writer.sheets["Distribution"]
    col = book.add_chart({"type": "column"})
    n = len(hist)
    col.add_series({"name": "Orders", "categories": ["Distribution", 1, 0, n, 0], "values": ["Distribution", 1, 1, n, 1],
                    "gap": 0, "border": {"color": "white"}})
    col.set_title({"name": "Order values, September 2026"})
    col.set_x_axis({"name": "Order value"})
    col.set_y_axis({"name": "Number of orders", "major_gridlines": {"visible": True}})
    col.set_legend({"none": True})
    col.set_size({"width": 760, "height": 340})
    sheet.insert_chart("D2", col)
print("written", n, "bins")

xlsxwriter always writes its axes as visible, so there is no equivalent of the openpyxl axis setting. Add a Chart to an Excel File with xlsxwriter covers the other chart options.

Step 5 — A live histogram with FREQUENCY

When readers will paste new values into the workbook, the counts should update with them. Excel's FREQUENCY function counts values into bins given their upper edges, and in current Excel it returns all the counts at once as a dynamic array. Write the upper edges and the formula, and point the same chart at the formula results:

Python
wb = Workbook(); ws = wb.active; ws.title = "Live"
ws.append(["Value", "", "Up to", "Orders"])
for v in values:
    ws.append([float(v)])
last = len(values) + 1
for i, hi in enumerate(edges[1:], start=2):
    ws.cell(row=i, column=3, value=float(hi))
ws.cell(row=len(edges) + 1, column=3, value="over")
ws["D2"] = f"=FREQUENCY(A2:A{last},C2:C{len(edges)})"
wb.save("live_histogram.xlsx")
print("bins:", len(edges) - 1, "+ overflow; formula:", ws["D2"].value)

FREQUENCY returns one more count than there are edges — the values above the last edge — which lands next to the "over" label automatically. Written by openpyxl the formula has no cached result, so the counts appear only when Excel recalculates the file; for spilling dynamic-array formulas written from Python, see Write Dynamic Array Formulas with Python. For reports that are regenerated anyway, the static counts from Step 2 are simpler and show correctly in every program.

Common pitfalls

  • Expecting the native histogram chart. Neither library writes it; chart pre-counted bins.
  • Gaps between columns. Set gapWidth = 0 (openpyxl) or "gap": 0 (xlsxwriter).
  • Unreadable bin labels. Round the width to a friendly step and label ranges clearly.
  • A few outliers stretching the axis. Use an overflow bin and say how many it holds.
  • Unequal bin widths. Column heights mislead when bins differ in width; keep them equal.
  • Missing axes in Excel. Set delete = False on both axes of openpyxl charts.

Performance notes

np.histogram counts millions of values in milliseconds; only the table of counts goes into the workbook, so the chart stays small however large the data. The live FREQUENCY version stores every value in the sheet and recalculates on every change, which is fine for thousands of rows but slow for millions.

Conclusion

Pick bin edges with a round width, guided by numpy.histogram_bin_edges and a percentile rather than the extreme values, and send outliers to an overflow bin. Count with np.histogram, check the counts add up, label each bin as a range, and chart the table as a column chart with no gaps — gapWidth = 0 and visible axes in openpyxl, "gap": 0 in xlsxwriter. Use FREQUENCY with upper edges when the workbook must recount as its data changes.

Frequently asked questions

Can openpyxl or xlsxwriter create Excel's built-in histogram chart? No. Excel's histogram, box-and-whisker and waterfall charts use a newer chart format that neither library writes. Count the values into bins in Python and draw the counts as a column chart with no gap between the columns — it looks and reads the same.

How do I choose histogram bins? Start from numpy.histogram_bin_edges(values, bins="auto") to get a sensible number of bins, then round the width to a readable step such as 10, 25 or 50 so the bin labels are round numbers. Keep all bins the same width.

How do I remove the gaps between columns in an openpyxl chart? Set chart.gapWidth = 0 on the BarChart. In xlsxwriter, pass {"gap": 0} in the series options. Touching columns tell the reader the bins are continuous ranges rather than separate categories.

How do I handle a few very large values? Put them in a final overflow bin labelled, for example, "500 and over", instead of stretching the axis to include them. Say in the chart or a note how many values the overflow bin contains.

More on charts & images

More on charts & images →