Basic Excel Formulas: SUM, AVERAGE and More

Basic Excel formulas: SUM, AVERAGE and more

Excel has become an essential program for anyone who works with numbers and data. Whether you keep track of your household budget, manage a membership list or create business reports, with the right formulas Excel becomes your best friend. In this article we explain the most important basic formulas step by step, so you can get started right away.

What are Excel formulas?

A formula in Excel is an instruction that tells the program what to calculate. Every formula starts with an equals sign (=) followed by the calculation you want to perform. Excel performs the calculation automatically and shows the result in the cell.

The great thing about formulas is that they update automatically when you change the source data. Change a number in your table? Excel immediately adjusts all related calculations. This not only saves time, it also prevents calculation errors.

The SUM formula: adding up numbers

The SUM formula is probably the most used formula in Excel. It lets you add numbers together without having to add them up by hand.

How does the SUM formula work?

The basic syntax is: =SUM(range)

Say you have ten numbers in cells A1 through A10 that you want to add up. Instead of typing =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10, you simply use: =SUM(A1:A10)

Practical example

You track your monthly expenses in a column. Cell A1 contains 250 euros for groceries, A2 contains 80 euros for gas, A3 contains 45 euros for insurance, and so on up to A12. To calculate the total:

1. Click the cell where you want to see the total
2. Type =SUM(A1:A12)
3. Press Enter

Excel now automatically shows the total of all your expenses.

Tips for the SUM formula

You can also add up multiple ranges: =SUM(A1:A10,C1:C10) adds both columns together. Note that in the Dutch version of Excel you use a semicolon (;) as the separator, not a comma.

The AVERAGE formula: calculating the average

Want to know the average of a series of numbers? The AVERAGE formula does this in no time.

How does the AVERAGE formula work?

The syntax is: =AVERAGE(range)

This formula adds up all the numbers in the range and divides the total by the count of numbers. Empty cells are ignored automatically.

Practical example

Say you recorded the temperature of the past week in cells B1 to B7: 18, 21, 19, 22, 20, 23, 21 degrees. To calculate the average temperature:

1. Select an empty cell
2. Type =AVERAGE(B1:B7)
3. Press Enter

The result is 20.57 degrees, the average temperature of that week.

The MIN and MAX formulas: lowest and highest value

Sometimes you want to know the lowest or highest value in a series. This is where MIN and MAX come in handy.

MIN: finding the lowest value

The formula =MIN(A1:A20) returns the smallest value in the range A1 to A20. Useful for finding the cheapest price or the lowest score, for example.

MAX: finding the highest value

The formula =MAX(A1:A20) does the opposite and returns the highest value. Ideal for identifying the best performance or the highest amount.

Practical example

You are comparing prices from different online stores. Column C contains the prices: 29.95, 34.50, 27.99, 31.00, 28.50.

• =MIN(C1:C5) returns 27.99, the lowest price
• =MAX(C1:C5) returns 34.50, the highest price

The COUNT formula: counting cells

The COUNT formula counts how many cells in a range contain numbers. Note: this formula only counts cells with numeric values, not text.

Variations of the COUNT formula

• =COUNT(range): counts cells with numbers
• =COUNTA(range): counts all non-empty cells, including text
• =COUNTBLANK(range): counts only the empty cells

Practical example

You have an attendance list where people who attended get a 1. With =COUNT(D1:D50) you can see right away how many people were present.

The IF formula: conditional calculations

The IF formula is a bit more advanced, but extremely useful. It lets Excel make a decision based on a condition.

How does the IF formula work?

The syntax is: =IF(condition,value_if_true,value_if_false)

Practical example

You want to automatically determine whether a grade is a pass or a fail. The grade is in cell E1.

=IF(E1>=5.5,"Pass","Fail")

If the grade is 5.5 or higher, Excel shows "Pass". Otherwise it shows "Fail".

Another example

You give a discount to customers who spend more than 100 euros. The amount is in F1.

=IF(F1>100,F1*0.9,F1)

For orders over 100 euros a 10% discount is applied; otherwise the amount stays the same.

ROUND: rounding numbers neatly

In financial calculations you often get long numbers with many decimals. The ROUND formula tidies these up.

How does ROUND work?

The syntax is: =ROUND(number,num_digits)

Practical example

You have a calculation that results in 34.7856, but you want to round it to 2 decimals:

=ROUND(34.7856,2) returns 34.79

Want to round to whole numbers? Then use 0 as the second argument:

=ROUND(34.7856,0) returns 35

Handy tips for working with formulas

Tip 1: Use the function wizard

Not sure exactly how a formula works? Click the fx symbol next to the formula bar. Excel then helps you step by step to enter the right data.

Tip 2: Copying formulas

Created a formula you want to use in multiple cells? Drag the small square at the bottom right of the cell down or to the side. Excel adjusts the cell references automatically.

Tip 3: Understanding error messages

• #VALUE! means the formula contains text where a number is expected
• #DIV/0! means you are dividing by zero
• #NAME? means Excel does not recognize the formula (possibly a typo)

Tip 4: Absolute references

Want a cell reference to stay the same when you copy it? Put dollar signs before the letter and the number: $A$1 always refers to cell A1, no matter where you copy the formula.

Practice makes perfect

The best advice we can give is: practice, practice, practice. Create a practice file and experiment with the different formulas. You cannot break anything, and you learn fastest by trying things yourself.

Start with simple SUM and AVERAGE formulas. Once you have mastered those, you can move on to slightly more complex formulas such as IF. Before you know it, you will be an Excel expert who can help others.

Frequently asked questions

Why doesn't my formula work and why do I only see the text?

Check that your formula starts with an equals sign (=). Without this sign, Excel thinks you are just entering text. For example, type =SUM(A1:A10) and not SUM(A1:A10). Also check that the cell is not formatted as text; you can change this via Format Cells.

Can I combine formulas in one cell?

Yes, you certainly can. You can nest formulas inside each other. For example, =ROUND(AVERAGE(A1:A10),2) first calculates the average and then rounds it to 2 decimals. Start with simple combinations and build up slowly.

What is the difference between a formula and a function?

A function is a preprogrammed formula with a name, such as SUM or AVERAGE. A formula is any calculation you create in Excel, including simple calculations such as =A1+B1. Functions make complex calculations easier because you do not have to write the whole calculation yourself.

How can I see which formulas are in my worksheet?

Press Ctrl+` (the grave accent key, to the left of the 1 key) to show all formulas in your worksheet. Press the same key combination again to return to the normal view with results.