Simple IF statements are great for handling one condition, like checking if a value is above a certain threshold. But what happens when your decisions have multiple layers? Imagine calculating sales commissions that change based on different performance tiers, or assigning student grades from A to F. You need a way to chain decisions together.

The classic way to handle this is by nesting IF functions. A nested IF is an IF statement tucked inside another one. This creates a cascade of logical tests. Excel evaluates the first condition. If it's true, it gives a result. If not, it moves on to the next IF statement in the sequence.

Let's use a grading example. If a student's score is in cell A2, we can assign a letter grade based on these rules:

• Score > 90: "A"
• Score > 80: "B"
• Score > 70: "C"
• Otherwise: "D"

The formula would look like this:

This formula works its way down the chain. It first checks if the score is greater than 90. If not, the second IF function takes over and checks if it's greater than 80, and so on. The order is critical. If you checked for >70 first, a score of 95 would incorrectly get a "C" because 95 is indeed greater than 70. While powerful, nested IFs can become confusing to read and edit, especially when you have many conditions. Tracking all the parentheses is a common source of errors.

Sometimes a single condition isn't enough. You might need to check if multiple criteria are met simultaneously, or if at least one of several is true. This is where the logical operators AND, OR, and NOT come in. You can combine them with IF to create much more specific tests.

Let's say you want to identify top sales associates for a bonus. They must have sold over $50,000 (cell B2) and have a customer satisfaction score above 90% (cell C2). Using AND makes this easy.

Now, imagine a different rule. An item gets a shipping discount if it's either fragile (cell D2) or its value is over $1000 (cell E2). Here, OR is the right tool.

These operators give you fine-grained control for building complex business rules directly into your spreadsheet.

Because nested IF statements can get messy, newer versions of Excel introduced the IFS function. It's designed specifically for checking multiple conditions in a cleaner, more readable way. Instead of nesting, IFS lets you list your conditions and their results as a series of pairs.

Let's revisit our grading example. Using IFS looks like this:

Each condition is paired with its outcome. The formula is flatter and easier to follow. One important difference is that IFS doesn't have a final "else" argument like IF. To create a default or catch-all case, you need to provide a final condition that will always be true, like A2<=70 or simply TRUE.

Logical formulas aren't just for putting new values into cells. They can also control how cells look. This is called Conditional Formatting with a formula. It's a powerful way to make your data speak for itself, highlighting key information visually.

Imagine you have a project plan with due dates in column B and completion status in column C. You want to highlight any tasks that are still "In Progress" and past their due date. You would select your data range, go to Conditional Formatting, and create a new rule using a formula.

The formula would be:

Then you'd set the format to a red fill. Now, any row meeting both those criteria will automatically turn red, instantly drawing your attention to overdue tasks. The TODAY() function returns the current date, ensuring the formatting updates automatically each day. The dollar signs ($) are important for locking the column reference as the rule is applied across the selection.

Finally, even the best formulas can produce errors if the data isn't what they expect. A VLOOKUP that finds no match returns #N/A, and division by zero returns #DIV/0!. The IFERROR function provides a clean way to handle these situations. It wraps around your original formula, allowing you to specify a fallback value or message if an error occurs.

If the VLOOKUP succeeds, its result is shown. If it fails for any reason, the cell will display "Not Found" instead of an ugly error code, making your spreadsheets more professional and user-friendly.