Forum Discussion
How to change bin number/width in a histogram in Excel for Mac (Office 2020)
Hello,
I have created a histogram and now I want to modify the width and number of the bins. Can someone please advise? When I select 'format axis' there is no option to modify bin (see attached).
I have seen various videos and web pages that show how to do this in Excel 2016 and later, so I am confused as to why bin width/number is apparently hard wired in my Excel (2020 Microsoft 365, Excel version 16.41).
Thank you for your help.
15 Replies
HelloLucaPellegrini this was very helpful!
Do you know by any chance how to do this in a waterfall chart?
Thank you
LucaPellegrini I use Excel for Mac, and I came across the same issue of not being able to change the bins, as that option was not seen when clicking the Format Axis tab. So the solution to this, which I accidentally discovered, is that if you right-click on the histogram bars, select Format Data Series... Now you will get a window open with the option to change the bins. Select Format Data SeriesChange Bins
LucaPellegrini On Excel for the Mac, right-click (mouse) or two-finger click (track pad) on the data series inside the chart and choose "Format Data Series..." You'll find the BIN settings in the Options tab, in contrast to Excel for Windows where the BIN settings are part of "Format Axis..."
- I was struggling with this today and it turned out I had actually selected a column chart, not a histogram, which was why the bin options weren't displaying. Hoping that leaving this comment saves someone else the same frustration...
- I figured it out!!!!!! Okay so Select the Actual Data Series (The bars in the histogram), and it'll give you an option to format it. From there you can adjust the bins! -There's also a tutorial on youtube that shows how to do it. https://www.youtube.com/watch?v=wmCIEwKO5no
Hi, LucaPellegrini , I just figure it out.
1. Right-click on your data area
2. Select format data series
3. Select options
4. Select bin width from the drop-down menu
I hope it works for you.
- As Ruthbain says below, my bin width option will not appear. Any suggestions? I've tried saving the chart, closing, and re-entering Excel.
- Oh nevermind. I saved it, closed out, reopened and there it is.
/sigh
Found the answer: Select your histogram chart by clicking on one of the bins. The options to modify the bins will be available under the histogram tab in the Format Data Series panel on the right. So nothing to do with 'Format axis'...
Hope this helps someone with the same question.
Luca
LucaPellegrini just to clarify, in Office 365 select the number of bins, bin width, etc., under FORMAT AXIS > AXIS OPTIONS > bin width, etc. Click on the AXIS for the menu to appear on the right.
- This worked for me! Thank you!
