CODEWITHSUNDEEP
excel
Logan
Logan

Reputation: 49

Is there a way in Microsoft Excel to give specific bins different bin widths when making a histogram plot?

I am trying to give specific bins on my histogram plot different bin widths. Is there a way to say I want bin 1 to have a width 1-10 and bin 20 to have a width of 300-1000?

Upvotes: 3

Views: 7458

Answers (1)

Viet Than
Viet Than

Reputation: 320

Excel builtin histogram tool only allows equal bin-width. We must create instead a "variable width column chart" as explained by Jon Peltier. This can be a tedious and error-prone process if you've got a lot of bins. Video tutorial for Excel 2016. The main steps are as followed:

  1. Create a cascade table:

Input table

should turn into:

Cascade table

Note: dummy + Label columns aren't required, but helps with labeling

  1. generates an stacked area chart (a type of Area chart)

stacked area chart

  1. Then change the Primary Axis's category to Time-Scale to straighten the areas into bars. As explained by Jon Peltier, this is because:

This is somewhat misleading, as Excel time-scale axes only consider dates and ignore times.

straightened columns

Upvotes: 3

Related Questions