Top 20 Most Popular Excel Formulas (with Examples & Real-World Use)
Whether you’re tracking expenses, managing a project, or crunching sales data, Excel formulas are the powerhouse behind smart spreadsheets. The right formula can save you hours and eliminate human error.
In this blog, we’ll explore the 20 most popular Excel formulas, explain what they do, why they matter, and show examples you can try today.
Let’s turn your spreadsheets from basic to brilliant. 💡
📌 1. SUM() – Total Everything in a Snap
Use: Adds numbers in a range.
Formula: =SUM(A1:A5)
Example: Adds values in cells A1 to A5 (e.g., monthly expenses).
👉 Perfect for: Budgeting, sales totals, invoices.
📌 2. AVERAGE() – Find the Mean Value
Use: Calculates the average of a range.
Formula: =AVERAGE(B1:B5)
Example: Average test score from 5 exams.
👉 Perfect for: Grades, performance reviews, pricing trends.
📌 3. IF() – Make Decisions with Logic
Use: Returns a value based on a condition.
Formula: =IF(A1>50, "Pass", "Fail")
Example: Checks if score in A1 is more than 50.
👉 Perfect for: Status tracking, automated labels.
📌 4. VLOOKUP() – Find Data in a Table (Vertical Lookup)
Use: Searches for a value in the first column of a table and returns a value in the same row.
Formula: =VLOOKUP(101, A2:C10, 3, FALSE)
Example: Finds the price (column 3) of product ID 101.
👉 Perfect for: Inventory, employee directories, product catalogs.
📌 5. HLOOKUP() – Horizontal Lookup
Use: Like VLOOKUP, but searches rows instead of columns.
Formula: =HLOOKUP("Q1", A1:D5, 2, FALSE)
Example: Finds Q1 sales from the second row.
👉 Perfect for: Time-based reports, matrix data.
📌 6. INDEX() – Get Data by Row & Column Numbers
Use: Returns the value at a given position in a range.
Formula: =INDEX(A2:C5, 2, 3)
Example: Returns value in row 2, column 3 of the range A2:C5.
👉 Perfect for: Advanced lookups, dashboards.
📌 7. MATCH() – Find the Position of a Value
Use: Returns the relative position of a value in a range.
Formula: =MATCH(50, A1:A10, 0)
Example: Finds the position of 50 in A1 to A10.
👉 Perfect for: Lookup combos with INDEX.
📌 8. CONCATENATE() / CONCAT() – Join Text Together
Use: Joins multiple text values into one.
Formula: =CONCATENATE(A1, " ", B1)
Example: Combines first and last name.
👉 Perfect for: Full names, custom messages, formatting data.
📌 9. LEFT(), RIGHT(), MID() – Extract Parts of Text
=LEFT(A1, 4)– Gets first 4 characters.=RIGHT(A1, 3)– Gets last 3 characters.=MID(A1, 3, 2)– Gets 2 characters starting from position 3.
👉 Perfect for: Extracting codes, dates, or initials from strings.
📌 10. LEN() – Count Characters in Text
Use: Counts the number of characters in a string.
Formula: =LEN(A1)
Example: Returns how long the string in A1 is.
👉 Perfect for: Data validation, trimming entries.
📌 11. TRIM() – Remove Extra Spaces
Use: Removes all spaces except single spaces between words.
Formula: =TRIM(A1)
Example: Cleans up messy pasted text.
👉 Perfect for: Cleaning imported data.
📌 12. NOW() / TODAY() – Get the Current Date/Time
=TODAY()– Returns current date.=NOW()– Returns current date and time.
👉 Perfect for: Dynamic reports, timestamps, date comparisons.
📌 13. COUNT() / COUNTA() / COUNTBLANK()
=COUNT(A1:A10)– Counts numbers only.=COUNTA(A1:A10)– Counts non-empty cells.=COUNTBLANK(A1:A10)– Counts empty cells.
👉 Perfect for: Data completeness checks, surveys, tracking.
📌 14. ROUND(), ROUNDUP(), ROUNDDOWN()
=ROUND(3.14159, 2)→ 3.14=ROUNDUP(2.1, 0)→ 3=ROUNDDOWN(2.9, 0)→ 2
👉 Perfect for: Currency rounding, grade sheets, dashboards.
📌 15. ISERROR() / IFERROR() – Handle Errors Gracefully
Use: Check for errors or replace them.
Formula: =IFERROR(A1/B1, "Divide by 0 error")
Example: Prevents errors when B1 is 0.
👉 Perfect for: Cleaner reports, user-friendly sheets.
📌 16. AND() / OR() – Check Multiple Conditions
=AND(A1>50, B1<100)– True if both are true=OR(A1>50, B1<100)– True if either is true
👉 Perfect for: Conditional formatting, logic rules.
📌 17. TEXT() – Format Numbers as Text
Use: Converts numbers to text in a specific format.
Formula: =TEXT(A1, "MM/DD/YYYY")
Example: Formats dates, currency, percentages.
👉 Perfect for: Invoices, printable reports, exports.
📌 18. UNIQUE() (Only in Excel 365+) – Find Distinct Values
Use: Returns a list of unique values.
Formula: =UNIQUE(A1:A10)
👉 Perfect for: Filtering data, summarizing lists.
📌 19. SORT() / FILTER() (Excel 365+)
=SORT(A1:A10)– Sorts data=FILTER(A1:B10, B1:B10>100)– Filters values above 100
👉 Perfect for: Real-time filtering, dynamic tables.
📌 20. XLOOKUP() (Excel 365+) – The VLOOKUP Upgrade
Use: A more flexible lookup formula.
Formula: =XLOOKUP("Apples", A2:A10, B2:B10)
Example: Finds value in B2:B10 where A2:A10 is “Apples”.
👉 Perfect for: Modern replacements of VLOOKUP & HLOOKUP.