How to Use IF-THEN Statements in Excel

To take pleasure in Excel absolutely, you should know use the IF-THEN operate.

The IF-THEN is one in all Excel’s most spectacular features, as you need to use it to investigate information, draw inferences and make choices beneath sure preset circumstances. However what precisely are IF-THEN statements, and how are you going to begin utilizing them?

On this information, we’ll present you use IF-THEN statements to enhance your mastery of Excel.

Desk of Contents

What are IF-THEN Statements in Excel?

IF-THEN statements are features in Excel that return a singular set of actions after your preset situation is met.

Merely put, whenever you use the “If” assertion, you’re setting a situation and instructing Excel to carry out a singular set of actions when your preset situation is true and one other when the situation is fake.

To make use of the “If “ operate in Excel, you begin by writing the equal signal (=), adopted by “if,” then your situation, and a two-part syntax instructing this system on what to do when your necessities are or aren’t met.

The syntax often appears like this:

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

The “If” operate is helpful, particularly when you will have a big information quantity and need to keep away from the stress of computing formulation for every information. Right here’s an instance of how easy it’s to make use of IF-THEN statements in Excel:

On this instance, I’ve a spreadsheet containing three columns of information. The place column A comprises title information, and columns B and C, include integer information.

Utilizing the If operate, I set a situation utilizing the syntax:

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

This operate initiates a command telling the Excel program to check if the info worth in cell B1 is bigger than the info worth in cell C1.

I’ve merely instructed Excel to present me a response of “sure” beneath the situation that the worth of cell B1 is bigger than cell C1 and a response of “no” beneath the situation that the worth of cell B1 is lower than the worth of cell C1.

I then prolonged this operate to the opposite respective cells utilizing a easy autofill trick. Take a look at our information on utilizing Excel like a professional for some useful methods to make your Excel expertise simpler.

It’s vital to notice that the IF-THEN assertion solely means that you can set a situation for 2 variables. Meaning you’ll be able to solely set circumstances together with, say, the info worth in cells B1 and C1 or a situation together with information values in cell B1 and an outlined integer or textual content.

For instance, you’ll be able to set a situation instructing Excel to return a response of “Sure” if the worth in cell B1 is bigger than the worth of cell B2 as proven within the instance above. Or, you’ll be able to set a situation asking Excel to return the response ”sure” or “no” if the worth of cell B1 is bigger or lower than 1000.

Right here’s a fast instance:

Right here, I advised Excel to return the response “sure” if the info worth in cell C1 is bigger than 5000 and “no” if the worth is lower than 5000.

Right here’s the response Excel returned:

As talked about earlier, the IF-THEN assertion is a robust Excel operate with quite a few advantages. Studying to make use of this operate correctly will astronomically enhance your mastery of Excel.

The Advantages of IF-THEN Statements In Excel

Listed below are some advantages that IF-THEN statements in Excel can give you:

Checking for Errors in Information

You need to use IF-THEN statements to examine for errors when inputting information into the spreadsheet.

For instance, for those who really feel like you will have made a mistake by inputting a quantity as an alternative of a textual content into your spreadsheet, you’ll be able to examine utilizing an if operate:

• IF(B1 = “”, “Textual content”, “Quantity”)

Calculating Debt Schedule or Depreciation Schedule

Monetary Analysts can use IF-THEN statements to calculate debt and depreciation schedules.

Utilizing the IF-THEN assertion, you’ll be able to set circumstances asking this system to return a response for months with excellent balances and even draw up a compensation schedule.

For Budgeting

As a monetary analyst, utilizing IF-THEN statements would make budgeting simpler. IF-THEN features will mean you can create as much as 64 circumstances, which may help you in your budgeting course of.

To Set up Information

The first potential of IF-THEN statements is setting directions beneath sure circumstances. Thus it’s simple to prepare information in your spreadsheet beneath sure circumstances utilizing if statements.

You’ll be able to set circumstances for a specific cell field or a number of cells and arrange your spreadsheet information beneath stated circumstances.

Works Properly With Different Formulation

An thrilling benefit of IF-THEN statements is their flexibility. Because of its flexibility, you need to use IF-THEN statements with different formulation in Excel.

