GETPIVOTDATA function in Excel

Hello all. Another short section regarding getpivotdata function in excel. Getpivotdata function uses / queries the existing Pivot table and returns data based on specified conditions as arguments. Syntax =getpivotdata(data_field, pivot_table, [field1,item1],..) Here, data_field is the name of the value field to be queried. Pivot_table is the reference to any cell in the pivot table.Continue reading “GETPIVOTDATA function in Excel”

FormulaText function in Excel

Hello all. In this short section, let’s cover the formulatext function in excel. Formulatext field would return the formula used in the reference cell as a text value. Syntax =Formulatext(ref) Here, ref is any cell reference. Working Example In the below example, I have used an offset function to return the ops metrics for FebContinue reading “FormulaText function in Excel”

Match function in Excel

Hello all. Let’s look at the MATCH function in excel. Match function will return the position of the lookup value as numerical value. Match function supports approximate and exact matches (in the form of optional argument). Syntax =Match(lookup_value, lookup_array, [match_type]) Working Examples: In the below data set, we have the lookup value as the nameContinue reading “Match function in Excel”

Sortby function in Excel

Hello all. In this section, let’s look at the “sortby” function in excel. Unlike the sort function, sortby sorts the values in a range or array based on value in another list or array. Please note the range or array, that is used to sort, doesn’t necessarily need to appear in the output. Syntax =sortby(array,Continue reading “Sortby function in Excel”

Sort function in Excel

Hello all. In this section let’s look at SORT function in excel. Sort function, as the name says, sorts the content of a range or an array based on the optional arguments we specify. Values, using this function, can be sorted by rows or columns and the function returns dynamic array. Syntax =SORT(array, [sort_index], [sort_order],Continue reading “Sort function in Excel”

Choose function in Excel

Hello all. In this section let’s explore CHOOSE function in excel. CHOOSE function helps to choose a value from list of values based on specified index number. Syntax: =choose(index_num, value1, [value2],..) Let’s start off with a simple example. In the below sheet, without any base data, we just use choose function to return 3rd valueContinue reading “Choose function in Excel”

Index function in Excel

Hello all. In this section, let’s explore the Index function. Index is a commonly used function that returns the value of cell at the intersection of specified row and column. In a given range if row and column number is provided, the function returns the value at the intersection. Syntax: =Index(array,row_num,[column_num]) =Index(reference, row_num, [column_num], [area_num])Continue reading “Index function in Excel”

Hlookup in Excel

Hello all. Let’s look at Hlookup function. Hlookup is a function that looks up the specified value in horizontally sorted values and returns value from specific row in the table. Syntax =hlookup(lookup_value, table_array, row_index_num, [range_lookup]) Here are certain points to note before looking at examples: Lookup value must always be located in the first rowContinue reading “Hlookup in Excel”

Vlookup in Excel

Hello all. Let’s look at one of most commonly used functions in excel – Vlookup. Vlookup function lookup for a specified data in a selected table / range and returns the match. Syntax =vlookup(lookup_value, table_array, col_index_num, [range_lookup]) There are certain conditions while using vlookup: Lookup value should always be the first column of the tableContinue reading “Vlookup in Excel”

Indirect Function in Excel

Hello all. Let’s look at Indirect function. Indirect function would return a reference (or reference range) from a specified string. Syntax: =Indirect(ref_text, [a1]) Let’s look at some examples. In the below example, we have one value in each worksheet (sheet1,2&3). Let’s return each of them using the indirect function Next, let’s create a named rangeContinue reading “Indirect Function in Excel”