Basic Excel Formulas & Functions Explained

Updated 6 November 2026 · Standard formula, shown below

This guide explains the most useful basic Excel formulas and functions—what they do, how to write them, and when to use each one. You’ll learn the difference between a formula and a function, see real examples you can copy, and avoid the common mistakes that trip up new users.

What Is a Formula vs. a Function?

In Excel, a formula is any expression you type that starts with an equals sign (=), such as =A1+A2 or =B1*C1. A function is a built-in operation that simplifies a formula, like =SUM(A1:A10) or =AVERAGE(B1:B10). Every function is a formula, but not every formula uses a function. You can combine them freely: =SUM(A1:A10)/COUNT(A1:A10) is a formula that uses two functions.

The 10 Most Useful Basic Functions

These are the functions you’ll use in nearly every spreadsheet, whether you’re budgeting, tracking sales, or analyzing survey data.

SUM – Adds numbers. Example: =SUM(A1:A10) adds all values in cells A1 through A10.
AVERAGE – Returns the arithmetic mean. Example: =AVERAGE(B1:B10).
COUNT – Counts cells that contain numbers. Example: =COUNT(C1:C10) ignores text and blanks.
COUNTA – Counts cells that are not empty (including text). Example: =COUNTA(D1:D10).
MAX – Returns the largest value. Example: =MAX(E1:E10).
MIN – Returns the smallest value. Example: =MIN(F1:F10).
IF – Tests a condition and returns one value if true, another if false. Example: =IF(G1>100,"High","Low").
ROUND – Rounds a number to a specified number of digits. Example: =ROUND(H1,2) rounds to two decimal places.
CONCATENATE (or &) – Joins text. Example: =CONCATENATE(A1," ",B1) or =A1&" "&B1.
VLOOKUP – Searches for a value in the first column of a table and returns a value from another column. Example: =VLOOKUP("Apple",A1:B10,2,FALSE).

How to Write a Basic Formula

Click the cell where you want the result, type =, then enter your formula. You can type cell references (like A1) or click the cells directly. Press Enter to finish.

Example: To add the values in B2 and C2, click D2, type =B2+C2, and press Enter. The result appears in D2. If you change B2 or C2 later, D2 updates automatically.

For ranges, use a colon: =SUM(B2:B10) adds every cell from B2 to B10. A comma separates individual arguments: =SUM(B2,C2,D2) adds only those three cells.

Common Mistakes and How to Avoid Them

Missing the equals sign – Without =, Excel treats your entry as text, not a formula.
Using the wrong range – Double-check that your range includes all the cells you intend. A missing row can skew totals.
Mixing up COUNT and COUNTA – COUNT only counts numeric cells; COUNTA counts all non-empty cells. If you have text labels in the range, they can inflate the count.
Dividing by zero – =A1/B1 returns #DIV/0! if B1 is 0 or blank. Use =IFERROR(A1/B1,"N/A") to display a friendly message instead.
Forgetting absolute references – When copying a formula down, relative references change (A1 becomes A2). If you need a fixed cell, use $A$1 (absolute reference).

How to Choose the Right Function

Start with what you want the result to be. If you need a total, use SUM. If you need an average, use AVERAGE. If you need to count entries, decide whether you want only numbers (COUNT) or everything (COUNTA). For a yes/no decision, use IF. For looking up a value in a table, use VLOOKUP (or XLOOKUP in newer versions).

When in doubt, type = in a cell and start typing the function name. Excel shows a list of matching functions with a brief description. You can also click the fx button next to the formula bar to open the Insert Function dialog and search by keyword.

Real-World Examples You Can Try

1. Calculate a Sales Total

You have prices in A2:A10 and quantities in B2:B10. In C2, type =A2*B2 and drag down to fill. Then in C11, type =SUM(C2:C10) to get the total revenue.

2. Grade a Test

If scores are in D2:D20, use =IF(D2>=90,"A",IF(D2>=80,"B",IF(D2>=70,"C","F"))) to assign letter grades. This nests three IF functions.

3. Look Up a Price

Suppose A1:B5 is a product table (product name in column A, price in column B). To find the price of “Widget�, use =VLOOKUP("Widget",A1:B5,2,FALSE). The FALSE ensures an exact match.

Limits and When to Use a Different Tool

Basic formulas work well for small to medium datasets. But if you need to:

Summarize data by category – use a PivotTable instead of many COUNTIF/SUMIF formulas.
Perform complex statistical analysis – use the Analysis ToolPak or a dedicated stats package.
Handle millions of rows – consider Power Query or a database like Access or SQL.
Automate repetitive tasks – use macros (VBA) or Office Scripts.

Also remember that formulas recalculate every time you change a value. If your workbook becomes slow, you can set calculation to manual (Formulas tab → Calculation Options) and press F9 to recalculate when ready.

Frequently Asked Questions

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

If the cell displays the formula as text (e.g., =A1+B1), check that the cell is not formatted as Text. Select the cell, go to Home → Number Format, and choose General. Then re-enter the formula by clicking in the formula bar and pressing Enter.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches vertically (down the first column) and returns a value from a column to the right. HLOOKUP searches horizontally (across the first row) and returns a value from a row below. Use VLOOKUP for column-based tables and HLOOKUP for row-based tables.

How do I copy a formula down a column without changing the reference?

Use absolute references with dollar signs, like $A$1. When you copy the formula down, the row number stays fixed. If you only want the column fixed, use $A1; only the row fixed, use A$1.

Why does my VLOOKUP return #N/A?

This usually means the lookup value isn’t found in the first column of the table. Check for extra spaces, inconsistent text (e.g., “apple� vs. “Apple�), or numbers stored as text. Use =TRIM(A1) to remove spaces, and ensure both columns have the same data type.

Can I use text in a SUM formula?

No, SUM ignores text values. If you try to add a cell containing text, SUM will skip it. To count text, use COUNTA. To concatenate text, use & or CONCATENATE.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.