For instance, you need to use it with the DATEVALUE() operate, the SUM() operate, and even the COUNT() operate.

How one can Use IF-THEN Statements in Excel

Now that you understand what an IF-THEN assertion is and the way it can profit you, the subsequent step is realizing use it correctly.

There are two strategies to make use of the operate, and we’ll begin with the fundamentals. First, right here’s a easy step-by-step process for writing IF-THEN statements in Excel.

Step 1: Click on on the cell you want to format, say cell A1.

Step 2: Write the IF-THEN operate system straight into the cell field.

Or within the system field.

Step 3: Start writing your circumstances and anticipated responses to stated circumstances.

Keep in mind: All the time begin the system with an “equal” signal and enter the suitable punctuation so that you don’t get an error message after inputting the operate.

Right here’s an alternate step-by-step you need to use.

Different Step 1: Click on on the “Formulation” tab within the taskbar menu of your Excel program.

Different Step 2: Click on on the “Insert Perform” possibility, which can launch a dialog menu:

Different Step 3: Choose “IF” from the checklist of choices within the dialog menu and click on “OK.” It should launch a dialog field.

Different Step 4: Within the dialog field, enter your situation and directions telling this system what solutions to return when the situation is met and when it isn’t met.

Be aware: It’s higher to make use of the “Insert Perform” technique for those who’re writing a easy IF-THEN operate, because it launches a system builder that may enable you remove the dangers of errors when inputting your system.

This technique will robotically enter the suitable punctuations and codecs and solely require you to enter your circumstances and directions within the three fields of the dialog field.

Now that you understand how to begin with the IF-THEN operate, let’s evaluation the opposite use circumstances for if features.

Writing IF-THEN Perform for texts

IF-THEN features for texts are common IF-THEN features, however on this case, the operate checks for a string of textual content after which returns a preset response relying on whether or not the situation is fulfilled.

Utilizing the IF-THEN operate is kind of easy, and we’ve touched on it just a few instances within the examples to date. Nevertheless, right here’s a step-by-step process for writing IF-THEN statements in Excel with textual content.

Step 1: Click on on the cell field the place you need to insert the operate.

Step 2: Write the “equal” (=) signal.

Step 3: Begin writing the if assertion, together with the circumstances for the check and the responses to be returned.

Step 4: After writing the if assertion, click on enter.

Right here’s an instance exhibiting generate a comment stating who handed or failed a course for a first-year class utilizing IF-THEN statements in Excel with textual content:

On this instance, utilizing the IF-THEN operate:

• If(B2 = “Cross,” “Scored above 50”, “Didn’t rating above 50”)

This system was requested to return a response of “Scored above 50” or “Didn’t rating above 50” if the string of textual content in cell B2 is the same as “Cross.”

Nevertheless, we will check the opposite approach round utilizing the operate:

• If(B2 <> “Cross,” “Scored above 50”, “Didn’t rating above 50”)

This system was requested to return a response of “Scored above 50” or “Didn’t rating above 50” if the string of textual content in cell B2 shouldn’t be equal to “Cross.”

Be aware: All the time insert double citation marks when stating your textual content parameters.

IF-THEN statements are case-insensitive except in any other case said.

To make an IF-THEN assertion case-sensitive, you should precede your situation parameters with the phrase “EXACT.” Utilizing the instance above, Excel will check for uppercase textual content with this system:

• IF(EXACT(B2, “PASSED”), “Scored above 50”, “Didn’t rating above 50”)

Writing IF-THEN Capabilities for Numbers

It’s essential to know the fundamental Excel operators and their indicators earlier than writing IF-THEN statements in Excel for numbers. Right here’s a information to Excel that may enable you study the fundamental Excel formulation, operators, and extra.

Observe these steps.

Step 1: Click on on the cell field the place you need to insert the operate.

Step 2: Write the “equal” (=) signal.

Step 3: Begin writing the if assertion, together with the circumstances for the check and the responses to be returned.

Step 4: After writing the if assertion, click on enter. Your system ought to appear like this:

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

