How to change bin number/width in a histogram in Excel for Mac (Office 2020)

Copper Contributor

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

@LucaPellegrini 

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. 

@Echeban HI Ethen. I saw the pic you uploaded. But I still can't find where to modify 'bin width' under the tab 'Axis Options'. 

KaiChang_0-1615497146506.png

 

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. 

@PaoWaLee 

 

I still do not have the option to adjust my chart's bins to what I want. I do not get the window at all. I have Microsoft 365. I've looked all over the internet for 2 hours.

Oh nevermind. I saved it, closed out, reopened and there it is.
/sigh
As Ruthbain says below, my bin width option will not appear. Any suggestions? I've tried saving the chart, closing, and re-entering Excel.

@vantonich lmao, the assignment is due tmr and we're both at a lost. I cant find the solution anywhere.

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

@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..."

@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 SeriesSelect Format Data SeriesChange BinsChange Bins

This worked for me! Thank you!

It would be great if the help told you this. would have saved me about 2 hours @rpill99 

Hello@LucaPellegrini this was very helpful!

Do you know by any chance how to do this in a waterfall chart? 

 

Thank you