Lab 1: How to Make a Histogram in Excel

Make sure the Analysis ToolPak Add-in is active (follow the directions above if needed).

1. You must choose an appropriate bin size for your data set. The bin size refers to the range of values that fall into a specific class. For example, if your data set is ages of people in the United States, set your bin size to 10 years. This makes the bins correspond to ages 0-10, 11-20, 21-30 and so on, with a maximum bin of 101-110. Select a bin width that creates five to 20 groups of data.  For the earthquakes data, we will use 10 years.

Type the bin widths (from step 3) in column K. In row one (cell K1) add the label Decades.  Starting with K2 enter the years begin with 1909 then 1919, up to 2019.

1. At the top of the page click the Data tab

Under Analysis click Data Analysis

1. Under Analysis Tools select Histogram and click OK.
2. First select the data to create the histogram from. To the right of the box labeled Input Range click on the icon that looks like a little spreadsheet.  A small window pops up.  From the Earthquakes worksheet select the entire D column by clicking on the D at the top of the column and then hit Return.  The Input Range should read $D:$D

Repeat the process for Bin Range only this time, after the small window pops up, change to the Bins worksheet. Select all of the rows in you bin column.  If your data starts in cell K1 and ends in cell K13 with the mouse, left click on K1 and while holding the left mouse button down drag the selection box to cell K13 then release the mouse button. Hit Enter. The Bin Range should look like $K$1:$K$13

Make sure the Labels box is checked, your data has labels in Row 1.

Under Output Options, select the radio button New Worksheet Ply

Select “Chart Output” in the output options section to generate a histogram graph.

1. Click “OK.”

A new worksheet (Sheet1) will be created that has the histogram data labeled Decades and Frequency. If you attempt the histogram creation multiple times, the sheet name will be incremented; Sheet2, Sheet3, etc.)

Delete the More line (line 14) by selecting the entire row (click directly on the row label, the number 14) right click and select Delete.

On the graph, click on the word Frequency.  A box will appear around the word.  Right click on the box and select Delete.  Alternatively, you can just use the Delete key on the keyboard.

1. Next we want to modify the Decades column. This will change the labeling of the x-axis on the graph.  For the first line of information replace 1909 with ‘1900-1909 (be sure to include the single apostrophe at the start, it indicates characters and not math operations follow).

In the remaining rows replace 1919 with ‘1910-1919, 1929 with ‘1920-1929, etc. on down to ‘2010-2019. This indicates that the histogram is showing he number of earthquakes in the years 1900-1909, 1910-1919, and so on.

You will need to enlarge the graph. Left click on any white-space in the graph.  You will see small boxes surrounding the graph.  Drag the middle, bottom box down enlarging the graph until the numbers 1910-1919, etc. are clearly visible.

1. Next, modify the appearance of the plot by selecting any of the histogram bars, pressing the right mouse button, and clicking on “Format Data Series…” A pane will open to the right.

Under Series Options set the Gap Width to 0%.

Close the pane by clicking on the x in the upper right corner of the pane (not the x in the upper right corner of the window).

When completed, upload your completed Excel spreadsheet here.  Your spreadsheet must have all the work you did to create the chart and the final chart. Your chart should look like the one below.