Histogram Microsoft Excel Mac

Posted on by

If you’re using Excel 2016, you get the luxury of using Excel’s new statistical charts. Statistical charts help calculate and visualize common statistical analyses without the need to engage in brain-busting calculations. This new chart type lets you essentially point and click your way into a histogram chart, leaving all the mathematical heavy lifting to Excel.

A histogram is the best chart you can use to illustrate the frequency distribution of your data. Before Excel 2016, making a histogram is a bit tedious. But now, you can make one in a matter of seconds. Oct 22, 2012  The instructions here are for Excel 2013, but histograms can be created in prior versions of Excel in a similar fashion. The screenshots and directions presented here are from Excel 2013; however, previous versions of Excel can easily create histogram through these same methods. Excel 2007, 2010, and 2011 for Mac all have tested using this.

To create a histogram chart with the new statistical chart type, follow these steps:

  1. Start with a dataset that contains values for a unique group you want to bucket and count.

    For instance, the raw data table shown here contains unique sales reps and the number of units each has sold.

  2. Select your data, click the Statistical Charts icon found on the Insert tab and then select the Histogram chart from the drop-down menu that appears.

Microsoft

Note that you can also have Excel create a histogram with a cumulative percentage. This would output a histogram with a supplemental line showing the distribution of values.

Excel outputs a histogram chart based on the values in your source dataset. As you can see here, Excel attempts to derive the best configuration of bins based on your data.

Excel auto-generates a histogram based on its own derived bins.

You can always change the configuration of the bins if you’re not happy with what Excel has come up with. Simply right-click the x-axis and select Format Axis from the menu that appears. In the Axis Options section (see the following figure), you see a few settings that allow you to override Excel’s automatic bins:

Histogram Microsoft Excel Mac

  • Bin width: Select this option to specify how big the range of each bin should be. For instance, if you were to set the bin width to 12, each bin would represent a range of 12 numbers. Excel would then plot as many 12-number bins as it needs to account for all the values in your source data.

  • Number of bins: Select this option to specify the number of bins to show in the chart. All data will then be distributed across the bins so that each bin has approximately the same population.

  • Overflow bin: Use this setting to define a threshold for creating bins. Any value above the number to set here will be placed into a kind of “all other” bin.

  • Underflow bin: Use this setting to define a threshold for creating bins. Any value below the number to set here will be placed into a kind of “all other” bin.

    Configure the x-axis to override Excel’s default bins.

The next figure illustrates how the histogram would change when the following settings are applied:

Make a Histogram in Microsoft Excel 2016 for Mac

A histogram displays the frequency values in a proportional graph.
You're going to need some data to work with. Here's the data used in the example below. Type this into a blank worksheet:
Follow these steps to make a really great looking histogram.
1. Select any cell within the range of cells that includes the data.
2. Click the Inserttab on the Ribbon.
3. In the Charts group on the Ribbon, click the Recommended Charts button.
4. Choose Clustered Column chart type.
5. Click once on any of the columns measuring Value. In this example, the Value row is represented by the taller columns. Little round 'handles' will appear on all of the columns to indicate they are selected. Your chart should look something like this with the Value measure selected:
6. Press the Delete key. Values no longer display and Frequency columns remain visible. Now your chart should look like this:
7. Next, we need to put the proper values in the x-axis. From the Chart Design tab of the Ribbon click the Select Data button. The Select Source Data dialog opens. If you don't see the Chart Design tab in the Ribbon, you clicked away from the chart. Click anywhere on your chart to activate the Chart Design tab.
8. Note that the Horizontal (Category) axis labels field in the dialog is blank. We need to fill this in. Click the little button to the right of the empty field.
9. Drag over the cell range that has your values, but do not include the data label. Then press the Return key. Using our example, you would select the range B2:H2. Excel fills in the dialog box for you, but you might have to click into the empty field to get the display to refresh.
10. Click the OK button to close the Source Data dialog box.
11. You can click on the Frequency label and press delete if you want to tidy things up. Your finished chart will look about like this:

If you're making a histogram for a course, your instructor may be anal-retentive. If you're unfortunate enough to have one of these ultra-picky types, you're not done yet. To make your teacher happy you'll have to get rid of the gaps between the bars.
Here are the additional steps to take:
12. Click once on any of the columns so that they are all selected.
13. Right-click on a column and choose Format Data Series from the pop-up menu. The Format Data Series pane will open.
14. In the Format Data Series pane, adjust the Gap width to 0%
15. Click the OK button.
Your chart should now look like this:
If you really want to impress your teacher, apply different formatting options. Here's the same chart after formatting was changed. Well, maybe this isn't better. I am sure you can do a better job!

How To Create A Histogram In Excel

Alternative Methods for making histograms

Excel Histogram Tutorial

Mike Middleton has a free add-in that makes Histograms:
http://betterhistogram.betteraddins.com/free-download/
The Excel store has a free trial of a Javascript add-in called Data Bucket Chart. Here's how to try it:
1. On the Insert tab of the Ribbon click the Add-ins button
2. Click the Store button
3. Click into the Search field and type Histogram then press Return
4. Click the Buy button
5. Follow the instructions to add this add-in to your add-ins collection

Histogram Microsoft Excel Machine