Excel Formulas for Beginners: 9 That Actually Work

The nine excel formulas for beginners that actually matter are SUM, AVERAGE, COUNT, IF, VLOOKUP, XLOOKUP, SUMIF, COUNTIF, TRIM, and CONCAT. Learn those nine and you will handle roughly 80% of what a spreadsheet throws at you. Each section below gives you the syntax, a real working example, and the one thing that makes it break silently.

Works in Excel 2016, Excel 2019, Excel 365, and Google Sheets. Version differences are flagged where they exist.

SUM, AVERAGE, and COUNT: the three formulas you will type every day

These three do exactly what they say.

=SUM(A1:A10)

Adds every value from A1 to A10. You can also add non-contiguous ranges:

=SUM(A1:A10, C1:C5)

Add numbers from two separate column sections in one call. No need to add them in a helper column first.

=AVERAGE(B2:B20)

Arithmetic mean. One thing worth knowing: AVERAGE ignores blank cells but includes cells containing zero. If you have empty months in a sales table, AVERAGE skips them. If those empty months are filled with 0, it counts the zeros. Choose your fill strategy before you build the formula.

=COUNT(A1:A100)

Counts numeric cells only. For a mix of text and numbers, use COUNTA:

=COUNTA(A1:A100)

Any non-empty cell counts. This is what you want when your column has labels and numbers mixed together. COUNT alone would skip every text entry and give you a lower number than you expect.

The three together cover nearly every aggregation task a beginner will face: adding up totals, finding averages, and counting rows. If you know nothing else yet, start here and build from this foundation.

IF: let your spreadsheet make a call for you

IF checks a condition and returns one value when true, another when false.

=IF(logical_test, value_if_true, value_if_false)

A classic use case: a sales table where you want to flag anyone who hit quota.

=IF(B2>=10000, "Hit", "Miss")

If B2 is 10,000 or more, the cell shows "Hit". If not, "Miss".

You can nest IFs for multiple conditions:

=IF(B2>=20000, "Excellent", IF(B2>=10000, "Hit", "Miss"))

Three tiers, two levels of nesting. Keep it at two levels maximum. Beyond that, switch to IFS (available in Excel 2019+, Excel 365, and Google Sheets):

=IFS(B2>=20000, "Excellent", B2>=10000, "Hit", TRUE, "Miss")

The last condition is TRUE, which acts as a catch-all (the equivalent of an else). Cleaner to read, easier to update when the quota changes next quarter.

The mistake that kills nested IFs: unbalanced parentheses. Count your opening brackets. You need the exact same number of closing ones. Excel underlines the problem in red if you get it wrong, so at least it is obvious when you are off.

IF also composes cleanly with other formulas. You will use it inside IFERROR later to handle errors gracefully, and you can nest it with AND or OR to test multiple conditions at once.

VLOOKUP: how to pull data from another table

VLOOKUP scans the first column of a range and returns a value from another column in the same row.

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Example: employee IDs in column A, salary table on a separate sheet. You want the salaries pulled into your main view.

=VLOOKUP(A2, Salaries!A:C, 3, FALSE)

Breaking that down:

• A2 is the ID you are looking up

• Salaries!A:C is the reference table on another sheet, and the lookup column must always be the first column of this range

• 3 tells VLOOKUP to return the value from the 3rd column of that range

• FALSE means exact match

Use FALSE. Always. The fourth argument defaults to TRUE if you leave it blank, which enables approximate match. That can return wrong results quietly, with no error message to alert you. It is the most common VLOOKUP mistake and it takes about 20 minutes to find when it happens in a real file.

Biscuit l'a déjà cherchée pour vous. But in all seriousness: this is the formula that makes people realize spreadsheets are actually worth learning properly. VLOOKUP alone will save you hours of copy-pasting between sheets.

XLOOKUP: worth learning if you are on Excel 365

XLOOKUP is available in Excel 2021+ and Excel 365, and in Google Sheets. It solves VLOOKUP's main limitation: the lookup column no longer has to be the first column of your range.

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)

The same salary lookup, XLOOKUP style:

=XLOOKUP(A2, Salaries!A:A, Salaries!C:C, "Not found")

More readable. The fourth argument replaces ugly #N/A errors with a message you control. You can also search from the bottom up or return multiple columns at once.

If you are on Excel 2016 or Excel 2019, XLOOKUP is not available. Use VLOOKUP or learn INDEX-MATCH, which works in any direction on any Excel version. INDEX-MATCH has a steeper learning curve but never limits you to left-first columns.

For new spreadsheets started on Excel 365, XLOOKUP is the better default. For files that need to open on older versions, stick with VLOOKUP.

SUMIF and COUNTIF: add or count only what matches

SUMIF adds values in a range, but only for rows where a condition is met:

