In this walkthrough we're going to make a frequency table for the years 1951 to 1980
during the months of June, July and August. iIt's a good idea when you make tables to
put them in a separate place from your data so that's the first thing we're going to do.
We're going to create a fresh page for our table. At the bottom of the worksheet you'll
see there's a little plus sign click on this and add a new page to your worksheet file.
In column A of this new page we will put a title at the top, so we know what the table is
and then starting at row 3 type these values: -0.3, then go down - 0.25.
Excel magically understands the pattern we want to follow for the rest of the column,
so if you highlight these first two values you can drag the box down to cell A30 and it will
give you all the values right up to 1.05. Now we're going to filter their data
so that it only shows the years 1951 to 1980. First return to the tab with the temperature
data on it, click on 'Sort & Filter' in the top right and filter. This box will look slightly
different in Windows and on a Mac, so first of all we're going to do it in Windows. As you can
see small arrows appear in the cells in row 2 that are the column headings click on the arrow
in A2 and we're going to eliminate all the years that we don't want. It's too much work to
click on all the years that we want to select so click on 'Number filters' and 'Between'. In the
top box type 1951, in the bottom 1980 and click OK. Don't worry, the data for the other years
has not been deleted, it's still there but the filter has hidden it from us and now we
can only see the years between 1951 and 1980, which is exactly what we want. The operation is
the same in the Mac version but the dialog box looks slightly different so let's show you that.
You make the selections and the results are the same.
Now switch back to the second tab again: we're going to fill column B using the FREQUENCY function.
Click on B3 and drag the box down to B30. Click on the text entry box at the top of the
worksheet and type =FREQUENCY, open brackets - that's the name of the function that we are going to use.
As you can see Excel suggests to us how we have to complete this function. Excel will
automatically fill in the frequency table if we select the right cells for its accounts, even if
they're on a different page. So once more we click on the tab bottom left to go back to
our data. We want June, July, and August which are columns G to I and select all the numbers
in those columns and you can see - it's filled the reference to the cells into the frequency function.
Now type a comma and we go back to our table again and select the values A3 to A30.
Close the frequency function by typing a closed bracket. Don't hit Enter yet and it
should look like this. Now to fill in all of those cells B3 to B30, you press Control+Shift+Enter
all at the same time or if you're a Mac user that's Command+Shift+Enter.
Whether you're using Windows or Mac you should see the frequencies filling in column B like this.
But what does this mean? The frequency table shows you how many values of the data fall into the
intervals we specified. For example, in the first row the table this shows us how often
the temperature anomaly in these months and years was between -0.3 and -0.25.