📘 Module 3: Essential Formulas

🎯 Purpose of Formulas in Excel

Formulas in Excel allow users to perform automatic calculations using values in cells. They're used
to:

Analyse and summarise data

Automate repetitive calculations

Maintain data consistency and reduce human error

✍ Where to Write a Formula

You can type a formula:

Directly in the cell (start with = )

Or in the formula bar (top of the Excel window)

✅ All formulas must start with an equal sign (=)

✅ BASIC FORMULA OPERATIONS
📌 Why Are We Using Cell Referencing?
Using cell references in formulas helps:

Auto-update results if input values change

Apply the same logic across rows/columns

Avoid manual errors and save time

📌 Apply Formula to Multiple Rows

1. Enter the formula in the first row.

2. Hover at the bottom-right corner → shows a + symbol

3. Drag down to copy the formula across cells

👣 Apply Across Records (AutoFill):

1. Enter =SUM(A2:A5) in the first row.

2. Select the cell.

3. Drag the bottom-right corner (the small + sign) down to apply the formula across rows.

This auto-updates cell references if relative referencing is used.

📘 Module 3: Essential Formulas 1

 📎 Types of Cell References
Type Example What it does

Relative A1 Changes as you copy the formula

Absolute $A$1 Fixed column and row

Mixed (Column Lock) $A1 Column stays fixed, row changes

Mixed (Row Lock) A$1 Row stays fixed, column changes

➕ SUM FORMULA
🧾 Syntax: =SUM(number1, number2, ...)
Or for a range: =SUM(D2:D10)

✅ Basic Usage:
Adds up all numeric values in the specified cells or range.

➕ Adding a Constant Value to Each Cell in a Column

Scenario:
Add ₹50 delivery charge to every product price.
Assume:

Prices in A2:A10

Delivery fee in cell B1 = 50

Formula in C2 : =A2 + $B$1

Now drag down the formula from C2 . The A2 part changes (A3, A4...), but $B$1 stays constant.

🧠 Tip: Use $ for constants or fixed references in repeated formulas.

📘 Module 3: Essential Formulas 2

 ✖ PRODUCT FORMULA
🧾 Syntax: =PRODUCT(number1, number2, ...)
Or: =PRODUCT(A1:A3)

✅ Usage:
Multiplies all values in the specified range.

Example:

➗ AVERAGE FORMULA
🧾 Syntax: =AVERAGE(number1, number2, ...)
Or: =AVERAGE(A1:A5)

✅ Usage:
Calculates the mean value from the range.

Example:

🧠 Tip: Use with relative references unless averaging with a fixed bonus (e.g., =AVERAGE(A2, $B$1) ).

📘 Module 3: Essential Formulas 3

 🧠 KEY TAKEAWAYS
Use Relative Reference for formulas that adapt per row/column.

Use Absolute Reference ( $ ) to fix a cell in all copied formulas.

Use Mixed Reference for semi-dynamic formulas in tables.

SUM, PRODUCT, and AVERAGE work similarly and support ranges, constants, and
relative/absolute referencing.

Drag Down (Fill Handle) is your best friend for repeating patterns efficiently.

🧮 Part 2: Mathematical Functions in Excel

🧾 Before You Begin: Formula Tips
✅ Formula Suggestion Feature
When you type = followed by letters (e.g., =S ), Excel automatically shows a dropdown list of
matching functions, such as:

=SUM
=SUBTOTAL
=SQRT

You can press Tab to autocomplete a function from the list.

🔍 Use of : in Formulas
The colon : is used to define a range between two cells.

For example:

A1:A5 → refers to cells A1, A2, A3, A4, and A5

B2:D2 → includes B2, C2, and D2

Used in almost all functions like SUM , MIN , MAX , etc.

➕ SUM()
📘 Definition:
Adds values in a selected range.

🧾 Example: =SUM(D2:D10)
✅ Use Case:
Find total marks, total sales, etc.

📘 Module 3: Essential Formulas 4

 🔁 Fill Down:
Use fill handle (bottom-right corner) to apply the formula across rows.

