How to Use IF Function and Structured Reference in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Excel provides us with different ways to calculate using various functions. For example, we can use explicit cell references or structured references in Excel. This article will show you how to use IF function and structured reference in Excel.


Download Practice Workbook

Download this workbook and practice while going through this article.


Introduction to Structured Reference

The syntax for referencing Excel Tables is known as a structured reference. Excel gives the table and each column heading it contains a name when you create an Excel table. When adding formulas to an Excel table, you can choose the references from the table. When you use structured reference, the column names will pop up despite a particular cell. Every time you add or remove data from the table, the names in structured references change because of their dynamic nature.


2 Steps to Use IF Function and Structured Reference in Excel

This is the dataset for today’s article. We have some students and their marks in a particular subject. I am going to assign the grades using the IF function. More to that, I will use the structured reference while applying the formula.

how to use if function and structured reference in excel


Step 1: Create Table with Proper Data

First of all, I will create a table with the entire dataset. This is a requisite to use structured references. For this,

  • Select the range B4:D11.
  • Then, go to the Insert.
  • After that, select Table.

  • Create Table box will appear. Check My table has headers box as the table has headers.
  • Then, click OK.

how to use if function and structured reference in excel

  • Excel will create a table.


Step 2: Apply Formula Using IF Function

Now, I will use the IF function to assign grades to the students. Let’s see how to do it.

  • Go to D5 and write down the following formula
=IF([@Marks]>=90,"A",IF([@Marks]>=80,"B",IF([@Marks]>=70,"C",IF([@Marks]>=60,"D","F"))))

how to use if function and structured reference in excel

Note: [@Marks] appears because the D4:D11 range is a table. That’s why Excel is using the structured reference i.e., selecting the entire column when you select a single cell (C5) under the Marks column.

Explanation:

The value in D5 is 81.

  • Excel tests the logical tests one by one. First, it tests [@Marks]>=90.
  • [@Marks]>=90 is FALSE. That’s why it checks the next logical test. That is [@Marks]>=80.
  • [@Marks]>=80 is TRUE. So, Excel will return the output “B”.
  • Now, press ENTER. Excel will return the output.

  • Now, use the Fill Handle to AutoFill up to D11.

how to use if function and structured reference in excel

Read More: How to Reference a Dynamic Component of a Structured Reference in Excel

Things to Remember

  • When using Nested IF, please remember that Excel will first check the first logical test. If the test is FALSE, Excel will check the 2nd one, and so on. But if the 1st test is TRUE, Excel will stop checking other conditions and get you the output for the 1st logical test.
  • You can use CTRL+T as a keyboard shortcut to create a table after selecting the range.

Conclusion

In this article, I have shown you how to use IF function and structured reference in Excel in easy steps. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit ExcelDemy for more useful articles like this.


Related Articles

Akib Bin Rashid

Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo