Basic Text Functions
- Concatenate:
=CONCATENATE(A1, " ", B1)
- Merge Text:
=A1 & " " & B1
- Uppercase:
=UPPER(A1)
- Lowercase:
=LOWER(A1)
- Proper Case:
=PROPER(A1)
- Trim Spaces:
=TRIM(A1)
Mathematical Functions
- Sum:
=SUM(A1:A10)
- Average:
=AVERAGE(A1:A10)
- Round Off:
=ROUND(A1, 2)
- Minimum:
=MIN(A1:A10)
- Maximum:
=MAX(A1:A10)
Date & Time
- Today:
=TODAY()
- Now:
=NOW()
- Day:
=DAY(A1)
- Month:
=MONTH(A1)
- Year:
=YEAR(A1)
- Days Between:
=DATEDIF(A1, B1, "d")
Logical Functions
- IF:
=IF(A1>100, "Yes", "No")
- IF with AND:
=IF(AND(A1>10,B1<100), "OK", "Fail")
- IF with OR:
=IF(OR(A1="Yes",B1="Yes"), "Approved", "Rejected")
Lookup & Reference
- VLOOKUP:
=VLOOKUP(A1, A2:B10, 2, FALSE)
- HLOOKUP:
=HLOOKUP(A1, A2:Z2, 2, FALSE)
- XLOOKUP:
=XLOOKUP(A1, A2:A10, B2:B10)
- INDEX-MATCH:
=INDEX(B2:B10, MATCH(A1, A2:A10, 0))
Data Cleaning
- Remove Extra Spaces:
=TRIM(A1)
- Remove Non-Numeric:
=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)+0), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))
Convert to Text
- Convert Formula to Text: Copy then Paste Special → Values
- Using
TEXT(): =TEXT(A1, "dd-mm-yyyy")
Tables & Layout
- Create Table: Ctrl + T
- Freeze Top Row: View → Freeze Panes → Freeze Top Row
- Wrap Text: Home → Wrap Text
- Merge Cells: Home → Merge & Center
Remove Duplicates
- Highlight Duplicates: Conditional Formatting → Highlight Cells → Duplicate Values
- Remove Duplicates: Data → Remove Duplicates
- Formula to Check Duplicates:
=COUNTIF(A:A, A2)>1
AMC Management Related
- Next Due Date:
=EDATE(A1, 12)
- Status (Paid/Not Paid):
=IF(B1="", "Not Paid", "Paid")
- Upcoming in 30 Days:
=IF(TODAY()+30>=A1, "Upcoming", "")
Office Utilities
- Count Blank:
=COUNTBLANK(A1:A100)
- Count Non-Blank:
=COUNTA(A1:A100)
- Text Length:
=LEN(A1)
- Find Text Position:
=FIND("@", A1)
- Replace Text:
=SUBSTITUTE(A1, "old", "new")