To make a good histogram in Excel, put your numeric measurements in one column, select the values, then choose Insert > Insert Statistic Chart > Histogram. Set a sensible bin width, label the axes, and check that the chart shows the shape of your data clearly. A histogram groups numeric values into ranges and shows how many observations fall in each range.
What You’ll Need
- Excel for Microsoft 365 on Windows. The built-in Histogram chart is also available in current Excel for Mac; menu wording and the Format Axis pane can vary slightly. In Excel for the web, use the desktop app if a chart control described here is missing.
- A column of numeric measurements, such as response times, ages, test scores, or product weights. Use one row per observation and a descriptive column heading.
- A question the chart should answer, such as whether values cluster near a typical result, spread widely, or include unusually high or low values.
Step-by-Step
- Prepare the data. Place each observation in the same column, with a clear heading in the first row—for example, Delivery time (days)—and one measurement per row beneath it. Remove totals and subtotals, since these are not observations. Check that values are stored as numbers rather than text, and decide how to handle blanks, error values, or impossible measurements before charting. Keep legitimate zero values if zero is a possible measurement.
- Select the measurement column. Select the heading and the numeric data beneath it, but do not include unrelated columns or a grand total. Excel normally uses the heading as the chart’s series name. If your values are in a table, you can select the relevant table column and its header. A clean, single-column selection reduces the chance that Excel treats a second field as another data series.
- Insert the histogram. On Windows, go to Insert > Insert Statistic Chart > Histogram. In some ribbon layouts, the statistic-chart button is shown as an icon, so open its menu and choose Histogram rather than Pareto or Box & Whisker. Excel creates a chart with automatically chosen bins. On Mac, look under the Insert tab for the Statistical Chart or Histogram option; the exact label can differ by Excel build.
- Check that Excel used the right data. Confirm that the horizontal axis represents ranges of your measurement and the vertical axis represents the number of observations in each range. If the chart looks like it has one bar per row, or displays dates and names as categories, undo the insertion and check the selected cells and data types. A histogram is intended for numeric measurements, not a list of category names such as departments or colors.
- Choose an appropriate bin width. Right-click the horizontal axis and choose Format Axis. In the Format Axis pane, open Axis Options and find the Bins settings. Excel starts with an automatic choice; try setting Bin width to a meaningful interval in the units of your data. For example, with delivery times measured in days, a width of 1 groups observations into one-day ranges. For scores from 0 to 100, a width of 10 can make a useful first view. These are starting points, not universal rules.
- Compare alternative bin choices. Try a slightly wider and a slightly narrower width, then see whether the overall story remains understandable. Too few wide bins can hide clusters or gaps; too many narrow bins can make ordinary variation look like a series of dramatic spikes. Choose bins that help answer your question and preserve meaningful detail. If the measurement has a natural unit or threshold, use it as a guide rather than selecting a width only because the bars look attractive.
- Set boundaries when they matter. In the same Axis Options pane, you can set an underflow bin or overflow bin to group values below or above a specified boundary. This can be useful when a decision depends on a threshold, such as times above a service target. Check the displayed ranges after changing these settings: observations at a boundary can be grouped according to the bin edges Excel creates. Do not use a boundary merely to conceal an inconvenient value.
- Add a descriptive title and axis titles. Select the chart, then use Chart Design > Add Chart Element > Chart Title and Axis Titles. Name the chart for the data and question, such as Delivery times for October. Label the horizontal axis with the measurement and unit, such as Delivery time (days), and label the vertical axis as Frequency or Number of deliveries. A reader should not have to guess what a bin or count represents.
- Make the chart easy to read. Enlarge the chart enough for the bin labels to be legible. Use a simple, high-contrast fill and avoid decorative effects that compete with the distribution. Keep the vertical count axis visible and, if you change its scale, make sure it does not distort comparisons. Data labels are usually unnecessary when the bars and count axis are clear; add them only if exact counts matter and the chart remains uncluttered.
- Review the result against the source data. Look for an unexpected empty chart, an implausible range, or a single extreme bar. Check that the sample is complete, the measurement units are consistent, and the bin width has not hidden important structure. If you make a decision based on the histogram, verify exact counts in the underlying data rather than estimating them from bar heights.
Common Mistakes
Using categories instead of measurements: A histogram groups numeric values into intervals. For categories such as regions or product types, use a column or bar chart instead. If the numeric values are actually codes for categories, they should not be treated as measurements.
Accepting automatic bins without looking: Excel’s default bins are convenient, but they may not suit the range or purpose of your data. A width that is much too large hides variation; one that is too small creates many sparse bars. Always inspect the range labels and test a reasonable alternative.
Including summaries or mixing units: A total, average, or heading accidentally included as an observation can distort the distribution. So can combining values measured in different units—for example, entering some times in minutes and others in seconds. Clean the source column before making the chart.
Reading a histogram like a time series: The bars show counts in adjacent numeric ranges. They do not show the order in which observations happened. If chronology matters, use a time-series chart or another view that preserves dates.
Over-formatting or overclaiming: Gradients, 3-D effects, and unnecessary labels make the pattern harder to see. A histogram can reveal shape and spread, but by itself it does not explain why the pattern exists or prove that one group differs from another.
Tips
- Choose bin widths in units a reader understands: whole years for age, minutes for duration, or a suitable score interval for test results.
- Use the same bin width and boundaries when comparing two distributions. Different binning can make similar data look different, so a fair comparison needs consistent intervals and scales.
- Keep the measurement and unit in the horizontal-axis title. A chart labelled only Value is difficult to interpret when it is copied into a report.
- Use the underflow and overflow options when tail values are not the main focus, but make the grouping explicit in a note or axis label. This keeps the chart readable without implying the extreme values disappeared.
- Retain a copy of the source data and avoid deleting outliers just to make the chart look balanced. Investigate unusual values first; they may be valid and important.
- For a presentation, give the chart a short takeaway title only if the data supports it. For example, Most deliveries took 2–4 days is more informative than Chart 1, but confirm the bins and counts before stating that conclusion.
- If the chart will be updated regularly, add new observations to the source table and check that the chart’s data range expands to include them. A chart that silently excludes recent rows can mislead even when its formatting is excellent.
Troubleshooting
The Histogram option is missing. Confirm that you are using a current desktop version of Excel and have selected numeric data. Check the Insert tab’s chart menus for the Statistical Chart group. If you are working in a browser and cannot find the required option, open the workbook in desktop Excel. Older Excel versions may not offer the built-in histogram chart; the Analysis ToolPak can provide a frequency-based alternative, but its setup is different.
The chart is blank or has unexpected bars. Look for numbers stored as text, error cells, empty cells, or a selection that includes the wrong range. Select only the measurement column and its header, then insert the chart again. If values came from an imported file, use Excel’s number-format and cell alignment clues as a first check, and verify a few cells directly rather than assuming they are numeric.
You cannot find the bin-width setting. Right-click a label on the horizontal axis—not a bar or the vertical count axis—and choose Format Axis. In the pane, expand Axis Options and locate Bins. If you selected a vertical axis, Excel displays count-axis controls instead. On Mac, the pane may open or arrange differently, but select the horizontal axis and look for its bin settings.
Labels overlap or disappear. Make the chart wider, reduce the number of bins by increasing bin width, or simplify the axis number format. Do not rotate or shrink labels until they are unreadable just to preserve an unnecessarily narrow bin size. If exact ranges are essential, provide them in a nearby note or data table.
The shape changes dramatically when you adjust bins. That can happen, especially with a small dataset. Compare a few sensible widths, report the chosen width, and avoid making a strong claim based on one appearance alone. If a particular threshold matters, set and clearly describe the boundary rather than repeatedly adjusting bins until the chart matches a preferred story.
Frequently Asked Questions
What is a good bin width for a histogram?
There is no single best width for every dataset. Start with a meaningful unit, then compare a somewhat narrower and wider setting. Choose the version that shows useful structure without producing either a handful of broad bars or a wall of sparse bars.
How do I change the number of bins in Excel?
Right-click the horizontal axis, choose Format Axis, and open Axis Options. In Bins, enter a value for Number of bins or set Bin width. The two controls describe alternative ways to group the data, so use the one that best fits your purpose.
Why does my histogram look like a bar chart?
Both use rectangular bars, but they represent different things. A histogram groups numeric measurements into ranges, usually shown in order along a continuous scale. A bar chart compares separate categories. If your axis lists names or labels rather than value ranges, check the chart type and source selection.
Can I make a histogram with text or dates?
A standard histogram needs numeric values. Convert text-formatted numbers to real numbers before charting. Dates are stored as serial numbers in Excel, but a date histogram may not communicate the intervals you intend; consider grouping dates into clear periods or using a column chart if the periods are categories.
Should histogram bars touch?
They commonly touch because the bins represent adjacent intervals on a numeric scale. Excel’s built-in Histogram chart handles the bin display. Avoid changing the chart into a regular column chart simply to control bar spacing, since that can change how the data is represented.
Related
- How to Hide Columns in Excel: A Step-by-Step Guide
- How to Hide Columns in Excel on Mac Without Losing Data
- Add a Plus Sign to Hide Columns in Excel with Grouping
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Histogram Log Scale: What Works and What Doesn’tYes. In Excel for Microsoft 365 on Windows, you can usually put a built-in Histogram chart’s vertical frequency…
- Column Sparklines in Excel: A Step-by-Step How-ToTo insert column sparklines in Excel for Microsoft 365 on Windows, select the cells where you want the…
- Pivot Chart Examples for Excel: Sales, Trends, and MoreUseful Excel PivotChart examples include monthly sales by region, expenses by department, inventory by product category, and support…
- Combo Pivot Chart in Excel: What Works and HowA combo PivotChart cannot be created directly in Excel for Microsoft 365: PivotCharts do not support combining chart…
- Excel Bar Over Bar Graph: How to Overlay Two Data SeriesTo put one data series over another in an Excel bar chart, select a bar, open Format Data…
- Google Sheets Pivot Table Auto Refresh ExplainedYes. Google Sheets pivot tables update automatically when the cells in their source range change; there is no…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.