Excel formulas help you calculate totals, test conditions, count matching records and summarize data without doing every calculation by hand. This guide explains five everyday formulas using a small student-budget and coursework example. Formula names and separators can vary with Excel language and regional settings, so use the syntax shown by your own version when needed.
Start with a simple worksheet
Imagine a worksheet with student names in column A, course names in column B, scores in column C and assignment status in column D. A formula uses cell references such as C2:C20 to operate on a range rather than one value at a time.
In Excel, formulas begin with an equals sign. If a cell shows the formula as text, check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign.
- Use clear column headers.
- Keep one record per row.
- Avoid merged cells inside the data table.
- Check that numbers are stored as numbers, not text.
SUM and AVERAGE: calculate totals and means
SUM adds numeric values. AVERAGE calculates their arithmetic mean and ignores blank cells, but a numeric zero still affects the average. These functions are useful for expenses, grades, hours and survey values.
If assignment scores are in C2:C10, a total and an average can be calculated separately. Make sure the range contains only the values you intend to include.
=SUM(C2:C10)
=AVERAGE(C2:C10)IF: return a result based on a condition
IF tests a condition and returns one value when it is true and another when it is false. It can label a score as Pass or Review, flag a deadline, or categorize a budget result. The condition should reflect the real rule you are trying to apply.
For example, if C2 contains a score and 70 is the cutoff used in a particular class, the formula below returns Pass at 70 or above. The cutoff is illustrative; use the requirement for the actual course.
=IF(C2>=70,"Pass","Review")COUNTIF and SUMIF: count or add matching rows
COUNTIF counts cells that meet one condition. SUMIF adds values from matching rows. They are useful when you need to count submitted assignments or add expenses from a chosen category.
If D2:D20 contains status labels, COUNTIF can count how many are marked Submitted. If B2:B20 contains expense categories and C2:C20 contains amounts, SUMIF can add just the amounts labelled Books.
=COUNTIF(D2:D20,"Submitted")
=SUMIF(B2:B20,"Books",C2:C20)Use absolute references when copying formulas
Relative references change when you copy a formula. An absolute reference such as $F$1 keeps pointing to the same cell. This is useful when many rows need to compare a value with one fixed threshold stored in a separate cell.
Press F4 while editing a reference on many Windows Excel setups to cycle reference styles; the exact shortcut can vary by device.
=IF(C2>=$F$1,"Meets target","Below target")Common Excel formula mistakes
Check the range endpoints, spelling of text labels, parentheses and the difference between a number and text. COUNTIF criteria need quotation marks when entered as text, while criteria that use comparison operators often combine the operator and a value.
Build formulas from a tiny test range first, verify the result manually, and only then fill the formula down the full table.
- Check whether a range accidentally omits the last row.
- Use consistent spelling for labels such as Submitted.
- Use $ references for fixed criteria cells when copying formulas.
- Check for hidden spaces that make text comparisons fail.
- Verify a few results independently before relying on the whole sheet.
Understanding the concept is the first step.
Explore related resources or learn about subject-focused tutoring designed to help you understand the material.