John Harris, Head of Geography, Radley College, Abingdon, UK
mjh @ radley.org.uk
This article can be dowloaded in Word format excel6.doc (256k)
The Excel 8.0 spreadsheets (all 8k) examples can also be downloaded:
Spreadsheets have many uses in Geography including:
Microsoft Excel 8.0 will be used for the following examples.
For earlier versions of Excel select Insert/Chart or click the Chart icon to draw charts and graphs.
Spreadsheets are composed of columns (vertical, letters) and rows (horizontal, numbers).
Cells are identified by column and row reference eg cell A3 is column A and in row 3
Calculate population density in column D by typing = B3/C3 in cell D3.
While cell D3 is highlighted drag the small block in the bottom right corner down to cell D12.
This will automatically copy the correct formula and results into the cells below.
Calculate the % population growth rate in column G by typing = (E3-F3)/10 in cell G3.
While cell G3 is highlighted drag the small block in the bottom right corner down to cell G12.
This will automatically copy the correct formula into the cells below.
Calculate averages for each column by typing =AVERAGE(B3:B14) in cell B14.
While cell B14 is highlighted drag the small block in the bottom right corner across to cell G14.
This will automatically copy the correct formula into all of the cells in row 14.
The table should now look like this:
Select all of the spreadsheet cells from A3 to G12 (the selected area will be highlighted in black).
Any of the data columns can now be sorted in either ascending or descending order.
Data/Sort/Area km2/Descending to sort the countries in order of size.
Data/Sort/growth/Descending to sort the countries in order of growth rate.
Multiple sorting is possible on up to three columns.
File/Save the spreadsheet with a suitable name eg nations.xls
Spreadsheet data can be selected and plotted as a variety of graphs and charts.
Select the data in columns E and F by dragging down from E3 to F12.
If you wish to perform calculations you must type the = sign into a cell and then specify the required calculation eg type =B3+C3 into cell D3 to add up the two sets of test marks in columns B and C.
While cell D3 is highlighted drag the small block in the bottom right corner down to cell D6.
This will automatically copy the correct formula and results into the cells below.
Enter the formula =D3/75*100 into cell E3 to convert the mark out of 75 into a percentage.
While cell E3 is highlighted drag the small block in the bottom right corner down to cell E6
The spreadsheet should now look like the example below:
Sorting data
Select all of the cells from A3 to E6 by holding down the mouse button and dragging across the spreadsheet.
Data/Sort/Descending/% and the students will be ranked form highest to lowest.
Enter the rank into column F.
File/Save the spreadsheet with a suitable name eg marks.xls
The example shows a simplified departmental budget spreadsheet.
The termly breakbown of the budget is shown.
The total annual budget is in Cell B6 using the formula = SUM(B3:B5)The current spending for each term is calculated automatically
eg in Cell C3 using the formula = SUM(C10:C12) for the Autumn termThe % of the termly or annual budget spent can be automatically calculated
eg in D3 using the formula =C3/B3*100Insert/Cells to insert additional rows as required for more purchases.
File/Save the spreadsheet with a suitable name eg budget.xls