# Excel Formulas for Beginners: 9 That Actually Work URL: https://formula.dog/journal/excel-formulas-for-beginners Type: blog Locale: en Published: 2026-08-08 Updated: 2026-08-12 --- > Nine essential Excel formulas with syntax, real examples, and common error fixes. Works in Excel 2016, Excel 365, and Google Sheets where they differ. 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. ![Person typing Excel formulas on a laptop with a colorful spreadsheet on screen](https://fdzlnqpwsaniezitwiuw.supabase.co/storage/v1/object/public/cms-media/formula-dog/2026-08/94b579-inline1.webp) ## 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. ![Golden retriever dog sitting beside a desk with a laptop showing a spreadsheet, attentively helping with work](https://fdzlnqpwsaniezitwiuw.supabase.co/storage/v1/object/public/cms-media/formula-dog/2026-08/077d34-inline2.webp) ## 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. ![Flat lay of a desk with an open notebook showing hand-drawn data diagrams, colored pens and sticky notes](https://fdzlnqpwsaniezitwiuw.supabase.co/storage/v1/object/public/cms-media/formula-dog/2026-08/4ae55c-inline3.webp) ## 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. ## FAQ ### What is the easiest Excel formula for beginners to start with? SUM is the simplest starting point. =SUM(A1:A10) adds up a range and the syntax is impossible to get wrong. Once you have that, IF and VLOOKUP open up most of what you will actually need day to day. ### Do these Excel formulas also work in Google Sheets? Most of them, yes. SUM, AVERAGE, COUNT, IF, VLOOKUP, SUMIF, COUNTIF, TRIM, and CONCAT all work identically in Google Sheets. XLOOKUP is also available in Sheets. TEXTJOIN works in Sheets but not in Excel 2016. Version differences are noted in each section above. ### Why does my SUMIF return 0 when there should be a result? The most common cause is numbers stored as text. If the values in your sum range are left-aligned in their cells, Excel is treating them as text and ignoring them. Select the column, reformat it to Number, and your SUMIF result should correct itself. ### What is the difference between COUNT and COUNTA? COUNT only tallies cells that contain numbers. COUNTA counts any non-empty cell, including text. Use COUNTA when your range contains a mix of labels and numbers and you want to count all filled cells regardless of type. ### Is XLOOKUP better than VLOOKUP? For Excel 365 and Excel 2021+ users, yes. XLOOKUP is more flexible (no requirement for the lookup column to be first), handles errors more cleanly with a built-in if_not_found argument, and can search in any direction. If you are on Excel 2016 or 2019, XLOOKUP is not available and VLOOKUP remains your main option. ### How do I stop seeing #N/A errors in my VLOOKUP? Wrap your VLOOKUP in IFERROR: =IFERROR(VLOOKUP(A2, Table!A:C, 3, FALSE), "Not found"). If you are using XLOOKUP, use its fourth argument: =XLOOKUP(A2, Table!A:A, Table!C:C, "Not found"). Both return your chosen message instead of an error code when no match is found. ### What is the fastest way to get an Excel formula without memorizing the syntax? Describe your problem in plain English to Formula.dog and it returns the correct formula with the right syntax. Useful when you know what you want but cannot remember which argument goes where, or when you need a complex SUMIFS or nested IF that would take several minutes to build by hand.