How to use Excel OFFSET function

Introduction OFFSET function in excel is a reference function. This function is used to refer data from a cell or range of cells to a defined range. The number of rows and columns to be returned is also specified. Reference returned by offset function is in parts: start point, row and column offset and height and width in rows and columns. It is also used with function which use dynamic calculation values. Syntax  =OFFSET (Reference, Rows, Cols, Height, Width) Argument…

Continue Reading

How to use Excel LOOKUP function

Introduction LOOKUP function in excel is a lookup and reference function. This function is used to lookup or approximately match data in single column or single row and retrieve value from corresponding column or row. This function has two forms, one is called vector and the other one is called array form. Vector form Syntax  =LOOKUP(Lookup_Value, Lookup_Vector, [Result_Vector]) Vector form Argument Lookup_Value: Value that will be searched in the first vector. Can be text, number, reference etc.Lookup_Vector: It is the…

Continue Reading

How to use Excel TRANSPOSE function

Introduction TRANSPOSE function in excel is a lookup/reference function. This function is used to rotate or flip or switch cells and range of cells without duplicating the data. When copy and pasting of data is done, it leads to duplication of data, by using TRANSPOSE function this problem can be avoided. Syntax  =TRANSPOSE(array) Argument Array:  It is an array or a range of cells that will be transposed. A vertical range is converted into horizontal range and a horizontal range…

Continue Reading

How to use Excel GETPIVOTDATA function

Introduction GETPIVOTDATA function in excel is a lookup function. This function is used to return specific data from a pivot table. It uses pivot table structure instead of reference cell reference for retrieving data. Syntax  =GETPIVOTDATA (Data_Field, Pivot_Table, [field1,item1]) Argument Data_Field: It is the name of the field entered in quotation marks which contains the data to be returned.Pivot_Table:  It is the reference made to the cell in pivot table which has the data to be returned.Field_Item: These are pairs…

Continue Reading

How to use Excel FORMULATEXT function

Introduction FORMULATEXT function in excel is a Math/Trig function. It is a worksheet function. This function is used to retrieve a formula from a reference in form of a text string. Syntax =FORMULATEXT(Reference) Arguments Reference- t is the reference made to a range or cell. Keynotes Reference argument can be from other worksheet also.If the other worksheet to which reference is made is not open, #N/A error is displayed.If the reference is made two an entire range, the value in…

Continue Reading

How to use Excel MATCH function

Introduction MATCH function in excel is a lookup function. It helps in locating cell number corresponding to a lookup value of a row, column or table. It does this by searching for specific value in a range of cells and returns position of value. For Example, if the range A3:A6 have the values 10, 15, 20 respectively then function =MATCH(20,A3:A6,0) gives value 3 as 20 is 3rd in the range. It is also used commonly with the INDEX function. Syntax…

Continue Reading

How to use Excel HYPERLINK function

Introduction HYPERLINK function in excel a shortcut function that creates a shortcut in the cell in which it is used. This shortcut may contain a reference to another cell of the same worksheet or another worksheet OR may contain a link to a network, internet, web, and intranet. When one clicks on the cell containing that hyperlink, excel opens the link provided in the function. Syntax =HYPERLINK(Link_location, Friendly_name) Arguments Link_Location- It is name of the file or its path which…

Continue Reading

How to use Excel ROWS function

Introduction ROWS function in excel is a lookup or reference function. It provides us with number of rows in an array or reference. We have to enter a range in Excel format and it returns number of rows present in that range. For example row A1:A6 gives 6 as the result.     Syntax =ROWS(Array) Arguments array – It can be the range of cells or an array formula, or an array.                                                  Keynotes A name range may also be used…

Continue Reading

How to use Excel ROW function

Introduction ROW function in excel is a reference function. It returns the row number of a cell to which reference is made. If we provide no reference than function returns the reference value of the cell in which the function is written. For example =ROW(A9) returns 9 as A9 is the 9th row in Excel. Syntax =ROW(Reference) Arguments Reference- It is the reference made to a cell or a range of cells whose row number has to be found. It…

Continue Reading

How to use Excel INDIRECT function

Introduction INDIRECT function in excel is a reference function. It is used to change a text string to a valid reference in formula. It can be used when a reference in form of text need to be changed into a valid reference. This reference does not change when rows and columns are added into the sheet.  Syntax =INDIRECT(ref_text, a1 ) Arguments Refer_text:- It is the reference to a cell as a text string, a name as a reference, A1 style…

Continue Reading
Close Menu
×

Cart