Offset function is an information retrieval function. If you have a huge table containing certain data and you want to retrieve data from it, the best way to do it is insert an offset formula for the table in that worksheet or another sheet in the same workbook. Thus, once you have created the offset retrieval table, you simply have to enter the data for example ‘month’ and you will get the ‘sales’ or ‘income’ of that month from that table.
Going through the table is another option, but would you consider the option if you have sheets and sheets of data all containing a 1000 column data table? This is where offset is combined with other functions.
Excel Offset function
The concept of pivot table comes from offset. A pivot table is a general table and from that specific combination of data and graphs is obtained as and when required. Also dynamic tables can be created in the same manner. For these tables you will have to create the excel offset formula = OFFSET(reference point, row, column, height, width).
There are five arguments of the offset that need to be satisfied to get a error free information. If you write a incorrect formula, a Ref error occurs.
Example: Let us take a table that contains Name, email id, date of birth and age. The table begins at A1 cell, containing the ‘Name’ heading. The data runs for say A11. And the rows run till D1. The entire table runs A1:D11. You want a retrieval system that gets you the name when you enter the email id, or the email id when you enter the name along with other details. You create a formula by satisfying the 5 arguments.
Reference point is the cell from which the look up should start. In this case it should be A2. That is the general standard.
Rows – you ask excel to move the number of rows to get the info. In this case it has to move all around the table and so simply leave a blank denoted by a comma (,). Otherwise, to move a column forward give value as 1 and to move a column prior give a value -1.
Columns – this is to drive the function to the number of columns. The value system is same as that for row.
Height – the height of the table, in this case A11.
Width – width of the table, in this case D11.
After you set up the offset function, running a trial is important to weed out any errors.
Lookup function is same as offset but utilizes functions such as Match, Counta and IF to work a dynamic table.
Using offset in Excel requires skill and good knowledge of Excels functions and how they work together. Pulling it off without any error is the biggest hurdle.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.