Module 1: Data Analytics with Excel

Excel Functions

Private and Confidential © Career247

DISCLAIMER
The training content and delivery of this presentation is confidential, and cannot be
recorded, or copied and distributed to any third party, without the written consent
of Career247.
• Logical Functions: IF, IFS,
AND, OR, NOT
Table format
• Math/Statistical Functions:
SUM, AVERAGE, COUNT,

Agenda
COUNTA, ROUND, INT, MOD
• Text Functions: LEFT, RIGHT,
MID, LEN, TRIM, UPPER,
LOWER, CONCAT, TEXTJOIN
• Date Functions: TODAY, NOW,
DATE, EDATE, DATEDIF,
NETWORKDAYS
• Lookup Functions: VLOOKUP,
HLOOKUP
Module 1 Excel Functions

Formula in Excel

Formula in Excel is an expression that

performs calculations or operations on
values, cell references, or other
formulas, and returns a result.

Private and Confidential © Career247

Module 1 Excel Functions

Explanation

Formulas allow Excel A formula always This tells Excel that

to automatically begins with an the content in the cell
calculate numbers, equal sign (=). is a formula, not plain
analyze data, and text or a number.
make decisions based
on logic.

Private and Confidential © Career247

Module 1 Excel Functions

Example 01 - Salary Computation

Scenario: A company maintains employee salary components.

Explanation
Employee Basic (₹) Bonus (₹) Total Salary (₹)

Excel automatically calculates Total Salary by

adding the Basic and Bonus using the formula Anil 30,000 5,000 =B2 + C2

Seema 28,000 4,000 =B3 + C3

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Prerequisite

Which of the following best

describes a formula in Excel?

A. A tool for formatting cells

B. A pre-defined function

C. A user-defined calculation that starts with

"=“

D. A table design feature

Private and Confidential © Career247

Module 1 Excel Functions

Prerequisite
Practical Exercise

Scenario: A small business tracks product sales and profits.

Product Cost Price (₹) Selling Price (₹) Units Sold Total Cost Total Revenue Profit
A 100 150 20 =B2*D2 =C2*D2 =F2-E2
B 200 280 10 =B3*D3 =C3*D3 =F3-E3

Tasks:

• Enter up to 20 records into Excel, follow the sample data above.

• Use formulas for Total Cost, Total Revenue, and Profit.
• Apply basic formatting (currency symbols, borders).
• Sort the data by Profit.
• Filter to show only products where Profit is more than ₹1000.
Private and Confidential © Career247
1. Logic Functions (IF,
IFS, AND, OR, NOT)
Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

IF Function

The IF function returns one value if a condition is TRUE, and another value if it's FALSE.

Syntax

=IF(logical_test, value_if_true, value_if_false)

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

IF Function
Example 01
Check if a student has passed (passing marks = 40).

=IF(B2>=40, "Pass", "Fail")

Example 02

Give a 10% discount if the purchase amount is more than ₹500.

=IF(C2>500, "10% Discount", "No Discount")

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

IFS Function

The IFS allows multiple conditions to be tested without nesting multiple IF functions.

Syntax

=IFS(condition1, value1, condition2, value2, ...)

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

IFS Function

Example 01

Assign grades

=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2<70,"D")

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Logic Functions

Which function is best to assign

a grade based on several score
ranges?

A. IFS

B. OR

C. CONCAT

D. TRIM

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

AND Function

Returns TRUE if all conditions are TRUE.

Syntax

=AND(condition1, condition2, ...)

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

AND Function

Example 01

Check if a student passed both subjects.

=AND(B2>=40, C2>=40)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Logic Functions

=AND(TRUE, FALSE) will return:

A. TRUE

B. FALSE

C. #VALUE

D. 1

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

OR Function

Returns TRUE if any one condition is TRUE.

Syntax

=OR(condition1, condition2, ...)

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

OR Function

Example 01