Excel automatically adjusts the cell references (if not using $ ).

🔻 MIN() and 🔺 MAX()

📘 Definitions:
MIN() returns the smallest value in the range.

MAX() returns the largest value in the range.

🧾 Example:
=MIN(D5:D16)
=MAX(D5:D16)

✅ Use Case:
Find the lowest/highest score in a test.

Identify the minimum or maximum sales in a month.

📘 Module 3: Essential Formulas 5

 📊 AVERAGE() / AVG()
📘 Definition:
Returns the arithmetic mean (sum ÷ count).

🧾 Syntax: =AVERAGE(A1:A5)
Note: AVG() is not a valid function. Use AVERAGE() .

✅ Use Case:
Calculate average marks.

Analyze trends like monthly sales averages.

📘 Module 3: Essential Formulas 6

 🏅 RANK()
📘 Definition:
Displays the rank of a number in a list.

🧾 Syntax: =RANK(number, ref_range, [order])

number : the value to rank.

ref_range : list of values.

order : 0 for descending (default), 1 for ascending.

✅ Example: =RANK(A2, A2:A10, 0)

Ranks the value in A2 among A2 to A10.

📦 SUMPRODUCT()
📘 Definition:
Multiplies corresponding elements in two (or more) arrays and returns their sum.

🧾 Syntax: =SUMPRODUCT(array1, array2)

✅ Use Case:
Total cost: Multiply price × quantity row-wise.

=SUMPRODUCT(C3:C11, D3:D11)/SUM(D3:D11)

🎲 RAND() & RANDBETWEEN()

📘 RAND()
Returns a random decimal between 0 and 1.

Changes every time the worksheet recalculates.

🧾 Syntax: =RAND()

📘 Module 3: Essential Formulas 7

 📘 RANDBETWEEN()
Returns a random integer between two numbers (inclusive).

🧾 Syntax: =RANDBETWEEN(bottom, top)

✅ Example: =RANDBETWEEN(B5,C5)

⚠ Issues with RAND/RANDBETWEEN:

Dynamic updates: The value keeps changing whenever the sheet recalculates (pressing
Enter, editing a cell, etc.).

❗ If you need a fixed result, use:

Paste Special → Values to lock the current result.

📘 Module 3: Essential Formulas 8

 📋 PASTE OPTIONS (for RAND/RANDBETWEEN or any function)
When copying a formula like =RANDBETWEEN(1, 100) :

Right-click ➝ Paste Options:

Option Icon Use

Formulas fx Pastes only the formula (e.g., =RANDBETWEEN(...) )

Values 123 Pastes the result, not the formula

Formatting brush Pastes the visual style, not the data

Transpose ↕↔ Switches row to column or vice versa

Paste All 📋 Pastes everything (formulas, values, formatting)

🧠 How to Paste as Values:

1. Copy the cell with the formula ( Ctrl+C )

2. Right-click the destination cell

3. Select Paste Values (123 icon)

This will lock the random number so it doesn’t change.

💡 The Power of $ in Mathematical Functions

Use $ to fix cells when applying formulas across multiple rows/columns.

✅ Example in SUMPRODUCT with fixed price column:

=SUMPRODUCT(A2:A5, $B$2:$B$5)

This ensures the second range doesn’t change when copied.

🧠 Key Takeaways
Use : to define a range of cells.

Function suggestions appear as you type (use Tab to select).

Use RAND/RANDBETWEEN with care — they change on recalculation!

Use Paste as Values to lock random or formula-generated results.

$ is essential to fix cell references in any function (especially when copying).

📘 Module 3: Essential Formulas 9

 ✍️ Part 3: Textual Functions in Excel
🎯 Why Use Text Functions?
Excel is not just for numbers — text functions help you clean, analyze, or manipulate text data like
names, addresses, codes, etc.

Text functions help you:

Clean messy data

Join names or codes

Format text (like UPPER/lowercase)

Extract part of a string (like first name, last 4 digits, etc.)

🧭 Where to Find These?

📍 Formulas Ribbon
Go to: Formulas Tab → Function Library

