• Skip to main content
  • Skip to secondary menu
Productivity Portfolio logo

Productivity Portfolio

Valuing your time & technology

  • HOME
  • Tutorials
    • Excel
    • Google
    • Word
    • Outlook
    • Terms
  • Quick Tips
    • Email
    • Security
    • Web
    • Windows
    • Internet of Things
    • Cell
  • Reviews
    • Health
    • Services
    • Software
    • Hardware
  • About
    • About Me
    • Contact Page
    • Privacy Policy
  • Newsletter
  • Resources
    • Practical Gift Ideas
    • Books
    • Favorite Podcasts
    • Online Courses
    • Online Services
  • SEARCH
You are here: Home › Tutorials › Excel › The Basics of the Excel IF Function

The Basics of the Excel IF Function

LAST UPDATED: November 8, 2020 By Anne Hennegar

TwitterPinterestLinkedInRedditFacebookPocketEmail

Oh, have times changed. When I was a kid, my parents always told me, “no ifs, ands, or buts”. Now, Microsoft Excel wasn’t around at that time or I could’ve provided a better argument for using these words. The solution lies with understanding how to use the IF function in Excel. It’s an easy, but versatile logical function for handling many scenarios. (Includes Excel worksheet with formula examples.)

Article Contents

  • What is Excel’s IF Function?
  • The Function Arguments
  • Set Up the IF Function
  • Enter IF Function Arguments
  • Another Approach - Example 2
  • Sample Excel File and Formula

What is Excel’s IF Function?

The IF function is one of simplest and useful Excel functions. It can fill cell fields for you based on evaluating a condition. You can find it in the Logical category.

The wizard-like dialog allows you to fill 3 Function Arguments or data elements. This is the easiest way if you’re just learning Excel formulas because you can see if the function returns your expected result. At the bottom of the dialog, there is a line that reads “Formula result =“.

blank dialog for IF function.Pin
Blank IF function dialog

The Function Arguments

FieldDefinition
Logical_testA test on a cell value that is either TRUE or FALSE.
Value_if_trueThe value Excel will put in a cell if the test is true.
Value_if_falseThe value Excel will put in a cell if the test fails.

Despite not having Microsoft Excel, my parents routinely employed this type of logic when calculating my allowance. Their version read:

IF you empty the garbage AND mow the lawn AND wash the dishes AND walk the dog, you get your full allowance. And since I grew up in New England, this logic would change with the seasons to account for things like leaves and snow.

Setting Up the IF Function

Although Excel can’t issue an allowance, it can calculate the amount using a logic test based on whether a cell met a formula condition.

For example, I could create a spreadsheet with the tasks needed to get an allowance. If the task was completed, (TRUE situation) a value would be applied toward the allowance. If the task wasn’t completed, (FALSE situation), nothing would be added.

These examples are noted by labels [1] and [2] in the screen snap below.

Excel IF logical test examplesPin
Logical test examples

Using the example above, you might express the logic in the following way:

IF cell B2 equals “Y” , then use the Rate value in cell C2 ($3.00).

IF cell B2 does not equal “Y”, then place 0 in cell D2.

As you can see in this example, the IF logical condition is either TRUE or FALSE. And it pays to take out the garbage.

To enter your IF Function Arguments,

  1. Click the spreadsheet cell where you wish to use the Excel formula.
  2. From the Formulas tab, click Insert function…
  3. In the Insert Function dialog text box, type “if“.
  4. Make sure your cursor is in the Logical_test text box.
  5. Click the spreadsheet cell you wish to evaluate. Excel will fill in the cell reference such as “B2”.
  6. Add the equals sign = and your desired value in quotes. For example =”Y”.
  7. In the Value_if_true field, type the value you would like entered in your cell if B2 equals “Y”. In our example, I’ll click cell C3.
  8. In the Value_if_false: field enter the value the cell should have if B2 does not have a “Y”. I’ll enter 0. I could leave it blank, but the cell would show “FALSE”
  9. Review the dialog to see if the Formula result= value (label 1 below) is what you expect. If not, check to see if any errors show to the right of the fields (label [2] below).
  10. Excel If function arguments.Pin
    Example of a completed IF formula and result
  11. Click OK.
  12. Copy the formula to the other cells in your column.

Excel IF Example 2

The above spreadsheet might have been Version 1 for my parents. A new incentive program would appear based on some parent/child negotiations and competitive neighborhood rates. I probably would’ve fought for pay on partial tasks. No doubt, my parents would counter with a penalty clause if something was less than half done.

Excel is flexible when it comes to IF statements and can evaluate more than a simple “Y” or “N”. For example, if we convert our previous Done? column to a % Done column with a number, we can accommodate these new requirements.

The new formula returns the allowance off of the % Done column. If the task completion number is greater than .5, a prorated amount was applied to the allowance. If the task completion rate was .5 or below, a negative amount was applied to the allowance. Loosely translated, a “half-assed” performance costs money. You could also apply colors using conditional formatting.

IF condition based on percentagePin
Using a numeric value instead of Y/N value

Excel’s IF function is a versatile and useful function. Once you get the hang of it, you’ll start using it in more scenarios. The two examples presented here were foundational. But you can use IF functions to handle other transactions such as applying sales tax, shipping charges, fixing Excel DIV 0 errors or even nested IF functions with Boolean logic. And if you have kids, let them build the Excel spreadsheet and give them a bonus for using the IF function.

Related Excel Resource

attach_file Excel IF Function Example Spreadsheet

You Might Also Like These Excel Tutorials

  • How to Use Excel VLOOKUP
  • How to Create an Excel Pivot Table
  • The Benefits of Creating Excel Tables
Excel Formulas & Functions For Dummies
Bluttman, Ken (Author); English (Publication Language); 400 Pages - 11/06/2018 (Publication Date) - For Dummies (Publisher)
$20.19

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-01-14

TwitterPinterestLinkedInRedditFacebookPocketEmail

Filed Under: Excel, Tutorials Tagged With: Excel Formulas & Functions

© 2021 · Productivity Portfolio | PO BOX 117361 Burlingame, CA 94011

I use cookies to ensure that I give you the best experience on our website. If you continue to use this site I'll figure that you are happy with that. You can find more details in the privacy policy.OKNoPrivacy policy