How To Make Frequency Tables

Many types of companies use frequency tables and excel as a frequency calculator. They are a mathematical calculation that shows the distribution of responses to a question in a survey, for example.

They could also show the frequency distribution of occurrences within a data set.

For example, temperature data over the course of the year could be grouped into ranges to see climate data trends. Learning how to make frequency tables using Excel is a bit challenging at first, but it becomes very easy after you have done it a few times.

Step 1

Load your data into Excel. It is easiest to have the data in columns per question, and the responses from the different participants in rows.

For example, you might have 100 responses to a survey in your data set. Start numbering your first row with the question numbers, and the respondent responses in the first column in cell A2.

Cell A1 would be blank, but cell A2 would have the first respondent's answers to the questions going across. Cell A2 would have the first question's results, cell A3 would be the second question, and so on, to the end of the questionnaire.

Step 2

Look over your spreadsheet after all of the data have been entered, then determine the range of the data. If you have 100 respondents in your data set, you will have 100 rows of data, ending on row 101. (Remember, the first row is the question number.)

So your first column's data range will be A2:A101. Your second question will be data range will be B2:B101.

Step 3

Use the simple formula for counting. Say you have six possible responses to your first question. The formula would read as follows:

\=countif(a2:a101,1)

This formula tells Excel to count the times that the number 1 occurs in the data range found in column A from row 2 to row 101.

The formula to count all the 2's in column A would read as follows:

\=countif(a2:a101,2)

The formula for the 3's would be countif(b2:b101,3), and so on through all your possible responses to the question.

Step 4

Simplify the process by pasting the first counting formula–countif(a2:a101,1)–into the cells for the number of possible responses you have.

For example, if you have six possible responses, copy that formula into the first six cells in the area of your spreadsheet where you are doing your counting.

Change the criteria manually from 1 in the second cell to 2, and the third to 3, and so on. Once you have made all the changes to 1 through 6, put in the formula for calculating the percent distributions.

Step 5

Total the count of column results in the first cell below your count.

For example, if you are using A105 through A110 to do your counting, you would either use the sum button on the formula toolbar in Excel to sum the column, or this formula: =sum(a105:a110). You would use cell A111 to put in the formula.

Step 6

Use the following formula to calculate the frequency distributions of the results in A105 through A110, starting it in cell A112: =a105/a111). This will give you a decimal response, which you can reformat into a percentage for easier viewing.

Simply copy the formula in A112 and apply it to the five cells that fall below A112 to get the percentage distribution of all the responses.

Step 1

Create or find the data that you want to summarize.

Step 2

Determine the ranges you want.

For example, if your data set goes from 1 to 100, you would probably want to break it into 10 segments, 1 to 10, 11 to 20, 21 to 30, and so on. Let's assume your data are in column A, and rows 1 to 100.

Step 3

Type in the following numbers in B1 through B10, in a column next to the data series: 10, 20, 30, 40, 50, 60, and so on, with each number in a separate cell.

Step 4

Select 10 cells with the mouse in the column C next to the data range (column B).

Step 5

Position the mouse in the function bar above the spread sheet (where it says "fx"), then type in your formula. The formula for counting frequencies is pretty easy: =frequency(b1:b100b1:b10).

Since this is an array function, you have to hold down control shift while you hit enter. Otherwise you will get an error like "=NAME?" or something like that. If you have entered your formula correctly, the results will be shown in Column C1 through C10.

Step 6

Total the results of C1through C10 as discussed in Section 1, Step 5, but using the cells in C1 through C10.

Step 7

Use the following formula to calculate the frequency distributions of the results in C1 through C10, starting it in cell C11: =c1/b$b11). This will give you a decimal response, which you can reformat into a percentage for easier viewing.

Simply copy the formula in C1 and apply it to the nine cells that fall below C1 to get the percentage distribution of all the ranges.