Check if a student passed in either subject.

=OR(B2>=40, C2>=40)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Logic Functions

Which function returns TRUE if

at least one condition is TRUE?

A. IF

B. AND

C. OR

D. NOT

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

NOT Function

Reverses the logical value. Returns TRUE if the condition is FALSE.

Syntax

=NOT(condition)

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

OR Function

Example 01

Check if a student has not passed.

=NOT(B2>=40)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Logic Functions

=NOT(TRUE) returns ?

A. TRUE

B. FALSE

C. 0

D. N/A

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

Example 01 - Identify high-value sales (₹10,000 or more)

IF Function

=IF(H2>=10000, "High Value", "Regular Sale")

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

Example 02 - Categorize sales by value

IFS Function

=IFS(H2>=100000, "Bulk Order", H2>=10000, "High Value", H2<10000,

"Low Value")

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

Example 03 - Check if sale was made by Priya Sharma in North region

AND Function

=AND(B2="Priya Sharma", C2="North")

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

Example 04 - Check if the product is either "Laptop" or "Printer"

OR Function

=OR(E2="Laptop", E2="Printer")

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

Example 05 - Identify sales that are NOT from Electronics category

NOT Function

=NOT(D2="Electronics")

Private and Confidential © Career247

Module 1 Excel Functions

1. Logic Functions (IF, IFS, AND, OR, NOT)

1. Explain how the IFS function improves readability over nested IF functions.

2. Differentiate between AND and OR with examples.

3. Check if an employee is eligible for bonus (Sales > ₹50000 AND Experience >

2 years).

4. What is the importance of logical functions in data validation tasks?

5. Describe a scenario in real life where NOT would be useful in filtering data.

Private and Confidential © Career247

Module 1 Excel Functions

1. Logical Functions: IF, IFS, AND, OR, NOT

Practical Exercise

Tasks:

• Use AND + IF to determine if employees are eligible for a bonus (Sales >
50000 AND Experience > 2).
• Apply conditional formatting to highlight eligible employees in green.
• Create a column with NOT to find employees not eligible for bonus.
Private and Confidential © Career247
2. Math/Statistical
Functions: SUM, AVERAGE,
COUNT, COUNTA, ROUND, INT,
MOD, MIN, MAX,
RANDBETWEEN, ABS,
ROUNDUP, ROUNDDOWN,
CEILING, FLOOR
Module 1 Excel Functions

2. Math/Statistical Functions: SUM, AVERAGE, COUNT,

COUNTA, ROUND, INT, MOD
Math and Statistical functions in Excel are built-in formulas designed to perform arithmetic
operations (like sum, average, round-off) and statistical analysis (like counting values) on
numeric data within a worksheet.

• These functions automate numerical processing and analysis.

• Instead of manually computing totals or averages, you can use functions like SUM or

AVERAGE.

• Functions such as INT and ROUND help in formatting numbers as per business logic, while

MOD is useful in cyclic patterns like weekly schedules.

Private and Confidential © Career247
Flashback
Module 1 Excel Functions

Math/Statistical Functions

Which function returns the total

of numeric values in a selected
range?

A. AVERAGE

B. SUM

C. ROUND

D. COUNTA

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
SUM

• Formula: =SUM(G2:G6).

• Use: Adds total units sold.

Example

• Find the total sale.

• =SUM(F2:F6)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
AVERAGE

• Formula: =AVERAGE(G2:G6)

• Use: Calculates the average unit price of products.

Example

• Find the average sale.

• =AVERAGE(F2:F6)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions

COUNT

• Formula: =COUNT(H2:H6)

• Use: Counts the number of cells with numeric data.

Example

• Find the total number of sale count.

• =COUNT(H2:H6)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions

COUNTA

• Formula: =COUNTA(E2:E6)

• Use: Counts non-empty cells.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
COUNTA
Example

• Find the total variety of products.

