How to Construct a Frequency Distribution in Excel

Remark: All Excel Tutorials are made for working with Excel 97, though most of it will hopefully also be valid for later versions.

The easy way

  1. In the worksheet that contains your data, click on the Tools menu and then Data Analysis. If you do not see Data Analysis, then you will need to load the Add-In by following instructions on "Loading add-in programs".

  2. In the Window that appears, highlight Histogram and click OK.

  3. In the Window that now appears, opposite Input Range, either write in the range of data values of the data in the file, typically $A$1:$A$30 into the text box or click on the icon to the left of the textbox and highlight the range of data values, to tell the software that these are the values to use.

  4. From the cell at A32 to cell A37 write in 1, 2, 3, 4, 5, 6 in each cell in turn. Now in the text box opposite Bin Range, type in this range, that is type in $A$32:$A$37, or again click on the icon to the right of this text box and highlight this range of values to get the software to enter these values into the text box.

  5. Check that these ranges are in the correct text boxes in the Window.

  6. Click on OK to have the Histogram displayed in its own Worksheet.

The long way

  1. From cell A32 type in 1, 2, 3, 4, 5 and 6 in cells A32 to A37.

  2. In cell B32, make the cell active by clicking in it, then from the Main Menu click on Insert and then Function. A Window appears to choose a function.

  3. Type in COUNTIF into the top text box and Press Go. COUNTIF appears in the main text box, highlight it and Press OK.

  4. In the text box opposite Range, either type in the range of data values; $A$1:$A$30 or highlight the ikon and then highlight the range of values for this range to be entered into the text box. Make sure that the range includes the dollar values as shown. If it does not, then add them; they will stop the numbers changing when we copy this formula a little later.

  5. In the text box opposite Criteria, write in A32; do not include dollar signs as we want this to change for each copy of the formula. Click OK.

  6. Copy the formula by highlighting it and Pressing Copy in the Main Menu.

  7. Copy this formula to the cells opposite A33 to A37, that is highlight cells B33 to B37 and press Paste.

  8. You now have your histogram for your data.

To graph it

  1. Highlight the histogram values. Press the Graph icon on the main menu and then press Next as required and then Finish to display the histogram. You can right click in the graph area to then change the Chart Type, the Source Data or the Chart Options.

  2. When complete, save this as a file with name Dice01 for future use.
Download this tutorial as a pdf file.