How to Use IF-THEN Statements in Excel

To bask Excel fully, you must cognize really to usage nan IF-THEN function.

The IF-THEN is 1 of Excel’s astir awesome functions, arsenic you tin usage it to analyse data, tie inferences and make decisions nether definite preset conditions. But what precisely are IF-THEN statements, and really tin you commencement utilizing them?

In this guide, we’ll show you really to usage IF-THEN statements to amended your mastery of Excel.

Table of Contents

• What are IF-THEN statements successful Excel?
• The Benefits of IF-THEN Statements successful Excel
• How to Use IF-THEN Statements successful Excel
• Best Practices for IF-THEN Statements successful Excel

What are IF-THEN Statements successful Excel?

IF-THEN statements are functions successful Excel that return a unsocial group of actions aft your preset information is met.

Simply put, erstwhile you usage nan “If” statement, you’re mounting a information and instructing Excel to execute a unsocial group of actions erstwhile your preset information is existent and different erstwhile nan information is false.

To usage nan “If “ usability successful Excel, you commencement by penning nan adjacent motion (=), followed by “if,” past your condition, and a two-part syntax instructing nan programme connected what to do erstwhile your requirements are aliases aren’t met.

The syntax usually looks for illustration this:

• =IF(logical_test, [value_if_true], [value_if_false])

The “If” usability is handy, particularly erstwhile you person a ample information measurement and want to debar nan accent of computing formulas for each data. Here’s an illustration of really elemental it is to usage IF-THEN statements successful Excel:

In this example, I person a spreadsheet containing 3 columns of data. Where file A contains sanction data, and columns B and C, incorporate integer data.

Using nan If function, I group a information utilizing nan syntax:

• =IF(B1>C1, “yes,” “no”)

This usability initiates a bid telling nan Excel programme to trial if nan information worth successful compartment B1 is greater than nan information worth successful compartment C1.

I’ve simply instructed Excel to springiness maine a consequence of “yes” nether nan information that nan worth of compartment B1 is greater than compartment C1 and a consequence of “no” nether nan information that nan worth of compartment B1 is little than nan worth of compartment C1.

I past extended this usability to nan different respective cells utilizing a elemental autofill trick. Check retired our guideline connected using Excel for illustration a pro for immoderate adjuvant tricks to make your Excel acquisition easier.

It’s important to statement that nan IF-THEN connection only allows you to group a information for 2 variables. That intends you tin only group conditions including, say, nan information worth successful cells B1 and C1 aliases a information including information values successful compartment B1 and a defined integer aliases text.

For example, you tin group a information instructing Excel to return a consequence of “Yes” if nan worth successful compartment B1 is greater than nan worth of compartment B2 arsenic shown successful nan illustration above. Or, you tin group a information asking Excel to return nan consequence ”yes” aliases “no” if nan worth of compartment B1 is greater aliases little than 1000.

Here’s a speedy example:

Here, I told Excel to return nan consequence “yes” if nan information worth successful compartment C1 is greater than 5000 and “no” if nan worth is little than 5000.

Here’s nan consequence Excel returned:

As mentioned earlier, nan IF-THEN connection is simply a powerful Excel usability pinch galore benefits. Learning to usage this usability decently will astronomically summation your mastery of Excel.

The Benefits of IF-THEN Statements In Excel

Here are immoderate benefits that IF-THEN statements successful Excel tin connection you:

Checking for Errors successful Data

You tin usage IF-THEN statements to cheque for errors erstwhile inputting information into nan spreadsheet.

For example, if you consciousness for illustration you person made a correction by inputting a number alternatively of a matter into your spreadsheet, you tin cheque utilizing an if function:

• IF(B1 = “”, “Text”, “Number”)

Calculating Debt Schedule aliases Depreciation Schedule

Financial Analysts tin usage IF-THEN statements to cipher indebtedness and depreciation schedules.

Using nan IF-THEN statement, you tin group conditions asking nan programme to return a consequence for months pinch outstanding balances and moreover tie up a repayment schedule.

For Budgeting

As a financial analyst, utilizing IF-THEN statements would make budgeting easier. IF-THEN functions will let you to create up to 64 conditions, which tin assistance you successful your budgeting process.

To Organize Data

The superior expertise of IF-THEN statements is mounting instructions nether definite conditions. Thus it’s easy to shape information successful your spreadsheet nether definite conditions utilizing if statements.

You tin group conditions for a peculiar compartment container aliases aggregate cells and shape your spreadsheet information nether said conditions.

Works Well With Other Formulas

An breathtaking advantage of IF-THEN statements is their flexibility. Due to its flexibility, you tin usage IF-THEN statements pinch different formulas successful Excel.

For example, you tin usage it pinch nan DATEVALUE() function, nan SUM() function, aliases moreover nan COUNT() function.

How to Use IF-THEN Statements successful Excel

Now that you cognize what an IF-THEN connection is and really it tin use you, nan adjacent measurement is knowing really to usage it properly.

There are 2 methods to usage nan function, and we’ll commencement pinch nan basics. First, here’s a elemental step-by-step process for penning IF-THEN statements successful Excel.

Step 1: Click connected nan compartment you wish to format, opportunity compartment A1.

Step 2: Write nan IF-THEN usability look straight into nan compartment box.

Or successful nan look box.

Step 3: Begin penning your conditions and expected responses to said conditions.

Remember: Always commencement nan look pinch an “equal” motion and input nan due punctuation truthful you don’t get an correction connection aft inputting nan function.

Here’s an replacement step-by-step you tin use.

Alternative Step 1: Click connected nan “Formulas” tab successful nan taskbar paper of your Excel program.

Alternative Step 2: Click connected nan “Insert Function” option, which will motorboat a dialog menu:

Alternative Step 3: Select “IF” from nan database of options successful nan dialog paper and click “OK.” It will motorboat a dialog box.

Alternative Step 4: In nan dialog box, input your information and instructions telling nan programme what answers to return erstwhile nan information is met and erstwhile it isn’t met.

Note: It is amended to usage nan “Insert Function” method if you’re penning a elemental IF-THEN function, arsenic it launches a look builder that will thief you destruct nan risks of errors erstwhile inputting your formula.

This method will automatically input nan due punctuations and formats and only require you to input your conditions and instructions successful nan 3 fields of nan dialog box.

Now that you cognize really to commencement pinch nan IF-THEN function, let’s reappraisal nan different usage cases for if functions.

Writing IF-THEN Function for texts

IF-THEN functions for texts are regular IF-THEN functions, but successful this case, nan usability tests for a drawstring of matter and past returns a preset consequence depending connected whether nan information is fulfilled.

Using nan IF-THEN usability is rather simple, and we’ve touched connected it a fewer times successful nan examples truthful far. However, here’s a step-by-step process for penning IF-THEN statements successful Excel pinch text.

Step 1: Click connected nan compartment container wherever you want to insert nan function.

Step 2: Write nan “equal” (=) sign.

Step 3: Start penning nan if statement, on pinch nan conditions for nan trial and nan responses to beryllium returned.

Step 4: After penning nan if statement, click enter.

Here’s an illustration showing really to make a remark stating who passed aliases grounded a people for a first-year people utilizing IF-THEN statements successful Excel pinch text:

In this example, utilizing nan IF-THEN function:

• If(B2 = “Pass,” “Scored supra 50”, “Didn’t people supra 50”)

The programme was asked to return a consequence of “Scored supra 50” aliases “Didn’t people supra 50” if nan drawstring of matter successful compartment B2 is adjacent to “Pass.”

However, we tin trial nan different measurement astir utilizing nan function:

• If(B2 <> “Pass,” “Scored supra 50”, “Didn’t people supra 50”)

The programme was asked to return a consequence of “Scored supra 50” aliases “Didn’t people supra 50” if nan drawstring of matter successful compartment B2 is not adjacent to “Pass.”

Note: Always insert double quotation marks erstwhile stating your matter parameters.

IF-THEN statements are case-insensitive unless different stated.

To make an IF-THEN connection case-sensitive, you must precede your information parameters pinch nan connection “EXACT.” Using nan illustration above, Excel will trial for uppercase matter pinch this formula:

• IF(EXACT(B2, “PASSED”), “Scored supra 50”, “Didn’t people supra 50”)

Writing IF-THEN Functions for Numbers

You must cognize nan basal Excel operators and their signs earlier penning IF-THEN statements successful Excel for numbers. Here’s a guideline to Excel that will thief you learn nan basal Excel formulas, operators, and more.

Follow these steps.

Step 1: Click connected nan compartment container wherever you want to insert nan function.

Step 2: Write nan “equal” (=) sign.

Step 3: Start penning nan if statement, on pinch nan conditions for nan trial and nan responses to beryllium returned.

Step 4: After penning nan if statement, click enter. Your look should look for illustration this:

• IF(B2>=50, “Passed”, “Failed”)

Using nan usability above, we tin constitute an IF-THEN connection to cheque if a student passed aliases grounded a course. Here’s an example:

This usability sets a information to trial if compartment B2 contains an integer worth greater than aliases adjacent to 50. If this information is met, nan programme will return a consequence of “Passed” and a "Failed" consequence if nan information isn’t met.

