Tutorial Playlist
68 Lessons1. Excel Tutorial
2. Excel Worksheet for Practice
3. Google Sheets Vs. Excel
4. Excel Shortcut Keys
5. Excel Keyboard Shortcuts
6. Excel AutoSum Shortcut
7. Redo Shortcut in Excel
8. Charts in Excel
9. Pivot Charts in Excel
10. Excel Sum Formula
11. Excel Percentage Formula
12. Excel Age Calculation Formula
13. Excel Range Formula
14. Round Off Formula in Excel
15. VLOOKUP Formula in Excel
16. Excel Transpose Formula
17. Average Equation in Excel
18. How to Use DATEDIF Formula in Excel
19. IRR Formula in Excel
20. Standard Deviation Formula
21. Excel Age Calculation Formula
22. Excel MAX Function
23. Excel LEFT Function
24. Excel RIGHT Function
25. Trim Function In Excel
26. LookUp function in Excel
27. Columns in Excel
28. How To Sort Data In Excel
29. How To Freeze Panes in Excel
30. Page Setup in Excel
31. How to Recover an Unsaved Excel File
32. Concatenate in Excel
33. Count In Excel
34. IF Condition In Excel
35. H LOOK UP in Excel
36. How Do You Move Columns in Excel
37. Split Cells in Excel
38. Remove Blank Rows in Excel
39. How To Lock Cells in Excel
40. Data Validation in Excel
41. How to Insert Checkbox in Excel
42. How To Highlight Duplicates in Excel
43. Fill Series in Excel
44. How to Create Excel Drop-Down List
45. What-If Analysis in Excel
46. How to Use SUMIFS Function in Excel
47. INDIRECT Function in Excel
Now Reading
48. Pivot Table in Excel
49. Slicers in Excel
50. How to Create a Dashboard in Excel
51. Excel Data Cleaning
52. Data Analysis In Excel
53. Goal Seek in Excel
54. Solver in Excel
55. Power Query in Excel
56. Macros In Excel
57. How To Make Graph in Excel
58. How To Make Histogram In Excel
59. How To Convert PDF to Excel Without Software
60. Barcode Font For Excel
61. Gantt Chart in Excel
62. Excel RANK function
63. AND Function in Excel
64. How to Calculate NPV
65. Format Painter in Excel
66. Count Colored Cells in Excel
67. Amortization Schedule in Excel
68. Master Cell References in Excel
Did you ever waste your time trying to create the perfect formula and feel content, only for it to collapse the moment a new column appeared?
I did. At that instant, all the references I so laboriously created pointed to something that did not exist. I started to feel completely overwhelmed by all the data. Then I found out about the INDIRECT function in Excel!
I could manage a large spreadsheet recording sales levels of numerous products across several regions with a click. There were also separate columns containing sales data for each region.
Want to build your sheet, too, like I did? Explore the detailed process here!
Picture this. You need to point to data in various cells or sheets. You'd usually type the cell ref like A1 or B23. But what if you want this point to be more changeable?
This is where the INDIRECT in Excel comes in handy! The tool takes a text, like "B12," and makes it a real cell reference that Excel can process.
The product? You can make cool sums that can shift based on other cell info.
But how would you use this? Say you're making a cash tracker. In one cell, you write "Income," and in another, "Rent." Then, with INDIRECT, you can make a sum that takes the amount from "Income" and cuts the amount from "Rent." Simple.
This is exactly what the INDIRECT function in Excel does. An online Excel course with certification can help you master this program quickly.
The INDIRECT function in Excel is a handy tool for making your formula change references independently. Here's the formula:
=INDIRECT(ref_text, [a1])
where
Let's try it out!
Let’s say you've got a table with titles at the top in row 1 and data below that. In cell B2, you want to show the number from the cell in row 2 under the "Sales" title.
Here's how to use INDIRECT function in Excel:
Step 1. In cell B2, punch in this formula:
=INDIRECT(A2&"2")
where
Step 2. Hit Enter.
Cell B2 will now show you the data from the second row that's under "Sales."
Here's a quick look at what the INDIRECT function does:
Let’s look at how you can use INDIRECT easily.
Step 1: Grasping the rule
First, know the intricacies. As an INDIRECT rule, you will find the ref_text, which is a cell pointing to the text describing the cell you want to point to (for example, A1 pointing to "B3").
You can also find the actual text in quotes (like "B3"). You'll also find [a1], which is optional. (This sets the reference style (A1—default or R1C1).) For now, let's go with A1.
Step 2: Implementing it
Consider you have product names in column A and their prices in column B. In cell C2, you want to show the product price mentioned in cell A2. Here's the real use of INDIRECT function in Excel:
1. In cell C2, put the formula:
=INDIRECT(A2&"1")
2. Hit Enter.
The result?
Cell C2 will now display the price corresponding to the product name in cell A2. Cool, right? For more such skills, read up on an Excel tutorial specially crafted for beginners.
Multiple examples exist outlining the versatility of this formula:
You got sales info for different months on sheets named "Jan," "Feb," etc. You aim for a formula in a main sheet to add up the total sales for any month.
Source: MS Excel
Step 1. In A1 (say), write the month's name to analyze (e.g., "Feb").
Step 2: In a different cell like B1, write this formula: =SUM(INDIRECT(A1&"!B:B"))
Result: When you change the month name in A1 (e.g., to "Mar"), the formula in B1 automatically updates to reference column B in the "Mar" sheet, giving you the total sales for March.
You have a product list on a separate sheet ("Products") with product names in column A and prices in column B. In another sheet ("Sales"), you want to use VLOOKUP to find the price of a product based on its name listed in the "Sales" sheet.
However, the sheet name "Products" might change in the future.
Source: MS Excel
Step 1: In the "Sales" sheet, let's say cell A1 contains the product name you want the price for (e.g., "Product 1").
Step 2: In another cell (e.g., B1), enter the following formula:
=VLOOKUP(A1,INDIRECT("Products"!A:B),2,FALSE)
Result: When you enter a product name in cell A1 (e.g., "Product 1"), the VLOOKUP formula uses INDIRECT to reference the correct sheet ("Products"). It then searches for the product name in column A and returns the corresponding price from column B (10 in this case).
You're keeping track of sales in different sheets for regions like North, South, etc. You want a formula in a main sheet to get the total sales for any chosen area.
Source: MS Excel
Step 1: Type the area name in A1, for example, "North". This tells the formula which sheet to focus on.
Step 2: In another cell, say B1, use this formula:
=SUM(INDIRECT(A1&"!B:B"))
Note: If you change the area name in A1 to "South", the formula in B1 updates to refer to column B in the "South" sheet, giving you the total sales for the South region.
You track expenses by category (Rent, Food) on separate sheets. You want a formula to find the total spent in any chosen category without manually typing the sheet name.
Source: MS Excel
Step 1. List categories in column A (e.g., A1: Rent, A2: Food).
Step 2. Create a dropdown list in B1 referencing A1:A2 (Data tab > Data Validation > Allow: List, Source: =A1:A2).
Step 3. In cell C1, enter the formula:
=SUM(INDIRECT(B1&"!B:B"))
You have a table with things in column A and costs in column B. You want a way in cell C2 to show the cost for the thing in cell A2. But you don't want it to change if you add new rows or columns.
Source: MS Excel
Step 1. Enter product names in A (e.g., A1: Apple, A2: Banana) and prices in B (e.g., B1: $1, B2: $2).
Step 2. Enter the formula with INDIRECT (Cell C2):
=INDIRECT("B"&A2)
You've got a list with item names in A and their costs in B. You need a way to put a rule in box C1 that will always show the cost of the item in box A1, no matter where you drag or copy that rule.
Source: MS Excel
Step 1. In box A1, put the item's name (like "Apples").
Step 2. In box C1, write down the rule:
=INDIRECT("B1")
Result: This establishes a steady link to box B1, no matter where you shift the rule.
You have a table with product names in A and sales figures in B. You want to make your named range more adaptable.
Source: MS Excel
Step 1. In column A, list different products while specifying their values in column B.
Step 2. Name this range “SalesData” covering B2 to B10, where the sales figures range.
Step 3. In another area, such as cell C1, input this formula:
=SUM(INDIRECT("SalesData"))
Result: It gives flexibility to your formula since you keep adding more data rows and expanding the named range, such as SalesData:B2:B15; everything will be calculated, including new data in the C1 formula.
Despite its cool functionality INDIRECT, too, has its limitations.
So here it is! You're all set to use the INDIRECT tool like an expert, making changeable links or even tricky fixed ones that don't move.
Want to boost your Excel game even more? Consider learning more with hands-on training from upGrad. They offer numerous certifications, beating complex equations, data study, and beyond. Go look at their site to find a course that suits you!
Till then, keep digging into Excel's magic and be surprised with what you can do with some fresh ideas and the perfect method. Enjoy your time with those spreadsheets!
The INDIRECT function changes the text to a cell link. Suppose you have items in A1:A10 and costs in B1:B10. In C1, put =INDIRECT("A1") to get the item name in A1, and bring the rule down to see names in A2, A3, and so on.
There is no "indirect match" rule. Yet, you can mix INDIRECT with MATCH for strong searches. Suppose you have a set of codes in A1:A10, and paired values in B1:B10. In C2, put =INDIRECT(MATCH("codeX",A:A,0)) to find the price linked with "codeX" (swap "codeX" with your real code).
INDIRECT doesn't work right with filters. But, you can use it for live rules for filters. For example, if A1 has "North," use =INDIRECT("A1") in your filter rules to only show lines where a region column fits "North."
Like filters, INDIRECT isn't used right with COUNTIF. However you can use it for live ranges for tallying. Suppose your info begins in A2. Use =COUNTIF(INDIRECT("A2:A"&A1)) to count things from A2 to the line number in A1 (which can switch with user picks).
INDIRECT gets a text string as input. This string can be a cell tag itself (like "A1") or put right within quotes (like "B3"). INDIRECT then reads that text as a cell spot and returns the value or tag it holds.
Excel doesn't have a direct function. But, INDIRECT is seen as an indirect link as it uses text to find the cell spot. On the other hand, a direct link just uses the cell tag itself (like A1, B23).
Devesh
Passionate about Transforming Data into Actionable Insights through Analytics, with over 3+ years of experience working in Data Analytics, Data V…Read More
Talk to our experts. We’re available 24/7.
Indian Nationals
1800 210 2020
Foreign Nationals
+918045604032
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enrolling. upGrad does not make any representations regarding the recognition or equivalence of the credits or credentials awarded, unless otherwise expressly stated. Success depends on individual qualifications, experience, and efforts in seeking employment.
upGrad does not grant credit; credits are granted, accepted or transferred at the sole discretion of the relevant educational institution offering the diploma or degree. We advise you to enquire further regarding the suitability of this program for your academic, professional requirements and job prospects before enr...