Lesson 4: Basic Formulas and Functions in Excel

Welcome to Lesson 4 of our Excel tutorial series! Now that you’re comfortable with navigating the Excel interface, managing columns, rows, and cells, and basic data entry and formatting, it’s time to unlock one of Excel’s most powerful features: formulas and functions. This lesson will guide you through creating basic formulas to perform calculations, introduce you to some essential functions, and explain the concept of cell references. Let’s get started!

Creating Basic Formulas

Formulas in Excel are expressions used to perform calculations on your data. Here’s how to create a basic formula:

• Addition (+): To add two or more numbers, click on a cell and type =2+3. Press Enter, and Excel will display the result.
• Subtraction (-): Similarly, for subtraction, you could type =5-2.
• Multiplication (*): For multiplication, =3*4.
• Division (/): For division, =8/2.

Remember, all formulas in Excel start with an equal sign (=).

Practice Exercise: Try creating formulas in a new worksheet for each of the basic arithmetic operations.

Basic Functions

Functions are predefined formulas in Excel that simplify complex calculations. Here are a few basic functions you should know:

• SUM: Adds up all the numbers in a specific range. For example, =SUM(A1:A5) adds the values in cells A1 through A5.
• AVERAGE: Calculates the average of the numbers in a range. =AVERAGE(B1:B5) will give you the average of values in cells B1 through B5.
• MIN: Finds the smallest number in a range. =MIN(C1:C5) will return the smallest value in cells C1 through C5.
• MAX: Finds the largest number in a range. =MAX(D1:D5) will return the largest value in cells D1 through D5.

Practice Exercise: Use each of the functions mentioned above on a range of data in your worksheet.

Understanding Cell References

Cell references are how you tell Excel which cells to use in your formulas and functions. There are two main types:

• Relative References: Change when a formula is copied to another cell. If you copy a formula with the reference A1 down one cell, it changes to A2.
• Absolute References: Stay constant, no matter where the formula is copied. You make a reference absolute by adding dollar signs ($), like this: $A$1.

Practice Exercise: Create a formula using relative references, then copy it to another cell. Repeat this exercise using absolute references.

Wrapping Up

By mastering basic formulas and functions, you’ve taken a significant step forward in your Excel journey. These tools are the foundation for all data analysis in Excel, and with practice, you’ll find them indispensable for your work.

For Next Time

In our next lesson, we’ll dive deeper into managing worksheets, including how to insert, rename, and organize your sheets effectively. This knowledge will help you keep your data well-organized and accessible.

Remember, the key to becoming proficient in Excel is practice. Experiment with different formulas and functions, and try using both relative and absolute cell references in your calculations. See you in the next lesson!