How to Calculate Frequency in Excel

Last Updated: August 25, 2020

Do you have data entered into your Excel sheet and want to see the frequencies of numbers in your data? While you can use that data to create visual of the frequencies, this wikiHow will teach you how to calculate frequency in Excel with a formula.

Steps

1
Open your project in Excel. If you're in Excel, you can go to File > Open or you can right-click the file in your file browser.
- This method works for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019-2007, Excel 2019 for Mac, Excel 2016-2011 for Mac, and Excel Starter 2010.
2
Enter the bin numbers in another column. You'll want to input the top number in a range you want to check across from the data you want to check. So you'll input 20, 30, 40 if you're looking for how many numbers in your data set appear 0-20 times, 21-30 times, and 31-40 times.
- For example, you're scoring a test with the scores listed in B2-B10, so you'll input 70, 79, 89 in C2-C4 as your bin numbers. 70 will return the frequency of data that's less than or equal to 70; 79 will return the frequency of data that's 71-79; 89 will give you the frequency of data that's 80-89. (This example is taken from the Microsoft help page).
Advertisement
3
Select a range with one extra cell to display the results. You can select a range with 4 cells (one more than your bin numbers) anywhere on the spreadsheet to display the frequencies.
- For example, to continue the previous example where your bin numbers (70, 79, and 89) are located in cells C2-C4, you can highlight either cells C14-C17 or D4-7.
4
Enter the formula {{{1}}}. Since the test results are displayed in B2-B10, you'll need to make sure that data is used in your formula; C2-C4 is the range you are checking.
- In this case, your formula is telling Excel to look at B2-B10 and use the ranges in C2-C4 to calculate frequency.^{[1]
  X
  Research source}
5
Press ↵ Enter or Ctrl+⇧ Shift+↵ Enter. If the first keypress does not work for you, try the second.
- The function formula will apply to the selected cells and you'll see the results. In the example, B2-B10 contained test scores while C2-C4 contained bin numbers (70, 79, and 89). You entered the function formula into 4 cells, which shows you the frequency of scores less than or equal to 70, scores 71-79, scores 80-89, and scores greater than or equal to 90.^{[2]
  X
  Research source}