• =COUNTA(E2:E6)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Math/Statistical Functions

Which function can be used to

count cells containing text or
numbers (non-empty)?

A. MOD

B. COUNTA

C. ROUND(3,1)

D. COUNT

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
ROUND

• Formula: =ROUND(H2, -2)

• Use: Rounds off total sales to nearest

hundred.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
ROUND
Example

• Round up the commission to next hundred.

• =ROUND(H2, -2)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
INT

• Formula: =INT(U2)

• Use: Converts into integer.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
INT
Example

• Convert the commission to whole number.

• =INT(F2, 2)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
MOD

• Formula: =MOD(F2, 2)

• Use: To check if Units Sold is even or odd. Return 0 for Even and 1

for Odd.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
MOD
Example

• Find if the products sold is even or odd.

• =MOD(F2, 2)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Math/Statistical Functions

Which of the following returns 1

if 3 units are sold?

A. =MOD(3,2)

B. =INT(3)

C. =ROUND(3,1)

D. =COUNT(3)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
MIN

The MIN function returns the smallest numeric value in a range of cells.

• Formula: ==MIN(number1, [number2], …)

• To identify lowest sales, minimum stock, or earliest dates.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
MIN
Example

• Find the lowest value in the Total Sales column of the sales table:

• =MIN(H2:H6)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
MAX

The MAX function returns the largest numeric value in a range of cells.

• Formula: =MAX(number1, [number2], …)

• To find the highest sale, maximum stock level, or largest value in any dataset.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
MAX
Example

• To find the highest sale value in the Total Amount column:

• =MAX(H2:H6)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
RANDBETWEEN

RANDBETWEEN returns a random integer number between two specified numbers,

useful for simulations, generating test data, or creating sample values for analysis.

• Formula: =RANDBETWEEN(bottom, top)

• Bottom - the lowest number in the range and top- is the highest number in the range.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
RANDBETWEEN
Example

• Add Random Discount Between ₹100 and ₹1000 =RANDBETWEEN(100, 1000)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
ABS

The ABS function returns the absolute value of a number — that is, it removes the

negative sign and always returns a non-negative number.

• Formula: =ABS(number)

• number: The numeric value or cell reference whose absolute value is to be calculated.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
ABS
Example

• Let’s say each product has a target sales amount of ₹10,000.

• Calculate the absolute variance =ABS([@[Total Amount]] - 15000)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
ROUNDUP

The ROUNDUP function rounds a number upward, away from zero, regardless of its decimal

value. a non-negative number.

• Format =ROUNDUP(number, num_digits)

• number: The value to round.

• num_digits: The number of digits to which you want to round. 0 → nearest whole number, 1 → one

decimal place and -1 → round to nearest ten, and so on.

Private and Confidential © Career247
Module 1 Excel Functions

2. Math/Statistical Functions
ROUNDUP
Example

• If sale quantity must be processed in packs of 10, you can round it up:

• =ROUNDUP([@[Quantity]] , -1)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Math/Statistical Functions

What does =ROUNDUP(12.01, 0)

return?

A. 12

B. 13

C. 12.0

D. #VALUE!

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
ROUNDOWN

The ROUNDDOWN function rounds a number downward, toward zero, no matter the decimal value.

• Format =ROUNDOWN(number, num_digits)

• number: The value to round.

• num_digits: The number of digits to which you want to round. 0 → nearest whole number, 1

→ one decimal place and -1 → round to nearest ten, and so on.

Private and Confidential © Career247
Module 1 Excel Functions

2. Math/Statistical Functions
ROUNDOWN
Example

• Discount in the Sales Table is ₹128.95, and you want to remove the fractional value:

=ROUNDDOWN([@[Discount]], 0)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Math/Statistical Functions
Your order capacity is fixed at full
packs of 10. You receive a request
for 48 items. What will
=ROUNDDOWN(48, -1) return?

A. 40

B. 50

C. 48

