Thursday, January 9, 2014

Histogram in Excel

I’ve had the need to create a histogram in Excel on occasion and every time I’m forced to relearn the steps.  I recently came across a question on regarding a histogram and decided to create this guide to remind myself and perhaps help others.

The original question on asked how to create a histogram in Excel with the x-axis as the percent distribution.

I'm sure there are many ways accomplish this task, and this is one approach that uses the frequency function and an array-table.

Let’s assume the data is already a percentage and we want the x-axis to represent 10% increments from 10 to 100.

The data could represent anything.  The next step is to create our bins.

Notice that the label for Bins is to the left and not above; this is to let Excel know that Bins is an axis and not a data set when we create the graph later.

My spreadsheet now looks like this:

Now that we have our data and our table, we will use the Frequency function to create the data for our histogram chart.

In cell C:26 enter, =FREQUENCY(B2:B21,B26:B35) and press enter.

It should look like this:

**Magic happens here!**

Now we use the Excel magic known as an array formula.  The first step is to select cells C26 through C35 and type F2 on Windows or CTRL-U on a Mac to enter edit mode:

Now simply press CTRL+SHIFT+ENTER to make this an array formula and Excel does its magic!

Let’s add a label above the data and we are ready to create our histogram.

Now select cells A:25 through C35 (everything you see here) and create a clustered column chart.

And there you have it.  You can dress the chart up in a number of ways.  For example, I like to remove the y-axis.

You can also add percentages to the bars by creating a % of total column and charting that instead of the count:

And the chart looks like this:

There you have it: a simple way to create a histogram in Excel.

No comments:

Post a Comment