The Secret To An Analysis Tool-Pakless Histogram

One of the main skills you will need to learn and master for the General Chemistry laboratory class is to make a histogram, which is a common graphical method used to visualize data. Unlike a bar chart, a histogram can actually project how frequency a certain data value appears in a given sample pool, which can be very useful when one is analyzing data. For the most part, we can create a histogram using the Histogram Tool in the Data Analysis Tool-Pak, which can be found in your MS Office 2000/2003/2007 Installation CD. It is supposedly the easiest way to create a histogram, even if you do have to re-run the tool every time you do a new simulation. However, for those of us who either 1. cannot find the MS Office installation CD, or 2. are coping with the hidden menu options that are one of the new features of Office 2007, there is another way to make a histogram in Excel of any version. There are two steps involved:

Step 1: Create an array of bins
For starters, we need to create a dynamic array of bins, or an array of N evenly-spaced numbers. Consider the figure below:



In order to create a dynamic array, you have to enter the formulas below. You can really use any cells in your spreadsheet, depending on what your table looks like. You don't have to use the same cells as me.

In cell D1, I have entered my minimum value, which you can find by looking over your data. Likewise, in cell D2, I entered in my maximum value. Therefore, I entered into the following cells:

E5 =$D$1
E6 =E5+($D$2-$D$1)/10

I then copied this formula from E6 to E15. This gives me a dynamic array of 10 bins, which is what you need for the histograms you make for this class.


Step 2: Using Excel's FREQUENCY Formula
The figure below is a screenshot of my spreadsheet with my sample data set. I won't go into what exactly the FREQUENCY function is in Excel, since for those of you who are curious enough to do so, you can always use the Help menu for such purposes. One thing I will point out to you though is that you will need to select the cells you want to have your frequencies projected (in this case, F6 to F15, since it matches with my bins cells) BEFORE you enter your FREQUENCY formula. In addition, because this FREQUENCY function is an array function, after you enter the formula, you will need to press Ctrl+Shift+Enter.


FYI, the basic formula for the FREQUENCY function is: FREQUENCY(data_array,bins_array) First, I selected cells F6 to F15.

Second, I entered my array formula into F6: =Frequency(A2:A28,E5:E15)

Lastly, I pressed Ctrl+Shift+Enter


Creating the Histogram Chart
To create the histogram chart, simply create a bar chart using the Chart Wizard. Choose the Bins column for the Labels and the Count column as the Values.

Hope this helps, and happy graphing!