The relative frequency of a sequence of numerical data allows analyzing and making mathematically sound conclusions about a particular distribution or phenomenon. This is one of the most popular features used by millions of Excel users in the world.
To understand how to calculate the relative frequency in Excel, you should only take a few simple steps. We will also show how to build a colorful and informative histogram based on the data received. With the help of it, you can present the results in an understandable and convenient way.
First step: How to calculate the relative frequency in Excel:
01. Launch Microsoft Excel and create a new spreadsheet.
02. Fill the “A1” and “B1” cells with the data titles.
03. Fill in the columns “A” and “B” with the required numeric values starting from the second row.
04. Then we enter a designation of the relative frequencies Δw in the “C1” cell.
05. To calculate the relative frequency, we enter the following formula for calculating
=B2/SUM($B$2:$B$16)
in the “C2” cell:
06. Then you need to drag the mouse cursor from the “C2” cell down to the “C16” cell with the left mouse button being pressed.
Second step: How to make a relative frequency histogram in Excel
Without deselecting, go to the “Insert” tab on the top panel and select “Charts” and then “Clustered Column”.
07. Now, you should click with the right mouse button on the histogram area and select “Select Data”.
08. Select the range of cells A2:A16 and click “OK”. The chart values on the OX axis have changed.
09. Next, click “OK”.
10. Double-click on the histogram title area (Δw) and change it to our “Title Chart”
11. Click on the horizontal line and in the window opened, go to the line settings and put “No Line” on the “Format Major Gridline Options” panel.
12. Make a title for the OX axis: go to “Chart Design”, then to “Add Chart Element” and select “Axis Titles”, and “Primary Horizontal”.
13. Make a title for the OY axis in a similar way.
So, you have learned how to calculate the relative frequency in Excel for a series of data. You can do this in a Microsoft spreadsheet in just 5 steps and a few seconds. We recommend using histograms to make it easier in perceiving, analyzing the results and presenting them to other users. They are also conveniently built using the Chart Wizard in Excel.
