Excel Formulas

Lesson 5: Excel Formulas

Excel formulas are one of the most important features for anyone learning
data analysis with Microsoft Excel. Formulas allow you to perform
calculations, transform information, compare values and build automated
analysis directly inside a worksheet.

Instead of manually calculating every result, you can create a formula once
and allow Excel to calculate the result automatically.

Learning Objectives

By the end of this lesson, you should be able to:

• Explain what an Excel formula is.
• Understand how formulas begin with an equals sign.
• Perform arithmetic calculations.
• Use cell references in formulas.
• Understand relative, absolute and mixed references.
• Use operators in Excel formulas.
• Combine multiple calculations.
• Use parentheses to control calculation order.
• Copy formulas across rows and columns.
• Identify common formula errors.
• Build practical formulas for data analysis.

1. What Is an Excel Formula?

An Excel formula is an instruction that tells Excel how to calculate a
result.

Most Excel formulas begin with the equals sign:

=

For example:

=10+20

Excel evaluates the formula and returns:

30

2. Why Formulas Matter in Data Analysis

Data analysts frequently need to calculate values from datasets.

Examples include:

• Total sales.
• Profit.
• Average score.
• Percentage change.
• Discounts.
• Tax.
• Commission.
• Growth rates.
• Performance indicators.

Formulas make these calculations repeatable and reduce the need for manual
calculation.

3. Formula Structure

A simple Excel formula can contain:

• An equals sign.
• Numbers.
• Cell references.
• Operators.
• Functions.
• Parentheses.

For example:

=A1+B1

This tells Excel to add the values contained in cells A1 and B1.

4. Arithmetic Operators

Excel supports common mathematical operators.

5. Addition

The plus sign is used for addition.

=20+30

Result:

50

Using cell references:

=A2+B2

6. Subtraction

The minus sign performs subtraction.

=100-35

Result:

65

Using cells:

=B2-C2

7. Multiplication

Excel uses the asterisk symbol for multiplication.

=10*5

Result:

50

For sales analysis, you may calculate total value using:

=Quantity*UnitPrice

With cell references:

=E2*F2

8. Division

Excel uses the forward slash for division.

=100/4

Result:

25

For example, percentage calculations can use:

=Actual/Target

9. Exponents

The caret symbol ^ is used for powers.

=5^2

Result:

25

10. Using Cell References

Instead of typing numbers directly into formulas, analysts normally use
cell references.

Suppose:

A2 = 100
B2 = 50

The formula:

=A2+B2

returns:

150

If A2 or B2 changes, Excel automatically recalculates the result.

11. Why Cell References Are Powerful

Consider a sales spreadsheet where quantity and price change regularly.

Instead of calculating:

10 × 5000

you can use:

=E2*F2

If the quantity changes from 10 to 15, Excel automatically recalculates the
result.

12. Relative Cell References

A relative reference changes when a formula is copied to another cell.

For example:

=A2+B2

When copied one row down, Excel normally changes it to:

=A3+B3

This behavior is extremely useful when working with datasets.

13. Copying Formulas

You can copy a formula by:

• Copying and pasting.
• Dragging the fill handle.
• Double-clicking the fill handle where appropriate.

For example, if D2 contains:

=B2*C2

copying the formula to D3 normally produces:

=B3*C3

14. Absolute Cell References

Sometimes a formula should always refer to the same cell.

An absolute reference uses the dollar sign.

$A$1

Both the column and row are fixed.

For example:

=B2*$E$1

When copied down, B2 changes to B3, B4 and so on, but E1 remains fixed.

15. Mixed Cell References

A mixed reference fixes either the row or the column.

Examples:

$A1
A$1

In $A1, the column A is fixed while the row can change.

In A$1, the row 1 is fixed while the column can change.

16. The F4 Key

In many Excel environments, pressing F4 while editing a reference can
cycle through relative, absolute and mixed reference styles.

For example:

A1
$A$1
A$1
$A1

The exact keyboard behavior can vary depending on the operating system and
keyboard configuration.

17. Operator Precedence

Excel follows mathematical rules when evaluating formulas.

For example:

=10+5*2

The multiplication is performed before the addition.

The result is:

20

It is not 30.

18. Using Parentheses

Parentheses allow you to control the order of calculation.

For example:

=(10+5)*2

Result:

30

