How to Use Basic Formulas in Excel (SUM, AVERAGE, COUNT)

Microsoft Excel is a powerful tool for organising and analysing data, and at the heart of its usefulness are formulas. If you are new to Excel, the good news is that you don’t need to be a mathematician to use it effectively. Some of the most useful formulas are also the simplest — SUM, AVERAGE, and COUNT.

In this guide, we’ll explain what these functions do, how to use them step by step, and provide practical examples so you can apply them in your own spreadsheets. By the end, you’ll be confident using these formulas to perform everyday calculations in Excel.

👉 If you’re completely new to Excel, our Microsoft Excel Introduction Course covers these formulas and more in a hands-on way, making it the ideal starting point.

Why Formulas Matter in Excel

Without formulas, Excel is just a grid of numbers and text. Formulas allow you to:

• Perform calculations automatically.
• Reduce errors compared to manual maths.
• Save time on repetitive tasks.
• Gain insights from your data.

The three most common functions used by beginners are:

• SUM – adds up a range of numbers.
• AVERAGE – calculates the mean (average) of a range.
• COUNT – counts how many numbers are in a range.

Understanding Excel Formulas

All formulas in Excel start with an equals sign (=). This tells Excel you’re entering a calculation rather than plain text.

• Example: typing =2+2 in a cell will display 4.
• You can also use references: =A1+B1 adds the contents of cells A1 and B1.

Functions are special formulas that Excel provides for common tasks. They follow the format:

=FUNCTION_NAME(arguments)


For example:

• =SUM(A1:A10)
• =AVERAGE(B2:B20)
• =COUNT(C1:C100)

Using the SUM Function

The SUM function adds up numbers in a range. Instead of typing =A1+A2+A3+A4, you can use:

=SUM(A1:A4)


Step-by-step

1. Click the cell where you want the result.
2. Type =SUM(.
3. Highlight the range of cells you want to add.
4. Type ) and press Enter.

Example

If cells A1 to A5 contain sales figures (£100, £200, £150, £175, £225), then:

=SUM(A1:A5)


returns £850.

Tip: The AutoSum button (Σ) on the Home tab lets you apply SUM instantly.

Using the AVERAGE Function

The AVERAGE function calculates the mean value.

=AVERAGE(range)


Step-by-step

1. Select a cell for the result.
2. Type =AVERAGE(.
3. Highlight the cells you want to average.
4. Close with ) and press Enter.

Example

If cells B1 to B4 contain exam scores (65, 72, 80, 93):

=AVERAGE(B1:B4)


returns 77.5.

Using the COUNT Function

The COUNT function counts the number of numeric values in a range.

=COUNT(range)


Example

If C1 to C6 contain:

• 45
• 32
• Text “N/A”
• 50
• Blank
• 60

Then:

=COUNT(C1:C6)


returns 4 (because there are four numbers).

Note: Use COUNTA to count all cells that contain anything (numbers, text, dates).

Combining SUM, AVERAGE, and COUNT

You can use these functions together. For example, to calculate an average only of cells that contain numbers, COUNT ensures blanks don’t affect the result.

Example dataset: D1:D5 contains 10, 20, blank, 30, 40.

• =SUM(D1:D5) → 100
• =COUNT(D1:D5) → 4
• =SUM(D1:D5)/COUNT(D1:D5) → 25 (average ignoring blank)

Common Mistakes to Avoid

• Forgetting the equals sign at the start.
• Using commas instead of colons in ranges (e.g., A1:A10, not A1,A10).
• Including text or blank rows that skew your averages.
• Mixing numbers stored as text (Excel won’t calculate them).

Practical Examples

Example 1: Monthly Expenses

You track your expenses in column A.

• Use =SUM(A1:A30) to calculate total spending for the month.
• Use =AVERAGE(A1:A30) to find the average daily spend.

Example 2: Test Results

Student marks in column B.

• =AVERAGE(B2:B21) gives the class average.
• =COUNT(B2:B21) shows how many results were entered.

Example 3: Sales Targets

Sales reps’ totals in column C.

• =SUM(C2:C11) gives total team sales.
• =AVERAGE(C2:C11) shows average sales per person.
• =COUNT(C2:C11) counts how many reps have submitted results.

Shortcuts and Efficiency Tips

• Use AutoSum (Σ) for quick totals.
• Double-click the formula autofill handle to copy formulas down a column.
• Press Ctrl + ` (grave accent) to toggle showing formulas instead of results.

Taking Your Skills Further

Once you’re comfortable with these basics, you’ll want to learn:

• MAX and MIN to find highest/lowest values.
• IF statements for conditional logic.
• VLOOKUP/XLOOKUP for searching data.
• PivotTables for advanced summaries.

Final Thoughts

SUM, AVERAGE and COUNT may be simple, but they are the foundation of almost everything you’ll do in Excel. Mastering these three functions will make your work quicker, more accurate, and more professional.

With these skills in place, you’ll be ready to move on to more advanced techniques — and with structured training, you’ll get there even faster.

👉 To get started with Excel the right way, check out our Microsoft Excel Introduction Course.

👉 If you already know the basics and want to go further, explore our Excel Intermediate Course

‍