Note: IF-THEN functions do not only activity for integers. They tin besides activity for existent numbers and antagonistic numbers.

Writing IF-THEN functions for dates

One of nan unsocial forms of penning IF-THEN functions is nan day format. Many group whitethorn deliberation they tin constitute IF-THEN functions for dates for illustration they constitute IF-THEN functions for numbers.

Unfortunately, that is not so.

Since nan Excel programme cannot publication nan regular day format of mm/dd/yyyy, you’ll person to insert nan “DATEVALUE” usability into your IF-THEN usability to make nan Excel programme cognize that you’re testing a day condition.

Your usability should look for illustration this:

• IF(B2>=DATEVALUE(“mm/dd/yyyy”), “yes,” “no”)

Here’s a step-by-step process connected really to usage this successful your spreadsheet:

Step 1: In your spreadsheet, click connected nan compartment wherever you want to input nan function.

Step 2: Write nan “equal” (=) sign.

Step 3: Start penning nan if function. Your syntax should look thing for illustration this:

Step 4: Click Enter connected your keyboard, and nan usability will return a position consequence of either “Released” aliases “Unreleased.”

The usability supra tests whether nan day successful compartment B2 is greater than nan 11th of April 2023. The usability will springiness a “Released” aliases “Unreleased” consequence depending connected whether nan conditions are met aliases not.

Note: You tin group nan IF-THEN usability to comparison against nan existent day by inputting nan TODAY() usability into your IF-THEN function.

This is what your usability should look like:

• IF(B2>TODAY(), “Released”, “Unreleased”)

Writing IF-THEN Functions for Multiple Conditions (Nested IF statements)

Multiple IF-THEN statements aliases Nested if statements, arsenic commonly called, let you to adhd aggregate if statements into 1 if statement.

A Nested if connection will look for illustration this:

• IF(logical_test, [value_if_true], if(logical test, [value_if_true], if(logical test, [value_if_true], [value_if_false])))

If you wanted to constitute an IF-THEN usability to delegate remarks to scores of first-year students successful a peculiar course. Here’s really to do this successful a fewer steps:

Step 1: Click connected nan compartment container wherever you want to insert nan function

Step 2: Write nan “equal” (=) sign.

Step 3: Begin penning nan IF-THEN function. Your usability should look thing for illustration this:

• IF(B2>90, “Excellent”, IF(B2>=80, “Very Good”, IF(B2>=70, “Good”, IF(B2>= 50, “Fair”, “Poor”))))

Step 4: Click nan Enter cardinal to person a consequence to your function.

The usability will return a consequence of “Excellent” for students who people 90 aliases above; “Very Good” for students who people betwixt 79 and 90; “Good” for students who people 69 and 80; “Fair” for students who people betwixt 49 and 70; and “Fair” for students that people beneath 50.

Best Practices for IF-THEN Statements successful Excel

Here are immoderate practices to travel to get nan champion retired of your IF-THEN functions successful Excel.

Always usage parentheses.

The champion measurement to debar errors successful your formulas successful Excel is by utilizing parentheses. Therefore, if you want your if connection to usability properly, guarantee you usage parentheses correctly.

For example, erstwhile penning aggregate conditionals aliases a nested if statement, guarantee you usage parentheses successful nan correct places to debar “#NAME?” errors.

Use scope alternatively of cells.

When you usage Range alternatively of Cells, you tin debar errors arising erstwhile compartment information changes aliases your look needs to beryllium adjusted.

Write retired each conception arsenic its ain formula.

It whitethorn sound unnecessary, but penning each spreadsheet conception arsenic its ain look would make your information much readable and easier to understand.

Another use to doing this is that you tin find everything you request successful 1 spot and edit it according to your needs.

Even more, if there’s an correction pinch your function, penning each conception arsenic its ain look makes it easier for you to debug and find immoderate errors successful your formula.

Never hesitate to usage different formulas.

One awesome point astir Excel is that it lets you usage aggregate functions by inserting them into 1 another.

Like different formulas successful Excel, you tin insert different functions into your IF-THEN usability to get nan champion retired of it.

Using your IF-THEN usability alongside different formulas makes it easier to make calculations, saves clip (especially erstwhile you’re moving pinch ample data), and makes it easy to spot immoderate errors.

Getting Started

IF-THEN statements are definite to make your life easier. Although it whitethorn beryllium difficult to grasp everything astatine once, you'll find it easy pinch changeless believe and continuous use.

So don’t hesitate to sojourn this guideline anytime you look a problematic IF-THEN connection challenge.