Can't Set Histogram Bins Correctly in Excel 2016

[Freddie_deBoer]

Hey guys, I have a persistent problem with Microsoft Excel histogram that drives me crazy. I will have a row of data that represents a row of choices from a survey. (In the attached example I have marked them 1-5 for Very Unsatisfied to Very Satisfied, sometimes I do Strongly Disagree to Strongly Agree, etc.) When I use the histogram function in Excel 2016, the program never sets the bins correctly, and trying to set them manually doesn't work either. I want bins of 1, 2, 3, 4, 5, and for the bars to represent the counts of each. But Excel will automatically give me numbers like 2 - 2.74, 2.74 - 3.48, etc. I try to set bin width to 1.0, and it only gives me three thick bins labeled (2,3), (3,4), and (4,5). I try to set the number of bins to 5 and it gives me bins from 2 to 2.26, from 2.26 to 3.2, etc. See attached images. I'm pulling my hair out and would love advice.

[Dissitesuxba11s]

It's doing this because it thinks each row is a seperate point with a value of whatever is in the cell. You can use...

=countif(range,criteria)

...where range is the data range and criteria is the value you are counting (ex. 2). Make 5 cells of countif functions for values 1-5. Then instead of using a histogram, create a 2-D graph of the 5 cells.

 

Example:

Spoiler