Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > How to Use the Excel IFS Function? – 2 Easy Examples

How to Use the Excel IFS Function? – 2 Easy Examples

Note: This tutorial on the IFS function in Excel is suitable for Excel 2016 version and later, including Office 365.

Have you wondered if there is a simpler way to handle multiple nested-IF statements in one step? Meet the Excel IFS function.

Introduced in Excel 2016, it is designed to eliminate complex nested-IF statements. It is much easier to use than the IF function, as it can compare up to 127 conditions in one go, unlike an IF formula, which can compare only one statement at a time. 

In this guide, I will walk you through the basics of the Excel IFS function and teach you how to use it effectively in your day to day work. 

You’ll learn:

  • Excel IFS Function Syntax
  • How to Use the IFS Function in Excel?
    • Example 1: Assign Grades Using the Excel IFS Function
    • Example 2: Assign Default Value to the IFS Function

Related:

The Excel SMALL Function – 3 Best Examples

How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide

How to Use Excel COUNTIFS: The Best Guide

Excel IFS Function Syntax

=IFS(logical_test1,value_if_true1, logical_test2, value_if_true2,……logical_test127,value_if_true127)

Please keep in mind that you should enter at least one pair of logical tests and value_if_true inside the IFS formula. The rest of the other logical tests are optional. 

How to Use the IFS Function in Excel?

The IFS function can be used as a normal IF function, except that instead of specifying value if false, we list all criteria under the same function. Let us look at some examples to understand better. 

Example 1: Assign Grades Using the Excel IFS Function

In the following example, to assign grades to students’ marks without using complicated IF statements, you can use the following IFS formula in the first cell and drag it to the rest of the cells.

=IFS(C3>90,”A+”,C3>80,”A”,C3>70,”B”,C3>60,”C”,C3>50,”D”,C3<50,”F”)

Excel IFS Function
Assigning grades using IFS function

As you can see, this simple formula eliminates the need for complex loops of nested if-statements. For example, if we used a normal IF formula to accomplish the same task, it will look something like this.

=IF(C3>90,”A+”,IF(C3>80,”A”,IF(C3>70,”B”,IF(C3>60,”C”,IF(C3>50,”D”,”F”)))))

IF Function
Assigning grades using IF function

Also Read:

How to Add Error Bars in Excel? 7 Best Methods

How to Extract an Excel Substring? – 6 Best Methods

How to Make a Line Graph in Excel? 4 Best Line Graph Examples

Example 2: Assign Default Value to the IFS Function

There is no set default value for the IFS function. That is it will not return any preset value if all the conditions are FALSE.

The IFS Function will return a #N/A error in case no criteria are met
The IFS Function will return a #N/A error in case no criteria are met

However, you can manually set a default value by setting the last logical test to TRUE and its corresponding value_if_true to the default value. 

Set the default value as the last TRUE criterion
Set the default value as the last TRUE criterion

IFS(C2=1,”OK”,C2=2,”LOW”,C2=3,”HIGH”,TRUE,”INVALID”)

Here, “INVALID” is the assigned default value if all criteria are not met. 

Points to Remember: 

  • The IFS does not return any default if all conditions are FALSE. It will return an #N/A error instead. 
  • All logical tests should either return a TRUE or FALSE binary value.  A #VALUE error will appear if any other value is returned. 

Suggested Reads:

How to Shade Every Other Row in Excel? (5 Best Methods)

How to Superscript in Excel? (9 Best Methods)

How to Split Cells in Excel? 3 Easy Methods

Conclusion 

In this short tutorial, we have covered the IFS function’s syntax, its usage and common errors faced by people while using it. We hope you find this useful. If you have any questions about this or any other Excel feature please let us know in the comments section. We are always happy to help. 

Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. You can train your entire team in Excel and other business software, for a low one-time monthly fee here.

Visit our free Excel Resources Centre for more high-quality Excel guides. 

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

Similar Posts

How to Add Page Numbers in Word? 3 Easy Examples

How to Use ANOVA in Excel: 4 Simple Steps

How to do a Mail Merge in Microsoft Word 2013 – Part 2

Arranging Objects in PowerPoint 2016

How to Use Microsoft Excel 2016 Formulas and Functions

How to Use Layers in Photoshop Elements 2019 – Part 5 – Blending Modes

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)