12 Excel Formulas Every Student Should Know

Spreadsheets turn up everywhere in student life: tracking attendance, splitting a trip budget, analysing survey data for a project, or preparing a lab report. Knowing a handful of Excel formulas well saves hours of manual counting and removes calculation errors. This guide uses one small marks sheet throughout, so you can type it in once and try every formula yourself. All twelve formulas also work in Google Sheets with the same syntax.

The Practice Sheet

Open a blank workbook and enter this data in cells A1 to E7. The students are an example.

	A: Roll	B: Name	C: Section	D: Marks	E: Attendance %
2	101	Aarav	A	78	92
3	102	Diya	B	35	68
4	103	Kabir	A	64	81
5	104	Meera	B	91	95
6	105	Rohan	A	47	74
7	106	Sana	B	58	88

Row 1 holds the headings. Every formula below starts with an equals sign; without it, Excel treats what you type as plain text.

1. SUM: Add a Range

=SUM(D2:D7)

Result: 373. The colon means "everything from D2 to D7". SUM ignores text cells, so a stray "Absent" will not break it, though it will quietly leave that student out. You can also add separate ranges: =SUM(D2:D4, D6).

2. AVERAGE: Find the Mean

=AVERAGE(D2:D7)

Result: 62.1666…, which is 373 divided by 6. Be careful with blank cells versus zeros: AVERAGE skips blanks but counts a 0. If a student missed the test, leave the cell blank rather than typing 0, unless your teacher actually awarded zero.

3. ROUND: Control Decimal Places

=ROUND(AVERAGE(D2:D7), 1)

Result: 62.2. The second argument is the number of decimal places. This shows a useful habit: formulas can be nested, with one formula's result feeding another. Note that changing the number format only changes how a value looks, while ROUND changes the value itself, which matters when you later add rounded figures together.

4. COUNTIF: Count Cells That Meet a Condition

=COUNTIF(D2:D7, ">=40")

Result: 5, because only Diya scored below 40. Conditions with comparison signs must sit inside quotes. To count students below the 75% attendance mark, use =COUNTIF(E2:E7, "<75"), which returns 2 (Diya and Rohan).

5. COUNTIFS: Count With Several Conditions

=COUNTIFS(C2:C7, "A", E2:E7, ">=75")

Result: 2. This counts Section A students whose attendance is at least 75%: Aarav and Kabir qualify, while Rohan (74%) does not. Each condition comes as a pair of range and criterion, and all pairs must be true for a row to count. The ranges must be the same size.

6. SUMIF: Add Only Matching Rows

=SUMIF(C2:C7, "B", D2:D7)

Result: 184 (35 + 91 + 58). The first range is checked against the condition, and the third range is what gets added. This is perfect for expense sheets: put categories like "Food" or "Travel" in one column and amounts in another, then total each category instantly. For several conditions, use SUMIFS, where the sum range comes first.

7. AVERAGEIF: Average a Group

=AVERAGEIF(C2:C7, "A", D2:D7)

Result: 63, because (78 + 64 + 47) ÷ 3 = 63. Comparing it with the same formula for Section B, which gives 61.33…, tells you at a glance which section did better on average.

8. IF: Make a Decision

=IF(D2>=40, "Pass", "Fail")

Enter this in F2 and drag the fill handle down to F7. Aarav shows Pass, and Diya in row 3 shows Fail. The structure is always: condition, value if true, value if false.

You can nest IFs to assign grades. Check the highest band first:

=IF(D2>=75, "A", IF(D2>=60, "B", IF(D2>=40, "C", "F")))

Aarav (78) gets A and Kabir (64) gets B. If you checked ">=40" first, every passing student would wrongly get a C, because Excel stops at the first true condition. Newer versions also offer IFS, which avoids deep nesting.

9. VLOOKUP: Find a Value by Its Key

=VLOOKUP(104, A2:E7, 4, FALSE)

Result: 91, Meera's marks. VLOOKUP searches for 104 in the first column of A2:E7, then returns the value from the 4th column of that range (column D). Always use FALSE as the last argument for an exact match; leaving it out makes Excel assume an approximate match, which can return a wrong student without any warning.

VLOOKUP's main limitation is that it can only look to the right of the search column. If your version of Excel supports XLOOKUP, =XLOOKUP(104, A2:A7, D2:D7, "Not found") gives the same 91 and is more flexible.

10. IFERROR: Replace Ugly Errors

=IFERROR(VLOOKUP(110, A2:E7, 4, FALSE), "Roll not found")

Roll 110 does not exist, so VLOOKUP alone would show #N/A. Wrapping it in IFERROR shows Roll not found instead. Use it for clean reports, but do not use it to hide errors you have not understood, because it also masks genuine mistakes such as a mistyped range.

11. INDEX with MATCH: The Flexible Lookup

=INDEX(B2:B7, MATCH(MAX(D2:D7), D2:D7, 0))

Result: Meera, the topper. Read it from the inside out: MAX finds the highest marks (91), MATCH finds its position in D2:D7 (position 4, with 0 meaning exact match), and INDEX returns the 4th name in B2:B7. Unlike VLOOKUP, this works in any direction. Another example: =INDEX(E2:E7, MATCH(103, A2:A7, 0)) returns Kabir's attendance, 81.

12. RANK.EQ: Rank Students

=RANK.EQ(D2, $D$2:$D$7, 0)

Result for Aarav: 2, since only Meera scored higher. The 0 means descending order (highest marks get rank 1). The dollar signs create an absolute reference, so when you drag the formula down, D2 changes to D3, D4 and so on, but the comparison range stays fixed. Forgetting the dollar signs is one of the most common spreadsheet errors students make.

Bonus: Joining Text

The ampersand joins text and numbers: =B2&" ("&A2&")" gives Aarav (101). In newer versions, =TEXTJOIN(", ", TRUE, B2:B4) gives "Aarav, Diya, Kabir", which is handy for building lists of names.

Quick Reference

Task	Formula to reach for
Total or average a column	SUM, AVERAGE, ROUND
Count or total by condition	COUNTIF(S), SUMIF(S), AVERAGEIF
Label rows automatically	IF, IFS
Find one value from a table	VLOOKUP, XLOOKUP, INDEX + MATCH
Clean up errors	IFERROR
Order by score	RANK.EQ

Habits That Prevent Mistakes

• Keep one type of data per column, and never type units like "78 marks" into a number cell.
• Use Ctrl + ` (the backtick key) in Excel to show all formulas instead of results while checking your work.
• Check one result by hand, as done above, before trusting a formula you have copied across hundreds of rows.
• Turn your data into a table with Ctrl + T so new rows are included in formulas automatically.

FAQs

Do these formulas work in Google Sheets?

Yes. All twelve formulas above use the same names and argument order in Google Sheets. Some newer Excel functions differ slightly, so check the help page of the version you are using.

Why does my formula show the text instead of a result?

Usually the cell is formatted as Text, or the formula does not begin with an equals sign. Change the cell format to General, then re-enter the formula.

Should I learn VLOOKUP or INDEX MATCH?

Learn both. VLOOKUP is quick to write and appears in many existing files you will open, while INDEX with MATCH handles lookups VLOOKUP cannot, such as searching to the left.

What should I learn after these formulas?

Move on to sorting and filtering, conditional formatting, PivotTables and simple charts. Together with these formulas, they cover most of what college projects and entry-level office work require.