D. 45

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
CEILING

Rounds a number up to the nearest specified multiple.

• Format =CEILING(number, significance)

• number: The value to round up.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
CEILING
Example

• Round Unit Price to nearest ₹5 using the Sales Table

=CEILING([@[Unit Price]], 5)

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
FLOOR

Rounds a number down to the nearest specified multiple.

• Format =FLOOR(number, significance)

• number: The value you want to round down.

• significance: The multiple to which you want to round the number.

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
FLOOR
Example

• Round Unit Price down to nearest ₹5 using the Sales Table:

=FLOOR([@[Unit Price]], 5)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Math/Statistical Functions

What is the result of =FLOOR(83,

10)?

A. 90

B. 80

C. 83

D. 70

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions

1. What is the difference between COUNT and COUNTA? When would you use each?

2. How can MOD be used to determine sales made on odd-numbered days?

3. Describe a scenario where rounding total sales is necessary in business reporting.

4. Using the sales table, calculate and interpret the average unit price of electronic items only.

5. How would a retail manager use these functions to compare performance across regions?

Private and Confidential © Career247

Module 1 Excel Functions

2. Math/Statistical Functions
Practical Exercise

Task Title: Sales Performance Analysis using Math/Stat Functions

• Calculate: Total units sold, total sales amount, and average unit price.
• Rounded values for all total sales to the nearest 100
• Add a new column Even/Odd Sales using MOD to indicate if units sold were even or odd.
• Count how many items were sold in total (COUNT vs COUNTA)
Private and Confidential © Career247
3. Text Functions: LEFT,
RIGHT, MID, LEN, TRIM,
UPPER, LOWER, CONCAT,
TEXTJOIN, TEXT
Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT

Text Functions

In Excel, not all data is numerical. Sometimes, we work with names, codes, addresses, and

other text-based values.

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Text Functions
It allows to

• Extract a part of a text (like first names from full names),

• Combine multiple text entries,

• Remove unwanted spaces,

• Convert case and much more.

Private and Confidential © Career247

• These functions are especially helpful in data cleaning, preparation, and report generation.
Flashback
Module 1 Excel Functions

Text Functions

Which Excel function returns the

total number of characters in a text
string, including spaces?

A. LEFT

B. TRIM

C. LEN

D. CONCAT

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 01 - Extracts characters from the beginning (left) of a text string.

LEFT(text, [num_chars])

Private and Confidential © Career247

=LEFT("Priya Sharma", 5)
Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 02 - Extracts characters from the end (right) of a text string.

RIGHT(text, [num_chars])

Private and Confidential © Career247

=RIGHT(B2, 6)
Flashback
Module 1 Excel Functions

Text Functions

What is the output of

=RIGHT("SC005", 2)?’

A. SC

B. 005

C. 05

D. 5

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 03 - Returns characters from the middle of a text string..

MID(text, start_num, num_chars)

Private and Confidential © Career247

=MID("SC001", 3)
Flashback
Module 1 Excel Functions

Text Functions

What does the formula

=MID("Laptop", 2, 3) return?

A. "Lap"

B. "apt"

C. "top"

D. "ato"

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 04 - Returns the total number of characters in the string.

LEN(text)

