Presentation on Offset & Indirect Functions of Excel

A small and simple presentation on offset and indirect function in excel.

1. Technical Presentation 1 Revanth Kumar Matta Offset Function Indirect Function Topics

2. Offset Function With v-lookup & index functions we will get the result for only 1-cell; However, if we want answer for multiple cells or a range, then we can use offset function. Offset Formula: =OFFSET(reference, rows, columns,[height],[width]) Example: If we want the total the total of Q3 or total of sales for West Region, we can find them by using the above formula along with sum function. Limitation : This is a volatile formula, if there are multiple offset functions entered in a file then the file performance will be slower as it will calculate the formula for every action on the file. Revanth Kumar Matta

3. Indirect Function If we are having a set of data/range, then we can use the indirect function to create a list of that data. Formula for dependent cell: =Indirect( reference_cell ) Example: List(Header) Dependent List(depends on the 1 st list) Steps for dependent list: Make a name range of the relevant data. Create a list using the data source Create a dependent dropdown/list based on first list using indirect function. Revanth Kumar Matta