Spreadsheet skills help in coursework, projects and almost every job. You do not need to be an expert. These basics, practised on a simple class-scores sheet, will cover most of what you need. The same formulas work in Google Sheets.
The example sheet
Imagine a sheet with names in column A, test scores (out of 30) in column B and exam scores (out of 70) in column C, for students in rows 2 to 11.
Cells, ranges and references
- A cell is named by its column and row, like
B2. - A range is a group of cells, like
B2:B11. - Every formula starts with an equals sign,
=.
Basic formulas
- Total score in D2:
=B2+C2. Then drag the small square at the corner of the cell down to copy it to D11. Excel automatically adjusts the row numbers. - Sum of a range:
=SUM(D2:D11) - Average:
=AVERAGE(D2:D11) - Highest and lowest:
=MAX(D2:D11)and=MIN(D2:D11) - Count of entries:
=COUNT(D2:D11)
Making decisions with IF
To show "Pass" or "Fail" in E2 using a pass mark of 40:
=IF(D2>=40,"Pass","Fail")
The IF function checks a condition, then returns one value if it is true and another if it is false.
Counting with COUNTIF
How many students passed? =COUNTIF(E2:E11,"Pass")
How many scored 70 or more? =COUNTIF(D2:D11,">=70")
Absolute references with $
Suppose the pass mark is stored in cell H1 so you can change it later. If you write =IF(D2>=H1,"Pass","Fail") and drag it down, Excel changes H1 to H2, H3 and so on, which is wrong. Lock it with dollar signs: =IF(D2>=$H$1,"Pass","Fail"). Now it always points to H1.
Looking things up with VLOOKUP
To find a student's total by name, with the name typed in G2:
=VLOOKUP(G2,A2:D11,4,FALSE)
This looks for the value in G2 in the first column of A2:D11 and returns the value from the 4th column of that range. FALSE means "exact match only". Newer versions of Excel also have XLOOKUP, which is more flexible.
Sorting and filtering
Select your table, then use Data → Sort to rank students by total score, or Data → Filter to show only students who failed. Always select the whole table before sorting so names stay attached to the right scores.
Charts
Select the names and totals, then choose Insert → Chart. A column chart works well for comparing students. Give every chart a title and axis labels.
Good habits
- Use one row per record and one column per type of information.
- Put headings in row 1 and make them bold.
- Do not type totals by hand. Use formulas so they update automatically.
- Save often, and keep a backup copy.