Advanced Excel functions

Modified on Thu, 11 May 2023 at 11:38 AM

Power-user includes additional new functions that can be used in Excel to unlock the full potential of your spreadsheets.

Power-user l Advanced functions in Excel

TABLE OF CONTENTS

Using an advanced functions
List of Power-user's advanced functions
Compatibility considerations

Using an advanced functions

Advanced functions can be used in your formulas like any Excel function. Just type the "=" sign in a cell to start using these functions.

Power-user l Advanced functions in Excel

List of Power-user's advanced functions

The table below lists all advanced functions that can be used in your Excel formulas:

Function	Description	Example	Argument
RangeExists	Checks if the named range exists in the defined sheet	RangeExists("MyRange") returns the value TRUE if the defined sheet has a range named 'MyRange' and returns FALSE if not	sheet_name: Enter the name of the sheet that should contain the desired range. sheet_name should be provided as a text string. range_name: Enter the name of the range you are looking for. range_name should be provided as a text string, matching a named range. Range names can be found under Formulas / Name Manager.
ShapeExists	Checks if the named shape exists in the defined sheet	ShapeExists("Rectangle1") returns the value TRUE if the defined sheet has a shape named "Rectangle1" and returns FALSE if not	sheet_name: Enter the name of the sheet that should contain the desired shape. sheet_name should be provided as a text string. shape_name: Enter the name of the shape you are looking for. shape_name should be provided as a text string, matching a named range. Shape names can be found under Format / Selection Pane.
SheetExists	Checks if the named shape sheet exists in the active workbook	SheetExists("Sheet1") returns TRUE if the active workbook has a sheet named "Sheet1" and returns FALSE if not	sheet_name: Enter the name of the sheet that you are looking for. sheet_name should be provided as a text string.
LastCell	Returns the value of the last cell (bottom right) in the defined sheet	LastCell("Sheet1") returns the value contained in cell $C$9	sheet_name: Enter the name of the sheet of which you want to extract the last cell value. sheet_name should be provided as a text string.
LastCellAddress	Returns the address of the last cell (bottom right) in the defined sheet	LastCellAddress("Sheet1") returns the value "$C$9"	sheet_name: Enter the name of the sheet of which you want to extract the last cell address. sheet_name should be provided as a text string.
LastColumnNumber	Returns the number of the last column (bottom right) in the defined sheet	LastColumnNumber("Sheet1") returns the value "3"	sheet_name: Enter the name of the sheet of which you want to extract the last column number. sheet_name should be provided as a text string.
LastRowNumber	Returns the number of the last row (bottom right) in the defined sheet	LastRowNumber("Sheet1") returns the value "9"	sheet_name: Enter the name of the sheet of which you want to extract the last row number. sheet_name should be provided as a text string.
SheetName	Returns the name of the sheet that contains the reference cell.	SheetName(Sheet1!A1) returns the value "Sheet1"	reference_cell: Provide the reference of any cell in the sheet of which you want to extract the name.
HasFormulas	Checks if the reference cell has formulas.	HasFormulas(A1) returns TRUE if there is a formula in A1 and FALSE if it contains values or blank	reference_cell: Provide the reference of the cell for which you want to check if it contains formulas.
IsMerged	Checks if the reference cell is merged with other cells.	IsMerged(A1) returns TRUE if A1 is merged with another cell and FALSE if not	reference_cell: Provide the reference of the cell for which you want to check if it is merged with others.
SumColor	Returns the sum of cells in the selected range that have the same fill color as the reference cell.	SumColor(A1:D10,A1) returns the sum of the cells in A1:D10 that have the same fill color as A1	range: Provide the reference of the range that contains the values you want to sum. reference_color_cell: Provide the reference of a cell that has the fill color you want to use as a condition to sum numbers.
CountColor	Returns the number of cells in the selected range that have the same fill color as the reference cell.	CountColor(A1:D10,A1) returns the number of the cells in A1:D10 that have the same fill color as A1	range: Provide the reference of the range that contains the values you want to count. reference_color_cell: Provide the reference of a cell that has the fill color you want to use as a condition to count the data.
SumFromAllSheets	Returns the sum of the values in the reference cell for all sheets in the active workbook.	SumFromAllSheets(A1,TRUE) returns the sum of the values in A1 for all sheets of the workbook while SumFromAllSheets(A1,FALSE) returns the sum of the values in A1 for all sheets of the workbook except the current sheet	reference_range: Provide the reference of the range that contains the data you want to sum in all sheets of the workbook. include_active_sheet: Type TRUE if the sum should include the current sheet and FALSE to exclude it from the sum.
CountUnique	Returns the number of unique values in the reference range.	CountUnique(A1:A10) returns 8 if there are 8 unique values in the range A1:A10	range: Provide the reference of the range that contains the data where you want to count the number of unique values.
CountVisible	Returns the number of visible cells in the reference range.	CountVisible(A1:A10) returns 5 if only half the cells are currently visible in the range A1:A10	range: Provide the reference of the range that contains the data where you want to count the number of visible values.
UsedRange	Returns the address of the range that is being used in the defined sheet.	UsedRange("Sheet1") returns A1:D10 if there is no data in Sheet1 that is below the 10th row or further right than the D column	sheet_name: Enter the name of the sheet of which you want to extract the used range address. sheet_name should be provided as a text string.
CountWords	Returns the number or words in a range that contains text.	CountWords(A1) returns 2 if there are 2 different words separated by a space in A1	range: Provide the reference of the range that contains the text of which you want to extract the number of words.
SlicerItems	Returns the list of items selected in the defined slicer.	SlicerItems("Slicer_Field1") returns the list of all items selected in the slicer 'Slicer_Field1', separated by a comma	slicer_name: Enter the name of the slicer of which you want to extract the selected items. slicer_name should be provided as a text string. To find a slicer's name, right-click it, click 'Slicer Settings' and look for 'Name to use in formulas'.
Vlookup_Max	Returns the maximum value of all results that match lookup value.		lookup_value: Provide the reference of the cell containing the 'key' for which you are looking to the maximum of corresponding values. table_array: Provide the reference of the range containing the lookup_value in the leftmost column and the expected result in any other column. column_number: Provide the number of the column that contains the expected result.
Vlookup_Min	Returns the minimum value of all results that match lookup value.		lookup_value: Provide the reference of the cell containing the 'key' for which you are looking to the minimum of corresponding values. table_array: Provide the reference of the range containing the lookup_value in the leftmost column and the expected result in any other column. column_number: Provide the number of the column that contains the expected result.
Hlookup_Max	Returns the maximum value of all results that match lookup value.		lookup_value: Provide the reference of the cell containing the 'key' for which you are looking to the maximum of corresponding values. table_array: Provide the reference of the range containing the lookup_value in the topmost row and the expected result in any other row. row_number: Provide the number of the row that contains the expected result.
Hlookup_Min	Returns the minimum value of all results that match lookup value.		lookup_value: Provide the reference of the cell containing the 'key' for which you are looking to the minimum of corresponding values. table_array: Provide the reference of the range containing the lookup_value in the topmost row and the expected result in any other row. row_number: Provide the number of the row that contains the expected result.
CAGR	Returns the Compound Annual Growth Rate between 2 values for a given number of years.	CAGR(130,100,4) returns 7%	ending_value: Provide the value of the series at the end of the period considered for the CAGR. beginning_value: Provide the value of the series at the beginnig of the period considered for the CAGR. beginning_value cannot be null. number_of_years: Provide the number of years on which the CAGR is performed, i.e. the number of years separating the ending_value from the beginning_value.
Word_n	Returns the n-th word from a text.	Word_n("I really love using Power-user",3) returns "love"	text: Text or reference from which you want to extract the n-th word. Word_position: Provide the position of the word you want to extract. The value should be an integer.
Concatenate_Range	Returns the concatenation of all cells within the defined range.		Reference_Range: Provide the range from which you want to concatenate all the cells. Separator: Optionnally, add a a separator between each cell that is concatenated. The separator is a string of characters.
Weighted_Average	Returns the average of values in a column weighted by the values in another column.		Values: Provide the range containing the values you are calculating the average of. Weights: Provide the range containing the weights for the average.
Percent_Change	Returns the percentage that changes one value into another.	Percent_Change(100,120) returns 20%	beginning_value: initial value, before the percent change. ending_value: new value after the percent change is applied.
RMS	Returns the Root Mean Square of a set of data.		Array: array of data to which the RMS formula is applied.

These functions can also be found in the Insert Function wizard in Excel, under the Power-user category:

Power-user l Excel Advanced functions wizard

Compatibility considerations

Advanced functions require the Power-user code to run. This means that if you use some advanced functions in a workbook, and send this workbook to a user who does not have Power-user installed, the functions will not work and return an error.

To mitigate this, we have included the advanced functions in our Free Forever license. This means that the recipient of your workbook can install Power-user without having to buy a license, and still read, edit or even add new advanced functions thanks to this Free Forever license.