=LEN("Laptop"
Private and Confidential © Career247
Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 05 - Removes all extra spaces from the text, leaving only single spaces between
words.

TRIM(text)

=TRIM(C2)
Private and Confidential © Career247
Flashback
Module 1 Excel Functions

Text Functions
You want to remove leading and
trailing spaces from the product
name in cell A2. Which formula is
correct?

A. =CLEAN(A2)

B. =TRIM(A2)

C. =SUBSTITUTE(A2, " ", "")

D. =REMOVE(A2)

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 06 - Converts text to all uppercase letters.

UPPER(text)

=UPPER(C2)
Private and Confidential © Career247
Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 07 - Converts text to all lowercase letters.

LOWER(text)

=LOWER(C2)
Private and Confidential © Career247
Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 08 - Combines multiple text values into one.

CONCAT(text1, text2, ….)

=CONCAT(D2,E2,F2)
Private and Confidential © Career247
Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 09 - Combines text with a specified delimiter (like comma or space).

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

=TEXTJOIN(“,”,TRUE, D2, E2, F2)

Private and Confidential © Career247
Flashback
Module 1 Excel Functions

Text Functions

In the formula =TEXTJOIN("-",

FALSE, A1, "", A2), what is the
result if A1 = "East", A2 = "Zone"?

A. East-Zone

B. East--Zone

C. EastZone

D. Error

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Example 10 - Combines text with a specified delimiter (like comma or space).

=TEXT(value, format_text)

=TEXT([@[Sale Date]], "dd-mm-yyyy")

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT

1. Explain the difference between CONCAT and TEXTJOIN with an example.

2. How can LEFT and FIND be used together to extract first names?

3. What are the practical differences between TRIM() and CLEAN()?

4. Write a formula to extract the last name from a full name.

5. How would you standardize the text in a column to all uppercase in a dataset?

Private and Confidential © Career247

Module 1 Excel Functions

3. Text Functions: LEFT, RIGHT, MID, LEN, TRIM, UPPER,

LOWER, CONCAT, TEXTJOIN, TEXT
Practical Exercise

Task Title
• Extract first names of Sales Reps using LEFT and FIND.
• Use UPPER to capitalize the entire Product Category column.
• Combine Product Name and Category using TEXTJOIN (e.g., "Laptop - Electronics").
• Use LEN to count characters in each product name.
Private and Confidential © Career247 • Remove unnecessary spaces (if any) from Sales Rep names using TRIM.
4. Date Functions:
TODAY, NOW, DATE,
EDATE, DATEDIF,
NETWORKDAYS, YEAR,
MONTH, DAY, WEEKDAY,
WEEKNUM
Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

Use Case

Date functions in Excel are used to create, manipulate, and calculate dates and times for

tracking schedules, deadlines, or time intervals.

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
TODAY

• Returns the current system date.

• It updates every day you open the workbook.

Example

To find how many days have passed since each sale?

=TODAY() - I2

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
NOW

• Returns the current date and time.

• Syntax: =NOW()

Example

To log a timestamp of report generation.

=NOW()

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Date Functions

Which function provides both date

and time in Excel?

A. DATE

B. NOW

C. TODAY

D. TIME

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
DATE

• Used to create a date from Year, Month, and Day.

• Syntax: =DATE(year, month, day)

Example

Assume you want to assign a delivery date that is 5 days after sale.

=[@[Sale Date]] + 5

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
EDATE

• Adds/subtracts a number of months to a date.

• Syntax: =EDATE(start_date, months)

Example

Find the warranty expiry for products sold (say 12 months).

=EDATE([@Sale Date], 12)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Date Functions

What does the function =EDATE(A1,

-3) return?

A. Adds 3 years

B. Adds 3 days

C. Subtracts 3 months

D. Subtracts 3 years

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

DATEDIF

• Calculates the difference between two dates in days, months, or years.

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

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
DATEDIF
Example

find how many months ago a sale occurred:

=DATEDIF([@Sale Date], TODAY(), "m")

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Date Functions

What will DATEDIF("01-Jan-2024",

"01-Apr-2024", "m") return?

A. 3

B. 2

C. 90

D. 1

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

NETWORKDAYS

• Calculates working days between two dates (excludes weekends & optional

holidays).

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

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
NETWORKDAYS
Example

Calculate working days since sale:

=NETWORKDAYS([@[Sale Date]], TODAY())

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

YEAR

• Extracts the year from a date in Excel.

• Syntax: =YEAR(serial_number)

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
YEAR
Example

Find the year in which a sale occurred from the Sales Table:

=YEAR(I2)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Date Functions

What will be the result of

=DAY(DATE(2024, 2, 29))?

A. 29

B. 2

C. 24

D. 2024

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

MONTH

• Extracts the month as a number (1–12) from a valid date.

• Syntax: =MONTH(serial_number)

• serial_number: A valid Excel date or a reference to a cell containing a date.

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
MONTH
Example

Find the month in which a sale occurred from the Sales Table:

=MONTH(I2)

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

DAY

• Extracts the day of the month from a given date.

• Syntax: =DAY(serial_number)

• serial_number: A valid Excel date or a reference to a cell containing a date.

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
DAY
Example

To get the day from a sale’s date in your table:

=DAY(I2)

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

WEEKDAY

• Returns the day of the week as a number (1 = Sunday, 7 = Saturday by default).

• Syntax: =WEEKDAY(serial_number, [return_type])

• serial_number: A valid date or reference to a cell containing a date.

• return_type: (Optional) A number that determines which day is considered the first

day of the week.

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
WEEKDAY
Example

To get the day from a sale’s date in your table: =WEEKDAY(I2)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Date Functions

You want to identify all sales made

on a weekend using the Sales Table.
Which function will you use?

A. WEEKDAY + IF

B. NOW

C. YEAR + TEXT

D. VLOOKUP

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
WEEKNUM

• The WEEKNUM function returns the week number of the year for a given date.

• Syntax: =WEEKNUM(serial_number, [return_type])

• serial_number: The date for which you want to determine the week number.

• return_type (optional): A number that determines which day the week starts on:

o 1 or omitted – Week begins on Sunday.

o 2 – Week begins on Monday.

o ISO 8601 - week starts on Monday and week 1 is the first week with a Thursday.

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
WEEKNUM
Example

Find the week number of each sale date.

=WEEKNUM([@[Sale Date]], 2)

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Date Functions
You want to find which sales
occurred in the same week as
SC001. Which function should be
used?

A. YEAR

B. TEXT

C. WEEKNUM

D. NETWORKDAYS

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS

1. Explain the difference between TODAY() and NOW() with real use cases.

2. How can you use EDATE and DATEDIF to build a loan schedule?

3. Create a formula to find products sold more than 30 working days ago.

4. Why would an analyst use NETWORKDAYS instead of a simple subtraction?

5. How does DATE() help when dealing with dynamic report templates?

Private and Confidential © Career247

Module 1 Excel Functions

4. Date Functions: TODAY, NOW, DATE, EDATE, DATEDIF,

NETWORKDAYS
Practical Exercise

Task Title
• Add a new column: Days Since Sale → =TODAY()-[@Sale Date]
• Add a column: Working Days Since Sale → =NETWORKDAYS([@Sale Date], TODAY())
• Add a column: Warranty Expiry (12 months) → =EDATE([@Sale Date], 12)
• Create a column: Sale Age (Months) → =DATEDIF([@Sale Date], TODAY(), "m")
Private and Confidential © Career247 • Add a footer with current date and time using NOW()
5. Lookup Functions:
VLOOKUP, HLOOKUP
Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Use Case:

VLOOKUP (Vertical Lookup) and HLOOKUP (Horizontal Lookup) are Excel functions used to

search for a value in a table and return a corresponding value from another column or row.

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

• VLOOKUP searches a value in the first column of a table and returns a value from a

specified column in the same row.

• HLOOKUP searches a value in the first row and returns a value from a specified row in

the same column.

• Both functions are helpful for finding related data quickly from large datasets.

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Lookup Functions

Which function would you use to

look up data from a column in a
vertically arranged table?

A. HLOOKUP

B. INDEX

C. VLOOKUP

D. MATCH

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Syntax

• =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Explaination for Syntax

• lookup_value: The value you want to search for.

• table_array: The range of data containing the value.

• col_index_num / row_index_num: Column or row number of the data to return.

• [range_lookup]: Optional. Use FALSE for exact match and TRUE for approximate.

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Lookup Functions

Which function retrieves a value

from a specific column based on a
matching row?

A. INDEX

B. HLOOKUP

C. VLOOKUP

D. FIND

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Example 1 - What is the unit price of the product “Mouse”?

=VLOOKUP(F7, E2:H6, 3, FALSE)

Explanatio
n
• "Mouse" is searched in column E.

• The table range includes columns E to H.

• Column 3 of the range corresponds to Unit Price.

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Example 2 - In which region does Riya Malhotra operate?

=VLOOKUP(C9, B2:D6, 2, FALSE)

Explanatio
n
• "Mouse" is searched in column E.

• The table range includes columns E to H.

• Column 3 of the range corresponds to Unit Price.

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Lookup Functions

What does the second argument of

the VLOOKUP function represent?

A. Column number to return

B. Lookup value

C. Table range to search in

D. Match type (TRUE/FALSE)

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

To apply HLOOKUP, we shall transpose the original vertical Sales Table into a
horizontal format.

Example 3 - Get Unit Price of “Printer” using HLOOKUP

=HLOOKUP(B8, A2:F5, 3, FALSE)

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Explanatio
n
• "Printer" is the lookup value.

• A2:F5 is the horizontal table array.

• 3 is the row index of Unit Price row.

• FALSE ensures exact match lookup.

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Example 4 - Get Total Sales of “Mouse” using HLOOKUP
=HLOOKUP(B8, A2:F5, 3, FALSE)

Explanatio
n
• "Printer" is the lookup value.

• A2:F5 is the horizontal table array.

• 4 is the row index of Unit Price row.

• FALSE ensures exact match lookup.

Private and Confidential © Career247

Flashback
Module 1 Excel Functions

Lookup Functions

Which of the following is NOT true

about HLOOKUP?

A. It searches horizontally in the first row

B. It returns a value from a specified row

C. It only works with sorted data

D. It can use exact or approximate match

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

1. Explain the syntax and working of VLOOKUP using examples from the sales table.

2. Compare the structure required for VLOOKUP and HLOOKUP.

3. How would you handle a case where the lookup value isn’t found?

4. What are the limitations of VLOOKUP in dynamic tables?

5. Write a use-case for HLOOKUP in a reporting dashboard.

Private and Confidential © Career247

Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Practical Exercise

Task 1: VLOOKUP Applications

• Create a drop-down for Product Name.

• Use VLOOKUP to display:

• Unit Price

• Total Sales

• Sale Date
Private and Confidential © Career247
Module 1 Excel Functions

5. Lookup Functions: VLOOKUP, HLOOKUP

Practical Exercise

Task 2: HLOOKUP Applications

• Create a horizontal product summary table from Sales Table.

• Use HLOOKUP to fetch:

• Unit Price for “Notebook”

• Total Sales for “Headphones”

Private and Confidential © Career247

Module 1 Excel Functions

Summary
• Logical Functions (IF, IFS, AND, OR,
NOT) automate decisions like grading,
discount eligibility, and data
validation.

• Math & Statistical Functions (SUM,

AVERAGE, COUNT, ROUND, MOD,
etc.) make numerical analysis quick
and efficient.

Private and Confidential © Career247

Module 1 Excel Functions

Summary
• Text Functions (LEFT, RIGHT, MID, LEN,
TRIM, UPPER, TEXTJOIN, etc.) are
essential for data cleaning and text
manipulation.

• Date Functions (TODAY, NOW, DATEDIF,

NETWORKDAYS, etc.) help calculate
durations, set follow-ups, and manage
time-sensitive tasks.

Private and Confidential © Career247

Module 1 Excel Functions

Summary

• Lookup Functions (VLOOKUP,

HLOOKUP) retrieve related data from
tables, making reports dynamic and
reducing manual errors.

Private and Confidential © Career247

Thank You!
Q&A Session