20 Other Useful Excel Functions

Explore lesser-known but useful Excel functions like LEN, VALUE, ADDRESS, CELL, FORMULATEXT, and more with real-life examples for smart data work

Excel isn’t just about SUM and VLOOKUP. It has many hidden gems—functions that help automate and simplify advanced tasks, data validation, automation, and formatting.

In this article, we’ll cover a group of helpful Excel functions categorized into:

• Text Functions – LEN(), VALUE(), CHAR(), CODE(), REPT()
• Lookup & Reference Functions (Misc.) – ROW()/ROWS(), COLUMN()/COLUMNS(), HYPERLINK()
• Information Functions – FORMULATEXT(), CELL(), ISEVEN(), ISODD(), TYPE(), INFO(), ISTEXT(), ISLOGICAL(), ISFOURMULA(), ISNUMBER()

We’ll explain each with real-life examples in simple, human-friendly language.

---

TEXT FUNCTIONS – Very Useful Excel Functions

1. LEN(): Count Number of Characters

Syntax: =LEN(text)

Counts characters in a cell, including spaces.

Example:

=LEN("Hello World") ➔ Result: 11

Useful for checking input length (e.g., passwords or IDs).

👉 Note: It also counts spaces and special characters like @, $, %, etc.

---

2. VALUE(): Convert Text to Number

Syntax: =VALUE(text)

Converts numbers stored as text to actual numeric values.

Example:

=VALUE("1000") ➔ Result: 1000

=VALUE("INV1001") ➔ Result: #VALUE Error

Useful when you import data from web or systems where numbers are treated as text.

---

3. REPT(): Repeat Text Multiple Times

Syntax: =REPT(text, number_times)

Repeats a text string a specified number of times.

Example:

=REPT("*", 5) ➔ Result: *****

Use it to create custom bars or dashboards.

---

4. CHAR(): Return Character from ASCII Code

Syntax: =CHAR(number)

Returns the character for a given ASCII code.

Example:

=CHAR(65) ➔ Result: A
=CHAR(90) ➔ Result: Z

=CHAR(97)) ➔ Result: a
=CHAR(122) ➔ Result: z

Useful in generating dynamic alphabets or symbols.

---

5. CODE(): Return ASCII Code

Syntax: =CODE(text)

Returns ASCII code of the first character.

Example:

=CODE("A") ➔ Result: 65

Useful for sorting or coding logic.

Note: Reverse of CHAR()

---

LOOKUP & REFERENCE FUNCTIONS (Misc.)

1. ADDRESS(): Get Cell Address

Syntax: =ADDRESS(row_num, column_num)

Returns the cell reference as a string.

Example:

=ADDRESS(1, 1) ➔ Result: $A$1
=ADDRESS(2, 3) ➔ Result: $C$2
=ADDRESS(3, 3) ➔ Result: $C$3

Great for dynamic references.

---

2. ROW() / ROWS(): Get Row Number(s)

Syntax: =ROW([reference]) or =ROWS(range)

Example:

=ROW(A5) ➔ Result: 5
=ROWS(A3:A10) ➔ Result: 8

Useful in dynamic formulas or helper columns.

---

3. COLUMN() / COLUMNS(): Get Column Number(s)

Syntax: =COLUMN([reference]) or =COLUMNS(range)

Example:

=COLUMN(C1) ➔ Result: 3
=COLUMN(C5) ➔ Result: 3
=COLUMNS(A1:D1) ➔ Result: 4

Often used with INDEX or OFFSET.

---

4. HYPERLINK(): Create Clickable Links

Syntax: =HYPERLINK(link_location, [friendly_name])

Example:

=HYPERLINK("https://smarttejas.com", "Visit Smart Tejas")

Useful for reports, dashboards, or quick navigation.

---

INFORMATION FUNCTIONS

1. TYPE(): Get Data Type Code

Syntax: =TYPE(value)

Returns a number code based on data type:

• 1 = Number
• 2 = Text
• 4 = Logical (TRUE/FALSE)
• 16 = Error
• 64 = Array

Example:

=TYPE(100) ➔ 1
=TYPE("Hello") ➔ 2

---

2. CELL(): Info About Cell

Syntax: =CELL(info_type, [reference])

Gives various info like file name, sheet, format, etc.

Example:

=CELL("address", A1) ➔ $A$1

=CELL("filename") ➔ Returns full path, sheet name

Useful in dashboards.

---

3. INFO(): System Info

Syntax: =INFO(type_text)

Returns information about your system.

Examples:

=INFO("osversion")
=INFO("numfile") ➔ Number of open workbooks

---

4. FORMULATEXT(): Show Formula in Cell

Syntax: =FORMULATEXT(reference)

Displays the formula used in a cell.

Example:

=FORMULATEXT(B2)

Helpful in documentation or audits.

---

5. ISODD() / ISEVEN(): Check Parity

Syntax:

=ISODD(number) ➔ TRUE/FALSE
=ISEVEN(number) ➔ TRUE/FALSE

Example:

=ISODD(3) ➔ TRUE

Great for conditional formatting or filtering rows.

---

6. ISNUMBER(): Check if Value is a Number

Syntax: =ISNUMBER(value)

Returns TRUE if numeric.

Example:

=ISNUMBER("123") ➔ FALSE (because it's text)
=ISNUMBER(123) ➔ TRUE

---

7. ISTEXT(), ISLOGICAL(), ISFORMULA(): Type Checkers

• =ISTEXT(value) ➔ TRUE if text
• =ISLOGICAL(value) ➔ TRUE if TRUE/FALSE
• =ISFORMULA(reference) ➔ TRUE if the cell has a formula

Example:

=ISTEXT("Excel") ➔ TRUE
=ISFORMULA(B2) ➔ TRUE if B2 has formula

Read More: Error Handling in Excel

---

Summary Table

Function	Category	Description
LEN, VALUE	Text	Length, convert text to number
REPT, CHAR, CODE	Text	Repeat, ASCII to/from characters
ADDRESS	Lookup/Reference	Cell address as text
ROW, COLUMN	Lookup/Reference	Row/Column number(s)
HYPERLINK	Lookup/Reference	Create clickable links
TYPE, CELL	Info	Data type, cell info
INFO	Info	System and environment info
FORMULATEXT	Info	Show the formula in the cell
ISODD/ISEVEN	Info	Parity check
ISNUMBER, ISTEXT	Info	Type verification
ISLOGICAL	Info	TRUE/FALSE check
ISFORMULA	Info	Checks if the cell contains a formula

---

Final Thoughts

These functions may seem small individually, but they become powerful tools when used together. Whether you’re validating data, documenting formulas, or managing references, these Excel functions will save time and boost your productivity.

Start experimenting with them in your projects—you’ll be surprised how often they come in handy!

What’s Next?

In the next post, we’ll learn about the Relative and Absolute Reference in Excel