This article introduces 20 lesser-known but powerful functions to solve unique problems or improve your productivity.
1. CELL
• Usage: Returns information about a cell, such as its format, location, or contents.
• Example: =CELL("address", A1) returns the address of cell A1.
2. OFFSET
• Usage: Creates a dynamic range by offsetting from a reference cell.
• Example: =OFFSET(A1, 2, 3) returns the value two rows down and three columns to the right of A1.
3. EDATE
• Usage: Calculates a date a specified number of months before or after a given date.
• Example: =EDATE(A1, 3) returns the date three months after the date in A1.
4. ROMAN
• Usage: Converts a number to Roman numerals.
• Example: =ROMAN(2024) returns “MMXXIV.”
5. FRACTION
• Usage: Returns a number as a fraction.
• Example: =FRACTION(2.5) returns “5/2.”
6. SPELLNUMBER
• Usage: Converts a number to its written-out text.
• Example: =SPELLNUMBER(123) returns “one hundred twenty-three.”
7. HYPERLINK
• Usage: Creates a hyperlink.
• Example: =HYPERLINK("https://example.com", "Click here").
8. RANDBETWEEN
• Usage: Generates a random number between two bounds.
• Example: =RANDBETWEEN(1, 100).
9. TRANSPOSE
• Usage: Converts a horizontal range of data to vertical (or vice versa).
• Example: =TRANSPOSE(A1:D1).
10. ROUNDDOWN
• Usage: Rounds a number down to the nearest integer.
• Example: =ROUNDDOWN(3.7) returns “3.”
11. ROUNDUP
• Usage: Rounds a number up to the nearest integer.
• Example: =ROUNDUP(3.1) returns “4.”
12. NETWORKDAYS
• Usage: Calculates the number of working days between two dates.
• Example: =NETWORKDAYS(A1, A2).
13. PHI
• Usage: Returns the normal probability density function.
• Example: =PHI(1).
14. CONVERT
• Usage: Converts a value from one unit to another.
• Example: =CONVERT(10, "kg", "lb").
15. BASE
• Usage: Converts a number to a specified base (binary, hexadecimal, etc.).
• Example: =BASE(10, 2) returns “1010.”
16. ARABIC
• Usage: Converts a Roman numeral to an Arabic number.
• Example: =ARABIC("MMXXIV") returns “2024.”
17. CHOOSE
• Usage: Selects a value from a list based on an index.
• Example: =CHOOSE(2, "A", "B", "C") returns “B.”
18. FORMULATEXT
• Usage: Displays a formula as text.
• Example: =FORMULATEXT(A1) shows the formula in cell A1.
19. INDEX-MATCH
• Usage: Combines INDEX and MATCH for advanced lookups.
• Example: =INDEX(A1:C10, MATCH(5, A1:A10, 0), 2).
20. MOD
• Usage: Returns the remainder of a division.
• Example: =MOD(10, 3) returns “1.”
These functions, while rarely used, can be incredibly powerful in specific situations. By mastering them, you can explore new ways to automate, analyze, and present your data in Excel. Try them out and share your discoveries!