Histogram Bins Issue

Copper Contributor

Hi, 

 

I have my histogram inserted and setup with 4 bins grouped into a width of 20. The issue is that the groupings of 20 do not start and end on the numbers I want them to.  For example, the first bin excel created has the range of 146 - 166 while I need to count numbers in between 140 - 160. Please see the attached file for reference. Please help. Thank you.  

5 Replies

Hello,

 

insert a pivot chart with grouping starting at 140 and ending at 220 by 20.

 

Hello,

 

The histogram chart only works with existing values. The lowest value is 147, so that will be the start of the first bin. If you want the bin to start with 140 you need to change the lowest data point to 140.

Hi. This data is simply 1 array. I can convert it to a pivot table, but no chart. Please advise.
Hi,

If I add a lower data point it will alter the data set. Please advise.

I realize it will change the data set, but in chart preparation we often have to use these kinds of tricks to arrive at a desired chart. Since the histogram has no setting to define individual bin start points, manipulating the source data is the only way to go.

 

If you want to use a pivot chart instead, follow these steps:

 

  • Select the data from B4 to B14 and click Insert > Pivot Chart
  • drag the Data field into the rows area and again into the values area
  • In the values area, change the value field settings from Sum to Count
  • select a cell in the first column of pivot table that feeds the chart, right click and select "Group"
  • set the option to "Starting at" 140 and "By" to 20, leave ending at blank

let me know if that helps