Start Excel histogram X-Axis at 0

Question

I'm charting the frequency of a range of numbers in a histogram in Excel 365. The numbers range from 236 to 1736 and I've got the bins at 250. Excel is automatically starting the histogram bins at the 236-486 range. I don't see an option anywhere to make it start at 0-250. Can anyone help me change it?

Formatting options:

What I want it to look like:

The above was created in a different program that I have access to only through my school, using the same set of data. I'm trying to figure out how to do it in Excel because that's what I use in real life.

Here are a subset of my input data and the chart I get now:

(Ignore Columns A and B.  Columns Y and Z represent the frequencies of the numbers in Column M; e.g., there are two values between 0 and 250, four values between 250 and 500, and so on.)

Solution 1

Set 'Underflow bin' to 250. The first bin will be '<250' and the rest in 250 increments as needed.

Solution 2

You could set the "number range" for different X-Axis Labels first, and set the Bins as By Category. Then you could try to change the labels on X-Axis by change the Data Source. But you need to make sure the counts of "Frequency" correspond to these labels. Hope this workaround could help you~

Comments

[Kay]

The chart labeled What I Want It To Look Like was created in a different program that I only have access to through my school, using the same set of data. I'm trying to figure out how to do it in Excel because that's what I use in real life.

[Jon Peltier]

Its nice that Excel has a histogram. Unfortunately it's not as flexible as it could be. You can tell Excel how many bins or how wide to make the bins, but not both, and not where the bins should start. You can do your own frequency table (as you've done) and build your own chart (which you haven't). I have two tutorials that might help: Histogram with Actual Bin Labels Between Bars and Histogram on a Value X Axis. I also have written software that builds such histograms in Excel.

[Kay]

Setting the underflow value to 0 doesn't seem to do anything.

[Jon Peltier]

Did you try it before you posted this? Before I saw your answer, I thought it might work. But it didn't.

[Kay]

Yes, I had tried changing both the overflow and underflow (wasn't sure what they were till I did some research) prior to posting this. When you suggested to change it, I tried again just to be sure. No luck.

[SilverbackNet]

Hm, I recall it working some time back, but it doesn't now. Might have to do with the Excel version. It looks like Emily Hua has a working, if more manual effort, solution.

[Jon Peltier]

Check out my comment on the original question. The new built-in Excel histograms are not very flexible. The approaches I wrote about before Excel had histograms are just as relevant now that Excel has them.

[Renée]

Well, it's not a solution, but it's the fix I needed for my quick and dirty analysis. Thanks!

[Tripartio]

Having to create a data set of the bins manually defeats the purpose of using the histogram feature. If you're going to do that, then just plot a column chart on the bins and frequency columns directly.