Utilizing the operate above, we will write an IF-THEN assertion to examine if a pupil handed or failed a course. Right here’s an instance:

This operate units a situation to check if cell B2 comprises an integer worth better than or equal to 50. If this situation is met, this system will return a response of “Handed” and a “Failed” response if the situation isn’t met.

Be aware: IF-THEN features don’t solely work for integers. They’ll additionally work for actual numbers and unfavourable numbers.

Writing IF-THEN features for dates

One of many distinctive types of writing IF-THEN features is the date format. Many individuals might imagine they’ll write IF-THEN features for dates like they write IF-THEN features for numbers.

Sadly, that’s not so.

Because the Excel program can’t learn the common date format of mm/dd/yyyy, you’ll need to insert the “DATEVALUE” operate into your IF-THEN operate to make the Excel program know that you just’re testing a date situation.

Your operate ought to appear like this:

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

Right here’s a step-by-step process on use this in your spreadsheet:

Step 1: In your spreadsheet, click on on the cell the place you need to enter the operate.

Step 2: Write the “equal” (=) signal.

Step 3: Begin writing the if operate. Your syntax ought to look one thing like this:

Step 4: Click on Enter in your keyboard, and the operate will return a standing response of both “Launched” or “Unreleased.”

The operate above checks whether or not the date in cell B2 is bigger than the eleventh of April 2023. The operate will give a “Launched” or “Unreleased” response relying on whether or not the circumstances are met or not.

Be aware: You’ll be able to set the IF-THEN operate to check towards the present date by inputting the TODAY() operate into your IF-THEN operate.

That is what your operate ought to appear like:

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

Writing IF-THEN Capabilities for A number of Situations (Nested IF statements)

A number of IF-THEN statements or Nested if statements, as generally referred to as, mean you can add a number of if statements into one if assertion.

A Nested if assertion will appear like this:

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

Should you needed to write down an IF-THEN operate to assign remarks to scores of first-year college students in a specific course. Right here’s how to do that in just a few steps:

Step 1: Click on on the cell field the place you need to insert the operate

Step 2: Write the “equal” (=) signal.

Step 3: Start writing the IF-THEN operate. Your operate ought to look one thing like this:

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

Step 4: Click on the Enter key to obtain a response to your operate.

The operate will return a response of “Wonderful” for college kids who rating 90 or above; “Very Good” for college kids who rating between 79 and 90; “Good” for college kids who rating 69 and 80; “Truthful” for college kids who rating between 49 and 70; and “Truthful” for college kids that rating under 50.

Finest Practices for IF-THEN Statements in Excel

Listed below are some practices to comply with to get the very best out of your IF-THEN features in Excel.

All the time use parentheses.

One of the simplest ways to keep away from errors in your formulation in Excel is by utilizing parentheses. Due to this fact, in order for you your if assertion to operate correctly, make sure you use parentheses appropriately.

For instance, when writing a number of conditionals or a nested if assertion, make sure you use parentheses in the proper locations to keep away from “#NAME?” errors.

Use vary as an alternative of cells.

If you use Vary as an alternative of Cells, you’ll be able to keep away from errors arising when cell information modifications or your system must be adjusted.

Write out every part as its personal system.

It might sound pointless, however writing every spreadsheet part as its personal system would make your information extra readable and simpler to grasp.

One other profit to doing that is that yow will discover the whole lot you want in a single place and edit it in accordance with your wants.

Much more, if there’s an error together with your operate, writing every part as its personal system makes it simpler so that you can debug and discover any errors in your system.

By no means hesitate to make use of different formulation.

One improbable factor about Excel is that it allows you to use a number of features by inserting them into each other.

Like different formulation in Excel, you’ll be able to insert different features into your IF-THEN operate to get the very best out of it.

Utilizing your IF-THEN operate alongside different formulation makes it simpler to make calculations, saves time (particularly whenever you’re working with giant information), and makes it simple to identify any errors.

Getting Began

IF-THEN statements are sure to make your life simpler. Though it might be tough to know the whole lot without delay, you may discover it simple with fixed follow and steady use.

So don’t hesitate to go to this information anytime you face a problematic IF-THEN assertion problem.