To make a histogram in Excel for Microsoft 365 on Windows, select a single column of numeric data, then choose Insert > Insert Statistic Chart > Histogram. Excel groups the numbers into ranges, or bins, and shows how many values fall in each range; you can adjust the bin settings by right-clicking the horizontal axis and choosing Format Axis.
What a histogram shows
A histogram summarizes the distribution of numerical measurements. Its horizontal axis represents value ranges, and its vertical axis represents the number of observations in each range. The bars usually touch because the ranges describe continuous values, such as time, distance, temperature, or test scores. A tall bar means many observations fall within that interval; a short bar means few do.
For example, a shop could use a histogram to see how long customer orders take to ship. If most delivery times fall between two and four days, that range will have a taller bar than ranges containing only a handful of orders. The overall pattern can reveal a concentration, a long tail, gaps, or more than one cluster. It does not, by itself, explain why the pattern occurred.
A histogram is different from a column chart. A column chart normally compares separate categories, such as sales by department, and its bars are separated. A histogram groups numeric values into intervals, so the intervals have an ordered, continuous scale. Choosing the wrong chart type can make a distribution look like a set of unrelated categories.
Prepare the data before creating the chart
Put the observations in one column, with a descriptive heading in the first row. For example, use Delivery days in A1 and numeric measurements below it. Select the heading and the values together if you want Excel to use the heading as the chart title or series label. If the selection includes unrelated columns, Excel may interpret the data in an unexpected way.
- Use one observation per cell. A value such as 3.5 should be a number, not text that merely looks like a number.
- Remove or investigate entries such as “unknown,” dashes, and notes. Text entries are not numerical observations and may be ignored or interfere with the chart’s interpretation.
- Check for blank cells, accidental duplicates, and values entered in the wrong units. A single extra zero can stretch the scale and make the rest of the distribution difficult to see.
- Keep legitimate extreme values unless there is a documented reason to exclude them. An unusual result may be important rather than an error.
Do not sort the data first just to make a histogram. The chart groups the numbers into bins whether the source values are sorted or not. Sorting can still help you inspect the smallest and largest values and spot possible data-entry mistakes.
Create a histogram with the Insert chart command
- Select the numeric data, including its heading if you have one.
- On the ribbon, choose Insert > Insert Statistic Chart > Histogram. In some ribbon layouts, the statistical chart button is shown as a small chart icon, so look for the Histogram option in its menu.
- Excel inserts a histogram on the worksheet. Drag the chart to reposition it, or use the corner handles to resize it.
- Click the chart title and replace the default text with a specific description, such as “Order delivery time distribution.”
- Right-click the horizontal axis and select Format Axis to inspect or change how Excel groups the values.
Excel calculates the initial bins automatically. That is convenient for a first look, but the automatic choice is not always the clearest one for a report or for comparing two groups. Review the axis and adjust the grouping to suit the question and the measurement scale.
On Excel for Mac, select the data and use Insert > Statistical Chart > Histogram (the wording can vary slightly by release), then use the axis formatting pane to change the bins. Excel for the web may offer fewer chart-creation or formatting controls than the desktop app. If you cannot find Histogram or the bin controls online, open the workbook in desktop Excel rather than substituting an ordinary column chart.
Choose useful bins and set their width
A bin is an interval of values assigned to one bar. The bin width controls how much of the measurement scale each bar covers. In the Format Axis pane, open Axis Options and look for the Bins settings. Depending on the data and chart, Excel can use an automatic setting, a chosen bin width, or a chosen number of bins. You can also set an underflow or overflow bin to group values below or above a specified boundary.
To set a width, right-click the horizontal axis, choose Format Axis, and enter a value for Bin width. For delivery times recorded in days, a width of 1 groups results into one-day intervals; for measurements in millimeters, a width of 5 groups them into five-millimeter intervals. The appropriate width depends on the precision of the data and the purpose of the chart, not on a universal rule.
- Too few, wide bins: the chart may hide meaningful differences and look overly smooth.
- Too many, narrow bins: many bars may have very few observations, making random variation look like a strong pattern.
- Comparable charts: use the same bin boundaries and width when comparing distributions across groups. Otherwise, apparent differences may come from the chart settings rather than the data.
Changing the number of bins is not the same as changing the bin width. A fixed number of bins can produce different widths as the data range changes, while a fixed width keeps the intervals consistent but may produce a different number of bars. For comparisons across months or locations, consistent boundaries are often easier to interpret.
Read the shape without overinterpreting it
Look first for where the bars are tallest and how the counts change across the scale. A roughly symmetric mound may indicate that values cluster around a central range. A long tail to the right means a small number of relatively high observations extend beyond the main cluster; a tail to the left indicates the reverse. Separate peaks can suggest distinct subgroups, but a different bin width may merge or split those peaks.
Also check the axis labels, units, and sample size. A histogram of 20 observations can look quite different from one based on 2,000 observations, even when the underlying pattern is similar. The vertical axis in Excel’s standard histogram represents counts, not percentages. If groups have very different sample sizes, raw counts can make the larger group look more prominent simply because it contains more records.
Do not read a bar as an exact value. A bar represents all observations in an interval, not a single measurement. The chart also does not show which record produced a value or establish a cause-and-effect relationship. Use the source data and relevant context to investigate a surprising shape.
Use the Analysis ToolPak for explicit frequency tables
The Analysis ToolPak offers a separate Histogram analysis that can produce a frequency table and, optionally, a chart. It is useful when you want to define the bin limits in cells or need a table of counts for a report. Unlike the Insert histogram chart, the ToolPak workflow asks you to supply a bin range rather than relying on the chart’s automatic bin setup.
- Enter your desired bin limits in a separate column, in ascending order. For example, list the upper limits that define the intervals you want.
- If the ToolPak is not available, go to File > Options > Add-ins. Beside Manage: Excel Add-ins, click Go, select Analysis ToolPak, and click OK.
- Choose Data > Data Analysis > Histogram.
- Set the Input Range to the numeric observations and the Bin Range to the cells containing your limits. Include labels only if you also select the Labels option.
- Choose an output location, and select Chart Output if you want Excel to create a chart. Click OK.
The output table reports how many observations fall within each listed bin and may include a final category for values above the last limit. Read the bin labels carefully: the ToolPak uses the entered limits to define its frequency categories, so they are not automatically identical to the default intervals of an Insert histogram chart. The output is a separate analysis result; if the source data changes, run the analysis again to refresh it.
On Mac, the add-in is managed through Tools > Excel Add-ins in releases that provide the Analysis ToolPak. Availability and menu wording can differ by Excel release. If the add-in is missing, check the installed add-ins or use the standard Histogram chart in desktop Excel.
Customize labels and improve readability
Give the chart a title that states what was measured and, where useful, the time period or group. Add axis titles when the units are not obvious. Select the chart and use the chart elements button or the Chart Design tab to add or adjust titles and other elements. Keep the vertical axis as a count unless you have deliberately created a different measure.
Use a sensible number format for the horizontal scale. If the values are currency, dates, or decimal measurements, labels should make the units clear without crowding the axis. A chart can also be made easier to read by enlarging it, simplifying its colors, and removing decorative effects that compete with the bars. Avoid rotating or shrinking labels until they become hard to read; a wider chart or a more suitable bin width may solve the problem better.
When a chart is intended for readers unfamiliar with the data, explain the bin width in a caption or nearby note. The chart title alone cannot communicate every analytical choice. If you change the bin width after sharing the chart, update any explanation that refers to the original intervals.
Common problems and how to fix them
- Histogram is missing from the Insert menu: confirm that you are using a desktop Excel release with statistical chart support, and select numeric data. If using Excel for the web, try the desktop app.
- Bars appear too broad or too narrow: open Format Axis and change the bin width or number of bins. Check that the selected setting matches the units and purpose of the analysis.
- The scale includes a huge empty range: inspect the source for an accidental outlier or a value entered in the wrong unit. Do not delete a valid extreme value merely to improve the appearance; consider an overflow bin or explain the value instead.
- Some observations seem absent: check whether they are stored as text, contain errors, or fall outside a manually defined bin range. Review the ToolPak’s final overflow category when using that method.
- The chart changes after editing the data: the Insert chart is linked to its source range, but adding new rows outside that range may not include them. Expand the selected source range or use an Excel Table so added rows are more likely to be included in the chart’s data source.
Frequently Asked Questions
Can Excel make a histogram automatically?
Yes. Select numeric data and choose Insert > Insert Statistic Chart > Histogram. Excel selects an initial bin arrangement automatically, and you can change it in Format Axis.
How do I change the bin size in an Excel histogram?
Right-click the horizontal axis, choose Format Axis, and enter a value under Bin width in Axis Options. If you want a specific count of bars instead, choose the number-of-bins setting.
What is the difference between a histogram and a bar chart?
A histogram groups numerical observations into ordered intervals, and its bars usually touch. A bar chart compares distinct categories, such as products or departments, and its bars are generally separated.
Can I make a histogram in Excel without the Analysis ToolPak?
Yes. The built-in Histogram chart under the Insert tab does not require the ToolPak. The ToolPak is an alternative for producing a frequency table from specified bin limits.
Why does my histogram show an unexpected number of bars?
Excel chooses bins automatically unless you set them yourself, so its first result may not match your preferred intervals. Inspect the axis settings, check for unusually large values, and choose a width that fits the measurement scale.
Which method should you use?
For a quick visual summary, start with the built-in Histogram chart. It is convenient, remains connected to the selected data, and lets you adjust the bins through the chart axis. Use the Analysis ToolPak when you need a frequency table based on specific limits or want its chart output alongside that table. In either case, verify the data, choose intervals that answer the question, and state the units so readers can interpret the result correctly.
Related
- How to Add Rows to an Existing Group in Excel
- Add a Jigsaw Sudoku Solver to Excel
- Add Quadrants to an Excel Scatter Plot: Step-by-Step
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Pie Chart: Create, Filter, and Fix OneYes—Excel for Microsoft 365 on Windows can create a pie PivotChart from a PivotTable. Select a cell in…
- Excel Pivot Table Instructions for Building Clear ReportsTo create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data…
- Excel Pivot Table Calculated Field Filter ExplainedYes, you can filter a PivotTable based on a calculated field’s results, but usually you do it with…
- Excel Pivot Table Totals at Top? Show SubtotalsYes. To put category subtotals above their detail rows in Excel for Microsoft 365 on Windows, click inside…
- Excel Pivot Table: Sort Months ChronologicallyYes. To sort months chronologically in an Excel PivotTable, use a real date field and group it by…
- Best Pivot Table Design: Five Clear Layouts for ExcelFor most Excel reports, the best pivot table design is a simple summary in Tabular Form, with only…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.