GGRA30: GIS and Empirical Reasoning
Excel Intro: Pivot Tables, Frequency Tables and Charts
Practical #2 (Week 3)
Instructor: Glenn Brauen
Department of Human Geography
University of Toronto Scarborough
Fall 2023
Contents
1 Learning Objectives 1
2 Data Provided 1
3 Procedures 2
3.1 Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
3.2 Frequency Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
3.3 Graph it! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4 Sample Questions (See Quercus for Your Questions) 7
1 Learning Objectives
This exercise provides you with practice using Excel to:
• create pivot tables to aggregate and summarize data.
• create frequency tables and charts.
2 Data Provided
The data provided for this practical contains weather data from Pearson Airport, 1940–2012. It is avail-
able for download from Quercus and is the same data you used in week #2, except that the Mean Temp
column has already been completed for you.
File name GGRA30_Tut3_WeatherStation.xlsx
1
Practical #2 (Week 3) GGRA30 Fall 2023
3 Procedures
As for Practical #1, you will be asked to provide values from your work with Excel for your Quercus Quiz.
Your values should be rounded to one (1) digit after the decimal. See the instructions for Practical #1
if you need a reminder for how to make Excel do the rounding for you. Look at the Quercus quiz now
because you will want to start answering questions partway through Section 3.1.
3.1 Pivot Tables
Pivot tables allow you to selectively filter and aggregate data, creating a cross tabulation for comparing
subsets of data out of a raw data table. In this part of the exercise we will make a table showing total
precipitation for each month, averaged over the years 1940-2012.
1. The Excel sheet as provided contains no filter. Double-check and turn off any filters if any are on.
Show all of the data.
2. Select all the data in the table. Click on the cell containing “Date” at the top of column A, then
scroll to the bottom of the table and Shift-Click on the last entry in the “Total Precip” column
(G).
3. Using the INSERT tab of the ribbon, Click Pivot Table. A pop-up dialogue will appear on your
excel sheet and should already show the cells selected.
Notice that the pivot table option is in the “table” section of the ribbon and this may
be collapsed if your Excel window is narrow. This option is similar to but different from
the “PivotChart” option. These instructions were written for the former.
4. Ensure that the option to create a new worksheet for the pivot table is selected. ClickOK to create
the pivot table. A new worksheet will be created and you will see a configuration panel for pivot
table fields. See right side of Figure 1. This is used to configure how the pivot table will categorize
and summarize the data. We are going to examine the precipitation data from the original table.
All columns of the original data table are listed as fields you could use.
5. We want to calculate the averages from the data grouped by each month of the year. Drag the
field “month” from the list of fields to the Rows section of the pivot table configuration in the
side panel. These will be our categories and will organize the rest of the data into summary rows.
• The pivot table should now contain a single column showing all unique values (1–12) from
the month column of the original data, representing each of the months January–December.
6. To average total precipitation for all of the data, grouped by month, drag “Total Precipitation”
from the field list into the Values section of the pivot table configuration.
• What will appear in the values field will be a tab labelled with some method of calculating a
summary of the total precipitation values from the original table: sum, average, count, etc.
This allows you to reduce all of the raw precipitation values that fit each category to a single
value.
2 of 8
Practical #2 (Week 3) GGRA30 Fall 2023
Figure 1: Pivot Table showing sample result and corresponding configuration panel. The
configuration panel to the right defines how the pivot table will group records and the
calculation that will be used to summarize the record values that meet the grouping cri-
teria.
7. Click on the down arrow on the total precipitation entry in the values area of the pivot table
configuration and select “Value Field Settings.” A dialogue will open and you can configure the
summary function you would like to use. Click on count. Press OK.
• The pivot table now contains a summary of the number of entries in the original table for
each of the monthly categories (January–December) as shown in Figure 1. What do these
count values represent? Do the numbers make sense?
• Ensure that you understand the configuration of pivot tables because you will need to set
one up for Assignment 1:
– Fields listed in the Rows section of the pivot table configuration identify the column in
the original table that provide the unique values with which the table is structured.
– Change the configuration of the table by moving the Month field to the Columns sec-
tion of the pivot table configuration. What happens?
– Reconfigure the pivot table to summarize the count of total precipitation values grouped
by year, with each year shown as a row. What summary values are now shown? Do they
make sense?
• Before continuing on, reconfigure the pivot table to show a column of month identifiers and
a column summarizing the total precipitation field for each month (similar to Figure 1). It
does not matter which summary calculation is used — we’ll be changing that in the next
step.
3 of 8
Practical #2 (Week 3) GGRA30 Fall 2023
8. Open the Value Field Settings for the total precipitation field in the values section of the pivot
table configuration again. Choose “average” from the summary functions list and press OK.
• What do the values in the pivot table represent now?
9. Select the entire pivot table in the worksheet, including the column headers and all 12 data rows,
but not the totals row at the bottom of the table.
10. Using the INSERT tab of the ribbon, create a vertical bar chart (or a line chart). What does this
show?
Pivot tables are one very useful means of summarizing raw data fairly quickly. For more information
on pivot tables in Excel, see GCF (2022).
3.2 Frequency Tables
To demonstrate frequency tables, we are now going to compute the total amount of precipitation mea-
sured for each year, 1940–2012, and summarize that by developing a frequency table showing the range
of data values and howmany times similar values were observed.
In Excel, the frequency function calculates how often data values occur within a set of separately
defined data ranges, and then returns a vertical array of counts showing, for each range defined, how
many observations in the original data are in that range. The total of the frequency counts should
match the total number of values in the original table. Because the frequency function returns an array,
and is considered a legacy array function, it must be entered explicitly as a CSE formula (Microsoft
2023). The steps to do this are explained below.
1. Change the pivot table to compute the sum of the total precipitation values, grouped by year.
To do this, replace the “month” field in the rows area of the pivot table configuration with the
“year” field. Change the Value Field Settings for the total precipitation values to sum the grouped
records.
• If the pivot table is not selected, you may not see the configuration panel. To redisplay the
panel, click on any cell in the pivot table or click on the chart.
2. To set up our histogram, we must define a bins array that describes the data ranges into which
we want to subdivide the data. This is a column of values in which each value defines the upper
limit of the data range defined as part of the category. This array and the others can be defined
anywhere on your sheet but the instructions below assume that you are setting up bin ranges,
labels, and the calculated frequencies in columns E, F and G starting with cell E3.
(a) In cell E3, type Total Precip, annual (mm)
(b) In cell E4, type 50. This means that the first bin will count all values less than or equal to 50
(x ≤ 50).
(c) In cell E5, type 100.
(d) Select cells E4 and E5. Click on the small square at the bottom of the selection box and drag
down to auto-fill enough cells to give you sub-ranges with the highest being 1050.
4 of 8
Practical #2 (Week 3) GGRA30 Fall 2023
3. We are going to add a column of text labels for the chart we are designing. This is an optional
step but makes setting up the chart easier and allows us to clarify the labels shown in the chart.
(a) In cell F4, type in the following formula:
=CONCATENATE("<=", E4)
This should result in “<=50” being displayed in cell F4 (without the quotes — those are used
in the formula to designate <= as a character string).
Notice that <= is a simplified alternative to ≤ that doesn’t require you to insert
special characters. Excel lets you insert special symbols when entering text or for-
mulae in cells — find it on the insert ribbon called “symbols” (or your computer
may also have a character map application that will allow you to find symbols as
well). Go ahead and insert≤ in place of<= if easy to do — don’t spend a lot of time
on this though if it is not easy to find.
(b) Select cell F4. Copy it. (Control-C)
(c) Select all cells from F5 down to the cell beside the value 1050 defined in column E. Paste the
formula into all of these cells. (Control-V)
4. You are now ready to set up the frequency column.
(a) In cell G3, type frequencies.
(b) Select all cells from G4 down to include all rows for which range values have been defined
in column E. We will fill in an array formula for all of these cells at once. The formula we
want is:
= FREQUENCY(data-cell-range, bin-cell-range)
where:
data-cell-range is the range of cells containing total yearly precipitation values from the
pivot table. Don’t include the grand total value in the range.
bin-cell-range is the range of cells in column E containing the upper limits for each his-
togram bin.
Notes:
• This is an array formula. Type it in as you would any other Excel formula, except when
you are ready to complete it, type CONTROL-SHIFT-ENTER rather than just ENTER.
Important, press SHIFT and CONTROL and then hit ENTER. This special key sequence
tells Excel to fill the frequency formula you’ve just created across all of the frequency
cells as an array formula.
• Once you have entered the formula and before you press CONTROL-SHIFT-ENTER, the
set-up of your frequency table should look similar to Figure 2.
• The Frequency function includes values in the data range in a particular bin if that
value is less than or equal to the bin value (the maximum) and strictly greater than
the maximum bin value for the bin above it. The values in a bin range then are all
values that are strictly greater than the next lower bin’s maximum value and less than
or equal to the current bin’s maximum.
5 of 8
Practical #2 (Week 3) GGRA30 Fall 2023
• The frequency function takes the ordered set of maximum bin values into account and
does not count a value in more than one bin. Even though only the maximum value is
listed in each row, the minimum value is derived from the maximum of the next lower
category range.
• The Frequency function will insert one extra frequency value than the number of bins
you used when entering the array formula. This extra frequency count shows all values
(if any) greater than the maximum bin value. Because of this behaviour, you can select
one fewer cell for the bin values than the number of frequency table entries to be filled.
Figure 2 shows that the number of frequency column cells selected while editing the
frequency formula is one more than the number of selected bin cells.
Figure 2: Entering an array formula for the frequency table. Notice that all cells to be
filled with the formula have been selected as a group before entering the formula. The
bins formula is highlighted in red as the second part of the frequency formula. The data
range would also be highlighted blue to match the colour shown in the formula but is
not included in the figure.
You should now see a set of counts in the frequency column, each showing the number of total pre-
cipitation observations within the sub-range of values defined by that “bin.” For example, the count
beside the 800 entry in the bins column represents the number of precipitation totals in the range
750 mm< x ≤ 800 mm.
3.3 Graph it!
You can now graph the frequencies table you have created using a vertical bar chart.
6 of 8
Practical #2 (Week 3) GGRA30 Fall 2023
1. Select the cells in the labels and frequencies columns, including the title of the frequencies col-
umn, and then INSERT a new vertical bar chart.
You now have a visual summary of the frequencies computed from the summary in the pivot table.
All of these computations are still inter-related in the Excel chart, allowing you to alter the pivot table
summary and see updates in the chart immediately. This is very useful when you need to examine a
new data set.
When completing your assignment, you should add meaningful titles and labels as shown, for a
different chart than you just created, in Figure 3. To do this, click on the chart and then click on the “+”
symbol that appears beside the chart to see a list of chart elements, as shown in Figure 4, that can be
added and configured.
For your assignment, you will also save a copy of the chart as a clear image for use in your written
report. Excel is a fairly weak tool in this respect but does allow you to copy the image and then paste
it into other tools or documents. For an overview of different methods to use your chart as an image
elsewhere, please see Exceldemy (2022)
Continue with the practical quiz in Quercus. Sample questions are provided in Section 4.
0.0
10.0
20.0
30.0
40.0
50.0
60.0
70.0
80.0
1 2 3 4 5 6 7 8 9 10 11 12
A
ve
ra
ge
T
o
ta
l P
re
ci
p
it
at
io
n
(
m
m
)
Month (1=Jan., .. 12=Dec.)
Average Total Precipitation (mm), by month, 1940-2012
Figure 3: Labelled precipitation bar chart, Toronto Pearson Airport, 1940–2012.
4 Sample Questions (See Quercus for Your Questions)
These are sample questions only. See Quercus and answer the questions in your quiz.
1. How many monthly temperature records for January does the data contain?
2. What is the average monthly mean for total precipitation for August over the years 1940–2012?
3. According to the frequencies table created in Section 3.2, how many data occurrences are in the
range 750 mm< x ≤ 800 mm?
7 of 8
Practical #2 (Week 3) GGRA30 Fall 2023
Figure 4: Excel chart component switches. Click on the “+” symbol to open this list.
References
Exceldemy (2022). How to Save Excel Chart as High Resolution Image (6 Easy Ways). Available at
https://www.exceldemy.com/save-excel-chart-as-image-high-resolution/, accessed Sept. 2022.
GCF (2022). Excel: Intro to Pivot Tables. Goodwill Community Foundation. Available at
https://edu.gcfglobal.org/en/excel/intro-to-pivottables/1/, accessed Sept. 2023.
Microsoft (2023). Guidelines and examples of array formulas. Microsoft. Available at
https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-
ECFD5CAA57C7, accessed Sept. 2023.