✂ TRIM()
📘 What It Does:
Removes all extra spaces from a text string — leading, trailing, and additional spaces in between
words.
Cleans up text from left, right, and middle (keeps single spaces between words).

🧾 Syntax: =TRIM(text)
✅ Example:
=TRIM(" Hello World ") ➝ Hello World

⚠ Note:
The output is a formula. If you want plain text, use:

Copy ➝ Paste Special ➝ Values

📘 Module 3: Essential Formulas 10

 🧩 CONCATENATE / & / TEXTJOIN

📘 Purpose:
To join multiple text strings into one.

✅ Methods:
🔹 1. Using : =A1 & " " & B1
&

🔹 2. Using (newer): =CONCAT(A1, " ", B1)

CONCAT()

🔹C1)3. Using (best for skipping blanks): =TEXTJOIN(" ", TRUE, A1, B1,
TEXTJOIN()

🔁 SUBSTITUTE()
📘 Purpose:
Replaces specific text or characters with something else.

📘 Module 3: Essential Formulas 11

 🧾 Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
instance_num is optional — use it to replace only the nth occurrence.

✅ Examples:
=SUBSTITUTE(B5, "t", "b")

🔠 UPPER(), LOWER(), PROPER()

📘 What They Do:
Function Result
=UPPER("hello") HELLO

=LOWER("HELLO") hello

=PROPER("hello world") Hello World

📏 LEN()
📘 Purpose:
Counts the number of characters (including spaces) in a text string.

🧾 Syntax: =LEN("Hello") ➝ 5
✅ Use:
Validate data (e.g., 10-digit phone numbers)

Count letters in a name

📘 Module 3: Essential Formulas 12

 🔄 LEFT(), RIGHT(), MID()
📘 Purpose:
To extract part of a string from the left, right, or middle.

🧾 Syntax & Examples:

🔹 LEFT: =LEFT("Excel", 2) ➝ "Ex"
🔹 RIGHT: =RIGHT("Excel", 2) ➝ "el"
🔹 MID: =MID("ExcelFun", 6, 3) ➝ "Fun"
Starts at character 6, takes 3 characters.

🧾 Example

🔧 Common Task: Combine Functions

Example:
Trim, Proper Case & Combine: =PROPER(TRIM(A1)) & " " & PROPER(TRIM(B1))

Use when names/emails are messy:

" joHN " + " doe " → John Doe

⚠ Paste Special: Formula to Plain Text

When using functions like TRIM , SUBSTITUTE , etc., the result stays as a formula.

To convert it to plain text:

🧾 Steps:
1. Copy the result column

2. Right-click on the target cell. Choose Paste Special → Values ( 📋123 icon)
This locks the value and removes the formula.

📘 Module 3: Essential Formulas 13

 🧠 Tips & Takeaways
Use & or TEXTJOIN() to merge cells with custom separators.

Use TRIM() to clean up unwanted spaces.

Use SUBSTITUTE() for smart text replacements — with optional instance control.

Always use Paste Special → Values when you want to freeze the result.

LEFT , RIGHT , and MID are essential for code/data extraction.

All major functions are available under the Formulas Ribbon > Text.

🧠 Part 4: Logical Functions

🎯 Why Use Logical Functions?
Logical functions help Excel make decisions based on conditions. They're powerful for creating
dynamic spreadsheets, such as:

Checking if marks are above a pass level

Giving results like "Pass"/"Fail", "Yes"/"No"

Counting/summing values based on conditions

✅ IF() Function
📘 Definition:
Checks a condition, and returns one value if TRUE, another if FALSE.

🧾 Syntax: =IF(condition, value_if_true, value_if_false)

✅ Examples:
With Text:
=IF(A2>=40, "Pass", "Fail")

If value in A2 is 40 or more, result = "Pass", else "Fail".

With Numbers:
=IF(C5≥70, “Pass”, “Fail”)

📘 Module 3: Essential Formulas 14

 🧪 AND() Function
📘 Definition:
Returns TRUE only if all conditions are TRUE.

🧾 Syntax:
=AND(condition1, condition2, ...)

✅ Example:
=AND(A2>=40, B2>=40)

Returns TRUE only if both A2 and B2 ≥ 40

Can be used inside IF:

📘 Module 3: Essential Formulas 15

 🔁 OR() Function
📘 Definition:
Returns TRUE if at least one condition is TRUE.

🧾 Syntax:
=OR(condition1, condition2, ...)

✅ Example:
=IF(OR(A2="Math", A2="Science"), "STEM", "Other")

🔢 COUNTIF()
📘 Definition:
Counts the number of cells that meet a condition.

🧾 Syntax:
=COUNTIF(range, criteria)

✅ Examples:
Count students with marks ≥ 40:
=COUNTIF(A2:A10, ">=40")

Count how many are from "Delhi":

=COUNTIF(B2:B20, "Delhi")

➕ SUMIF()
📘 Definition:
Adds up values only if they meet a condition.

🧾 Syntax:
=SUMIF(range_to_check, criteria, [sum_range])

If sum_range is omitted, Excel sums values in range_to_check .

✅ Examples:
Sum all sales above ₹10,000:
=SUMIF(A2:A20, ">10000")

Sum sales from Region "North":

=SUMIF(B5:B14, F5, D5:D14)

📘 Module 3: Essential Formulas 16

 🧠 Tips & Best Practices
Always use quotation marks around text criteria (e.g., "Pass" , ">=40" ).

Combine IF , AND , OR for complex logic.

Use COUNTIF/SUMIF to perform conditional analysis on large datasets.

You can use absolute referencing ( $ ) in logical formulas when copying across rows.

📍 Logical Functions Quick Summary

Function Use

IF() Make decision: one result for TRUE, another for FALSE

AND() TRUE only if all conditions are met

OR() TRUE if any condition is met

COUNTIF() Count how many cells meet a condition

SUMIF() Add only those values that meet a condition

⏰ Part 5: Date and Time Functions (Complete with DAYS() )

🎯 Why Use Date & Time Functions?

Date and time functions allow Excel to:

Insert real-time date/time values

Extract parts like day, month, or year

Calculate differences between two dates

Create timelines, deadlines, schedules, and reports

🗓 TODAY() and NOW()

Function Description Output Example
=TODAY() Returns current date 21-May-2025

=NOW() Returns current date & time 21-May-2025 14:35

📘 Module 3: Essential Formulas 17

 🔄 Auto-Update:
These functions recalculate automatically when the workbook changes or opens.

🧠 Freeze Result: Use Copy → Paste Special → Values to stop auto-update.

🧾 Date Formatting
To change how a date appears:

1. Select the cell

2. Go to: Home → Number Group → Format Cells (Ctrl + 1)

3. Choose from:

Short Date → 21/05/2025

Long Date → Wednesday, May 21, 2025

Time formats like 1:30 PM

🔍 DAY(), MONTH(), YEAR()

Extracts parts of a date:

Formula Result Purpose

=DAY("21-May-2025") 21 Extracts day
=MONTH("21-May-2025") 5 Extracts month (1–12)
=YEAR("21-May-2025") 2025 Extracts year

🧠 Useful in sorting or filtering by month/year.

📏 DATEDIF()
Calculates difference between two dates.

🧾 Syntax:
=DATEDIF(start_date, end_date, unit)

Unit Meaning
"d" Days
"m" Months
"y" Years

✅ Example:
=DATEDIF(B5, C5, "y") ➝ Age in years

📘 Module 3: Essential Formulas 18

 🔢 DAYS() Function
📘 Definition:
Returns the number of days between two dates — simple and direct.

🧾 Syntax:
=DAYS(end_date, start_date)

✅ Example:
=DAYS("31-Dec-2025", "01-Jan-2025") ➝ 364

⚠ Make sure the end date is second, or you’ll get a negative result.

🔄 Comparison:
Function Use Case
DATEDIF() Multiple units (years, months)
DAYS() Simple day count between 2 dates

📘 Module 3: Essential Formulas 19

 📅 NETWORKDAYS()
📘 Purpose:
Returns number of working days between two dates (excludes weekends, optionally holidays).

🧾 Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

Add a range of holidays if needed.

🧠 Examples Using Today()

Task Formula

Days till new year =DAYS(DATE(2025,12,31), TODAY())

Age in full years =DATEDIF(DOB, TODAY(), "y")

Current month =MONTH(TODAY())

Days since joining date =DAYS(TODAY(), A2) (where A2 is join date)

🧠 Key Tips
Dates are stored as serial numbers (e.g., 21-May-2025 = 45141)

Use custom formats for readable output

Use DAYS() for quick differences, DATEDIF() for flexibility

Paste TODAY() or NOW() as values to freeze

🔍 Part 6: Lookup Functions

🎯 Why Use Lookup Functions?
Lookup functions allow Excel to search for a value in a range or table and return a corresponding
result. They are essential for:

Matching product codes to prices

Fetching student names, marks, or data from reference tables

Dynamic dashboard/report generation

🔎 1. VLOOKUP()
📘 Definition:
Searches vertically in the first column of a range and returns a value from a specified column.

🧾 Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

📘 Module 3: Essential Formulas 20

 Parameter Description
lookup_value The value to find
table_array The table range to search
col_index_num The column number (starting from 1) to return value from
range_lookup TRUE = Approximate match, FALSE = Exact match

✅ Example:

📉 2. HLOOKUP()
📘 Definition:
Searches horizontally in the top row of a range and returns a value from a specified row.

🧾 Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

✅ Example:

📘 Module 3: Essential Formulas 21

 🧩 3. INDEX()
📘 Definition:
Returns the value of a cell based on row and column numbers from a defined range.

🧾 Syntax:
=INDEX(array, row_num, [column_num])

✅ Example:
=INDEX(B5::E13, 5, 3)

Returns the value from 5nd row, 3rd column of range A2:C6.

🧠 Very useful when paired with MATCH() to create dynamic lookups.

🔢 4. MATCH()
📘 Definition:
Returns the position number of a value within a row or column.

🧾 Syntax:
=MATCH(lookup_value, lookup_array, [match_type])

Match Type Description

0 Exact match

1 Less than or equal

-1 Greater than or equal

✅ Example:

📘 Module 3: Essential Formulas 22

 🧠 5. INDEX + MATCH (Better than VLOOKUP)
Combines INDEX() and MATCH() to create a more flexible and powerful lookup (especially when
lookup column is not the first).

✅ Example:
=INDEX(C2:C10, MATCH("A102", A2:A10, 0))

MATCH("A102", A2:A10, 0) returns the row number

INDEX(C2:C10, …) returns the value from C column in that row

💪 Advantages over VLOOKUP:

Doesn’t require the lookup column to be the first

Faster and more efficient with large data. Supports left lookups (VLOOKUP cannot)

🧠 Use of $ in Lookup Functions

Use Case Example Why Use $

Lock the lookup table $A$2:$D$10 Prevents the range from shifting when copied

Lock the lookup column $A2 Fix column only

Lock the lookup row A$2 Fix row only

🧪 Lookup Functions Quick Summary

Function Use Case
VLOOKUP() Vertical search in first column
HLOOKUP() Horizontal search in top row
INDEX() Get value by row/column index
MATCH() Find position of a value
INDEX + MATCH Flexible, efficient lookup alternative

📘 Module 3: Essential Formulas 23

 💡 Real-Life Example
You have a product code in A2 , and a table in F2:H10 where:

Column F: Product Code

Column G: Product Name

Column H: Price

=VLOOKUP(A2, $F$2:$H$10, 3, FALSE)

Returns the price for the matching product code.

📌 BEST PRACTICES
Use Named Ranges for easier formula reading.

Avoid hardcoded values in formulas—use cell references.

Use $ (absolute referencing) for fixed references in reusable formulas.

Apply Paste Special → Values when you want to freeze result values.

Use IF + AND/OR for multi-condition logic.

Combine TEXT + DATE + LOGIC + LOOKUP functions for powerful dashboards.

📘 Module 3: Essential Formulas 24