Indirect Function in Excel is an interesting way to identify textual references and extract the data within the excel workbook. Indirect Function comes handly when users would not recommend taking a risk on varying a cell-carrying formula. Instead, they prefer to change a cell reference within a formula without changing the formula itself.
What Is an Indirect Function in Excel?
Indirect Function in Excel is considered one of the volatile functions that use a specific cell or string reference to carry out data manipulation operations. For example, the Indirect Function in Excel is employed to return a valid cell reference of a selected string. It is used to avoid the hassle of corrupting the cell formula by providing a cell reference rather than the actual cell data. This helps in the cells' data integrity and provides accurate information.
How to Implement Indirect Function in Excel?
Let us consider an example of students in a classroom. Here, each student earned marks in their respective subjects. Let us assume that you are the professor and your responsibility is to carry out the calculation of total marks obtained an average of all the subjects for each student.
Now, let us employ the Indirect Function in Excel to calculate the necessary information.
The typical table storing the student information looks as follows.
The next stage is to rename the cells. The typical cell on an excel sheet has its unique address. So, for example, let us select a random cell in the workbook, as shown below.
The cell that I have selected has an address as B7. The cells can be renamed. Microsoft Excel follows two methods to identify a cell address as follows.
- A1 - format
- R1C1 - Format
We can select either of the formats and try to rename the cells.
Now, according to the data table, I can rename the individual columns of the table based on the students' names. For example, I have selected cells ranging from B2:B5 and renamed them based on the students' name as Jake, as shown in the image below.
Now, after remaining all the columns in the same manner, we can continue calculating the average and total of the marks.
I have made three additional rows as follows:
- Student
- Average
- Total
The student row provides a drop-down menu that includes the student's name. The drop-down list can be created using Data Validation in Excel. The student's name is shown in a drop-down, as shown below.
Now, come the major steps.
Instead of calling the aggregate functions directly on the data cells, we indirectly carry out the aggregate Function on the cell reference.
Each set of columns with marks is renamed after the respective student.
The students' names are stored in the form of a list in the drop-down menu using the data validation in excel.
We employ the Indirect Function to refer to the cell address with the student's name as the student name refers to the marks obtained by students in the form of cell ranges. The process is shown in the image below.
Next, We employ the INDIRECT Function within Aggregate(SUM and AVERAGE) Functions. This way, the marks reference is used instead of the actual marks. The process is shown in the following images.
Now, the INDIRECT Function will help you with the details of the Total Marks and Average of a particular student based on the name you select via the drop-down menu.
The Final result will be available as shown below.
With that, we come to an end of this article on the INDIRECT Function in Excel.
Kick-start your career growth story with PGP in Business Analysis. Get a chance to master Excel, Tableau, and Python tools. Start learning now!
Next Steps
Flash Fill in Excel can be your next step in learning the fundamentals of Microsoft Excel. Flash Fill helps you discover the quickest ways to automatically fill the cells with the required information by understanding the pattern followed by the reference data.
Interested to learn more about Business Analytics? Then consider visiting the Business Analytics certification course from Simplilearn. It is Ranked among the five topmost business analytics courses in the Industry. This Simplilearn program is a result-oriented training and certification program that helps you master statistics and data analytics concepts.
Should you have any questions for us on this tutorial on "Indirect Function in Excel"? Please feel free to write to us in the comments below. Our expert team will resolve them and will be happy to answer them at the earliest.
