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


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:

 

FunctionDescriptionExampleArgument
RangeExistsChecks if the named range exists in the defined sheetRangeExists("MyRange") returns the value TRUE if the defined sheet has a range named 'MyRange' and returns FALSE if notsheet_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.
ShapeExistsChecks if the named shape exists in the defined sheetShapeExists("Rectangle1") returns the value TRUE if the defined sheet has a shape named "Rectangle1" and returns FALSE if notsheet_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.
SheetExistsChecks if the named shape sheet exists in the active workbookSheetExists("Sheet1") returns TRUE if the active workbook has a sheet named "Sheet1" and returns FALSE if notsheet_name: Enter the name of the sheet that you are looking for. sheet_name should be provided as a text string.
LastCellReturns the value of the last cell (bottom right) in the defined sheetLastCell("Sheet1") returns the value contained in cell $C$9sheet_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.
LastCellAddressReturns the address of the last cell (bottom right) in the defined sheetLastCellAddress("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.
LastColumnNumberReturns the number of the last column (bottom right) in the defined sheetLastColumnNumber("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.
LastRowNumberReturns the number of the last row (bottom right) in the defined sheetLastRowNumber("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.
SheetNameReturns 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.
HasFormulasChecks if the reference cell has formulas.HasFormulas(A1) returns TRUE if there is a formula in A1 and FALSE if it contains values or blankreference_cell: Provide the reference of the cell for which you want to check if it contains formulas.
IsMergedChecks if the reference cell is merged with other cells.IsMerged(A1) returns TRUE if A1 is merged with another cell and FALSE if notreference_cell: Provide the reference of the cell for which you want to check if it is merged with others.
SumColorReturns 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 A1range: 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.
CountColorReturns 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 A1range: 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.
SumFromAllSheetsReturns 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 sheetreference_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.
CountUniqueReturns the number of unique values in the reference range.CountUnique(A1:A10) returns 8 if there are 8 unique values in the range A1:A10range: Provide the reference of the range that contains the data where you want to count the number of unique values.
CountVisibleReturns 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:A10range: Provide the reference of the range that contains the data where you want to count the number of visible values.
UsedRangeReturns 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 columnsheet_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.
CountWordsReturns 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 A1range: Provide the reference of the range that contains the text of which you want to extract the number of words.
SlicerItemsReturns 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 commaslicer_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_MaxReturns 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_MinReturns 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_MaxReturns 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_MinReturns 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.
CAGRReturns 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_nReturns 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_RangeReturns 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_AverageReturns 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_ChangeReturns 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.
RMSReturns 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.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article