Parentheses make complex formulas easier to understand and help prevent
calculation mistakes.

19. Combining Operations

Excel formulas can contain several operators.

Example:

=A2+B2-C2

Another example:

=(A2+B2)*C2

Complex formulas should be structured carefully so that the intended
calculation is clear.

20. Formula Bar

The Formula Bar displays the content of the active cell.

If a cell contains a formula, the Formula Bar displays the formula rather
than only showing the calculated result.

This is useful when inspecting and troubleshooting formulas.

21. Formula Results vs Formula Expressions

Suppose cell D2 contains:

=B2*C2

If B2 is 10 and C2 is 500, Excel displays:

5000

The displayed value is the result, while the Formula Bar contains the
formula expression.

22. Editing a Formula

You can edit a formula by:

• Double-clicking the cell.
• Selecting the cell and pressing F2.
• Editing directly in the Formula Bar.

After making changes, press Enter to confirm the formula.

23. Basic Sales Calculation

Consider a sales dataset:

If Quantity is in B2 and Unit Price is in C2, the Total formula can be:

=B2*C2

Copy the formula down to calculate the other products.

24. Calculating Profit

Suppose:

• Sales amount is in B2.
• Cost amount is in C2.

Profit can be calculated with:

=B2-C2

25. Calculating Profit Margin

Profit margin can be represented as:

=Profit/Sales

For example:

=D2/B2

The result can then be formatted as a percentage.

26. Calculating Percentage

Suppose a student scored 75 out of 100.

A percentage formula could be:

=75/100

When formatted as a percentage, Excel displays:

75%

Using cells:

=B2/C2

27. Percentage Increase

A common analytical calculation is percentage increase.

A general formula is:

=(NewValue-OldValue)/OldValue

For example:

=(B2-A2)/A2

Format the result as a percentage when appropriate.

28. Percentage Decrease

Percentage decrease can be calculated using:

=(OldValue-NewValue)/OldValue

For example:

=(A2-B2)/A2

29. Using Constants in Formulas

A formula can contain a fixed number.

For example:

=B2*0.075

This could represent a 7.5% calculation.

However, for important business models, it is often better to store
important rates in dedicated cells and reference those cells.

30. Using a Fixed Tax Rate

Suppose E1 contains a tax rate of 7.5%.

A formula can be:

=B2*$E$1

The absolute reference ensures that E1 remains fixed when the formula is
copied.

31. Formula Ranges

A range represents a group of cells.

For example:

A1:A10

represents cells A1 through A10.

Another example:

B2:D20

represents a rectangular group of cells.

Ranges become especially important when working with Excel functions.

32. Formula References Across Worksheets

A formula can reference another worksheet.

For example:

=Sheet2!A1

This tells Excel to use the value in cell A1 on Sheet2.

Another example:

=Sheet2!B2*Sheet3!C2

This combines values from different worksheets.

33. Worksheet Names with Spaces

If a worksheet name contains spaces, Excel normally surrounds the sheet
name with single quotation marks.

Example:

='Sales Data'!A1

This is important when building formulas across multiple worksheets.

34. Formula Copying and Data Analysis

One of Excel’s major strengths is the ability to apply the same calculation
to thousands of records.

For example, if a dataset contains 5,000 transactions, a single formula can
be copied down the calculated column rather than manually calculating every
transaction.

35. Formula Error: #DIV/0!

The #DIV/0! error commonly appears when a formula attempts to
divide by zero or by an empty cell.

Example:

=A2/B2

If B2 contains zero, Excel may return:

#DIV/0!

36. Formula Error: #VALUE!

The #VALUE! error can occur when Excel receives an inappropriate
type of value for a calculation.

For example, attempting a mathematical operation on incompatible text data
can result in an error.

37. Formula Error: #REF!

The #REF! error indicates an invalid cell reference.

This can happen when referenced cells are deleted or a formula reference
becomes invalid.

38. Formula Error: #NAME?

The #NAME? error can occur when Excel does not recognize a
function, name or text used in a formula.

A spelling mistake in a function name is a common cause.

39. Formula Error: ####

A cell displaying a series of hash symbols does not always mean that the
formula is incorrect.

It can indicate that the column is too narrow to display the result.

Widening the column may solve the display problem.

40. Formula Auditing

Excel provides tools for examining formulas and their relationships.

Formula auditing can help identify:

