Right-click on anywhere on the vertical axis and select ‘Format Axis’ from the dropdown. This will open a window on the right-hand side of your screen. Then, change the ‘Maximum’ value to 50 and press Enter. Of course, you can also change the number of bins and the bin width of your histogram. I have no idea why the horizontal bins can't show a single number to represent a size of one or why I can't get rid of the superfluous vertical grids. The horizontal axis settings are: Bin width 1.0 why the decimal place?, Number of bins 56, Overflow bin 76.0, Underflow bin -13.0, Number category General, Format code General.
The Importance of Histograms and Descriptive Statistics
The word “statistics” comes from the German word “stadt”. Stadt means “city” but in the 18th century each city was a state in its own right. And the people who ran those states needed to have the facts. What is the population? What is the food production? How many men are available for the military? What is the birth rate? Information about the state came to be known as Statistics. In today’s complex world people do data mining, gather big data, and perform tests of statistical hypotheses. It is easy to forget that one of the primary tasks of statistics is purely descriptive.
Calculating descriptive statistics such as mean, median, and variance is easy, since Excel has functions for this purpose. Let’s look at something a little more complicated but a necessary tool in the statistician’s toolbox, the frequency distribution and its graphical comrade, the histogram.
How To Modify Bin Width In Excel On Mac
Creating a Histogram using Microsoft Excel
We’ll stick with the centuries-old tradition and gather some information about cities, starting with a table of income information about cities in the United States from the year 2009. We would like to observe the frequency distribution of median family incomes. The Excel data analysis toolpak has a nice dialog box for doing this automatically, but we’re going to take charge and do it ourselves using the FREQUENCY function.
We must first define the bins, which is to say the value ranges into which our data will be sorted. Generally this is done in a worksheet column, and Excel’s auto-fill feature makes this easy. In this example we will create bins from 10,000 to 170,0000 at increments of 5000.
Now we are ready to tabulate our frequency distribution. We highlight a second column next to our column of bin values. In the formula bar, we enter =FREQUENCY(. The first argument is the range containing our data. If you enter this range by highlighting it on the worksheet, be careful not to include the column heading. Only the data is highlighted. The second argument to FREQUENCY is the range containing the bin values. After entering the range in the formula bar close the parenthesis, but
Do not hit enter!
Highlight the range where the frequency results will appear and enter the frequency function.
Enter both the data ranges and the frequency bin range.
You’re almost ready but
Do not hit enter yet!
FREQUENCY if one of the many valuable Excel array functions. Mr. Excel calls them “CSE” functions, and this name is well chosen. The CSE acronym reminds us that you enter array functions not by hitting enter but by hitting <ctrl><shift><enter>. When you hit control-shift-enter, the formua will be entered in the highlighted cells. Note that in the formula bar, the function is now within curly braces. You cannot just type in these curly braces yourself. You must let Excel enter them after you type <ctrl><shift><enter>.
Now that we have our frequency distribution, it is impossible to resist creating a chart. The frequency range is already highlighted; we have only to click on “column chart” from Excel’s Insert ribbon tab. The chart looks OK, but we see that we will have to edit the horizontal axis label to reflect our frequency bins. We can right-click on the chart and choose “Select Data…” . Click the “Edit” button on the right-hand side, the side for the horizontal axis labels. Then enter the range containing the frequency bins.
Right-click the chart and choose “Select Data…” to edit the axis labels.
Right-click the chart and choose “Select Data…” to edit the axis labels.
After entering a title and choosing a style, we have our finished frequency histogram!
I hope you enjoyed my first Excel post! Stay tuned for my next one where we cover Standard Deviations.
In a previous post I demonstrated how to create histograms using PivotTables.
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?How To Change Bins In Histogram Excel Mac
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.
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
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
How To Modify Bin Width For A Histogram In Excel Mac Free
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.
This is a helpful tip because, as I mentioned, sometimes the logic of your data warrants unequal bins.
How To Modify Bin Width For A Histogram In Excel Macro
Now you’ve got one more tool to bend Excel to your will.
How To Modify Bin Width For A Histogram In Excel Mac 2011
Please subscribe for more will-bending data insights.