Excel Formulas Made Easy

The 10 Essential Formulas

1. =SUM(range) — Adds everything in a range. The foundation.
Example: =SUM(B2:B20) adds all values in column B, rows 2 through 20.

2. =AVERAGE(range) — The mean of a range. Faster than SUM/count.
Example: =AVERAGE(C2:C30) gives the average of student scores.

3. =COUNT(range) / =COUNTA(range) — COUNT counts numbers only. COUNTA counts everything (text, dates, numbers). Use COUNTA for attendance, COUNT for numeric data.

4. =MAX(range) / =MIN(range) — Highest and lowest values in a range. Great for finding top performers, outliers, or date ranges.

5. =IF(condition, value_if_true, value_if_false) — Makes decisions in your spreadsheet.
Example: =IF(B2>=50, "Pass", "Fail") — automatically grades students based on their score.

6. =COUNTIF(range, criteria) / =SUMIF(range, criteria, [sum_range]) — Count or sum only cells that meet a condition.
Example: =COUNTIF(D2:D100, "Lusaka") counts how many entries are from Lusaka.

7. =VLOOKUP(lookup_value, table, column_index, [range_lookup]) — The classic lookup. Finds a value in one column and returns a value from another.
Example: given a student ID, find their name in a different table.

8. =XLOOKUP(lookup_value, lookup_array, return_array) — VLOOKUP's modern replacement. Easier to write, more powerful, works in any direction. Available in Excel 2021 and Microsoft 365.

9. =CONCATENATE(text1, text2, ...) or =TEXTJOIN(delimiter, ignore_empty, text1, ...) — Combine text from multiple cells. TEXTJOIN is cleaner when you have many cells.
Example: =TEXTJOIN(" ", TRUE, A2, B2) combines first and last name.

10. =TRIM(text) / =PROPER(text) / =UPPER(text) / =LOWER(text) — Clean up messy data. TRIM removes extra spaces. PROPER capitalises the first letter of each word. Essential when importing data from other sources.

Common Errors and What They Mean

• #DIV/0! — You are dividing by zero or an empty cell.
• #VALUE! — Wrong type of data (e.g., trying to add numbers to text).
• #REF! — A formula references a cell that was deleted.
• #NAME? — Excel does not recognise a function name (usually a typo).
• #N/A — VLOOKUP or XLOOKUP could not find the value.

Practice Exercise

Open a blank spreadsheet. Create a simple grade book with columns for Student Name, Assignment 1, Assignment 2, and Exam. Add 10 fictional students with scores. Then try: =AVERAGE for each student's overall mark, =IF to assign Pass/Fail, and =MAX to find the top score. This exercise alone will make you more confident with Excel than 80% of users.