In this tutorial, I am going to share with you 4 practical examples to use the If Then Else statement in Excel. You can easily apply these examples in any set of data to conditionally attach appropriate comments. To achieve this task, we will also see some useful features that might come in handy in many other Excel-related tasks. Let’s say a teacher wants to remark on his students’ performances based on their grades. He may effectively use the If Then Else statement to do something like the following image.
Download Practice Workbook
You can download the practice workbook from here.
4 Practical Examples to Use If Then Else Statement in Excel
We have taken a concise initial dataset to explain the steps clearly. The dataset has approximately 7 rows and 4 columns. Initially, we are keeping all the cells in General format. For the initial dataset, we have 3 unique columns which are Student Name, Subject, Grade, and Comment. Although we may vary the number of columns later if that is needed.
1. Assessment of Students’ Grades (Adding Text Based on a Certain Value with OR Criteria)
In this first example, we will see how we can quickly generate comments according to the grades obtained by each student using the VBA If Then Else statement in Excel. Follow the steps below for this.
- Next, go to Insert and click on Module.
- Now, insert the following code in the module window:
Sub Student_Grade() For Each Sh_grade In Range("D5:D10") If Sh_grade = "A" Or Sh_grade = "B" Then Sh_grade.Offset(0, 1).Value = "Great Job" ElseIf Sh_grade = "C" Then Sh_grade.Offset(0, 1).Value = "Not Good" Else Sh_grade.Offset(0, 1).Value = "Need a Tutor" End If Next Sh_grade End Sub
- Then, select the Student_Grade macro and click on Run.
- As a result, this will automatically give the appropriate comments for each student’s grade.
2. Extracting Specified Word from Code Based on Condition
If we have shortcodes as letters, we can easily use the If Then Else statement in Excel to extract the complete word from that with just a few lines of code. Let us see how to do this.
- To begin with, open the VBA module window following the steps of Method 1.
- Now, enter the code below in the module window.
Sub Word_From_Code() Dim Sh_LRegion As String Dim Sh_LRegionName As String Sh_LRegion = Range("D5").Value If Sh_LRegion = "N" Then Sh_LRegionName = "North" ElseIf Sh_LRegion = "S" Then Sh_LRegionName = "South" ElseIf Sh_LRegion = "E" Then Sh_LRegionName = "East" Else Sh_LRegionName = "West" End If Range("E5").Value = Sh_LRegionName End Sub
- Then, open the Macro window following the steps in Method 1.
- Next, choose the Word_From_Code macro and click on Run.
- Consequently, you should get the full names from the region code values.
- How to Use Multiple IF Statements in Excel Data Validation
- Excel IF Statement with VLOOKUP for Multiple Conditions Range
- How to Use If Statement Based on Cell Color in Excel (3 Examples)
- Dynamic Data Validation List in Excel with IF Statement Condition
- How to Use IF Statement with Not Equal To Operator in Excel
3. Commenting on Product Price
Now in this third example, we will work to attach proper tags on the price of specific products using the If Then Else statement in Excel.
- Firstly, open the VBA macro module window by following the previous steps of Method 1.
- Now, type in the code below inside the module window.
Sub Product_Price() For Each Sh_price In Range("D5:D10") If Sh_price > 500 Then Sh_price.Offset(0, 1).Value = "Overpriced" ElseIf Sh_price > 200 And Sh_price <= 500 Then Sh_price.Offset(0, 1).Value = "Medium Price" Else Sh_price.Offset(0, 1).Value = "Lower Priced" End If Next Sh_price End Sub
- Then, carry out the opening instructions of Method 1 to access the Macro window as before.
- After that, select the Word_From_Code macro and press Run.
- Finally, you should immediately get the proper comment on the price for the various products.
4. Use of If Then Else Statement to Set Multiple Conditions with Variables in Excel VBA
Let us see in this final example, how can we show grades after inputting the obtained marks using the If Then Else statement in Excel.
- For this, open the VBA module as we did earlier in Method 1.
- Next, enter the following code:
Sub MultipleConditions() Dim sh_marks As Long sh_marks = InputBox("Enter the Obtained Marks:") If sh_marks >= 80 Then MsgBox "Obtained Grade: A+" ElseIf sh_marks < 80 And sh_marks >= 70 Then MsgBox "Obtained Grade: A" ElseIf sh_marks < 70 And sh_marks >= 60 Then MsgBox "Obtained Grade: B" ElseIf sh_marks < 60 And sh_marks >= 50 Then MsgBox "Obtained Grade: C" ElseIf sh_marks < 50 And sh_marks >= 40 Then MsgBox "Obtained Grade: D" Else: MsgBox "Failed" End If End Sub
- Now, open the Macro following the steps of Method 1.
- Then, select the MultipleConditions macro and click on Run.
- After that, insert a sample mark to test and click OK.
- Finally, this will quickly give you a message box showing the actual grade value.
Things to Remember
- The basic syntax for an ..Then…Else statement in VBA is: “If condition Then statement1 Else statement2”.
- Here the condition is a logical expression that evaluates to either True or False.
- Statement1 is executed if the condition is True, and the statement2 is executed if the condition is False.
- You can have multiple ..Else statements nested within an If…Then…Else statement for more complex logic.
- You can also use the ElseIf statement to check multiple conditions within an ..Then…Else statement.
- When comparing text strings in VBA, use the = operator or the Like
- You can use the And and Or operators to combine multiple conditions in an If
- Always use End If to close the If Else statement.
I hope you could apply the methods I showed in this tutorial on how to use the If Then Else statement in Excel. As you can see, there are quite a few ways to achieve this task. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
- How to Use Conditional Formatting If Statement Is Another Cell
- Excel IF Statement Between Two Numbers (4 Ideal Examples)
- Find Sum If Cell Color Is Green in Excel (4 Easy Methods)
- How to Use Wildcard with If Statement in Excel (5 Methods)
- Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)