11 lesser-known but useful Excel functions ( P2 )

11 lesser-known but useful Excel functions: Microsoft Excel provides hundreds of functions, so there are definitely a few that you may not be aware of. These unique functions have specific purposes that you’ll be excited to learn and use.

Address to locate a cell

Switch to the lookup function in Excel. To find the exact address of a cell, you can use the ADDRESS function. This is convenient when you want an error-free reference to a cell.

The syntax is ADDRESS(row, column, type, style, name), where only the first two arguments are required. Enter the row number for the first argument and the column number for the second argument.

Optional arguments are as follows:

• Type: The type of reference to return. Use blank or 1 for absolute row, 2 for absolute row and relative column, 3 for relative row and absolute column, or 4 for relative row.
• Style: Use TRUE for A1 style or FALSE for R1C1 style of cell reference.
• Name: The name of the worksheet for an external reference. If left blank, Excel assumes the current active worksheet.

To find the address of the cell in row 2, column 3, you use the following formula:

=ADDRESS(2,3)

To find the address of the same cell using absolute row and relative column, you would use the following formula:

=ADDRESS(2,3,2)

To find the address of the same cell in a worksheet named “Sheet2,” use the following formula. Note that the comma represents the empty “type” and “style” arguments.

=ADDRESS(2,3,,,"Sheet2")

PI for the value of Pi

If you need to use the value of pi in your spreadsheet equations, you can retrieve that value using the PI function.

The simple syntax is PI(), with no arguments. You can add more elements to the formula if you want to use the value in a calculation.

To return the value of pi, simply use the formula of the function, including the parentheses:

=PI()

To multiply the value of pi by 10, you would use the following formula:

=PI()*10

ARABIC and ROMAN for number conversion

Another useful mathematical function is converting to and from Arabic and Roman numerals.

The syntax for each is ARABIC(text) and ROMAN(value, form), where the first argument is required for both.

The optional argument for the ROMAN function specifies the type of Roman numerals from Classic to Simplified. Enter 0, TRUE, or omit the argument for Classic. Use 1, 2, or 3 for shorter results. Or, enter 4 or FALSE for Simplified.

To convert the Roman numeral “MMIM” to an Arabic number, use this formula, making sure to enclose the text in double quotation marks:

=ARABIC("MMIM")

To convert 2,999 to a Roman numeral, you would use the following formula:

=ROMAN(2999)

To convert the same number in Simplified form, use one of the following formulas:

=ROMAN(2999,4)

=ROMAN(2999,FALSE)

REPT to repeat text

If you want to add a series of characters, symbols, or text as a placeholder or to create a visual effect, use the REPT text function.

The syntax is REPT(text, number), where both arguments are required. Enter the text argument within double quotation marks and then the number of times to repeat that text.

To repeat “Excel” 10 times in a cell, you would use the following formula:

=REPT("Excel",10)

To repeat the asterisk symbol 20 times, you would use the following formula:

=REPT("*",20)

11 lesser-known but useful Excel functions ( P2 )

We have covered many Excel functions at How-To Geek, attempting to guide you on using the most popular options. Hopefully, these lesser-known functions are what you need for mathematics, engineering, statistics, lookup, or text data.