1. Change Bin Width Histogram Excel
2. Modify Bin Width For A Histogram In Excel Formula
3. Modify Bin Width For A Histogram In Excel
4. Modify Bin Width For A Histogram In Excel Using
5. Modify Bin Width For A Histogram In Excel Spreadsheet

In a previous post I demonstrated how to create histograms using PivotTables.

How To Modify Bin Width For A Histogram In Excel Mac 2017. How To Calculate Bin Width For A Histogram. Click the heading to the column you want to analyze. Press Ctrl+Shift+down to highlight all the cells of the column. PROTIP: Specify a range name (such as “Priority”, etc.) so you can refer to the same range in several functions. If you use the Histogram tool (Analysis ToolPak) and specify bin values that are not equally-spaced, Excel will create the frequency distribution correctly, but the Histogram chart will display equal-width bars representing your unequal-width ranges, so the Histogram chart will be distorted.

I like this tip so much that I recorded it as my favorite tip of the year for the MyExcelOnline podcast.

There is a shortcoming to this approach, however. By plotting a histogram in Excel using PivotTables, all bins must be of the same width. What if you wanted unequal bins?

Excel provides a tool to make this kind of histogram, using an array formula.

(Note: For this exercise you don’t really need to know much about array formulas, but they are worth checking out. I suggest Mike Girvin’s book Ctrl+Shift+Enter: Mastering Excel Array Formulas to learn more.)

Let’s get started. Download the exercise file here.

1. Mark your bins…

If you plot a histogram using either Excel’s built-in charting or from a PivotTable/PivotChart, you must group the bins by equal increments (e.g. 1-5, 6-10, 11-15, etc.)

This might not work for your analysis, for different reasons. In our example, we know that the majority of our data falls between 1 and 10. So we want smaller-sized bins for this range and wider bins for the upper reaches of our data.

To set our own bins we will type in our desired increments. I put mine in column E of the exercise file.

This is a little confusing at first so I provide an interpretation of what these bins mean in Column D. Each bin ends at the stated value and starts from where the last bin left off.

Change Bin Width Histogram Excel

Excel will automatically fill in any remaining values that don’t fit into the last bin. This is cell F12 in our example. So cell E12 is just marked as helpful additional information.

2. Create a frequency table using the FREQUENCY array function

Modify Bin Width For A Histogram In Excel Formula

Next we will get a count of the frequencies of each bin in our data using the FREQUENCY function. This takes two arguments: our data array and our bin array.

Highlight where we want our frequency table to go (next to the bins) and fill out that formula just like you normally would.

Important! You probably press Enter after filling out most formulas in Excel. This time, press Control + Shift + Enter to complete the FREQUENCY function. That’s because this function is calculating out over an array of cells rather than just one. (I won’t go into it more than that, so please check out Mike Girvin’s book above if you are interested. It’s a great Excel skill to have.)

Now we have a frequency count of our bins.

3. Create a bar chart of our frequency table

Now to plot our histogram, we will go to Insert and select a bar graph.

Usually columns in a histogram are rather close to each other. To modify width between the bars, right-click on any of the bars. In the drop-down menu, select Format Data Series. Then reduce the Gap Width to a small amount.

Modify Bin Width For A Histogram In Excel

This is a helpful tip because, as I mentioned, sometimes the logic of your data warrants unequal bins.

Modify Bin Width For A Histogram In Excel Using

Now you’ve got one more tool to bend Excel to your will.

Modify Bin Width For A Histogram In Excel Spreadsheet

Please subscribe for more will-bending data insights.