Excel Formulas and Functions Basics | Learn SUM, AVERAGE, MIN, MAX with Examples !

Excel Formulas and Functions Basics | Learn SUM, AVERAGE, MIN, MAX with Examples !

Summary

Want to learn how to calculate values easily in Microsoft Excel? This guide covers the essentials of using formulas and functions such as SUM, AVERAGE, MIN, and MAX, along with how relative and absolute references affect your calculations. You'll learn how to perform basic arithmetic, use built-in functions, and set up dynamic or fixed references in your spreadsheets. These are fundamental Excel skills that every beginner should know. From simple additions to identifying highest and lowest values, this post has you covered. The full tutorial is shared below:

Introduction to Excel Formulas and Functions

Microsoft Excel is not just a spreadsheet tool—it's a powerful calculator when you use it with formulas and functions. Whether you're adding up numbers, finding averages, or comparing data, formulas and functions help you automate tasks and save time. In this guide, we'll cover the basic arithmetic formulas, essential built-in functions like SUM, AVERAGE, MIN, MAX, and also help you understand how relative and absolute cell references work.

Basic Arithmetic Formulas in Excel

To get started, you can perform simple math calculations directly in Excel using formulas. Every formula in Excel begins with an equals sign =.

Common Arithmetic Operations

• Addition: =A1 + B1
• Subtraction: =A1 - B1
• Multiplication: =A1 * B1
• Division: =A1 / B1

You can use these formulas directly in a cell. Excel automatically calculates the result once you press Enter.

Using Excel Functions: SUM, AVERAGE, MIN, MAX

Functions in Excel are pre-built formulas that simplify complex tasks. Here are some of the most used functions for basic data analysis.

1. SUM Function

Adds values in a range of cells.

=SUM(A1:A5)

2. AVERAGE Function

Calculates the mean of a set of values.

=AVERAGE(A1:A5)

3. MIN Function

Returns the smallest number in a set.

=MIN(A1:A5)

4. MAX Function

Returns the largest number in a set.

=MAX(A1:A5)

Understanding Relative and Absolute Cell References

When you copy formulas in Excel, the cell references may change depending on whether they are relative or absolute.

Relative Reference (Default)

Adjusts when the formula is copied to another cell. Example: =A1 + B1 becomes =A2 + B2 when moved down one row.

Absolute Reference

Remains constant when the formula is copied. You use a dollar sign ($) before the column or row: =A1 + $B$1.

This is especially helpful when referencing fixed values like tax rates or constants.

Conclusion

Understanding Excel formulas and functions is essential for performing efficient data analysis. By mastering basic arithmetic, functions like SUM, AVERAGE, MIN, and MAX, along with the concepts of relative and absolute references, you'll gain the confidence to work with Excel spreadsheets more effectively.

You can learn more about functions on the official Microsoft Excel Wikipedia page.

Frequently Asked Questions (FAQs)

What is the difference between a formula and a function in Excel?

A formula is an expression that calculates a value, while a function is a predefined formula like SUM or AVERAGE.

How do I copy a formula without changing cell references?

Use absolute references with dollar signs (e.g., $A$1) to keep the reference fixed when copying.

Can I use multiple functions in one formula?

Yes, Excel allows nesting of functions. For example, =SUM(A1:A5) + MAX(B1:B5).

What happens if I divide by zero in Excel?

You'll get a #DIV/0! error because division by zero is undefined.

How do I show formulas in Excel instead of the result?

Use Ctrl + ` (grave accent) to toggle between showing formulas and results in the sheet.