• Precedent cells.
• Dependent cells.
• Errors.
• Calculation relationships.

This becomes increasingly important as worksheets become more complex.

41. Common Formula Mistakes

• Forgetting the equals sign.
• Using the wrong cell reference.
• Dividing by zero.
• Using the wrong operator.
• Forgetting parentheses.
• Copying formulas without checking references.
• Using relative references when a fixed reference is required.
• Referencing the wrong worksheet.
• Deleting cells used by formulas.
• Entering numbers as text when numerical calculations are required.

42. Formulas vs Functions

A formula is a calculation or expression created in Excel.

A function is a predefined Excel operation designed to perform a particular
calculation.

For example:

=A1+B1

is a formula.

A function example is:

=SUM(A1:A10)

Functions will be studied in greater detail in the next lesson.

43. Simple Data Analysis Example

Suppose a training center records student enrollment.

If the Students value is in B2 and Fee is in C2, revenue can be calculated
with:

=B2*C2

Copy the formula down to calculate revenue for each course.

44. Formula Best Practices

• Use clear and consistent datasets.
• Use meaningful column headings.
• Use cell references instead of repeatedly typing numbers.
• Use absolute references when a value must remain fixed.
• Use parentheses when they improve clarity.
• Check formulas after copying them.
• Keep calculation areas organized.
• Document important assumptions.
• Avoid unnecessarily complicated formulas.
• Check results against expected values.

45. Practical Exercise

Create an Excel dataset containing at least 15 sales transactions with these
columns:

• Product.
• Quantity.
• Unit Price.
• Total Sales.
• Cost.
• Profit.
• Profit Margin.

Complete the following:

1. Calculate Total Sales using Quantity × Unit Price.
2. Calculate Profit using Total Sales − Cost.
3. Calculate Profit Margin using Profit ÷ Total Sales.
4. Format Profit Margin as a percentage.
5. Copy the formulas down the dataset.
6. Create a fixed tax-rate cell.
7. Use an absolute reference to calculate tax.
8. Change one input value and observe the automatic recalculation.
9. Intentionally create a division-by-zero error and identify it.
10. Correct the error.

Knowledge Check

1. What is an Excel formula?
2. What symbol normally starts an Excel formula?
3. Which operator is used for multiplication?
4. Which operator is used for division?
5. What is a cell reference?
6. What is a relative reference?
7. What is an absolute reference?
8. What does $A$1 mean?
9. What is a mixed reference?
10. Why are parentheses useful in formulas?
11. What does #DIV/0! usually indicate?
12. What does #REF! indicate?
13. What is the difference between a formula and a function?
14. Why are cell references better than repeatedly typing numbers?

Key Terms

• Formula: An instruction used by Excel to calculate a result.
• Operator: A symbol used to perform a calculation.
• Cell Reference: The address of a cell such as A1.
• Range: A group of cells such as A1:A10.
• Relative Reference: A reference that changes when copied.
• Absolute Reference: A fixed reference such as $A$1.
• Mixed Reference: A reference where either the row or column is fixed.
• Formula Bar: The area used to view or edit cell contents.
• Function: A predefined Excel calculation.
• Precedent: A cell used by a formula.
• Dependent: A cell whose result depends on another cell.

Key Takeaways

• Excel formulas normally begin with an equals sign.
• Formulas can perform arithmetic calculations.
• Cell references make formulas dynamic.
• Relative references change when copied.
• Absolute references remain fixed.
• Mixed references fix either a row or a column.
• Parentheses can control the order of calculations.
• Formulas can reference cells on other worksheets.
• Formula errors provide clues about problems in calculations.
• Functions provide predefined calculations and will be studied next.

Lesson Summary

Excel formulas allow data analysts to perform calculations directly inside
a spreadsheet. They can be used for addition, subtraction, multiplication,
division, percentages, profit calculations, growth calculations and many
other analytical tasks.

Cell references make formulas powerful because the calculation can update
automatically whenever the underlying data changes. Understanding relative,
absolute and mixed references is essential when working with large
datasets.

Formulas become even more powerful when combined with Excel functions.
Understanding formulas therefore provides the foundation for more advanced
Excel analysis.

Next Lesson

Lesson 6: Functions

In the next lesson, you will learn how Excel functions such as
SUM, AVERAGE, MIN, MAX, COUNT and other functions can be
used to analyze datasets efficiently.