Why Formulas Are Excel's Superpower

Excel without formulas is just a fancy table. With formulas, it becomes a calculation engine, data analyzer, and automation tool — all in one. You don't need to be a data scientist to benefit; even knowing 10 core functions can transform how you work with spreadsheets.

This guide covers the 10 most practical Excel functions with plain-English explanations and real examples you can use immediately.

1. SUM — Add Up Numbers

=SUM(A1:A10) adds all values from A1 to A10. You can also sum non-contiguous cells: =SUM(A1, C1, E1). Use Alt + = to auto-insert a SUM formula for a selected range.

2. AVERAGE — Find the Mean

=AVERAGE(B2:B20) calculates the arithmetic mean of a range. Ignores blank cells but includes zeros — so be careful if zeros aren't meaningful in your data.

3. IF — Make Decisions

The IF function is the foundation of logic in Excel:

=IF(C2>100, "Over Budget", "OK")

If C2 is greater than 100, it shows "Over Budget." Otherwise, it shows "OK." You can nest IFs for multiple conditions, though SWITCH or IFS is cleaner for complex logic.

4. VLOOKUP — Find Data in a Table

VLOOKUP searches the first column of a range and returns a value from another column in the same row:

=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)

This looks up the value in A2 within columns E–G, and returns the corresponding value from the 2nd column (F). The FALSE at the end means exact match only.

Tip: If you're on a newer version of Excel, try XLOOKUP instead — it's more flexible and easier to read.

5. COUNTIF — Count Cells Matching a Condition

=COUNTIF(D2:D50, "Completed") counts how many cells in that range contain the word "Completed." Great for tracking statuses, categories, or frequencies.

6. CONCATENATE / CONCAT — Join Text

Combine text from multiple cells: =CONCAT(A2, " ", B2) merges first and last names with a space between them. In modern Excel, you can also use the ampersand: =A2&" "&B2.

7. LEFT, RIGHT, MID — Extract Text

  • =LEFT(A2, 3) — extracts the first 3 characters
  • =RIGHT(A2, 4) — extracts the last 4 characters
  • =MID(A2, 5, 3) — extracts 3 characters starting at position 5

These are invaluable when working with codes, IDs, or inconsistently formatted data.

8. TRIM — Remove Extra Spaces

=TRIM(A2) removes leading, trailing, and extra internal spaces from text. Essential when importing data from other systems that leaves invisible whitespace messing up your lookups.

9. TODAY and NOW — Dynamic Dates

=TODAY() returns today's date (updates daily). =NOW() returns the current date and time. Use these to calculate how many days have passed: =TODAY()-A2 gives the number of days since the date in A2.

10. IFERROR — Handle Errors Gracefully

Wrap any formula in IFERROR to display a friendlier message instead of #N/A or #DIV/0!:

=IFERROR(VLOOKUP(A2, $E$2:$G$100, 2, FALSE), "Not Found")

This makes your spreadsheets look professional and prevents errors from cascading through dependent cells.

Practice Makes Permanent

The fastest way to learn these is to apply them to real data you already work with. Create a practice sheet, import a CSV from work or a hobby project, and try applying each function. Within a few sessions, they'll become second nature.