💡 Small formulas, big impact on your daily workflow
Excel is a powerful tool for managing your accounts and business data. Here are some essential formulas that can save you time and reduce errors:
1️⃣ VLOOKUP 🔍
-
Quickly find data from a table.
-
Example: Lookup customer details by their ID.
2️⃣ COUNTIF ✅
-
Count the number of cells that meet a condition.
-
Example: Count how many invoices are overdue.
3️⃣ SUMIF 💰
-
Sum values based on a condition.
-
Example: Sum total sales for a specific product.
4️⃣ CONCATENATE (or CONCAT / TEXTJOIN) ✏️
-
Combine text from multiple cells into one.
-
Example: Merge first and last names into a full name.
5️⃣ TRIM 🧹
-
Remove extra spaces from text.
-
Example: Clean messy data imported from other systems.
6️⃣ PROPER 🔠
-
Capitalise the first letter of each word.
-
Example: Format names or addresses properly.
7️⃣ UPPER ⬆️
-
Convert text to all uppercase.
-
Example: Standardise codes or abbreviations.
7️⃣ UPPER ⬆️
-
Convert text to all uppercase.
-
Example: Standardise codes or abbreviations.
8️⃣ LOWER ⬇️
-
Convert text to all lowercase.
-
Example: Standardize email addresses.
💡 Tip: Combining these formulas can automate repetitive tasks and help you maintain clean, accurate data. For instance, TRIM(PROPER(CONCATENATE(A2, " ", B2))) can quickly give you a clean, properly formatted full name.
9️⃣ SUMIFS 💰
-
Sum values based on multiple conditions. (This is the multi-criteria version of
SUMIF). -
Example: Sum the total sales for a specific product and for a specific sales region.
🔟 COUNTIFS ✅
- Count the number of cells that meet multiple conditions. (This is the multi-criteria version of
COUNTIF). -
Example: Count how many invoices are currently marked as ‘Overdue’ and are for an amount greater than RM1,000.
