Microsoft Excel - Working with cell references + create basic formula + Order of Operation

📘 In this section, we will learn about:

1. What is a Cell Reference – Understanding how Excel identifies each cell using column letters and row numbers.
2. Relative vs Absolute Cell References – Knowing the difference between cell references that change when copied and those that stay fixed.
3. Creating a Basic Formula – How to write simple formulas like addition, subtraction, multiplication, or division.
4. Order of Operations (Order of Precedence) – The sequence Excel follows to calculate formulas correctly using the PEMDAS/BODMAS rule.

🔹 What is a Cell Reference?

A cell reference tells Excel which cell’s value to use in a formula. A cell reference is represented with the vertical column name and horizontal row number.
Example:
If cell A1 = 10, and in B1 you type

= A1 + 5

Excel adds the value from A1 (10) + 5 → Result = 15

Here, A1 is the cell reference.

---

🔹 Types of Cell References

1. Relative Cell Reference

👉 Example: =A1 + B1

• Changes automatically when you copy the formula to another cell.
• Excel adjusts the reference based on the new position.

Example:

Cell	Formula	Result
C1	=A1 + B1	Adds A1 & B1
C2 (copied down)	=A2 + B2	Adds A2 & B2

✅ Use when you want formulas to adjust for each row or column.

---

2. Absolute Cell Reference

👉 Example: =$A$1 + B1

• The $ locks the reference — it won’t change when copied.

Meaning of $ signs:

• $A$1 → Lock column A and row 1
• A$1 → Lock only row 1
• $A1 → Lock only column A

Example: If A1 = 10, and in B1 you type:

= $A$1 + C1

Then copy to B2 →
It becomes:

= $A$1 + C2

✅ A1 stays fixed, only C1 changes to C2.

---

3. Mixed Cell Reference

As above ($A1 or A$1) — part fixed, part relative.

---

🔹 Using in a Basic Function

Let’s say:

A	B
10	2
20	3
30	4

If you want to multiply column A by a fixed number in D1 (say D1 = 5):

In C1, type:

= A1 * $D$1

Then drag down —
Result:

A	B	C (A × D1)
10	2	50
20	3	100
30	4	150

Here $D$1 ensures the multiplier stays fixed.

---

How to create a basic formula in Excel:

✅ Steps to Create a Basic Formula

1. Click on the cell where you want the result.
2. Type an equals sign (=).
3. Type your numbers or cell references with the math operator (like + for addition).
4. Press Enter.

💡 Example 1 – Add Numbers Directly

=10 + 5

👉 Result: 15

💡 Example 2 – Add Values from Cells

If cell A1 = 10 and B1 = 5,
type this in C1:

=A1 + B1

👉 Result in C1: 15

Tip: You can also use the SUM function:

=SUM(A1, B1)

or for a range:

=SUM(A1:A5)

👉 Adds all numbers from A1 to A5.

Order of operations in excel:

In Excel, formulas follow the order of operations just like in math — based on PEMDAS (or BODMAS) rules.

---

🔢 Order of Operations in Excel

Step	Operation Type	Example	Description
1️⃣	Parentheses / Brackets (P or B)	(2 + 3) * 4	Excel first evaluates anything inside parentheses () or brackets [].
2️⃣	Exponents / Orders (E or O)	2^3	Then Excel evaluates powers (like ^ means “to the power of”).
3️⃣	Multiplication & Division (MD)	6 / 2 * 3	Next, Excel does multiplication and division from left to right.
4️⃣	Addition & Subtraction (AS)	10 - 4 + 2	Finally, Excel performs addition and subtraction from left to right.

---

🧠 PEMDAS = Parentheses, Exponents, Multiplication, Division, Addition, Subtraction

🧮 BODMAS = Brackets, Orders, Division, Multiplication, Addition, Subtraction

---

✅ Example

Formula:

=10 + 2 * (6 / 3) ^ 2

Step-by-step:

1. Parentheses → (6 / 3) = 2
2. Exponents → 2 ^ 2 = 4
3. Multiplication → 2 * 4 = 8
4. Addition → 10 + 8 = 18

So Excel shows 18.

---