Creating a Custom Histogram Module

Click to Download Workbook: Histogram

A histogram is synonymous with a frequency distribution. That is, we simply are plotting the number of times something occurs within a range on a chart, typically a bar graph. These ranges are called bins. We can create a histogram with Excel’s Analysis ToolPak Histogram feature. We can start by pasting in a random data set that constitutes one column. In this case, I pulled some prices for the S&P 500 off of Yahoo Finance. This will be adequate for a simple demonstration.

Note we must install the Analysis ToolPak first in order to use it. To do this, click File, then scroll down and click Options. Click Add-Ins, then click Go. You will see this window pop up:

Make sure the first two items are selected from this list. We are now all set to create a histogram. Navigate to the Data tab on the ribbon, and click on Data Analysis. Next, find the Histogram feature. We will arrive at this window here:

Our input range will be our data range. We will output this to a new worksheet. Note we have options for the output of our histogram at the bottom. Don’t worry about that for now. Click OK and then view the added page to the workbook.

We can graph the outputted bin range and frequency. Note that we had Excel choose the bins for us in this scenario. We could define our own bin range in a column and pass them into the bin range entry if we wanted to run the Histogram application again. So when we do this for the range specified below, we are saying for the first bin of 50, we are looking for the count of all of the numbers greater than or equal to 0 and less than or equal to 50. Likewise, for 150, we are looking for numbers greater than or equal to 50, and less than or equal to 150. We will autofill the range and let the Histogram application do the rest. Let’s try doing that, shall we?

Indeed, this Histogram application makes it easy to assemble a frequency distribution of our data very quickly. We can also make our own module for fun that I believe is a bit more readable and concise. I view bins as ranges, and I want them outputted to the graph as a range. Unfortunately, we will have to use VBA for this, but the good news is it is a very simple concept that won’t take much time at all to develop. The code is displayed below.

To start, we need to define a few variables, including the step size of the graph, the initial value we will start at, and the total number of bins we wish to display. We will have the user enter those on the worksheet. Of course, we need to have the data input range dynamic, so we will count the number of rows in the range and pass it into the necessary ranges as a variable. The for loop will entail looping through each bin creating a label and formula for that bin. The COUNTIFS() function will be used to count the number of occurrences within the specified range. Next, we will create a dynamic chart by passing in the last row of our created bin label and value range. Note we can modify specific aspects of the chart, such as the labels and the position of the chart in the worksheet. I also put in a form control button to easily run this macro.

Here is our output:

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *