Tutorial Playlist

Excel Tutorial

Overview

Your One-Stop Solution to Know About Excel Worksheets

Lesson - 1

Your One-Stop Solution For Excel UserForms

Lesson - 2

The Best Guide on How to Send an Email in Excel

Lesson - 3

Your One-Stop Solution That Will Help You Learn How to Convert PDF to Excel

Lesson - 4

A One-Stop Solution for Acing the Excel MIS Report

Lesson - 5

All You Need to Know About Barcode in Excel

Lesson - 6

Your One-Stop Solution to Learn Mail-Merge in Excel and Its Implementation

Lesson - 7

A Comprehensive Guide on Excel Flash Fill

Lesson - 8

Slicers in Excel: The Ultimate Guide to Help You Design and Develop Excel Slicers

Lesson - 9

One-Stop Solution to Learn Everything About Excel Budget Template

Lesson - 10

All You Need to Learn About Match Function in Excel and Its Real-Time Implementation

Lesson - 11

Everything You Need to Learn on How to Create a Pivot Table From Multiple Sheets

Lesson - 12

The Major Fundamentals About XLookUp in Excel Along With Practical Examples

Lesson - 13

The Ultimate Guide to Learn How to Compare Two Columns in Excel

Lesson - 14

The Best Guide and Your One-Stop Solution to Master the Fill Series in Excel

Lesson - 15

What Is the DATE Function in Excel and How to Implement It?

Lesson - 16

Your One-Stop Solution to Designing and Implementing Hyperlinks in Excel

Lesson - 17

One-Stop Solution to Master Everything You Need to Know About Auto-Sum in Excel

Lesson - 18

DAX in Excel: The Complete Guide for DAX Functions and Formulas in Excel

Lesson - 19

A Perfect Guide for All You Need to Know About Data Formatting in Excel

Lesson - 20

The Perfect Guide to Learn How to Add Columns in Excel

Lesson - 21

The Perfect Guide That Will Explain to You How to Remove Page Break in Excel

Lesson - 22

The Perfect Guide to Explain to You How to Remove Blank Rows in Excel

Lesson - 23

The Perfect Guide to Learning How to Highlight Duplicates in Excel

Lesson - 24

One-Stop Solution Understand the Fundamentals of Indirect Function in Excel

Lesson - 25
One-Stop Solution Understand the Fundamentals of Indirect Function in Excel

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. 

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

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.

Indirect-Function-in-Excel-Original

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.

Indirect-Function-in-Excel-cell-address

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.

Indirect-Function-in-Excel-cell-address-Rename

FREE Business Analytics With Excel Course

Start your Business Analytics Learning for FREEStart Learning
FREE Business Analytics With Excel Course

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

Indirect-Function-in-Excel-cell-address-columns

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.

Indirect-Function-in-Excel-Drop-Down.

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.

Indirect-Function-in-Excel-cell-INDIRECT-AVG.

Indirect-Function-in-Excel-cell-INDIRECT-TOTAL.

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.

Indirect-Function-in-Excel-cell-AVG-INDIRECT.

Indirect-Function-in-Excel-cell-TOTAL-INDIRECT.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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.

Indirect-Function-in-Excel-FINAL

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. 

About the Author

Ravikiran A SRavikiran A S

Ravikiran A S works with Simplilearn as a Research Analyst. He an enthusiastic geek always in the hunt to learn the latest technologies. He is proficient with Java Programming Language, Big Data, and powerful Big Data Frameworks like Apache Hadoop and Apache Spark.

View More

Find Business Analytics with Excel in these cities

  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.