100+ Excel Formulas & Functions with ChatGPT 2026

You open Excel.
You have three numbers sitting in different cells.
You just want one simple answer.

But then the confusion starts.

Do you type the numbers manually?
Do you copy and paste?
Why does Excel suddenly disable parts of the ribbon when you type “=”?

If you have ever felt that small panic when trying to write your first Excel formula, this lesson clears it up step by step.

In this Udemy video, you’ll see exactly what happens inside Excel the moment you type the equals sign. That tiny “=” symbol switches Excel into formula mode. The interface changes. The Formula tab activates. Excel understands you are about to calculate something.

And then the real magic begins.

Instead of typing numbers manually, you simply:

• Type =

• Click on the first cell

• Add a plus or minus sign

• Click the next cell

• Press Enter

That’s it.

You’ll learn how Excel reads cell references like E2, J2, and N2.
You’ll see how to build a basic addition formula.
You’ll understand how to subtract values using minus signs.
You’ll watch the result update automatically when numbers change.

This is where beginners usually get stuck. They don’t realize:

• Why the ribbon changes when typing =

• Why they can click cells inside a formula

• Why the result updates automatically

• How to edit a formula after pressing Enter

By the end of this video, you’ll be able to:

• Create your first Excel formula with confidence

• Add and subtract values from different cells

• Modify formulas without breaking them

• Understand how Excel calculates cell references

• See how dynamic formulas work in real time

If you are searching for:

• how to write a formula in Excel

• how to add cells in Excel

• how to subtract numbers in Excel

• what happens when you type equals in Excel

• Excel formula basics for beginners

This lesson answers those questions clearly and practically.

And this is just the beginning.

In the next video, you’ll move beyond simple formulas and start learning Excel functions, the tools that make complex calculations simple.

Let’s build from here.

You’re staring at a few numbers in Excel.

You just want the total.

Instead, you’re wondering… do I click something? Do I type something? Why is Excel selecting random cells for me?

Most people know the SUM formula exists. But they don’t really understand how to insert a function properly. And that’s where small mistakes start.

In this lesson, you learn two simple and practical ways to insert a function in Excel. No confusion. No guesswork.

The first way: using the Insert Function button.

You select a cell.
Go to the Formula ribbon.
Click Insert Function.

Excel opens a search-style window. You type what you need, like “sum”, and it shows matching functions. Just like searching on Google.

Once you choose SUM, Excel tries to guess the range you want to add. Sometimes it’s right. Sometimes it’s completely wrong.

You’ll see:

• How to correct Excel’s automatic selection

• How to manually choose individual cells from different areas

• How Excel shows a live preview of the result before you click OK

The second way: typing the formula directly.

You type:

