3.4.
6 INDIRECT FUNCTION
The INDIRECT Function converts a text string into a cell reference, it means Excel INDIRECT can be
used to
indirectly reference cells, ranges, other sheets, or workbooks, that is why the name.
You can use this function to create a reference that will not change if row or columns are inserted in
the worksheet.
Or use it to create a reference from letters and numbers in other cells.
The INDIRECT function has two arguments: INDIRECT (ref_text,a1)
• ref_text: A cell reference or a text string (or both), that create a range reference to a cell, range of
cells or
named range.
• a1: (optional) Does the reference use A1 reference style?
o If TRUE or omitted, ref_text is interpreted as an A1-style cell reference.
o If FALSE, ref_text is treated as a R1C1 reference.
BASIC USE
You have number 500 in cell D1, and text D1 in cell A1. Now, put the formula =INDIRECT(A1) in any
other cell and
see what happens:
The INDIRECT function refers to the value in cell A1, which is D1.
The function is routed to cell D1 where it picks the value to return, which is number 500.
Figure 3.4.15 Indirect Function
INDIRECT FOR NAMED RANGES
You can refer to named ranges in an INDIRECT formula. As an example, the INDIRECT function is
used to sum the
selected named range.
In cells A1:D7, there are Customer data and B2:B7, C2:C7 & D2:D7 have been named as JAN, FEB &
MAR.
Now in cell F1, you type the name of one of the ranges, Eg. MAR
In cell F2, type the formula: =SUM(INDIRECT(F1))
Press the Enter key, and the formula returns the sum of numbers in the MAR range.
Change cell F1 to FEB, and the formula returns the sum of numbers in the FEB range.
EXCEL- MAKING DATA CONSISTENT
ADVANCED INFORMATION TECHNOLOGY 143
Thus, INDIRECT indirectly refers to the MAR range.
Figure 3.4.16 Indirect Named Ranges
4.4 OUTLINING DATA
4.4.1 GROUPING/ UNGROUPING DATA
Worksheets with complex information and large content can sometimes feel overwhelming and
even become difficult to read. Microsoft Excel provides an easy way to organize data in groups
allowing you to hide and show rows or columns with related content to create a compact and
understandable view. You can create an outline of up to eight levels. You can also summarize
different groups using the Subtotal command and create an outline for your worksheet.
Grouping in Excel is good for structured worksheets that have column headings, no blank rows or
columns, and a Subtotal row for each subset of rows. If your data is properly organized, you can
easily group it.
An Outline can be created manually or automatically.
Manual - You identify each group of related data.
Automatic - This is typically used when you have a table that contains totals and subtotals.
4.4.2 GROUPING DATA MANUALLY
If your worksheet contains two or more levels of information, you can group rows manually through
the following
steps.
• Select the rows or columns you want to group
• Select the Data tab on the Ribbon, then click the Group command.
• The selected rows or columns will be grouped.
• You can repeat the steps above as many times as you like.
ORGANISE DATA USING EXCEL
ADVANCED INFORMATION TECHNOLOGY 173
Figure 4.4.1 Grouping data
• To hide/collapse a group, click the Hide Detail button.
• The group will be hidden. To show a hidden group, click the Show Detail button.
4.4.3 AUTO GROUPING DATA (CREATE AN OUTLINE)
If your dataset contains Totals and subtotals ie that have been created out of functions Subtotal or
Sum, the
fastest way would be to let Excel group rows for you automatically.
• Select any cell in one of the rows you want to group.
• Go to the Data tab > Outline group,
• Click the arrow under Group,
• Select Auto Outline.
DATA ANALYSIS USING EXCEL AND CAAT TOOLS
174 ADVANCED INFORMATION TECHNOLOGY
Figure 4.4.2 Auto Outline Data
4.4.4 UNGROUPING DATA
To remove grouping for certain rows without deleting the whole outline.
• Select the rows you want to ungroup.
• Go to the Data tab > Outline group,
• Click the Ungroup button.
• In the Ungroup dialog box,
• Select Rows and click OK.
Figure 4.4.3 Ungroup Data
• The same process can be used to clear outline
CASE STUDY: Your principal CA Chandiwala gives you an excel sheet containing the Fixed Assets chart
as shown in Figure 4.4.4. He wants you to organise it in such a way that when you want to present or
print it, you could be a brief outline, but when you want to check details or audit it you can show the
details.
ORGANISE DATA USING EXCEL
ADVANCED INFORMATION TECHNOLOGY 175
Figure 4.4.4 Fixed assets schedule
Strategy:
You can use GROUP from the OUTLINE group on the Data Ribbon.
Since the data has Totals and Subtotals, you can easily use the Auto Outline feature of the group.
1. Select anywhere in the data.
2. Go to Data tab> Group >outline as shown in Figure 4.4.5
Figure 4.4.5 Auto Outline
3. Click on the Auto outline, based on Totals and subtotals Excel will automatically create subtotal as
shown in Figure 4.4.6.
DATA ANALYSIS USING EXCEL AND CAAT TOOLS
176 ADVANCED INFORMATION TECHNOLOGY
Figure 4.4.6 Grouped Data
4. If you look above and to the left of cell A1, you’ll see a series of three small numbers,1,2,3, Excel
has
created 3 level row grouping and grouping for columns as well.
5. You can expand or collapse depending on the need for the presentation.
Figure 4.4.7 Collapsed View
Gist: You have created a well organised Fixed asset list that you can expand or collapse depending on
the
need.
Commands learnt: Data > Group >Auto Outline
