To make histogram bins start at 0 reliably in Excel, create bin intervals beginning at 0, count the values in each interval, and chart those counts as columns with no gaps. This is more dependable than changing the built-in Histogram chart’s Underflow bin, which groups values at or below a threshold rather than simply setting the first ordinary bin’s lower edge. The steps below use Excel for Microsoft 365 on Windows.
There are two different things people may mean by “start at 0.” You may want the bins along the horizontal axis to begin at zero, or you may want the vertical count axis to have a minimum of zero. The first requires bins that begin at zero; the second is a value-axis setting. The guide covers both, and explains when Excel’s built-in Histogram chart is suitable.
What You’ll Need
- A column of numeric measurements, such as response times, prices, or test scores. The main method assumes the values are zero or positive.
- A bin width, such as 10 units, chosen to suit the range and precision of your data.
- Excel for Microsoft 365 on Windows. Excel for Mac has comparable chart tools, although ribbon labels and locations can vary slightly. For some chart-formatting controls, open the workbook in desktop Excel if you are using Excel for the web.
Step-by-Step
- Check and prepare the data. Put the measurements in one column with a heading, for example, “Value” in A1 and the numbers in A2:A12. Remove text entries that are not measurements, and decide how you will handle blanks or errors. A histogram counts numeric observations; text such as “12 seconds” stored in a cell may not be counted as the number 12. If your data includes negative values but you want the displayed bins to begin at zero, decide whether to omit those values or report them separately. Do not silently discard valid observations.
- Pick a bin width and make a small example layout. A bin is an interval of values. With a width of 10, the intervals can be 0 to less than 10, 10 to less than 20, 20 to less than 30, and so on. In a blank area, create column headings such as “Lower bound,” “Upper bound,” “Bin label,” and “Count.” Enter lower bounds 0, 10, 20, and 30 in B2:B5, and upper bounds 10, 20, 30, and 40 in C2:C5. In E2:E5, enter labels such as “0–<10,” “10–<20,” “20–<30,” and “30–<40.” Using explicit lower and upper limits makes it easy to verify that the first interval really begins at zero.
- Count the observations in each interval. With measurements in A2:A12, lower bounds in B2:B5, and upper bounds in C2:C5, enter this formula in D2:
=COUNTIFS($A$2:$A$12,">="&B2,$A$2:$A$12,"<"&C2). The formula counts values that are greater than or equal to the lower limit and less than the upper limit. Copy D2 down through D5 using the fill handle. In this example, the ranges include both 0 and 40 only if they fall inside a stated interval: zero is included in 0–<10, while a value of exactly 40 belongs in a subsequent bin. The half-open intervals prevent a boundary value such as 10 from being counted twice. - Check the counts against a worked example. Suppose A2:A12 contains 0, 3, 7, 10, 12, 18, 21, 25, 29, 30, and 34. The four counts in D2:D5 should be 3, 3, 3, and 2. The first count includes 0, 3, and 7; the second includes 10, 12, and 18; the third includes 21, 25, and 29; and the last includes 30 and 34. The counts sum to 11, matching the 11 numeric observations. If they do not sum to the number of values you intend to chart, check for negative values, blanks, text, or a missing final interval.
- Make sure the bins cover the whole data range. Continue the lower and upper bounds in steps equal to your chosen width until the last upper bound is greater than the largest measurement. For example, if the largest value is 47 and the width is 10, include an interval from 40 to less than 50. If a measurement equals the final upper bound exactly, add another interval so that it is included. Otherwise, it will not be counted by the less-than upper-bound rule.
- Create the chart from the labels and counts. Select the bin labels and counts—for example, E1:E5 and D1:D5 if the headings are in row 1, or select the label and count columns together if they are adjacent. If they are not adjacent, select the first range, then hold Ctrl on Windows while selecting the other range. Choose Insert > Insert Column or Bar Chart > 2-D Clustered Column. Excel creates a column chart with one column for each interval. In Excel for Mac, the Insert tab also offers column charts; the exact gallery layout can differ. If the selection is awkward, copy the labels and counts beside each other first, then select that compact table.
- Remove the spaces between columns. Click one of the chart columns, right-click, and choose Format Data Series. Under Series Options, set Gap Width to 0% or a small value. Zero gap makes the columns touch, giving the chart the familiar histogram appearance. If you want the intervals to look distinct, use a small gap instead. Add a descriptive chart title and axis titles so readers know what the values and counts represent.
- Confirm that the horizontal bins begin at zero. Read the first category label: it should be 0–<10, or whatever interval matches your bin width. The horizontal axis in this custom chart displays category labels, not a continuous numeric scale. That is expected: the labels communicate the interval boundaries, and the first one explicitly starts at zero.
- Set the vertical count axis minimum to zero if needed. If you also mean that the count scale should begin at zero, right-click the vertical axis and choose Format Axis. In Axis Options, find Bounds and set Minimum to 0. Excel commonly starts a column chart’s value axis at zero already, but checking the setting prevents a cropped scale from exaggerating differences. This setting affects the vertical count scale; it does not change the first horizontal bin.
Alternative: use Excel’s built-in Histogram chart. Select the numeric data, then choose Insert > Insert Statistic Chart > Histogram. Right-click the horizontal axis, choose Format Axis, and use Axis Options > Bins to set a bin width. You can also select Underflow bin and enter 0. Be aware that this creates a separate bin for values less than or equal to 0; it does not simply label the first regular bin as 0–10. Use the built-in chart when its automatically formed bins are acceptable. Use the explicit interval table above when the first interval must be controlled and clearly labeled. On Mac, look for Histogram in the Insert tab’s statistical chart options; if a control is missing in Excel for the web, use desktop Excel.
Common Mistakes
- Confusing the two axes. Setting the vertical count axis minimum to zero does not make the first horizontal interval start at zero. Check the first bin label as well as the count scale.
- Treating Underflow as a minimum-axis setting. In a built-in Histogram chart, an underflow threshold makes a special group for values at or below that number. It is not the same as creating an ordinary first interval from zero to the bin width.
- Counting boundary values twice or not at all. Use intervals that include the lower bound and exclude the upper bound, such as 10–<20. Then make sure the last interval covers the largest value, including cases where a value equals a proposed final boundary.
- Choosing a width that hides the pattern. Very wide bins can conceal clusters; very narrow bins can make a chart noisy. Try a few reasonable widths and choose one that makes the distribution understandable without obscuring important variation.
- Using a line chart instead of columns. A line chart joins points and can imply values between the bins. Use a column chart for the custom frequency table, and reduce its gap width to make it look like a histogram.
- Forgetting that negatives are excluded. The example formula starts counting at zero, so negative measurements will not appear in any bin. If negatives are meaningful, either add negative intervals or make a separate category and explain the choice.
Tips
- Choose a bin width in the same units as the measurements. For whole-number ages, a width of 5 or 10 may be easy to interpret; for measurements with decimals, choose a width that matches the useful precision.
- Keep lower and upper bounds visible in the worksheet even if the chart only shows compact labels. This makes the counting logic auditable and helps you revise the chart later.
- For a data set that changes often, preserve the bounds and count formulas, then extend the data range as new observations arrive. A structured Excel Table can make a growing source range easier to maintain, but verify that each formula still covers the intended rows.
- Use a clear chart title and include units in an axis title or in the bin labels. A label such as “Response time (seconds)” is more useful than “Value.”
- If the chart will be printed or shared, check that labels remain readable and that the zero-to-first-bin interval is obvious. Do not rely on color alone to explain the boundaries.
- Keep the bin width consistent. Unequal intervals can be valid for some analyses, but a basic column chart of counts may then be misleading because wider intervals have more opportunity to contain observations. For ordinary histograms, equal-width bins are usually easier to compare.
Troubleshooting
The first bin does not start at zero. If you are using the built-in chart, confirm whether the displayed first label is an underflow category or an ordinary bin. For exact control, use the interval table and make the first lower bound 0. Check that you selected the correct label range when building the chart; Excel may otherwise use the counts or bounds as category labels.
The first count is zero even though there are small values. Check that the first interval’s lower bound is 0 and its upper bound is larger than those values. Confirm that the source cells contain numbers rather than text, and that the formula references the right data column. If measurements can be negative, they are not counted by a zero-starting set of bins.
Some values are missing from all bins. Compare the minimum and maximum of the data with the first lower bound and final upper bound. Add intervals as needed. A value exactly equal to an upper limit moves into the next interval because the formula uses a less-than test for that limit. Also inspect cells for errors, text-formatted numbers, and trailing rows omitted from the source range.
The chart has gaps or looks unlike a histogram. Right-click a column, choose Format Data Series, and reduce Gap Width. If the chart is a built-in Histogram chart, its bin controls are in Format Axis, not Format Data Series. If you cannot find them, verify that you inserted a Histogram chart rather than a regular column chart.
The vertical axis begins above zero. Right-click the vertical value axis, choose Format Axis, and set the minimum bound to 0. If the chart is a built-in Histogram chart, check the value axis rather than changing the horizontal bin settings. If the setting is unavailable in your current Excel interface, open the file in desktop Excel for Microsoft 365.
Frequently Asked Questions
Can I force a built-in Excel Histogram chart to start its bins at 0?
Excel provides bin width and underflow controls, but Underflow creates a separate bin for values at or below a threshold. If you need a clearly defined first regular interval such as 0–<10, make a frequency table with explicit limits and chart it as columns.
Is a column chart based on a frequency table still a histogram?
It represents a histogram-style frequency distribution when the columns show counts for adjacent numeric intervals. Use equal-width intervals for straightforward comparisons, label the ranges clearly, and set the gap width to zero or near zero.
How do I make the count axis start at zero?
Right-click the vertical axis, choose Format Axis, and set its minimum bound to 0 under Axis Options > Bounds. That changes the count scale, not the horizontal bin boundaries.
What if my data contains negative numbers?
Decide whether those numbers are valid observations. If they matter, include negative intervals in the frequency table so they are represented. If the chart is intentionally limited to nonnegative values, state that scope and do not present the remaining counts as covering the full data set.
What bin width should I use?
There is no single best width for every data set. Start with a width that produces a manageable number of intervals, inspect whether important clusters or gaps are visible, and compare alternatives. Keep the chosen width and units clear on the chart.
Related
- How to Hide Columns in Excel for the Web
- How to Hide Columns in Excel: A Step-by-Step Guide
- How to Hide Columns in Excel on Mac Without Losing Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Compare Two Excel Sheets With a Pivot TableTo compare two Excel sheets with a PivotTable, combine their rows into one table, add a column identifying…
- Excel Pivot Tables for BI: Uses, Limits, and AlternativesYes. Excel PivotTables can serve as a practical, entry-level business intelligence (BI) tool: they summarize data, reveal patterns,…
- Excel Pivot Table Unique Count: How to Get ItYes—Excel can count unique values in a PivotTable. In Excel for Microsoft 365 on Windows, create the PivotTable…
- Create a Pie Chart in Excel: Steps and TipsYes. In Excel for Microsoft 365 on Windows, select your category names and values, then choose Insert >…
- Excel to PowerPoint Gantt Chart: Create and Share ItTo put an Excel Gantt chart in PowerPoint, build it in Excel as a stacked bar chart, select…
- Excel Histogram Format Options: Bins, Labels, and StyleIn Excel for Microsoft 365 on Windows, select a histogram and use Format Axis to change its bins,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.