If you’re a Microsoft Excel user, then you know that the IFS function can be used to help organize data. This function can be used to group data by column, by row, or by both. To use the IFS function in Microsoft Excel, first open the spreadsheet and click on the cell that you want to group data by. Then use the arrow keys to move the data around so that it is organized in a way that you want it. You can also use the CTRL key to select all of the cells in a row or column and then use the left and right arrows to move them around. If you want to group data by column, then use the CTRL key and drag one of the columns down into position on your spreadsheet. Then use the arrow keys to move the data around so that it is organized in a way that you want it. You can also use the CTRL key and drag one of the columns up into position on your spreadsheet. If you want to group data by row, then use the CTRL key and drag one of the rows down into position on your spreadsheet. Then use the arrow keys to movethedata around so that it is organized in a way that you want it. You can alsousethe CTRLkeyanddragoneoftherowsupintopositiononyourspreadsheet. Finally, if you want to group data by both column and row, then first create a new sheet using Microsoft Excel’s Create New Sheet Wizard and then select Data Grouping from its drop-down list. Then enter values for each of your columns (by using either commas or spaces), and click on OK when prompted for confirmation.
If you’re familiar with using the IF function in Excel, then you might be ready to check out the IFS function. With it, you can test multiple conditions at once, instead of using nested IF statements.
Using IFS, you can test up to 127 conditions in a single Excel formula. Although this number of tests is probably much more than you need, the function is ideal for checking several conditions. The formula then returns the value you specify for the first true condition in the statement.
Use the IFS Function in Excel
The syntax for the function is IFS(test1, if_true1, test2, if_true2,…) where you enter the condition for each test argument and the value to return if the test is true for each true argument.
Let’s look at a few basic examples.
Return Cell Reference Values
Here, the condition looks at the ID number in cell C2. When the formula finds the matching condition, 1 through 5, it returns the corresponding name.
To break down the formula, if the value in cell C2 is 1, return the value in A2, if it’s 2, return the value in A3, if it’s 3, return the value in A4, if it’s 4, return the value in A5, and if it’s 5, return the value in A6.
If you were to use a nested IF statement instead of the IFS function, your formula would look like this:
While you obtain the same result using either option, the formula for the IFS function is a little less confusing and doesn’t take as much time to assemble.
RELATED: The Basics of Structuring Formulas in Microsoft Excel
Return Numbers
For another IFS example, we’ll apply bonuses to our salespeople based on their sales totals. Here’s the formula:
To break down this formula, if the total in cell F2 is greater than 100,000, return 1,000, if it’s greater than 75,000, return 750, and if it’s greater than 50,000, return 500.
To compare again, here is what the formula looks like as a nested IF statement instead:
By using the formula for the IFS function, you eliminate the need to repeatedly type IF with an opening parenthesis and remember the correct number of closing parentheses at the end.
Return Text Values
In this next example, we have a list of employees who haven’t finished their training. We’ll display a text result based on the percent complete in cell B2. Note that the text values must be in quotes.
To break down this formula, if the value in cell B2 is less than 50, return “Less than half,” if it equals 50, return “Half,” and if it’s more than 50, return “More than half.”
For one more comparison, here is what the nested IF statement looks like to obtain the same result:
It can be especially difficult to troubleshoot formulas errors in lengthy statements or those that needs extra care like including quotation marks. This is just one more reason to consider using the IFS function instead of a nested IF formula.
As Microsoft explains:
Microsoft goes onto say that nested IF statements can be hard to maintain. This is another consideration for using the IFS function, especially if you’re working on your spreadsheet with others.
RELATED: How to Fix Common Formula Errors in Microsoft Excel
When you want to test several conditions for a cell, or even a cell range, consider using the IFS function. For more, learn how to use the other logical functions in Excel like AND, OR, and XOR.
