6

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:

formatting options

What I want it to look like:

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:

Data

(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.)

Scott - Слава Україні
  • 21,399
  • 46
  • 64
  • 121
Kay
  • 61
  • 1
  • 1
  • 3
  • 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. – Kay Jan 11 '20 at 00:36
  • 1
    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](https://peltiertech.com/excel-histogram-bin-labels-bars/) and [Histogram on a Value X Axis](https://peltiertech.com/histogram-on-value-x-axis/). I also have written software that builds such histograms in Excel. – Jon Peltier Jan 14 '20 at 04:07

4 Answers4

6

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

Craig
  • 61
  • 1
  • 2
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~ enter image description here

Emily
  • 3,339
  • 1
  • 4
  • 6
  • 1
    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. – Tripartio Nov 15 '21 at 07:18
-1

If you're in a hurry, cheat: change one of the values in the first bin to a 0.

franzo
  • 117
  • 3
  • 1
    Well, it's not a solution, but it's the fix I needed for my quick and dirty analysis. Thanks! – Renée Nov 13 '20 at 08:35
-2

If you turn underflow bin on and set it to 0, the chart will start there. I don't remember if it skips multiple empty bins or shows them all, I'll have to check at the office, but data should always start on a multiple of the range + the underflow value.

SilverbackNet
  • 1,056
  • 6
  • 12
  • Setting the underflow value to 0 doesn't seem to do anything. – Kay Jan 12 '20 at 19:53
  • 1
    Did you try it before you posted this? Before I saw your answer, I thought it might work. But it didn't. – Jon Peltier Jan 14 '20 at 03:42
  • 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. – Kay Jan 15 '20 at 05:24
  • 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. – SilverbackNet Jan 16 '20 at 07:18
  • 1
    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. – Jon Peltier Jan 24 '20 at 01:51
  • does not work sadly – babipsylon Sep 21 '22 at 09:21
  • Unfortunately you need to actually have underflow data for this to work. You can add a pseudo data (-1), but then you'll have an extra bin. – syockit Sep 28 '22 at 09:41