How to adjust bin sizes/intervals
Excel calls the range (like the age range 7 to 17 years) a bin. This bin size (age range) doesn’t necessarily have to be 10 years. You can set it to any interval – 15, 20, 30, or any number of years.
But how can you do that? Learn it here.
The above graph has the bin size set to 10. To change the bin size:
- Double-click on the graph. This will launch the Format pane to the right of your worksheet.
- Go to Horizontal Axis from the drop-down menu of options as shown below.
- Click on the icon for Axis.
Here you have the option for Bin Width.
- Change it to any value as desired.
For example, let’s set it to 15.
Your histogram takes a whole new look like that.
Note how the bin sizes (age ranges) have now changed to 7 to 22 years, 22 to 37 years, and so on.
Why does the range start from 7 only and not a round number like maybe 5 or 0? 🤔
Take a quick look at our data set for this histogram. The smallest data point (age) that we have is 7, and so, that is where the range starts.
Adjust the Number of Bins
In addition to the Bin size, you can also adjust the number of bins.
In this case, you fix the number of bins (bars) that you need on the graph, and Excel calculates the bin size itself.
- To do so, check the option for “Number of Bins”.
- Set the “Number of Bins” to any number.
We have set it to 6.
And there you have 6 bins to your graph now. Excel has automatically set the bin size to 10.1666.
But the bin size on the horizontal axis is now weird. Excel has taken the decimal places too far.
- To adjust this, go to the Number menu just below.
- Set the category to “Numbers”.
- Turn the decimal places to 0 or any number as desired.
And you have your bin sizes adjusted.