=SUMIF(range, criteria, sum_range)

Example: sum all sales where the region column says "North".

=SUMIF(C2:C100, "North", D2:D100)

COUNTIF counts how many cells match a condition:

=COUNTIF(C2:C100, "North")

For multiple conditions, use SUMIFS and COUNTIFS (plural):

=SUMIFS(D2:D100, C2:C100, "North", E2:E100, ">1000")

That adds all sales in the North region where the order value exceeds 1,000. SUMIFS is what you reach for when a single filter is not enough.

SUMIF returning 0 when it should not: this is almost always a formatting issue. If your number column values are left-aligned in their cells, they are being stored as text. Reformat the column to Number and the SUMIF result will correct itself immediately. This is the formula equivalent of Biscuit coming back with the wrong stick -- the data looks right but something is off at the type level.

COUNTIF is equally useful for checking for duplicates:

=COUNTIF(A:A, A2)

If this returns a value greater than 1 for any row, that ID appears more than once.

Text formulas that save you from manual cleanup: TRIM, CONCAT, LEFT

If you have ever imported data from a CRM, an ERP, or a CSV export, you know the problem: invisible spaces before values, names split across two columns, product codes with extra characters at the start. These formulas clean that up.

TRIM strips leading, trailing, and extra internal spaces:

=TRIM(A2)

If VLOOKUP keeps returning #N/A even when the value is visibly in the table, run TRIM on your lookup value. Extra spaces are the invisible culprit in most of those cases. Wrap your lookup in TRIM and the match will work.

CONCAT joins values from multiple cells:

=CONCAT(A2, " ", B2)

First name in A2, last name in B2, a space character in the middle. In Excel 2016, use CONCATENATE instead (same behavior, older function name). TEXTJOIN handles a full list more cleanly:

=TEXTJOIN(", ", TRUE, A2:A10)

The TRUE argument tells it to skip empty cells in the range. TEXTJOIN is Excel 2019+ and Excel 365 only. In Google Sheets, it works fine in all versions.

LEFT, RIGHT, and MID extract characters from a string:

=LEFT(A2, 3)

Returns the first 3 characters from the value in A2.

=RIGHT(A2, 4)

Returns the last 4 characters.

=MID(A2, 2, 5)

Returns 5 characters starting at position 2.

Useful when product codes or account numbers embed category info you need to parse out without modifying the original data column.

What your error code is actually telling you

Error codes are not failures. They are messages. A spreadsheet returning an error is doing its job: it found a problem and it is telling you exactly what kind of problem it is.

Here is what each one means and what to do about it:

#N/A means VLOOKUP or XLOOKUP could not find the lookup value in the table. Check for typos, extra spaces (use TRIM), or a text-versus-number mismatch where the lookup value is a number but the table column stores text or vice versa.

#DIV/0! means the formula is dividing by zero or by a blank cell. Wrap the formula with IFERROR to handle it gracefully:

=IFERROR(A2/B2, 0)

#VALUE! means the formula received text where it expected a number. Check the data type of your input cells.

#REF! means a referenced cell no longer exists because you deleted a row or column that the formula pointed to. Open the formula bar, find the broken reference, and update it.

#NAME? means Excel does not recognize the function name. Check the spelling, or confirm the function is available in your version of Excel. XLOOKUP spelled wrong becomes XLOKUP and returns #NAME?.

IFERROR is the general solution for making errors display as something readable:

=IFERROR(VLOOKUP(A2, Salaries!A:C, 3, FALSE), "Not found")

If the VLOOKUP finds nothing, you see "Not found" instead of #N/A. Works in Excel and Google Sheets. XLOOKUP builds this directly into its fourth argument, which is one more reason to use it when you can.

Should you let an AI write the formula instead?

If you know what you want but not the exact syntax, yes. Describing your problem in plain English and getting a working formula back in a few seconds is faster than 10 minutes of searching. That is what Formula.dog does: describe the spreadsheet task, get the formula, paste it in. No sign-up required for the first few uses.

Worth letting an AI handle it:

• Complex SUMIFS with four or more criteria

• XLOOKUP with a match mode you have not used before

• Combining IFERROR with another function you are not sure about

• Any VBA snippet or regex helper you would otherwise lose an afternoon researching

Worth verifying manually afterward:

• Any formula that feeds a financial report or dashboard that other people rely on

• Results that look plausible but that you do not fully understand yet

À copier-coller directement. On vérifie ensemble. The two-second check is this: paste the formula, hit Enter, then confirm the output on a row where you already know the correct answer. If it matches, you are good to go.

Skip this if you want to learn the syntax deeply for yourself -- there is real value in understanding how VLOOKUP works rather than just running it. But if you are in a file, on a deadline, and the formula is SUMIFS with three criteria you cannot quite remember, let the AI fetch it.