Excel Essentials — Formulas & Tools Reference
Excel Formulas & Tools
Step-by-step guide to every formula and tool used in the Grade Book example
1. SUM — Add Up a Range of Numbers
Used in the Total column to add Test 1 + Test 2 + Test 3 for each student.
=SUM(B5:D5)
Steps
1. Click the empty cell where you want the total to appear (e.g. E5).
2. Type =SUM( and then select the range of cells you want to add — click B5 and drag to D5.
3. Close the bracket and press Enter: =SUM(B5:D5)
4. Copy the formula down to the rest of the students by dragging the small square at the bottom-right corner of the
cell.
2. AVERAGE — Calculate the Mean
Used twice: once per student (Average column) and once for the whole class (Class Average row).
=AVERAGE(B5:D5) → one student's average
=AVERAGE(F5:F14) → the whole class average
Steps
1. Click the cell where the average should appear (e.g. F5).
2. Type =AVERAGE( and select the range of test scores (B5:D5).
3. Close the bracket and press Enter.
4. For the class-wide average, repeat the same steps but select the whole Average column instead (F5:F14).
3. IF — Automatic Pass / Fail Labels
Used in the Result column to automatically write “Pass” or “Needs Support” based on the student's average.
=IF(F5>=60,"Pass","Needs Support")
Steps
1. Click the cell where the result should appear (e.g. G5).
2. Type =IF( then click the Average cell (F5), followed by the condition: >=60
3. Type a comma, then the text to show if the condition is TRUE, in quotes: "Pass"
4. Type another comma, then the text to show if it is FALSE: "Needs Support"
5. Close the bracket and press Enter: =IF(F5>=60,"Pass","Needs Support")
6. Copy the formula down for every student.
4. Sort & Filter — Instant Ranking, No Formula Needed
Turns the grade book into a Table with dropdown arrows on every header, so you can sort or filter with one click.
Steps
1. Click anywhere inside the data.
Page 1 of 2
Excel Essentials — Formulas & Tools Reference
2. Go to the Insert tab and click Table (or press Ctrl+T), then confirm the range and click OK.
3. A small dropdown arrow appears on each header cell.
4. Click the arrow on the Average column and choose Sort Largest to Smallest to rank students instantly.
5. Use the same arrow to Filter — for example, show only students with Result = “Needs Support.”
5. Conditional Formatting — Automatic Color Highlights
Colors each Average cell automatically: red below 60, amber for 60–79.9, and green for 80 and above.
Steps
1. Select the Average column cells (e.g. F5:F14).
2. Go to Home > Conditional Formatting > New Rule.
3. Choose “Format only cells that contain,” then set the condition, e.g. Cell Value less than 60.
4. Click Format, choose a red fill, and click OK.
5. Repeat two more times for 60–79.9 (amber) and ≥80 (green) using the same steps with a different condition and
color each time.
6. The colors will now update automatically whenever a score changes.
Quick Reference
Feature Formula / Path Used For
SUM =SUM(B5:D5) Total score
AVERAGE =AVERAGE(B5:D5) Student & class average
IF =IF(F5>=60,"Pass","Needs Support") Pass / Fail label
Sort & Filter Insert > Table (Ctrl+T) Ranking & filtering
Conditional Home > Conditional Formatting > New Rule Auto color by score
Formatting
Page 2 of 2