Power-user advanced functions are new Excel functions added by the Power-user add-in, such as CAGR, SumColor, Weighted_Average, CountUnique or Vlookup_Max, that you use in your formulas like any native Excel function. They cover growth rates, sums by cell color, lookups, counting, text and workbook checks that Excel does not offer, or only with complex formulas.
In short
- How: type = in a cell and the name of the function, as for any Excel function
- Where to browse them: the Power-user category of the Excel Insert Function wizard
- What you get: 28 functions for calculations, colors, lookups, counting, text and workbook checks
- Sharing: the recipient of your workbook needs Power-user installed for the functions to work
In this article
How to use an advanced function in Excel
- In a cell, type = followed by the name of the function, for example =CAGR(.
- Enter its arguments as for any Excel function, then press Enter.
Using a Power-user advanced function in an Excel formula
To browse the functions and their arguments, open the Excel Insert Function wizard and choose the Power-user category.
Power-user category in the Excel Insert Function wizard
List of the Power-user advanced functions
Power-user advanced functions in Excel
Growth and calculations
| Function | What it does | Example |
|---|---|---|
| CAGR(ending_value, beginning_value, number_of_years) | Compound Annual Growth Rate between two values over a number of years. beginning_value cannot be zero. | CAGR(130,100,4) returns 7% |
| Percent_Change(beginning_value, ending_value) | Percentage change from one value to another. | Percent_Change(100,120) returns 20% |
| Weighted_Average(values, weights) | Average of the values of a column weighted by the values of another column. | With 10 and 20 in A1:A2 and weights 1 and 3 in B1:B2, Weighted_Average(A1:A2,B1:B2) returns 17.5 |
| RMS(array) | Root Mean Square (quadratic mean) of a set of data. | With 3 and 4 in A1:A2, RMS(A1:A2) returns 3.54 |
| SumFromAllSheets(reference_range, include_active_sheet) | Sum of the same cell or range in all the sheets of the workbook. Set include_active_sheet to TRUE to include the current sheet, FALSE to exclude it. | SumFromAllSheets(A1,FALSE) sums A1 in all sheets except the current one |
Sum and count by cell color
| Function | What it does | Example |
|---|---|---|
| SumColor(range, reference_color_cell) | Sum of the cells of a range that have the same fill color as the reference cell. | SumColor(A1:D10,A1) sums the cells of A1:D10 with the fill color of A1 |
| CountColor(range, reference_color_cell) | Number of cells of a range that have the same fill color as the reference cell. | CountColor(A1:D10,A1) counts the cells of A1:D10 with the fill color of A1 |
Lookups: maximum and minimum match
Unlike VLOOKUP and HLOOKUP, which return the first match, these functions return the maximum or minimum of all the results that match the lookup value.
| Function | What it does | Example |
|---|---|---|
| Vlookup_Max(lookup_value, table_array, column_number) | Maximum of all the results matching lookup_value, searched in the leftmost column of table_array. | With regions in column A and sales in column C, Vlookup_Max("North",A2:C20,3) returns the highest sale of North |
| Vlookup_Min(lookup_value, table_array, column_number) | Minimum of all the results matching lookup_value, searched in the leftmost column of table_array. | With regions in column A and sales in column C, Vlookup_Min("North",A2:C20,3) returns the lowest sale of North |
| Hlookup_Max(lookup_value, table_array, row_number) | Maximum of all the results matching lookup_value, searched in the top row of table_array. | With regions in row 1 and sales in row 3, Hlookup_Max("North",B1:M3,3) returns the highest sale of North |
| Hlookup_Min(lookup_value, table_array, row_number) | Minimum of all the results matching lookup_value, searched in the top row of table_array. | With regions in row 1 and sales in row 3, Hlookup_Min("North",B1:M3,3) returns the lowest sale of North |
Counting
| Function | What it does | Example |
|---|---|---|
| CountUnique(range) | Number of unique values in a range. | CountUnique(A1:A10) returns 8 if A1:A10 contains 8 unique values |
| CountVisible(range) | Number of visible cells in a range. | CountVisible(A1:A10) returns 5 if only half of the cells are visible |
| CountWords(range) | Number of words in a range that contains text. | CountWords(A1) returns 2 if A1 contains two words separated by a space |
Text
| Function | What it does | Example |
|---|---|---|
| Word_n(text, word_position) | The n-th word of a text. word_position is an integer. | Word_n("I really love using Power-user",3) returns "love" |
| Concatenate_Range(reference_range, separator) | Concatenation of all the cells of a range, with an optional separator (a text string) between cells. | With North, South and East in A1:A3, Concatenate_Range(A1:A3,", ") returns "North, South, East" |
Workbook and sheet information
Sheet, range, shape and slicer names are entered as text strings, between quotes.
| Function | What it does | Example |
|---|---|---|
| SheetExists(sheet_name) | TRUE if the active workbook has a sheet with this name, FALSE otherwise. | SheetExists("Sheet1") |
| RangeExists(sheet_name, range_name) | TRUE if the sheet contains a named range with this name, FALSE otherwise. Range names are listed in Formulas > Name Manager. | RangeExists("Sheet1","MyRange") returns TRUE if Sheet1 has a range named MyRange |
| ShapeExists(sheet_name, shape_name) | TRUE if the sheet contains a shape with this name, FALSE otherwise. Shape names are listed in the Selection Pane. | ShapeExists("Sheet1","Rectangle1") returns TRUE if Sheet1 has a shape named Rectangle1 |
| SheetName(reference_cell) | Name of the sheet that contains the reference cell. | SheetName(Sheet1!A1) returns "Sheet1" |
| LastCell(sheet_name) | Value of the last cell (bottom right) of the sheet. | LastCell("Sheet1") returns the value of $C$9 |
| LastCellAddress(sheet_name) | Address of the last cell (bottom right) of the sheet. | LastCellAddress("Sheet1") returns "$C$9" |
| LastColumnNumber(sheet_name) | Number of the last column of the sheet. | LastColumnNumber("Sheet1") returns 3 |
| LastRowNumber(sheet_name) | Number of the last row of the sheet. | LastRowNumber("Sheet1") returns 9 |
| UsedRange(sheet_name) | Address of the range used in the sheet. | UsedRange("Sheet1") returns A1:D10 if no data is below row 10 or right of column D |
| HasFormulas(reference_cell) | TRUE if the cell contains a formula, FALSE if it contains a value or is empty. | HasFormulas(A1) |
| IsMerged(reference_cell) | TRUE if the cell is merged with other cells, FALSE otherwise. | IsMerged(A1) |
| SlicerItems(slicer_name) | List of the items selected in a slicer, separated by commas. The slicer name is shown in Slicer Settings, under Name to use in formulas. | SlicerItems("Slicer_Field1") |
Sharing a workbook that uses advanced functions
Advanced functions need Power-user to run. If you send a workbook that uses them to someone who does not have Power-user installed, the functions return an error.
That is why the advanced functions are included in the Power-user Free Forever license: the recipient can install Power-user without buying a license and still read, edit or add advanced functions. See Installing Power-user.
Frequently asked questions about Power-user advanced Excel functions
How do I calculate a CAGR in Excel?
With the Power-user add-in, use the CAGR function: =CAGR(ending_value, beginning_value, number_of_years). For example, =CAGR(130,100,4) returns 7%.
How do I sum cells by color in Excel?
Excel has no native function to sum by color. With Power-user, use =SumColor(range, reference_color_cell): it sums the cells of the range that have the same fill color as the reference cell. CountColor counts them.
How do I calculate a weighted average in Excel?
With Power-user, use =Weighted_Average(values, weights): it returns the average of the values weighted by the values of the second range.
How do I count unique values in Excel?
With Power-user, use =CountUnique(range): it returns the number of unique values in the range.
How do I get the maximum of all matches of a VLOOKUP?
With Power-user, use =Vlookup_Max(lookup_value, table_array, column_number): it returns the maximum of all the results that match the lookup value. Vlookup_Min returns the minimum.
Do Power-user functions work if the recipient does not have Power-user?
No. Power-user advanced functions return an error if Power-user is not installed. The recipient can install Power-user with the Free Forever license, which includes the advanced functions.
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article