Excel Secrets Revealed: 20 Basic Functions to Simplify Your Work
Excel is a powerful tool that offers many functions often overlooked by users. Here are 20 basic yet highly useful Excel functions with practical examples:
1. TRIM: Eliminates extra spaces from a text string, leaving only single spaces between words.
=TRIM(“ Hello World “)
Result: “Hello World”
2. TEXT: Converts a number into text in a specified format.
=TEXT(1234.567, “$#,##0.00”)
Result: “$1,234.57”
3. VALUE: Transforms text that appears as a number into an actual numeric value.
=VALUE(“1234”)
Result: 1234
4. PROPER: Capitalizes the first letter of each word in a text string.
=PROPER(“hello world”)
Result: “Hello World”
5. UPPER: Converts all letters in a text string to uppercase.
=UPPER(“hello world”)
Result: “HELLO WORLD”
6. LOWER: Converts all letters in a text string to lowercase.
=LOWER(“HELLO WORLD”)
Result: “hello world”
7. SUBSTITUTE: Replaces occurrences of a specified text string with another text string.
=SUBSTITUTE(“Hello World”, “World”, “Excel”)
Result: “Hello Excel”
8. REPT: Repeats a text string a specified number of times.
=REPT(“*”, 5)
Result: “*****”
9. FIND: Locates a text string within another text string and returns the starting position.
=FIND(“World”, “Hello World”)
Result: 7
10. SEARCH: Similar to FIND but is not case-sensitive.
=SEARCH(“world”, “Hello World”)
Result: 7
11. LEFT: Extracts a specified number of characters from the start of a text string.
=LEFT(“Hello World”, 5)
Result: “Hello”
12. RIGHT: Extracts a specified number of characters from the end of a text string.
=RIGHT(“Hello World”, 5)
Result: “World”
13. MID: Extracts a specified number of characters from a text string, starting from a given position.
=MID(“Hello World”, 7, 5)
Result: “World”
14. LEN: Returns the number of characters in a text string.
=LEN(“Hello World”)
Result: 11
15. EXACT: Checks if two text strings are exactly the same, considering case.
=EXACT(“Hello”, “hello”)
Result: FALSE
16. CONCATENATE: Joins multiple text strings into one text string.
=CONCATENATE(“Hello”, “ “, “World”)
Result: “Hello World”
17. CHAR: Returns the character corresponding to a specified number.
=CHAR(65)
Result: “A”
18. CODE: Returns a numeric code for the first character in a text string.
=CODE(“A”)
Result: 65
19. DAYS: Calculates the number of days between two dates.
=DAYS(“2024–12–31”, “2024–01–01”)
Result: 365
20. NETWORKDAYS: Computes the number of workdays between two dates.
=NETWORKDAYS(“2024–01–01”, “2024–12–31”)
Result: 261