=SUM(

As you type, Excel suggests all functions that start with S or SU. This built-in helper saves time and reduces spelling errors.

Then you:

• Select cells manually

• Separate them using commas

• Close the bracket

• Press Enter

No need to type plus signs. No need to explain the logic. When you use SUM, Excel already knows you want addition. You just provide the numbers.

By the end of this video, you will:

• Understand how to insert a function using the Formula ribbon

• Know how to write a formula manually

• Avoid common selection mistakes

• Confidently use SUM in different scenarios

• Understand how Excel handles logic inside functions

If you’re searching for:

• How to insert a function in Excel

• How to use SUM formula step by step

• Different ways to write formulas in Excel

• Why Excel selects the wrong cells

• How to manually write Excel formulas

This lesson walks you through it clearly.

Common questions this video answers:

• How do I insert a function in Excel?

• What is the Insert Function button used for?

• Why is Excel auto-selecting the wrong range?

• How do I select multiple cells in a SUM formula?

• What is the easiest way to write formulas in Excel?

This is where real Excel confidence starts.

Next, we go deeper into SUM and then move toward functions like AVERAGE and COUNT. If you already know SUM, you can move ahead. If not, now’s the time to master it properly.

You’re calculating totals in Excel.

Price × Quantity. Simple, right?

You write the formula once. It works. You feel good.

Then you scroll down and realize… you have 12 rows. Or 100. Or 1,000.

Are you supposed to write the same formula again and again?

This is where most beginners waste time. They don’t understand referencing. And referencing is what makes Excel powerful.

In this Udemy lesson, you’ll learn how Excel uses cell references inside formulas, and how that saves you from repetitive work.

Let’s say you have:

• A list of fruits

• Price per fruit

• Quantity purchased

• A column where you need the total price

You can use:

• A simple multiplication formula like =B2*C2

• Or the PRODUCT function

Both will multiply the values from two cells and give you the total.

But here’s the real lesson.

When you copy that formula and paste it into the next row, Excel automatically adjusts the references.

• B2 becomes B3

• C2 becomes C3

• Then B4 and C4

• Then B5 and C5

You didn’t rewrite anything. Excel handled it.

This behavior is called relative referencing.

And it’s a game changer.

You’ll clearly see:

• How Excel understands where your formula is placed

• Why cell references shift when you copy and paste

• How ranges move when you copy formulas like SUM

• How to calculate totals for large datasets in seconds

Instead of creating the same formula 50 times, you create it once and let Excel do the heavy lifting.

If you’ve ever searched:

• what is cell referencing in Excel

• how to copy formula in Excel without rewriting

• why does Excel change cell references

• how to calculate total price in Excel

• Excel relative referencing explained

This video walks you through it in a simple, practical way.

You write a formula. It works perfectly.

Then you copy and paste it.

Suddenly, the numbers are wrong.

What happened?

Excel automatically shifts cell references when you copy a formula. That’s helpful most of the time. But sometimes, you don’t want certain cells to move.

This lesson explains absolute referencing in Excel and how the dollar symbol fixes that problem.

Here’s the situation:

You’re calculating a final price after a discount.
The discount percentage stays the same for all rows.
Only the product price changes.

When you copy the formula down, Excel shifts everything. Even the discount cell. That breaks your calculation.

The solution is simple:

Add a dollar symbol before the column and row.

Example:

$B$1

The dollar sign tells Excel:

• Do not move the column

• Do not move the row

• Keep this reference fixed

This is called absolute or fixed referencing.

In this video, you’ll learn:

• Why formulas change when copied

• How to lock a cell reference using $

• The difference between moving and fixed references

• How to apply this in real discount calculations

If you’re searching for:

• What is absolute reference in Excel

• How to lock a cell in Excel formula

• Why is my formula changing when I drag it

• How to use dollar sign in Excel

This lesson clears it up in minutes.

Master this early, and you’ll avoid a lot of frustrating errors later.

You keep selecting the same data again and again.

Same vendor list.
Same product column.
Same dataset.

Click. Drag. Select. Repeat.

It works. But it’s slow. And honestly, a little annoying.

There’s a smarter way.

In this Udemy lesson, you’ll learn about Named Ranges in Excel. A simple concept that makes your formulas cleaner and your workflow faster.

Here’s the idea.

Instead of selecting E5 to G10 every time, you give that range a name. Something like “VendorList” or “FruitData”.

Now Excel remembers it.

So when you write a formula like:

• =COUNTA(VendorList)

Excel already knows which cells you’re talking about. No dragging. No guessing.

In this video, you’ll learn:

• What a named range is in Excel

• How to create a named range using the Name Box

• How to use Name Manager from the Formulas tab

• How to use named ranges inside formulas and functions

• How to delete a named range

You’ll also understand when not to use named ranges. They work best for data that does not constantly expand or change. They’re especially useful for automation and structured reports.

If you’ve searched:

• what is named range in Excel

• how to create named range in Excel

• how to use Name Manager

• how to use named range in formula

This lesson gives you a clear, practical walkthrough.

Clean formulas. Faster selection. Better structure.

And next, you’ll see how to apply this in real scenarios where it actually makes a difference.

You’re buying fruits. You note down every price in Excel.

Now you just want one thing.
How much am I spending in total?

You could add every number manually. Or write a long formula with plus signs. That works… but it’s slow and messy.

This is where the SUM function in Excel saves you.

SUM is one of the most used Excel formulas. It takes numbers and gives you one clean total in a single cell.

In this lesson, you learn:

• What the SUM function does

• Why it’s faster than manual addition

• How the SUM formula syntax works

• How to pass one value or multiple values

• How to sum different ranges

The basic structure is simple:

=SUM(number1, number2, ...)

You must give at least one numeric input. After that, you can include as many cells, rows, or columns as needed.

And it gets more powerful.

You can:

• Add specific cells like A1, B2, C1

• Add entire ranges

• Add data across different sheets

• Even combine ranges from multiple sheets

That’s why SUM is more than basic addition. It’s built for real business use cases where data is spread across sheets and tables.

If you’re searching for:

• How to use SUM in Excel

• SUM formula explained for beginners

• How to add numbers from different sheets

• Excel total formula

This video walks you through it clearly.

Next, we practice real scenarios so you can use SUM confidently in any situation.

You have sales data from January, February, and March.

Three different tables.
Three different months.
And one simple question from the business team:

“What’s the total?”

At first glance, it feels messy. Do you add everything manually? Do you create three separate totals and then add those?

There’s a cleaner way.

In this Udemy lesson, you’ll learn how to use the SUM function in Excel across multiple ranges, even when the data sits in different sections.

Instead of adding one number at a time, you:

• Type =SUM(

• Select the first month’s range

• Add a comma

• Select the second month’s range

• Add another comma

• Select the third month’s range

• Close the bracket and press Enter

Excel handles the rest. It picks every individual cell from those ranges and calculates the total instantly.

You’ll also see practical examples like:

• Finding total sales for all months combined

• Calculating total price of Apples in January

• Adding Kiwi sales across multiple months

This is where Excel starts to feel powerful.

You’re not just adding numbers.
You’re answering business questions quickly and accurately.

If you’ve searched:

• how to use SUM function in Excel

• how to sum multiple ranges in Excel

• how to add data from different months in Excel

• Excel SUM formula examples

This video walks you through it step by step.

And we’re not stopping at totals.

Next, you’ll learn how to calculate averages and start analyzing data, not just adding it.

You have a list of fruit prices.

Now you don’t just want the total. You want to know the average cost.

In theory, you know how averages work. Add everything. Count the values. Divide the total by the count.

In reality, doing that manually in Excel is slow and unnecessary.

That’s where the AVERAGE function comes in.

Instead of:

• Adding all numbers

• Counting how many entries you have

• Dividing one by the other

Excel handles the full calculation in one step.

You just write:

=AVERAGE(range)

And Excel:

• Sums the numbers

• Counts how many values exist

• Divides automatically

• Returns one clean result

The syntax is almost identical to the SUM function. You must provide at least one numeric input, and you can include as many cells or ranges as needed.

No practical limit. No extra math required.

If you’re searching for:

• How to calculate average in Excel

• AVERAGE formula explained

• How to find mean in Excel

• Excel formula for average value

This lesson shows you the simple way.

Next, we move into practice and apply this to real data so you can use AVERAGE confidently in your own sheets.

You calculated totals.

Great.

But then someone asks,
“What’s the average purchase price?”

Now it’s not just about adding numbers. It’s about understanding trends.

In this Udemy lesson, you’ll learn how to use the AVERAGE function in Excel using real monthly data.

You have three months of purchase prices. January. February. March.

And three simple business questions:

• What is the average purchase price across all months?

• What is the average price of Apples in January?

• What is the average price of Kiwi across all three months?

Instead of calculating manually, you:

• Type =AVERAGE(

• Select the required range or values

• Close the bracket

• Press Enter

Excel calculates the mean instantly.

You’ll see how Excel handles:

• Multiple ranges inside one formula

• Specific product-based averages

• Values selected from different parts of the sheet

This is how you move from raw numbers to insights.

If you’ve searched:

• how to calculate average in Excel

• how to use AVERAGE function

• how to find average of specific cells in Excel

• Excel average formula example

This lesson keeps it simple and practical.

Next, we take it one step further and learn how the COUNT function works, so you can measure not just values, but volume.

You want a quick answer.

How many transactions happened?
How many entries are there?
How many records did I create?

That’s where counting functions in Excel come in.

In this lesson, you learn two important formulas: COUNT and COUNTA.

First, COUNT.

COUNT only works with numbers.

If your column has numeric transaction values, COUNT will go through them and tell you how many numeric entries exist. It ignores text. Completely.

That’s important.

So if you’re searching for:

• How to count numbers in Excel

• COUNT formula not working

• Why is COUNT returning zero

The answer is simple. COUNT only counts numeric values.

Now comes COUNTA.

COUNTA counts anything that is not empty.

That means:

• Text

• Numbers

• Mixed values

If you want to count customer names, vendor names, or any text-based field, COUNTA is the correct function.

One key takeaway from this video:

COUNT is limited to numbers.
COUNTA is more flexible and can count both text and numbers.

The syntax is simple for both:

=COUNT(range)
=COUNTA(range)

If you understand COUNTA well, you can often skip COUNT altogether.

You’ve used SUM.
You’ve used AVERAGE.

Now comes a simple but important question:

“How many transactions actually happened?”

Not totals.
Not averages.
Just the count.

In this Udemy lesson, you’ll practice the COUNT function in Excel and understand where it works, and where it doesn’t.

To find total transactions across January, February, and March, you:

• Type =COUNT(

• Select the numerical ranges from all three months

• Close the bracket

• Press Enter

Excel counts only the cells that contain numbers.

And here’s where it gets interesting.

If a cell is empty, COUNT ignores it.
If a cell contains 0, COUNT includes it.

Zero is still a number. Blank is not.

You’ll also see what happens if you accidentally include a header while selecting data. COUNT ignores text automatically and still gives the correct result.

In this video, you’ll clearly understand:

• How to use the COUNT function in Excel

• Why empty cells are ignored

• Why zero values are counted

• What happens when text is included in the selection

If you’ve searched:

• how to count cells in Excel

• difference between blank and zero in Excel

• why COUNT is not counting some cells

• Excel COUNT function example

This lesson makes the behavior crystal clear.

You use COUNTA.

It gives you a number.

But something feels off.

That’s because COUNTA counts everything that isn’t empty, including headers.

In this practice session, you see a common mistake.

You select the full column, including the header.
COUNTA happily counts it.
Result? Wrong answer.

COUNTA does not know what a “header” is. It only checks:
Does this cell have a value? If yes, it counts it.

Key lesson:

• Always select only the actual data

• Avoid including headers when using COUNTA

• Double-check your selected range

You also learn how to fix formula mistakes.

If you selected the wrong range, you can:

• Double-click the cell and edit the formula

• Or edit directly in the formula bar

• Add more ranges using commas

• Close the bracket and press Enter

Same formula. Just corrected inputs.

Then comes a practical question:

How many times was “Pineapple” purchased?

Instead of counting numbers, you use COUNTA on text values.

You can:

• Select individual cells

• Select continuous ranges

• Combine both in one formula

Excel allows mixing single cells and ranges inside the same function.

One more caution:

If you select numeric columns with headers, COUNTA will still count the header. It counts values, not “types of data.”

If you’re searching for:

• Why is COUNTA giving wrong result

• How to edit an Excel formula

• How to count text in Excel

• How to count specific items in Excel

This lesson clears it up.

You’re looking at a long list of fruit prices.

And someone asks,
“What’s the highest price we’ve ever charged?”
Then another question follows,
“What’s the lowest?”

You could scan the entire sheet manually.

Or you could let Excel do it in one second.

In this Udemy lesson, you’ll learn how to use the MAX and MIN functions in Excel to quickly find the highest and lowest values in a dataset.

To find the most expensive fruit, you:

• Type =MAX(

• Select the numerical range

• Close the bracket

• Press Enter

Excel scans the entire range and returns the highest value.

To find the least expensive fruit, you:

• Type =MIN(

• Select the same range

• Press Enter

Excel returns the smallest number in that dataset.

Simple. Fast. Accurate.

The syntax works just like SUM, AVERAGE, and COUNT. You pass numerical values, and Excel gives you one clear answer.

In this video, you’ll learn:

• How to use MAX function in Excel

• How to use MIN function in Excel

• How to find highest value in Excel

• How to find lowest value in Excel

• When to use these functions in real business scenarios

These are foundational Excel functions that help you analyze data quickly instead of guessing.

You have months of purchase data.

Now you want quick answers:

What was the highest amount spent?
What was the lowest amount spent?

Instead of scanning rows manually, you use MAX and MIN in Excel.

For the first question, finding the maximum purchase across all months is simple:

=MAX(range1, range2, range3)

You can select multiple ranges from different months, separate them with commas, close the bracket, and Excel instantly gives you the highest value.

But here’s something important.

MAX only works with numeric values.

If you’re trying to find the maximum amount spent only on Amazon purchases, you must:

• Manually identify Amazon rows

• Select only the numeric cells

• Avoid selecting any text

MAX ignores text, so careful selection matters.

Then comes the MIN function.

If you want the minimum amount spent for February and March combined:

=MIN(February_range, March_range)

That’s it. Excel scans both ranges and returns the smallest value.

In this lesson, you see:

• How to use MAX across multiple sheets or ranges

• How to filter logically before selecting values

• How to use MIN for combined datasets

• Why correct cell selection matters

If you’re searching for:

• How to find maximum value in Excel

• How to use MIN formula

• Excel highest and lowest value formula

• MAX function not working

This video shows the practical approach.

You open a dataset.

Customer names. Orders. Pizza types. Toppings.

And immediately you notice the problem.

Duplicates.

John Smith appears twice.
Same pizza combo shows up again.
One full order row is copied and pasted.

Now the question is simple:

“How do I get only the unique values in Excel?”

In this Udemy lesson, you’ll learn the UNIQUE function in Excel Office 365. A powerful dynamic formula that automatically removes duplicates without filters or manual cleanup.

First, you’ll see the basics.

• Type =UNIQUE(

• Select the data range

• Close the bracket

• Press Enter

Excel instantly spills the unique values into a new list.

No copy-paste.
No advanced filter.
No manual sorting.

Then it gets more interesting.

You’ll learn how to:

• Extract unique customer names from a list

• Find unique combinations of two columns, like Pizza Type + Toppings

• Detect duplicate rows in a full dataset

• Return unique rows vs unique columns

• Return only distinct items or exclude duplicates completely

You’ll even see how changing one value in the original data automatically updates the unique list. That’s the power of dynamic arrays in Excel 365.

Important: This function works only in Excel Office 365 or newer versions that support dynamic array formulas.

If you’ve searched:

• how to remove duplicates in Excel 365

• how to use UNIQUE function in Excel

• how to get unique rows in Excel

• Excel dynamic array formula example

This lesson gives you practical business-style examples, not just theory.

And this is just the beginning of Office 365 formulas.

Your data looks fine.

Until you try to analyze it.

Months are in columns.
Days are in rows.
And now your analysis or summary just doesn’t flow the way you want.

You could copy, paste, and transpose everything manually.

Or you could use the TOCOL function in Excel 365.

In this lesson, you learn how TOCOL reshapes horizontal data into a clean vertical list.

Here’s the problem:

For better analysis, you want:

• Days in columns

• Months in rows

• Or simply a vertical structure instead of a horizontal one

TOCOL does exactly that.

Basic structure:

=TOCOL(array, [ignore], [scan_by_column])

You:

• Select the array you want to rearrange

• Decide whether to ignore blanks or errors

• Choose whether to scan by row or by column

If you scan by row, Excel reads the entire row and stacks it vertically.
If you scan by column, it reads column by column.

Within seconds, your horizontal sales data becomes a neat vertical list. No manual transpose. No copy-paste chaos.

You can even:

• Apply TOCOL to multiple months

• Use it on headers

• Combine it with SUM or other aggregation functions

• Format the results cleanly with Format Painter

If you’re searching for:

• How to convert row to column in Excel

• TOCOL function explained

• Excel 365 new functions

• How to reshape data in Excel

This video shows a practical, modern approach.

You get a list of customer names.

All stacked in one long column.

Then someone says,
“Can you arrange this properly into rows?”
And also,
“Remove the blanks.”

Now you’re thinking about copy-paste. Maybe manual rearranging. Maybe deleting empty cells one by one.

There’s a faster way.

In this Udemy lesson, you’ll learn the TOROW function in Excel Office 365. It helps you convert data from columns into a single row, cleanly and automatically.

Here’s what you’ll see:

• How to use =TOROW(

• How to select one column and convert it into a row

• How to combine multiple columns into one single row

• How to control whether blanks are kept or ignored

• How Excel handles empty cells and why they sometimes show as zero

For example:

• Convert May customer data from a column into a row

• Combine May and June customer lists into one row

• Ignore blank cells instead of showing zeros

• Clean messy lists without manual editing

The best part? It updates dynamically. No repeated formatting.

Important: TOROW works only in Excel Office 365 or versions that support dynamic array functions.

If you’ve searched:

• how to convert column to row in Excel 365

• how to remove blanks while rearranging data

• how to merge columns into one row

• Excel TOROW function example

This lesson shows you exactly how.

Oren has learned SUM, AVERAGE, COUNT, MAX, MIN.

Now he’s thinking bigger.

Can Excel calculate totals based on a condition?
Can it filter and calculate at the same time?

Yes. That’s where SUMIF comes in.

Imagine you gave pocket money every day. Now you only want to know:

How much did I give on Mondays?

Manually, you would:

• Find every “Monday”

• Pick the number next to it

• Add them all

That’s slow.

SUMIF does this in one step.

What SUMIF does:

1. Looks at a column with your criteria

2. Filters only matching values

3. Adds numbers from another column

Example use cases in this lesson:

• Total purchase amount only for March

• Total money spent on Amazon

• Total amount spent on Kiwi

The structure is simple:

=SUMIF(criteria_range, criteria, sum_range)

• Criteria range: where Excel should look for the filter value

• Criteria: what you are filtering, like "March" or "Amazon"

• Sum range: where the numbers to be added are

You also learn something powerful.

Instead of typing “Kiwi” manually, you can reference a cell that contains “Kiwi”. If you later change that cell to “Apple”, the result updates instantly.

That’s dynamic Excel.

If you’re searching for:

• How to use SUMIF in Excel

• Excel formula to sum based on criteria

• How to sum values for specific month

• How to calculate total by category in Excel

This lesson gives you practical clarity.

You try to use SUMIF.

It works for simple things like “Apple”.

But then someone asks:

“What’s the total amount spent on Apple and Pineapple together?”

Now your simple formula stops working.

Why?

Because the data isn’t clean. It’s messy.
Fruit names are buried inside full sentences.
Vendor names contain extra characters.
Months need pattern matching.

This is where wildcard characters in Excel change the game.

In this Udemy lesson, you’ll learn how to use wildcards inside SUMIF to solve problems that basic formulas cannot handle.

You’ll understand three wildcard characters:

• Asterisk * → means “anything”

• Question mark ? → means “one single character”

• Tilde ~ → used to escape wildcard symbols

Here’s what you’ll see in action:

Using * with SUMIF

• Calculate total amount for Apple and Pineapple together

• Include words that end with “Apple”

• Include text that contains “O” anywhere in a vendor name

Using ? with SUMIF

• Calculate total sales for months that start with “J”

• Match fixed-length patterns like Jan, Jun, Jul

• Control how many characters Excel looks for

You’ll clearly see how:

• “*Apple” matches text that ends with Apple

• “Apple” matches text that contains Apple anywhere

• “J??” matches any three-letter word starting with J

This is extremely useful when:

• Data is stored in sentences

• You don’t have separate clean columns

• You need flexible text matching

• SUMIF alone isn’t enough

If you’ve searched:

• how to use wildcards in Excel

• SUMIF with contains text

• how to match partial text in Excel

• Excel asterisk example

• Excel question mark wildcard

This lesson connects everything step by step.

Wildcards may feel slightly advanced at first.

But once you understand them, your formulas become far more powerful.

And we’re just getting started with building smarter Excel logic.

You’ve learned how to total based on a condition with SUMIF.

Now the next question is obvious.

Can Excel:

• Average based on a condition?

• Count based on a condition?

Yes. That’s where AVERAGEIF and COUNTIF come in.

Let’s start with AVERAGEIF.

Imagine you gave pocket money only on Mondays. Instead of manually adding those values and dividing by how many Mondays exist, AVERAGEIF does both steps automatically.

It:

1. Filters your data based on a condition

2. Picks the matching numbers

3. Adds them

4. Divides by how many matched

All in one formula.

Structure:

=AVERAGEIF(criteria_range, criteria, average_range)

You saw examples like:

• Average amount spent in March

• Average spending on Amazon

• Average purchase price for Kiwi

• Average for Apple and Pineapple together using wildcards

• Average for vendors containing the letter “O”

• Average for months starting with “J”

Wildcards make this powerful:

• apple matches apple and pineapple

• o matches any name containing “o”

• J* matches anything starting with J

Now COUNTIF.

COUNTIF is simpler.

It filters based on a condition and just counts how many times it appears. No extra calculation column needed.

Structure:

=COUNTIF(criteria_range, criteria)

Examples covered:

• How many purchases were made in March

• How many times Amazon appears

• How many Kiwi purchases

• How many items match apple and pineapple

• How many vendors contain “O”

• How many months start with “J”

If you’re searching for:

• How to use AVERAGEIF in Excel

• How to use COUNTIF with text

• Excel wildcard examples

• How to count based on condition

• How to average values with criteria

This lesson gives you real business-style use cases, not just theory.

You’ve mastered SUMIF, COUNTIF, AVERAGEIF.

Then the question changes.

“What’s the total spent on Kiwi in June?”
“And only if it was bought from Amazon?”
“And only in March?”

Now one condition isn’t enough.

This is where the IFS family of formulas steps in.

In this Udemy lesson, you’ll learn how to handle multiple conditions using:

• IFS

• SUMIFS

First, you’ll understand the logic behind IFS.

Instead of one true/false check like IF, IFS allows you to test multiple conditions in sequence.

For example:

• If price is greater than 6 → Above Budget

• If price is less than 3 → Below Quality

• Otherwise → Within Budget

You keep adding logic checks. No complicated nested IF statements.

Then we move to SUMIFS.

This is where things get powerful.

SUMIFS allows you to:

• Apply multiple criteria

• Filter by month and vendor

• Filter by fruit and vendor

• Filter by fruit and month

• Calculate totals only when all conditions match

For example:

• Total spent in March from DoorDash

• Total spent on Apple from Amazon

• Total spent on Kiwi in June

You’ll also learn something important: dynamic referencing.

Instead of typing criteria manually inside double quotes, you reference a cell. Change the cell value, and the formula updates automatically.

That means:

• No rewriting formulas

• No constant editing

• Faster analysis

If you’ve searched:

• how to use SUMIFS in Excel

• difference between SUMIF and SUMIFS

• how to apply multiple conditions in Excel

• Excel multiple criteria formula

• dynamic criteria in SUMIFS

This lesson connects everything clearly and practically.

You’ve used SUMIF with one condition.

Now the question gets tougher.

What if you need to calculate based on two or three conditions at the same time?

That’s where SUMIFS comes in.

Example 1:
What is the total amount spent on Kiwi, but only when the price was greater than 5?

Now you’re filtering by:

• Fruit = Kiwi

• Price > 5

SUMIFS handles multiple criteria in one formula.

Structure:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

Important insight from this lesson:

You can use the same column as both:

• The sum range

• And a criteria range

In the “greater than 5” example, the price column is used twice:

• Once for adding

• Once for filtering with ">5"

Example 2:
Total spent on Kiwi, but only between July 10 and July 25.

Now you’re applying three filters:

• Fruit = Kiwi

• Date >= July 10

• Date <= July 25

You can even use the same date column twice with different operators.

That’s powerful.

Example 3:
Total amount spent on Apple and Pineapple combined.

Using wildcards:

"apple"

This captures both Apple and Pineapple in one condition.

Key takeaways:

• SUMIFS supports multiple criteria

• You can use operators like >, <, >=, <=

• Wildcards work inside SUMIFS

• The same column can be used multiple times for different conditions

If you’re searching for:

• How to use SUMIFS in Excel

• Excel sum with multiple conditions

• How to sum values between two dates

• How to sum values greater than a number

• Difference between SUMIF and SUMIFS

This lesson connects all of it.

You don’t just want totals anymore.

You want patterns.

“What’s the average I spend in March from DoorDash?”
“What’s the average amount I spend on Kiwi, but only if it’s above $5?”
“What’s my average spending between two specific dates?”

Now one condition isn’t enough.

This is where AVERAGEIFS becomes powerful.

In this Udemy lesson, you’ll learn how to calculate averages using multiple criteria at the same time.

Think of it like this:

First, Excel filters the rows that match your conditions.
Then it calculates the average only for those filtered rows.

You’ll practice real business-style scenarios like:

• Average purchase price in March from DoorDash

• Average spending on Apple from Amazon

• Average amount spent on Kiwi when price is greater than 5

• Average spending between two specific dates

• Average for products that contain “Apple” using wildcards

• Average for vendors whose names contain “O”

• Average for months that start with “J”

You’ll also see how to use:

• Text criteria inside double quotes

• Greater than and less than operators

• Date-based conditions

• Wildcards like * for flexible matching

Most importantly, you’ll understand the structure:

=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2…)

Once this clicks, your analysis becomes sharper. You’re no longer calculating blindly. You’re asking smarter questions and getting precise answers.

If you’ve searched:

• how to use AVERAGEIFS in Excel

• Excel average with multiple criteria

• how to average with date conditions in Excel

• Excel average greater than value

• Excel wildcard in AVERAGEIFS

This lesson ties everything together in practical examples.

You open a report.

It shows a date.

But it’s yesterday’s date.

Now you have to update it manually. Again.

This is where the TODAY function in Excel becomes useful.

TODAY is a standalone function. It needs no input.

You just write:

=TODAY()

That’s it.

Excel automatically returns the current date based on your system.

No arguments. No ranges. No criteria.

Why is this powerful?

Because the date updates automatically every single day. That makes it perfect for:

• Dynamic reports

• Dashboards

• Age calculations

• Deadline tracking

• Comparing past dates with today

In this lesson, you see how simple the syntax is and how quickly it works inside a practice file.

If you’re searching for:

• How to insert today’s date in Excel automatically

• Excel formula for current date

• TODAY function explained

• How to create dynamic date in Excel

This is your starting point.

You have a full date in Excel.

23/06/2022
15/07/2022
01/03/2023

But all you need is the day.

Just the number.
Not the month.
Not the year.

Instead of manually typing it out or splitting the column, there’s a simple function for that.

In this Udemy lesson, you’ll learn how to use the DAY function in Excel.

The job of this formula is straightforward:

• Take a complete date

• Extract only the day portion

• Return it as a number

For example:

If the date is 26 June 2022,
=DAY(date) will return 26.

That’s it.

In the practice file, you’ll:

• Select the sales date column

• Use =DAY( and select the cell

• Press Enter

• Copy the formula down

Excel automatically pulls the correct day from every row.

If you’ve searched:

• how to extract day from date in Excel

• Excel DAY function example

• how to separate day from date in Excel

• Excel date formulas for beginners

This lesson shows the cleanest way to do it.

You have a full date in Excel.

But you don’t need the whole thing.

You just want the month.

Instead of manually reading it or splitting the text, Excel has a simple solution: the MONTH function.

MONTH extracts the month number from a date.

For example:

If the date is 15-06-2024,
=MONTH(date_cell)

returns 6.

The syntax is straightforward:

=MONTH(date)

You simply pass the date cell as input. Excel reads the date serial number in the background and returns the month as a number from 1 to 12.

In this lesson, you:

• Extract month values from a list of dates

• Copy the formula down for multiple rows

• Understand how Excel reads dates internally

If you’re searching for:

• How to extract month from date in Excel

• Excel month formula

• How to convert date to month number

• MONTH function explained

This video shows the quick and practical method.

You have a full date in Excel.

23/06/2022
15/07/2021
01/03/2023

But this time, you only care about the year.

Not the day.
Not the month.
Just the year.

Instead of manually reading each date, Excel can extract it instantly.

In this Udemy lesson, you’ll learn how to use the YEAR function in Excel.

The formula is simple:

• Type =YEAR(

• Select the date cell

• Close the bracket

• Press Enter

If the date is 26/06/2022, the formula returns 2022.

That’s it.

In the practice sheet, you’ll:

• Apply the YEAR function to a list of dates

• Copy the formula down

• Extract the year for every row in seconds

If you’ve searched:

• how to extract year from date in Excel

• Excel YEAR function example

• how to separate year from date in Excel

• Excel date formulas for beginners

This lesson shows you the quickest way to do it.

You check your report.

It shows the correct date.

But the time is missing. Or worse, it’s outdated.

That’s where Excel’s time functions come in.

First, the NOW function.

=NOW()

It’s a standalone function. No inputs required.

It returns:

• Current date

• Current time

Both together, based on your system clock.

And here’s something important. Every time Excel recalculates or refreshes, NOW updates automatically. That means it’s dynamic.

Next, breaking time into parts.

If a cell contains date and time like 11:26:15, you can extract:

Hour
=HOUR(cell)

Returns 11.

Minute
=MINUTE(cell)

Returns 26.

Second
=SECOND(cell)

Returns 15.

Even if seconds are not clearly visible in the cell format, Excel still stores them internally. You can see them in the formula bar.

In this lesson, you learn:

• How to use NOW for real-time date and time

• How to extract hour from a timestamp

• How to extract minute and second

• How dynamic time updates work in Excel

If you’re searching for:

• Excel current date and time formula

• How to extract hour from time in Excel

• MINUTE and SECOND function explained

• How NOW function works

This gives you the foundation.

You made a sale months ago.

Still no payment.

Now the question is simple:

“How many days has it been?”

You could count manually on a calendar.

Or you could let Excel calculate it instantly.

In this Udemy lesson, you’ll learn how to use the DAYS function in Excel to calculate the difference between two dates.

The formula is straightforward:

=DAYS(end_date, start_date)

Excel calculates the total number of calendar days between those two dates.

For example:

• Date of birth vs today

• Sale date vs current date

• Invoice date vs payment date

In the practice file, you’ll:

• Use TODAY() as the current date

• Select the sale date as the start date

• Calculate how many days have passed

• Apply absolute referencing to keep the end date fixed

• Copy the formula down for multiple rows

You’ll also understand why fixing the reference with F4 matters when copying formulas.

If you’ve searched:

• how to calculate days between two dates in Excel

• Excel DAYS function example

• difference between two dates in Excel

• how to lock cell reference in Excel

This lesson gives you the exact method.

You close a deal.

Now someone asks,
“How many working days has it been since the sale?”

You could count manually. Subtract dates. Then remove weekends.

Or you can use NETWORKDAYS in Excel.

NETWORKDAYS calculates the number of working days between two dates.

By default, it excludes:

• Saturdays

• Sundays

That’s the standard weekend logic.

The syntax is simple:

=NETWORKDAYS(start_date, end_date, [holidays])

You provide:

• Start date

• End date

• Optional holiday range

Excel automatically removes weekends. If you pass a list of holiday dates, it removes those too.

In this lesson, you:

• Calculate working days from a sale date to today

• Use TODAY as a dynamic end date

• Include optional holidays in the calculation

• Apply absolute referencing before copying the formula

If you’re searching for:

• How to calculate working days in Excel

• Excel formula to exclude weekends

• NETWORKDAYS explained

• How to count business days between two dates

This function handles it cleanly.

You’re calculating time between two dates.

But this time, total days aren’t enough.

You only care about working days.

Maybe it’s a project deadline.
Maybe it’s a product warranty.
Maybe it’s a payment cycle.

And weekends matter.

In this Udemy lesson, you’ll learn how to use the NETWORKDAYS.INTL function in Excel.

Unlike the basic DAYS function, this one:

• Calculates only working days

• Excludes weekends

• Lets you customize which day counts as weekend

The structure looks like this:

=NETWORKDAYS.INTL(start_date, end_date, weekend)

You’ll practice a scenario like:

• Purchase date to expiry date

• Calculate only working days

• Change the weekend from the standard Saturday–Sunday

• Set Wednesday as the weekend instead

Excel gives you full control over which days are counted as working days.

You’ll also see:

• How to select different weekend patterns

• How to copy the formula across multiple rows

• Why adjusting references before copying matters

If you’ve searched:

• how to calculate working days in Excel

• Excel NETWORKDAYS.INTL example

• how to change weekend days in Excel

• difference between NETWORKDAYS and NETWORKDAYS.INTL

This lesson makes it practical and easy to apply.

You have a number in Excel.

7.85.

But for reporting reasons, you don’t want 7.85.
You want it rounded and displayed as 8.
And sometimes, you even want that result stored as text.

That’s where the TEXT function comes in.

TEXT does not calculate.

It formats.

It takes a value and converts it into text based on the format you define.

Basic structure:

=TEXT(value, "format")

In this example:

• You select 7.85

• Use a format like "#"

• Excel rounds and returns 8

But here’s the key detail.

The result looks like a number.
It behaves like text.

If you try to use SUM on those converted values, Excel won’t calculate them properly. Because TEXT outputs text, not numeric values.

You also notice something important about rounding:

• 12.5 becomes 13

• 6.35 becomes 6

• 6.5 becomes 7

Excel follows standard rounding rules when formatting this way.

In this lesson, you learn:

• How to convert numbers to formatted text

• How to round using TEXT

• Why TEXT results can’t be used in calculations

• When formatting is different from calculating

If you’re searching for:

• How to convert number to text in Excel

• Excel TEXT function explained

• How to round numbers using formula

• Why SUM is not working after formatting

This is where that confusion starts getting cleared.

You have a date in Excel.

15/11/2022

You know how to extract the month number using =MONTH().

It gives you 11.

But that’s not what you want.

You want “November”.
Or maybe just “Nov”.

Numbers aren’t always presentation-friendly. This is where the TEXT function becomes useful.

In this Udemy lesson, you’ll learn how to extract the month name from a date using the TEXT function in Excel.

Here’s how it works:

• Type =TEXT(

• Select the date cell

• Add a comma

• Enter the format inside double quotes

If you use:

• "mmmm" → Excel returns the full month name, like November

• "mmm" → Excel returns the short version, like Nov

So instead of getting 11, you control how the month appears.

That’s the real power here.

You’re not just extracting information. You’re formatting it exactly the way you want.

If you’ve searched:

• how to get month name from date in Excel

• Excel TEXT function month format

• convert month number to month name in Excel

• Excel date formatting formula

This lesson shows you the cleanest way to do it.

You have a date and time stamp.

But you only need one tiny part of it.

Just the AM or PM.

The problem?
You can’t clearly see AM or PM in the cell. But when you click it, the formula bar shows the full time format.

Instead of changing cell formatting manually, you can use the TEXT function.

Structure:

=TEXT(value, "format")

To extract only AM or PM:

=TEXT(cell, "AM/PM")

That’s it.

Excel reads the full timestamp and returns just:

AM
or
PM

Nothing else.

You can also customize the format further.

For example:

=TEXT(cell, "hh:mm AM/PM")

This gives you:

• Hour

• Minute

• AM or PM

All formatted exactly how you want.

Key takeaway:

TEXT doesn’t change the original value.
It reformats it and outputs the result as text.

In this lesson, you learn:

• How to extract AM or PM from a timestamp

• How to customize time display using TEXT

• How to format hour and minute using formula

• Why formatting through formula can be more flexible

If you’re searching for:

• How to extract AM or PM in Excel

• Excel TEXT function time format

• How to show time with AM PM using formula

• How to format timestamp in Excel

This approach gives you full control.

You have a date.

You already know how to extract the day using =DAY().

It gives you 23.

But sometimes, you don’t want a number you can calculate with.
You want formatted text.

Maybe:

• “23” as text

• “Wed” instead of Wednesday

• A neatly formatted label for a dashboard

This is where the TEXT function becomes flexible.

In this Udemy lesson, you’ll explore more practical uses of the TEXT function in Excel.

Here’s what you can do:

Extract day as text

• =TEXT(date,"dd") → returns 23 as text

Get short weekday name

• =TEXT(date,"ddd") → returns Wed

• =TEXT(date,"dddd") → returns Wednesday

Format months

• "mmm" → Nov

• "mmmm" → November

The key idea is simple:

TEXT takes a numeric or date value as input and converts it into formatted text.

Important limitation:

• TEXT works on numbers and dates

• It does not transform plain text into something else

So your input must be a valid date or number. The output will always be text.

If you’ve searched:

• how to convert date to day name in Excel

• Excel TEXT function examples

• format date as text in Excel

• how to show weekday from date in Excel

This lesson gives you practical formatting tricks you’ll actually use in reports and dashboards.

You notice something small.

But annoying.

Someone typed dress size as S instead of XS.
Or a name has the wrong letter in it.

You don’t want to edit everything manually.

That’s where the SUBSTITUTE function helps.

SUBSTITUTE replaces specific text inside a cell.

Structure:

=SUBSTITUTE(text, old_text, new_text, [instance_number])

Let’s break it down.

• text: the original cell you want to change

• old_text: what you want to replace

• new_text: what you want instead

• instance_number: which occurrence to replace

Example 1:
Replace “S” with “XS”.

If a cell contains S, it becomes XS.
If it contains M or L, nothing changes.

Now it gets interesting.

If a word contains the letter “i” twice, SUBSTITUTE will replace all occurrences by default.

But what if you only want to replace the second “i”?

That’s where instance_number comes in.

• Use 1 → replaces the first occurrence

• Use 2 → replaces the second occurrence

• Leave it blank → replaces all occurrences

Another critical point:

SUBSTITUTE is case sensitive.

If you search for capital “I” but the word has lowercase “i”, nothing happens.

That catches a lot of people.

In this lesson, you learn:

• How to replace text inside a cell

• How to control which occurrence gets replaced

• Why case sensitivity matters

• How SUBSTITUTE behaves differently from manual editing

If you’re searching for:

• How to replace specific text in Excel

• Excel SUBSTITUTE function explained

• Replace only second occurrence in Excel

• Case sensitive replace formula

This is the function you need.

You have messy text.

Maybe a vendor name.
Maybe a product code.

And you know exactly which character needs to change.

Not “find this word and replace it.”
But “change the third character.”

That’s where the REPLACE function comes in.

In this Udemy lesson, you’ll learn how to use REPLACE in Excel to modify text based on position.

Here’s how it works:

=REPLACE(old_text, start_num, num_chars, new_text)

Simple breakdown:

• old_text → the original text

• start_num → the position where replacement begins

• num_chars → how many characters to replace

• new_text → what you want to insert

Example:

If a vendor name is “amazon”
And you replace the 3rd character with “A”

Excel changes only that character. It doesn’t search. It doesn’t scan. It just edits based on position.

That’s the key difference between:

• REPLACE → changes text based on position

• SUBSTITUTE → finds specific text and replaces it

Use REPLACE when:

• You know the exact character position

• The format is consistent

• You need structured text cleanup

If you’ve searched:

• how to use REPLACE function in Excel

• difference between REPLACE and SUBSTITUTE

• change specific character in Excel text

• Excel text functions explained

This lesson makes the distinction clear.

You have a column with dress sizes.

S
M
L

Now the brand decides to change the entire size mapping.

• S should become XS

• M should become S

• L should become M

You could manually edit every row.

Or you could use the SWITCH function.

SWITCH lets you change values dynamically based on conditions.

Structure:

=SWITCH(expression, value1, result1, value2, result2, ...)

Here’s how it works.

First, you give the expression.
This is the cell you want to evaluate.

Then you define:

• If it equals S → return XS

• If it equals M → return S

• If it equals L → return M

Excel checks the expression once and applies the matching rule.

That’s what makes SWITCH clean and powerful.
One input. Multiple possible outcomes.

Instead of writing multiple IF statements, SWITCH handles everything in a structured way.

In this lesson, you learn:

• How to remap values dynamically

• How SWITCH evaluates one expression against multiple cases

• How to copy the formula across rows

• Why SWITCH is cleaner than nested IF in many cases

If you’re searching for:

• How to use SWITCH in Excel

• Replace values based on condition in Excel

• Alternative to nested IF formula

• Change category names using formula

This is a modern and efficient way to do it.

You have two tables.

One has product IDs.
The other has prices.

And someone asks,
“Can you pull the price for this product?”

You don’t want to scroll manually. You don’t want to copy and paste. You want Excel to fetch it for you.

That’s where VLOOKUP comes in.

In this Udemy lesson, you’ll understand VLOOKUP clearly, not just the syntax, but how it actually works.

VLOOKUP stands for vertical lookup. It searches down the first column of a table and returns a related value from another column.

The formula has four parts:

=VLOOKUP(lookup_value, table_array, column_index_number, match_type)

Here’s what each part really means:

Lookup value
This is your clue. The value you already know. For example, a product ID or a name.

Table array
This is the area where Excel will search. Your full data table.

Column index number
This tells Excel which column contains the answer.
If your answer is in the second column of the table, you enter 2.

Match type
This decides how precise the match should be.

• FALSE → exact match

• TRUE → approximate match

Think of it like this:

You know what you’re looking for.
You know where to search.
You tell Excel which column has the answer.
And you decide how strictly it should match.

Excel scans vertically in the first column, finds the match, moves across to the correct column, and returns the result.

If you’ve searched:

• how to use VLOOKUP in Excel

• VLOOKUP formula explained simply

• what is column index number in VLOOKUP

• exact vs approximate match in VLOOKUP

This lesson breaks it down in a way that actually makes sense.

You have a sheet with fruit names and prices.

But something’s missing.

Vendor name.

Your job is simple:
Find which vendor sells Apple, Orange, Kiwi, Pineapple, and Grapes.

The vendor data exists.
It’s just stored somewhere else.

This is a classic VLOOKUP situation.

You have:

• A lookup value, like “Apple”

• A separate table that contains Fruit and Vendor

• A missing column you need to fill

Instead of manually matching them, you use VLOOKUP.

Structure:

=VLOOKUP(lookup_value, table_array, column_index_number, match_type)

Step by step:

1. Lookup value
   The fruit name you’re searching for, like Apple.

2. Table array
   The full range where Excel should search.
   Important: the lookup value must exist in the first column of this range.

3. Column index number
   Which column contains the result?
   If Vendor is in the second column, you enter 2.

4. Match type
   Use FALSE for exact match when working with text.

Example:

Apple → Amazon
Orange → BigBasket

If a fruit does not exist in the lookup table, Excel returns #N/A. That’s not a formula error. It simply means “Not Available.”

You also learn an important habit:

Use fixed referencing (F4) on the table array before copying the formula. Otherwise, your lookup range will shift.

Another powerful point:

VLOOKUP is dynamic.

If you change the vendor table:

• Replace Pineapple with Grapes

• Replace Orange with Kiwi

The results update automatically.

If you’re searching for:

• How to use VLOOKUP step by step

• VLOOKUP exact match example

• Why VLOOKUP returns NA

• How to fix VLOOKUP table array

This business scenario makes it clear.

You have two tables.

One table has fruit names.
Another table has vendor data and purchase month.

Now someone asks:

“What month was Apple purchased?”

You have the fruit name.
The month exists.
But it’s not in the first column.

That’s where things get slightly tricky with VLOOKUP.

In this Udemy lesson, you’ll learn how to use VLOOKUP when your lookup value is not in the very first column of the sheet, but is the first column inside your selected table array.

Here’s the situation:

• Your clue is “Apple”

• The fruit name is in the second column of the dataset

• The purchase month is in the third column

So what do you do?

You don’t start selecting from column A.

You start your table array from the column that contains your lookup value.

That’s the key.

Your formula structure looks like:

=VLOOKUP(lookup_value, table_array, column_index_number, FALSE)

Important points you’ll see:

• Always start the table array from the column that contains your clue

• Column index number is based on your selected range, not the sheet

• Use FALSE for exact match

• Lock the table array with F4 before copying the formula

Once done correctly, Excel:

• Searches for “Apple” in the first column of your selected range

• Moves to the next column

• Returns the purchase month

If you’ve searched:

• how to use VLOOKUP when value is not in first column

• VLOOKUP exact match example

• how to select table array correctly in Excel

• why VLOOKUP not working

This lesson clears that confusion step by step.

You have fruit names, prices, and vendors.

Now someone asks:

“In which month was each fruit purchased?”

Sounds simple.

Until you realize the second dataset doesn’t contain fruit names. It only contains:

• Price

• Purchase month

The only common link between both tables?

Price.

That becomes your lookup value.

This is a different VLOOKUP situation. Instead of using text like “Apple”, you’re using numbers like 7.85.

And yes, VLOOKUP works with numbers too.

Here’s how you approach it:

=VLOOKUP(lookup_value, table_array, column_index_number, FALSE)

Step by step:

1. Lookup value
   The price from your first table, like 7.85.

2. Table array
   The second dataset, including:

   • Price column

   • Any other columns needed

   • Purchase Month column

Important:
The lookup value must be in the first column of the selected table array.

1. Column index number
   If Purchase Month is in the third column of your selected range, you enter 3.

2. Match type
   Use FALSE for exact match because you are matching exact prices, not ranges.

What happens next?

• If 7.85 exists in the second table → you get the correct month.

• If 6.35 or 2.5 doesn’t exist → you get #N/A.

That’s not an error in logic. It simply means no match was found.

Key takeaway from this business case:

• VLOOKUP works with text and numbers

• Exact match is used when values must match perfectly

• Missing values naturally return #N/A

• Your lookup column must always be the first column in the selected range

If you’re searching for:

• Can VLOOKUP work with numbers?

• VLOOKUP exact match with price

• Why VLOOKUP returns NA

• How to use VLOOKUP when only one column matches

This scenario shows the practical answer.

You have a list of people and their dates of birth.

You’ve also created rules:

• Born on or after 1 Jan 2019 → 150

• Born on or after 1 Jan 2020 → 250

• Born on or after 1 Jan 2021 → 400

Now the question is:

How much pocket money does Mike get if he was born on 2 July 2021?

You could check manually.

Or you could use VLOOKUP with approximate match.

This is where VLOOKUP becomes powerful.

In this Udemy lesson, you’ll learn how approximate match works inside VLOOKUP and why it behaves differently from exact match.

The formula structure looks like this:

=VLOOKUP(lookup_value, table_array, column_index_number, TRUE)

Key difference:

• TRUE or 1 → approximate match

• FALSE or 0 → exact match

Here’s what happens behind the scenes:

• Excel treats dates as numbers

• It searches for the largest value that is less than or equal to your lookup value

• Then it returns the corresponding result

So for Mike:

• His date is after 1 Jan 2021

• Excel matches it to the nearest lower boundary, which is 1 Jan 2021

• It returns 400

Important rule for approximate match:

Your lookup column must be sorted in ascending order.
If it’s not sorted, results can be wrong.

You’ll also see why:

• Someone born before your lowest rule returns an error

• Excel doesn’t jump to the next higher value

• It always matches to the nearest lower value

If you’ve searched:

• how approximate match works in VLOOKUP

• VLOOKUP TRUE vs FALSE difference

• VLOOKUP with date ranges

• how to use VLOOKUP for grading or slab system

This lesson makes the logic clear with a real scenario.

You only have one piece of information.

Employee name.

But you need two things:

• Joining date

• Bonus amount

And here’s the twist.

The joining date is stored in one table.
The bonus amount is stored in another table.
And the bonus depends on the joining date.

This is where a two-step VLOOKUP comes in.

Step 1: Get the Joining Date

You start with the employee name.

=VLOOKUP(name, employee_table, 2, FALSE)

• Lookup value: Employee name

• Table array: Name and Joining Date table

• Column index: 2, because Joining Date is in the second column

• Match type: FALSE, since names require exact match

Now you have the joining date.

Step 2: Get the Bonus Amount

The second table doesn’t use names. It uses Joining Date ranges to decide bonus.

So now your lookup value becomes the joining date you just retrieved.

=VLOOKUP(joining_date, bonus_table, 2, TRUE)

Important difference here:

• You use approximate match (TRUE)

• Because bonus is assigned based on date ranges

• The bonus table must be sorted in ascending order for this to work properly

Example:

If Mike joined on 2 July 2021,
Excel checks the bonus table and finds the closest matching date range.
Then it returns the correct bonus amount.

Key lessons from this scenario:

• VLOOKUP can be chained

• One lookup result can feed into another

• Use exact match for text

• Use approximate match for range-based logic

• Approximate match requires sorted data

If you’re searching for:

• Two way VLOOKUP example

• Nested VLOOKUP in Excel

• VLOOKUP with date ranges

• How to calculate bonus based on joining date

This is a real business-style use case.

You have two sheets.

One sheet has employee names.
Another sheet has joining dates and bonus rules.

And your manager says:

“Pull the joining date here.”
“And calculate their bonus too.”

The data isn’t in one place. It’s sitting in another sheet.

This is one of the most common real-world uses of VLOOKUP.

In this Udemy lesson, you’ll learn how to use VLOOKUP across sheets and avoid the common mistakes people make.

Step 1: Get Joining Date from Another Sheet

• Use the employee name as the lookup value

• Navigate to the Employee Data sheet

• Select the full table array

• Press F4 to lock the range

• Use column index number 2

• Use FALSE for exact match

Important tip:
After selecting data from another sheet, always add the comma before clicking anywhere else. If you don’t, Excel may accidentally select the wrong range.

Step 2: Get Bonus Based on Joining Date

Now things get interesting.

• Use the joining date as lookup value

• Select the bonus slab table

• Lock the range

• Use column index number 2

• Use approximate match (TRUE)

Why approximate match?

Because bonus is based on ranges. Excel finds the nearest lower joining date slab and assigns the correct bonus.

You’ll see:

• How to reference another sheet inside VLOOKUP

• Why locking the table array matters

• When to use exact vs approximate match

• Why some rows may return errors

If you’ve searched:

• how to use VLOOKUP between two sheets

• VLOOKUP from another worksheet

• VLOOKUP bonus slab example

• common VLOOKUP mistakes

This lesson covers one of the most practical business scenarios you’ll face.

You have a list of people and their dates of birth.

And you’ve created a pocket money rule:

• Born on or after 1 Jan 2019 → ₹150

• Born on or after 1 Jan 2020 → ₹250

• Born on or after 1 Jan 2021 → ₹400

Now the question is simple.

Mike was born on 2 July 2021.
How much pocket money should he get?

This is not an exact match problem.

This is a range-based problem.

And that’s where VLOOKUP with approximate match becomes powerful.

Structure:

=VLOOKUP(lookup_value, table_array, column_index_number, TRUE)

Step by step:

1. Lookup value
   The date of birth.

2. Table array
   The criteria table with:

   • Date thresholds

   • Pocket money amount

3. Column index
   2, because pocket money is in the second column.

4. Match type
   TRUE for approximate match.

How approximate match works:

Excel treats dates as numbers.

When using TRUE:

• Excel looks for the largest value in the first column

• That is less than or equal to the lookup value

So for Mike:

2 July 2021 is greater than 1 Jan 2021.
So Excel matches him to the 1 Jan 2021 row.
Result: ₹400.

If someone was born in mid-2020:

Excel finds the nearest lower date threshold.
Result: ₹250.

Important rule:

When using approximate match:

• The first column must be sorted in ascending order

• Otherwise, results can be wrong

If someone’s date of birth is earlier than your smallest threshold, you’ll get #N/A. That simply means no matching rule exists.

If you’re searching for:

• VLOOKUP approximate match example

• VLOOKUP with date ranges

• How approximate match works in Excel

• How to assign values based on thresholds

This is the core logic.

You have one sheet with sales data.

It shows:

• Fruit

• Merchant

• Amount

• But one column is missing, Days in Transit

That information exists in another table in the same file.

In the past, you would reach for VLOOKUP.

But now, there’s something better.

In this Udemy lesson, you’ll learn how to use XLOOKUP inside the same sheet to fetch related data cleanly and safely.

Here’s what makes XLOOKUP different.

Instead of selecting a full table like VLOOKUP, you:

• Select only the lookup column

• Select only the return column

That’s it.

The structure looks like this:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

In this example:

• Lookup value → Merchant name

• Lookup array → Merchant column in reference table

• Return array → Days in Transit column

And here’s the best part.

You can define what happens if no match is found.

Instead of an ugly #N/A error, you can display:

"Not Found"

That alone makes XLOOKUP more powerful than VLOOKUP.

You’ll also learn:

• Why lookup_array and return_array must have the same size

• Why locking references with F4 still matters

• How XLOOKUP removes the need for column index numbers

• How to control exact match vs approximate match

If you’ve searched:

• how to use XLOOKUP in Excel

• difference between VLOOKUP and XLOOKUP

• XLOOKUP with if not found

• why XLOOKUP is better

This lesson shows why XLOOKUP is cleaner, safer, and more flexible.

You’ve used VLOOKUP.

Now let’s see something better.

You have:

• Fruit

• Merchant

• Amount

But you’re missing one field.

Number of days in transit.

The merchant information exists in another table. Along with:

• Profit percentage

• Days in transit

Instead of using VLOOKUP, this time you use XLOOKUP.

Structure:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Step by step:

1. Lookup value
   Merchant name, like Amazon.

2. Lookup array
   Only the column that contains merchant names.
   Not the entire table.

3. Return array
   The column that contains “Days in Transit”.

That’s the biggest difference from VLOOKUP.

You don’t select a full table and count column numbers.
You separately define:

• Where to search

• What to return

Cleaner. More flexible.

Now comes the powerful part.

The optional arguments.

If_not_found
Instead of getting #N/A, you can return something like "Not Found".

Match_mode
You can specify exact match.

Search_mode
Top-to-bottom or bottom-to-top.

That’s control VLOOKUP never gave you.

One more important habit:

Fix your lookup and return arrays using absolute referencing before copying the formula. Otherwise, your ranges will shift and cause errors.

Key takeaways:

• XLOOKUP separates lookup and return arrays

• No need to remember column index numbers

• Built-in error handling with if_not_found

• More flexible than VLOOKUP

If you’re searching for:

• XLOOKUP vs VLOOKUP

• How to use XLOOKUP step by step

• XLOOKUP with custom error message

• Why XLOOKUP is better than VLOOKUP

This example shows the difference clearly.

You’re used to looking up data vertically.

Name in one column.
Answer in the next column.

Easy.

But now the data is arranged differently.

Dates are placed across columns.
The values sit underneath them.

This is where older Excel users would reach for HLOOKUP.

But with XLOOKUP, you don’t need a separate function.

In this Udemy lesson, you’ll learn how to use XLOOKUP horizontally to fetch data based on dates stored across columns.

Here’s the situation:

• The lookup value is a date

• The lookup array is the row of dates

• The return array is the row below, like Number of Cases Sold or Billing Agent

The structure remains simple:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Key takeaway:

• XLOOKUP works both vertically and horizontally

• You don’t need to switch to HLOOKUP

• You can define what happens if the date doesn’t exist

If the date isn’t found, instead of showing an error, you can return:

"Not Found"

You’ll also see:

• How to use exact match

• Why fixing references still matters

• How XLOOKUP handles missing dates gracefully

If you’ve searched:

• how to use XLOOKUP horizontally

• XLOOKUP instead of HLOOKUP

• Excel lookup by date in columns

• XLOOKUP exact match example

This lesson shows how flexible XLOOKUP really is.

You have a margin percent.

And you need to find the number of days in transit based on that percent.

But here’s the twist.

The exact percent may not exist in your reference table.

So what should Excel do?

That’s where XLOOKUP’s match modes become powerful.

Basic structure:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])

The interesting part is match_mode.

You already know:

0 → Exact match

But now we use:

1 → Exact match or next larger item

Here’s what that means.

If your lookup value is 19%,
And your table has 18% and 20%,

Excel will match it with 20%.
Because 20% is the next larger value.

Then it returns the corresponding “Days in Transit”.

So:

19% → matched to 20% → returns 7 days
21% → matched to 23% → returns 8 days
8% → matched to 18% → returns 6 days

It always looks for:

• An exact match

• If not found, the next higher value

Important detail:

If there is no larger value available, you’ll get your custom “Not Found” result.

This is very useful for:

• Commission slabs

• Tax brackets

• Shipping tiers

• Profit ranges

• Bonus thresholds

If you’re searching for:

• XLOOKUP next larger value

• XLOOKUP approximate match example

• How XLOOKUP match mode works

• Difference between exact and approximate match in XLOOKUP

This example makes it practical.

You have your main sheet open.

But the reference data?
It’s sitting in two completely different Excel files.

Now the question is:

Can XLOOKUP pull data from another workbook?

Yes. And it does it cleanly.

In this Udemy lesson, you’ll learn how to use XLOOKUP across different Excel files and what to watch out for.

Here’s how it works:

• Lookup value → Merchant name in your main file

• Lookup array → Merchant column in another workbook

• Return array → Profit % or Days in Transit column from that same external file

• If not found → Display something like “Not Available”

• Match mode → Exact match

The formula structure remains the same:

=XLOOKUP(lookup_value, lookup_array, return_array, "Not Available", 0)

But there are a few important things you’ll notice.

First:

When you reference another workbook, Excel automatically applies absolute referencing.
Unlike same-sheet formulas, you don’t need to press F4.

Second:

Lookup array and return array must be the same size.
If one range has 7 rows and the other has 6, Excel throws a #VALUE error.

Third:

Small data issues like extra spaces can cause “Not Available” results.
Sometimes it’s not the formula. It’s the data.

You’ll also see:

• How Excel links external workbooks inside the formula bar

• Why consistent row selection matters

• How to debug errors caused by mismatched ranges

If you’ve searched:

• how to use XLOOKUP between two Excel files

• XLOOKUP external workbook example

• why XLOOKUP returns value error

• Excel cross-file lookup formula

This lesson shows how XLOOKUP handles multi-file lookups smoothly.

Now let’s flip the logic.

Earlier, we matched 19% to the next larger value.

This time, we match to the next smaller value.

In XLOOKUP, that’s match mode -1.

Structure:

=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found", -1)

What does -1 mean?

Exact match
Or
Next smaller item

So if the lookup value is 19%:

• Excel looks for 19%

• If not found, it finds the largest value that is less than 19%

In your table:

19% → matches 18% → returns 6 days

21% → matches 20% or 18% depending on your table structure → returns the corresponding days

Now look at 1%.

If your smallest value in the table is 5%, there is nothing smaller than 1%.

So Excel returns “Not Found.”

That’s correct behavior.

Key difference between match modes:

1 → exact or next larger
-1 → exact or next smaller

This is extremely useful in:

• Commission slabs

• Discount brackets

• Age categories

• Performance tiers

Where you want values to fall into the nearest lower bracket.

If you’re searching for:

• XLOOKUP next smaller value

• XLOOKUP match mode -1 explained

• How to match nearest lower number in Excel

• Approximate match using XLOOKUP

This is how you control the direction of matching.

Your data looks almost correct.

Almost.

You see:

• Amazo

• Walmar

• DG Bask

• Fresh Far

But your reference table has:

• Amazon

• Walmart

• DG Basket

• Fresh Farm

Now a normal XLOOKUP with exact match will fail.

Because “Amazo” is not equal to “Amazon”.

This is where wildcard match mode in XLOOKUP becomes powerful.

In this Udemy lesson, you’ll learn how to use XLOOKUP with wildcards to match incomplete or partial text.

Here’s the key idea:

You switch match mode to wildcard match.
Then you use the asterisk (*) to represent “any number of characters”.

Basic structure:

=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found", 2)

The 2 enables wildcard matching.

Now let’s say your lookup value is:

Amazo

If you modify it like this:

Amazo&"*"

It tells Excel:

Find anything that starts with “Amazo” and has any characters after it.

That successfully matches “Amazon”.

But what about “Basket” when the real value is “Big Basket”?

In that case, you use:

""&F74&""

This means:

Match anything before and after the word in the cell.

So Excel will:

• Look for any text containing “Basket”

• Ignore extra words before or after

• Return the correct result

You’ll also see:

• Why match mode must be set to wildcard

• Why forgetting wildcards returns “Not Found”

• How concatenation with & builds flexible lookup values

If you’ve searched:

• how to use wildcards in XLOOKUP

• XLOOKUP partial text match

• XLOOKUP contains text

• fix incomplete text lookup in Excel

This lesson shows how to solve messy data problems without manually cleaning everything.

You search for “Amazon” in a list.

Instead of returning the value next to it, Excel returns a number.

That number is the position of the match.

That’s what the MATCH function does.

MATCH does not return data from another column.
It returns the position of a value inside a range.

Structure:

=MATCH(lookup_value, lookup_array, match_type)

Example:

=MATCH("Amazon", vendor_column, 0)

• Lookup value → “Amazon”

• Lookup array → vendor list

• Match type → 0 for exact match

If “Amazon” appears in the first row of your selected range, MATCH returns 1.

If “Walmart” is in the second row, it returns 2.

Important behavior:

If the value appears multiple times, MATCH returns the position of the first occurrence only. It stops searching after that.

So yes, if “Amazon” appears twice, you’ll still get 1.

On its own, MATCH simply tells you:

“Where is this value located in this range?”

It doesn’t fetch related data.

That’s why MATCH becomes powerful when combined with INDEX.

Together, INDEX + MATCH can replace VLOOKUP and give you more flexibility.

If you’re searching for:

• How to use MATCH in Excel

• MATCH function exact match example

• Why MATCH returns a number

• Difference between MATCH and VLOOKUP

This is the foundation.

You want a value from a table.

Not by searching.
Not by matching text.

You just know:

“It’s in row 5, column 3.”

That’s where the INDEX function comes in.

In this Udemy lesson, you’ll learn how INDEX works and what makes it different from lookup formulas like VLOOKUP or XLOOKUP.

INDEX doesn’t search.

It simply returns the value from a specific position inside a selected range.

The structure looks like this:

=INDEX(array, row_number, column_number)

Here’s what each part means:

• array → the data range you select

• row_number → which row inside that range

• column_number → which column inside that range

Example:

If you select a table with 4 columns and 10 rows,
and you write:

=INDEX(range, 5, 3)

Excel will:

• Go to the 5th row inside that selected range

• Move to the 3rd column

• Return that cell’s value

Important:

INDEX does not “find” anything on its own.
You must manually tell it which row and column to retrieve.

That’s why, on its own, INDEX is simple.
But when combined with MATCH, it becomes extremely powerful.

If you’ve searched:

• how to use INDEX function in Excel

• Excel INDEX formula example

• difference between INDEX and VLOOKUP

• how INDEX works in Excel

This lesson gives you the foundation.

You’ve seen MATCH.

It tells you the position of a value.

Now let’s make it useful.

That’s where INDEX + MATCH comes in.

Many people say “Match and Index.”

But the correct structure is:

INDEX first.
MATCH inside it.

Structure:

=INDEX(array, row_number, [column_number])

Instead of manually typing the row number, you use MATCH to calculate it.

Example structure:

=INDEX(return_array,
MATCH(lookup_value, lookup_array, 0),
column_number)

Step by step:

1. INDEX
   Select the data range you want the result from.
   Do not include headers.

2. Row number
   Instead of typing a number, use MATCH.

3. MATCH

   • Lookup value → what you’re searching for

   • Lookup array → where Excel should search

   • 0 → exact match

MATCH returns the row position.
INDEX uses that row position to fetch the correct value.

Important clarification:

MATCH replaces the row number.
Column number is still manual.

If the value you want is in:

• First column → use 1

• Second column → use 2

• Third column → use 3

Why use INDEX + MATCH instead of VLOOKUP?

• You don’t need the lookup column to be first

• It’s more flexible

• It works left-to-right and right-to-left

• It handles structural changes better

If you’re searching for:

• INDEX MATCH formula explained

• How to combine INDEX and MATCH

• Why INDEX MATCH is better than VLOOKUP

• Excel dynamic lookup formula

This is the foundation.

You have numbers like:

7.85
3.25
9.95

But you don’t want decimals.

Maybe you’re preparing a report.
Maybe you’re cleaning financial data.
Maybe you just need clean whole numbers.

That’s where the ROUND function comes in.

In this Udemy lesson, you’ll learn how to round numbers properly in Excel using the ROUND function.

Here’s the structure:

=ROUND(number, num_digits)

Simple meaning:

• number → the value you want to round

• num_digits → how many decimal places you want

Examples:

• =ROUND(7.85, 0) → 8

• =ROUND(3.25, 0) → 3

• =ROUND(7.85, 1) → 7.9

• =ROUND(9.95, 0) → 10

Excel follows normal mathematical rounding rules:

• 0.5 and above → round up

• Below 0.5 → round down

If you set num_digits to:

• 0 → round to whole number

• 1 → keep one decimal

• 2 → keep two decimals

You’ll also see:

• How rounding behaves with different decimal values

• Why some numbers stay the same

• Quick keyboard shortcuts to copy formulas without using the mouse

If you’ve searched:

• how to round numbers in Excel

• Excel ROUND function example

• how to remove decimals in Excel

• Excel round to nearest whole number

This lesson gives you the clean, practical method.

You’ve used ROUND before.

It follows normal math rules.

If the number is 787.8569 and you round to 0 digits, you get 788.

But what if you don’t want normal rounding?

What if you always want to round up?

That’s where ROUNDUP comes in.

ROUNDUP ignores standard rounding rules.
It always pushes the value upward.

Structure:

=ROUNDUP(number, num_digits)

Example:

If the value is 5.122 and you round to 1 digit:

ROUND → 5.1
ROUNDUP → 5.2

Now let’s make it practical.

You’re billing customers.
You want every value rounded up to the nearest 10.

So instead of using 0 as num_digits, you use:

-1

Example:

=ROUNDUP(787.8569, -1)

Result: 790

Why?

When you use negative digits:

• -1 → nearest 10

• -2 → nearest 100

• -3 → nearest 1000

So:

346 → 350
506 → 510
411 → 420

Always rounded upward to the nearest 10.

Important note:

You can use negative digits with ROUND as well.
But ROUND follows mathematical logic.
ROUNDUP always forces the number higher.

If you’re searching for:

• How to round up to nearest 10 in Excel

• ROUNDUP function explained

• Difference between ROUND and ROUNDUP

• How to round numbers for billing

This is the function you need.

Now flip the situation.

Earlier, rounding was neutral. Just normal mathematical rounding.

But imagine this:

You’re the customer.
The bill says 787.8569.

Do you want that rounded to 788?

Probably not.

You’d prefer 787.
Or maybe even 780.

That’s where ROUNDDOWN comes in.

In this Udemy lesson, you’ll learn how to use the ROUNDDOWN function in Excel to always push numbers downward, regardless of decimal value.

Here’s the structure:

=ROUNDDOWN(number, num_digits)

Key difference from ROUND:

• ROUND follows math rules

• ROUNDDOWN always reduces the value

Examples:

• =ROUNDDOWN(787.8569, 0) → 787

• =ROUNDDOWN(787.8569, -1) → 780

When you use:

• 0 → removes decimals

• -1 → rounds down to nearest 10

• -2 → rounds down to nearest 100

This is useful in situations like:

• Conservative budgeting

• Discount calculations

• Inventory planning

• Risk control

The important takeaway:

Rounding direction matters in business.
Sometimes you round up.
Sometimes you round down.

If you’ve searched:

• how to always round down in Excel

• Excel ROUNDDOWN function example

• round down to nearest 10 in Excel

• difference between ROUND and ROUNDDOWN

This lesson makes the difference clear.

You’ve seen ROUND, ROUNDUP, and ROUNDDOWN.

Now let’s talk about two very practical rounding functions:

CEILING and FLOOR.

Think of it literally.

Ceiling → always goes up.
Floor → always goes down.

But there’s a twist.

These functions round to a specific multiple.

CEILING

Structure:

=CEILING(number, significance)

Significance is the multiple you want to round to.

Example:

=CEILING(411, 50)

Result → 450

Even if the number is 401, it still becomes 450.
Because 450 is the next multiple of 50 above the number.

If the value is 399:

=CEILING(399, 50)

Result → 400

It always rounds upward to the nearest multiple you define.

This is extremely useful for:

• Pricing slabs

• Packaging quantities

• Shipping weight brackets

• Bulk order calculations

Now FLOOR.

Structure:

=FLOOR(number, significance)

This does the opposite.

It rounds downward to the nearest multiple.

Example:

=FLOOR(411, 25)

Result → 400

If the value is 426:

=FLOOR(426, 25)

Result → 425

It finds the nearest lower multiple of 25.

Key difference from ROUNDUP and ROUNDDOWN:

ROUNDUP and ROUNDDOWN work based on digits.
CEILING and FLOOR work based on multiples.

If you’re searching for:

• How to round to nearest 50 in Excel

• CEILING function explained

• FLOOR function example

• Difference between CEILING and ROUNDUP

This is where that clarity comes in.

You bought products at different prices.

787
457
723

But now you need to adjust them.

Maybe for pricing strategy.
Maybe for inflation adjustment.
Maybe to standardize everything to neat values.

You don’t just want to round normally.

You want to round to the nearest 50.

That’s where MROUND comes in.

In this Udemy lesson, you’ll learn how to round numbers to the nearest multiple using the MROUND function in Excel.

Here’s the structure:

=MROUND(number, multiple)

Simple idea:

• number → the value you want to adjust

• multiple → the number you want to round to

Examples:

• =MROUND(787, 50) → 800

• =MROUND(457, 50) → 450

• =MROUND(723, 50) → 700

Excel rounds to the nearest multiple you specify.

If you use:

• 1 → rounds to nearest whole number

• 2 → rounds to nearest even number

• 5 → rounds to nearest 5

• 50 → rounds to nearest 50

• 100 → rounds to nearest 100

It automatically decides whether to round up or down based on which multiple is closer.

This is especially useful for:

• Price standardization

• Packaging quantities

• Budget grouping

• Inflation adjustments

• Retail pricing strategies

If you’ve searched:

• how to round to nearest 50 in Excel

• Excel MROUND function example

• round to nearest multiple in Excel

• difference between ROUND and MROUND

This lesson shows how to control rounding using custom multiples.

You could solve this in three steps.

Step 1: Add all prices.
Step 2: Add all quantities.
Step 3: Multiply both totals.

It works.

But it’s messy.

Now imagine doing that across a bigger dataset. Multiple helper cells. Extra calculations. More room for mistakes.

That’s why nested formulas exist.

A nested formula simply means:

Using one function inside another function.

Instead of:

• Writing SUM for prices

• Writing SUM for quantity

• Then multiplying the results

You can combine everything.

Example:

=PRODUCT(SUM(price_range), SUM(quantity_range))

Here’s what’s happening:

• SUM(price_range) calculates total price

• SUM(quantity_range) calculates total quantity

• PRODUCT multiplies both results

All inside one formula.

No helper cells. No intermediate steps.

That’s the power of nesting.

You’re not limited to PRODUCT and SUM.
You can nest:

• IF inside SUM

• MATCH inside INDEX

• AVERAGE inside IF

• And much more

In simple terms:

Nested formulas make Excel smarter and more dynamic.
Instead of breaking calculations into pieces, you let functions talk to each other.

If you’re searching for:

• What is a nested formula in Excel

• How to use one function inside another

• Excel PRODUCT and SUM together

• Array formula basics

This is the foundation.

You have a scorecard.

Students scored 95, 78, 35, 82.

Now the rule is simple:

• Above 80 → No coaching

• Between 60 and 80 → Recommended coaching

• Below 60 → Mandatory coaching

But how do you turn that logic into a formula?

This is where nested IF formulas come into action.

In this Udemy lesson, you’ll learn how to build logical decision-making formulas step by step using nested IF in Excel.

Here’s the thinking process:

First question:
Is the score greater than 80?
If yes → “No Coaching”

If not, move to the next condition:

Is the score greater than 60?
If yes → “Recommended Coaching”

If neither condition is met → “Mandatory Coaching”

The structure looks like this:

=IF(score>80,"No Coaching",
IF(score>60,"Recommended Coaching",
"Mandatory Coaching"))

What Excel does:

• Checks the first condition

• If false, moves to the next IF

• If false again, returns the final result

You’ll see how:

• Multiple conditions are evaluated in sequence

• Order of logic matters

• Closing brackets correctly is critical

• Copying the formula applies logic across all rows

This is real-world logic building. Not just formulas.

If you’ve searched:

• how to use nested IF in Excel

• Excel IF multiple conditions example

• student grading formula in Excel

• how to write logical formulas in Excel

This lesson shows how to think through conditions clearly before writing them.

You’re building a grading rule.

If marks are less than 60 → Mandatory coaching.
If marks are greater than 80 → No coaching.
Everything in between → Something else.

This is where nested IF logic starts.

Instead of checking the highest condition first, you’re building from the bottom up.

Structure:

=IF(A3<60,
"Mandatory coaching",
IF(A3>80,
"No coaching",
"Recommend coaching"
)
)

Here’s what happens step by step:

1. Excel checks: Is the score less than 60?
   Yes → Mandatory coaching.
   No → Move to the next condition.

2. Then it checks: Is the score greater than 80?
   Yes → No coaching.
   No → Use the final fallback result.

Now here’s the common mistake you saw.

If you leave the last part blank, Excel doesn’t know what to return when neither condition is true. So it shows:

• 0

• Or blank

• Or unexpected output

Because you didn’t define what should happen in that case.

That final argument in IF is important. It acts as your default outcome.

Key takeaway:

Every IF needs a clear result for:

• Condition is TRUE

• Condition is FALSE

If you don’t define it, Excel guesses. And guessing is rarely what you want.

If you’re searching for:

• Nested IF formula example

• IF with multiple conditions in Excel

• Why IF formula returns 0

• How to fix incomplete IF statement

This is the core idea.

You thought three conditions were tough?

Now imagine five.

Same student scorecard.
But this time grading rules are stricter:

• 90 and above → A

• 80 to 89 → B

• 70 to 79 → C

• 60 to 69 → D

• Below 60 → E

That’s a proper grading system.

And yes, this means multiple nested IF conditions.

In this Udemy lesson, you’ll learn how to build a complex nested IF formula step by step without getting overwhelmed.

Instead of writing one giant formula at once, you:

1. Build each IF condition separately

2. Test it

3. Remove unnecessary closing brackets

4. Combine them carefully

5. Let Excel auto-complete missing brackets

The final logic structure looks like this:

=IF(score>=90,"A",
IF(score>=80,"B",
IF(score>=70,"C",
IF(score>=60,"D","E")
)
)
)

Here’s why this works:

Excel checks conditions in order.
The first TRUE condition stops the evaluation.

So if a student scores 92:

• It checks >=90 → TRUE

• Returns A

• Stops there

No need to check the remaining conditions.

Key takeaways:

• Order of conditions is critical

• Always test one condition at a time

• Nested IF works like layered decision making

• Excel evaluates from top to bottom

If you’ve searched:

• how to grade students in Excel

• nested IF formula multiple conditions

• Excel grading formula example

• how to write complex IF statements

This lesson shows a practical, structured way to build large logical formulas without panic.

You’re looking at a sales sheet.

Agents.
January sales.
February sales.

And a commission slab that depends on performance.

Now the task:

Calculate commission for both months.

You could manually build nested IF formulas.
Check each slab.
Multiply by the right percentage.
Repeat for two columns.

It’s doable.

But it’s time-consuming.

So instead, you clearly define the logic and use AI to generate the IF formula.

Here’s what matters most in this process:

Clarity.

You specify:

• Data starts from row 4

• Column B = January sales

• Column C = February sales

• Use only IF formulas

• Apply the commission slab exactly as defined

The first output referenced row 3.
Why? Because the prompt said row 3 had headers, but didn’t specify where the formula would be pasted.

Small detail. Big impact.

You corrected it.

That’s the real lesson here.

AI can generate the structure.
But you must:

• Clearly define row references

• Verify logic

• Adjust based on context

Once corrected:

• 8000 × 9% = 560

• Lower slabs correctly returned zero

• February logic worked the same way

And the whole commission model was built in minutes instead of manually nesting multiple IF statements.

Key takeaway:

Complex IF logic becomes manageable when:

• You break down slabs clearly

• You explain structure precisely

• You validate outputs logically

If you’re searching for:

• How to build commission slab formula in Excel

• Nested IF for commission calculation

• How to use AI to generate Excel formulas

• Step by step commission logic using IF

This approach saves serious time.

You have employee data.

Column B → Performance rating
Column C → Department
Column D → Final action

But here’s the twist.

Marketing and Operations follow one logic.
HR follows a completely different logic.

Now imagine writing this manually using nested IF.

Multiple departments.
Multiple rating slabs.
Different outcomes.

One small mistake and the whole thing breaks.

In this Udemy lesson, you’ll see how to use ChatGPT to generate a complex nested IF formula correctly in one go.

The key isn’t just asking for a formula.

It’s giving precise instructions:

• Which column has ratings

• Which column has department

• Where the output should go

• Which row the formula should start from

• That only IF function must be used

That clarity is what makes AI generate the right logic.

Once pasted into Excel, the nested IF formula:

• Checks department first

• Then evaluates rating slabs

• Applies correct hike percentage or action

• Returns “Fire” where applicable

• Handles both Marketing/Operations and HR separately

And the result?

• 12% hike where rating fits

• 17% hike for higher performers

• 24% hike for top ratings

• HR-specific rules applied correctly

• Low ratings handled properly

You’ll also see:

• Why row references must align

• How to validate the formula quickly

• How AI reduces manual errors in complex logic

If you’ve searched:

• how to write complex nested IF in Excel

• Excel formula for performance appraisal

• multi-condition IF formula example

• how to use ChatGPT for Excel formulas

This lesson shows how to combine Excel logic building with generative AI smartly.