Change bin width by editing the histogram axis directly

The fastest way to change bin width in Excel is to right-click on the horizontal axis of your histogram, select Format Axis, and adjust the Bin Width field under the Axis Options panel. This method works in Excel 2016 and later versions on both Windows and Mac. The bin width you enter becomes the size of each column in your histogram — for example, entering 5 makes each bin span 5 units of your data.

Excel calculates bin width automatically when you first create a histogram, but that default often groups your data too broadly or too narrowly for what you need to see. Changing it manually lets you control how granular your histogram appears and which patterns in your data become visible.

Key Takeaways

  • Right-click the horizontal axis of your histogram, choose Format Axis, and enter a new number in the Bin Width field to resize your columns when ready.
  • Smaller bin widths show more detail but create more columns; larger bin widths smooth out the view but hide variation.
  • If you do not see a Bin Width option, your chart is not a true histogram — it may be a bar chart that needs to be recreated as a histogram.
  • You can also set a specific starting point for your bins using the Overflow Bin and Underflow Bin options in the same panel.

Step-by-step: accessing the bin width control

Open your histogram in Excel. Click once on the chart to select it, then click once more on the horizontal axis (the bottom edge where your numbers run). The axis will show selection handles — small squares at the corners and edges.

Right-click anywhere on the selected axis. A context menu appears. Click Format Axis. The Format Axis pane opens on the right side of your screen. Under Axis Options, you will see a section labeled Bins. The Bin Width field is where you enter your new value. Type the number you want and press Enter. Your histogram redraws when ready with the new column widths.

Choosing the right bin width for your data

There is no single correct bin width — it depends on what you are trying to show. If your data ranges from 0 to 100 and you want to see broad patterns, a bin width of 10 or 20 works well. If you want to spot smaller variations, try 2 or 5. Start with the default, then adjust up or down based on what the histogram reveals.

A common mistake is making bins too small, which creates a jagged, hard-to-read histogram with many nearly-empty columns. If that happens, increase the bin width. Conversely, if all your data seems to collapse into one or two columns, decrease the bin width to spread it out. You may need to try two or three values before the histogram becomes useful.

Bin width versus number of bins

Excel offers two ways to control how your data is grouped: Bin Width and Number of Bins. Bin Width sets the size of each column directly. Number of Bins tells Excel how many columns to create, and Excel calculates the width automatically. You can use either one, but not both at the same time — if you enter a bin width, the number of bins adjusts to fit; if you specify a number of bins, the width adjusts instead.

For most purposes, bin width is easier to think about because you control the actual size of each group. Number of Bins is useful if you know you want exactly 10 columns but do not care what size they are.

Setting overflow and underflow bins

Below the Bin Width field, you will see checkboxes for Overflow Bin and Underflow Bin. These create extra columns at the edges of your histogram to catch data that falls outside your main range. Check the Overflow Bin box if you have a few data points much larger than the rest; check Underflow Bin if you have a few much smaller than the rest. This keeps those outliers from distorting your main histogram.

You can also set a specific starting point for your bins using the Bin Boundary field. For example, if your data starts at 10, you might set the boundary to 10 so your first bin begins exactly there rather than at 0.

Troubleshooting: when bin width does not appear

If you right-click the axis and do not see a Bin Width option in the Format Axis pane, your chart is probably not a true histogram. Excel sometimes creates a bar chart instead, especially if your data is not sorted or if you selected the wrong chart type. Delete the chart and create a new one: select your data, go to the Insert tab, click Charts, and choose Histogram explicitly from the menu. Do not choose Column Chart, which looks similar but does not have bin controls.

Another reason the option might be missing: you are using an older version of Excel (2013 or earlier). Those versions do not have a built-in histogram tool. You would need to create bins manually using formulas and then build a column chart around them — a more involved process that is beyond the scope of this guide.

Frequently Asked Questions

What is the difference between a histogram and a bar chart in Excel?

A histogram groups continuous numerical data into bins and shows the frequency of each bin as a column. A bar chart displays categories or discrete values. Histograms have bin width controls; bar charts do not. If you need to change bin width, you must be working with an actual histogram, not a bar chart.

Can I change bin width after I have already created the histogram?

Yes. Right-click the horizontal axis, select Format Axis, and enter a new bin width. The histogram updates when ready. You can change it as many times as you want without recreating the chart.

What happens if I set the bin width too large?

Your histogram will show only a few columns, and you will lose detail about how your data is distributed. You will see only the broadest patterns. If that happens, reduce the bin width to reveal more variation.

Can I use decimal numbers for bin width?

Yes. If your data includes decimals, you can set a bin width of 0.5, 0.1, or any other decimal value. Excel will create bins of that exact size.

Does changing bin width affect my original data?

No. Changing bin width only changes how the histogram displays your data. Your original numbers in the spreadsheet remain unchanged.