Excel Function
CONCAT / CONCATENATE
Theory: Joins two or more text strings into one.
Syntax: =CONCAT(text1, text2, ...)
Example: If A1 is "Sita" and B1 is "Ram":
o =CONCAT(A1, " ", B1) → SitaRam TEXTJOIN
Theory: The "pro" version of CONCAT. It allows you to add a delimiter (like a comma or
space) automatically and skip empty cells.
Syntax: =TEXTJOIN(delimiter, ignore empty, text1, ...)
Example: Joining a list of names with a comma:
o =TEXTJOIN(", ", TRUE, "Red", "Blue", "Green") → Red, Blue, Green
2. Extracting Text (The "Scissors")
LEFT & RIGHT
Theory: Grabs a specific number of characters from the start (Left) or end (Right) of a cell.
Syntax: =LEFT(text, [num_chars]) | =RIGHT(text, [num_chars])
Example: Extracting the area code from "(555) 123-4567":
o =LEFT(A1, 5) → (555)
MID
Theory: Starts in the middle of a string and grabs a set number of characters.
Syntax: =MID(text, start_num, num_chars)
Example: Extracting "Day" from "GreatDay":
o =MID("GreatDay", 6, 3) → Day
3. Cleaning & Formatting
TRIM
Theory: Removes all extra spaces from text except for single spaces between words. Perfect
for fixing data imported from the web.
Syntax: =TRIM(text)
Example: =TRIM(" Too many spaces ") → "Too many spaces"
UPPER, LOWER, & PROPER
Theory: Changes the capitalization of your text.
o UPPER: ALL CAPS
o LOWER: all lowercase
o PROPER: Capitalizes Every First Letter
Example: =PROPER("jane doe") → Jane Doe
4. Finding & Replacing
SUBSTITUTE
Theory: Replaces existing text with new text.
Syntax: =SUBSTITUTE(text, old_text, new_text)
Example: Changing a year in a title:
o =SUBSTITUTE("Report 2023", "2023", "2024") → Report 2024
LEN
Theory: Simply counts the number of characters in a cell (including spaces).
Syntax: =LEN(text)
Example: =LEN("Hello") → 5
1. The IF Statement
Theory: It checks a condition and returns one value if it's true, and another if it's false.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Example: Checking a passing grade.
o =IF(A1>=60, "Pass", "Fail")
o If A1 is 75, the result is "Pass".
2. The Operators (The Language of Logic)
Symbol Meaning Example
= Equal to A1=B1
<> Not equal to A1<>0
> Greater than A1>100
< Less than A1<50
>= Greater than or equal to A1>=10
<= Less than or equal to A1<=5
3. Combining Conditions: AND & OR
AND
Theory: Returns TRUE only if all arguments are true.
Syntax: =AND(condition1, condition2, ...)
Example: Bonus for employees who sold > $1000 AND worked > 2 years.
o =IF(AND(A1>1000, B1>2), "Bonus", "No Bonus")
OR
Theory: Returns TRUE if at least one argument is true.
Syntax: =OR(condition1, condition2, ...)
Example: Discount for seniors (65+) OR students.
o =IF(OR(Age>=65, Status="Student"), "Discount", "Full Price")
4. Handling Multiple Choices: IFS
If you have more than two possible outcomes (not just Pass/Fail), use IFS.
Theory: Checks multiple conditions in order and returns the value for the first TRUE
condition.
Syntax: =IFS(test1, value1, test2, value2, ...)
Example: Grading scale.
o =IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C")
1. Today and Right Now
TODAY
Theory: Returns the current date.
Syntax: =TODAY() (No arguments needed inside the parentheses).
Example: =TODAY() → 1/29/2026
NOW
Theory: Returns the current date and the current time.
Syntax: =NOW()
Example: =NOW() → 1/29/2026 13:45
2. Extracting Parts of a Date
Use the month, year, or day of the week.
Example (if A1 is
Function Theory Syntax
1/29/2026)
Gets the day of the
DAY =DAY(serial_num) =DAY(A1) → 29
month (1-31).
Gets the month (1-
MONTH =MONTH(serial_num) =MONTH(A1) → 1
12).
YEAR Gets the 4-digit year. =YEAR(serial_num) =YEAR(A1) → 2026
Example (if A1 is
Function Theory Syntax
1/29/2026)
Gets the day of the =WEEKDAY(A1) → 5
WEEKDAY =WEEKDAY(serial_num)
week (1=Sun, 7=Sat). (Thursday)
3. Creating and Calculating Dates
DATE
Theory: Combines individual year, month, and day numbers into a valid Excel date.
Syntax: =DATE(year, month, day)
Example: =DATE(2026, 12, 25) → 12/25/2026
DATEDIF
Theory: Calculates the difference between two dates in Days, Months, or Years. (Note: This
is a "hidden" function—Excel won't suggest the syntax as you type).
Syntax: =DATEDIF(start_date, end_date, "unit")
o Units: "Y" for years, "M" for months, "D" for days.
Example: =DATEDIF("1/1/2020", "1/1/2026", "Y") → 6
4. Workdays and Deadlines
NETWORKDAYS
Theory: Calculates the number of working days between two dates, automatically excluding
weekends.
Syntax: =NETWORKDAYS(start_date, end_date, [holidays])
Example: =NETWORKDAYS("1/1/2026", "1/10/2026") → 7 (excludes Sat/Sun)
EDATE
Theory: Moves a date forward or backward by a specific number of months.
Syntax: =EDATE(start_date, months)
Example: =EDATE("1/29/2026", 3) → 4/29/2026 (3 months later)