15 Basic Excel Formulas Every Beginner Must Learn

Basic Excel formulas are the single most important skill you can build if you’re tired of doing repetitive number-crunching by hand. Whether you’re a college student, a new hire drowning in spreadsheets, or a freelancer trying to manage invoices, this guide is for you. Most tutorials throw a wall of syntax at you and call it a day. 

This one doesn’t. Instead, you’ll get real-world context, honest limitations, and the exact formulas that actually matter, ranked and explained the way someone who uses Excel daily would explain them.

No filler. No fluff. Just formulas that work.

What Is an Excel Formula?

Think of a formula in Excel as a set of instructions you give the spreadsheet. Your formulas are always written with an equal sign (=) first, followed by the name of the function and its arguments. For example, when you enter =SUM(A1:A10), you are asking Excel to calculate the sum of cells A1 through A10.

The difference between a formula and a function can be confusing for new users. A formula is the complete expression you input. A function or built-in tool found within a formula (e.g., SUM, IF, VLOOKUP) provides an automatic way of providing data to and performing calculations from within the context of the entire formula. 

Pro Tip: Always start every formula with =. Excel ignores anything written without it.

Learn Excel Fundamentals First

When you start working with formulas in Excel, you must understand the following three items: 

• Cell references: Each cell in your spreadsheet has its own unique cell address (e.g., B3). These cell addresses are used in your formulas to retrieve numbers from the spreadsheet. 
• Relative vs. absolute references, A1 shifts when you copy a formula. $A$1 stays locked. This single concept saves you from countless headaches.
• Cell ranges: A1:A10 means all cells from A1 through A10. Ranges are the backbone of most spreadsheet formulas.

Skip these basics, and your formulas will break constantly. Spend 15 minutes on them, and everything else clicks fast.

Why Are Excel Formulas Important?

Here’s a stat worth knowing: according to Microsoft, over 1.1 billion people use Microsoft Office globally, and Excel sits at the center of most professional workflows. Yet surveys consistently show that most users only know 10–20% of what Excel can do.

Excel formulas eliminate human error. Manual addition across 500 rows? You’ll make a mistake. A SUM formula? It won’t. They also scale instantly; one formula handles 10 rows or 10,000 rows with zero extra effort.

The difference between someone who knows basic Excel functions and someone who doesn’t isn’t just time. It’s job opportunities, salary negotiations, and the ability to deliver results faster than anyone else in the room.

Advance Your Career with Excel

Employers aren’t subtle about this. Job postings for roles in finance, HR, marketing, logistics, and operations almost always list Excel proficiency as a requirement, not a nice-to-have.

The Microsoft Office Specialist (MOS) certification is one of the most recognized entry-level credentials you can add to a résumé. Platforms like Coursera, LinkedIn Learning, and Microsoft Learn all offer structured paths to build these skills efficiently.

Knowing how to use Excel fluently puts you in the top tier of candidates for data analyst, project coordinator, and financial analyst roles, even at the junior level.

How to Use Excel Formulas

Learning how to put a formula in Excel is simpler than most people expect. Click a cell, type =, then start typing your function name. Excel’s AutoComplete will suggest matching functions as you type, use the arrow keys to select one, and press Tab to confirm.

A few things beginners consistently get wrong:

• Forgetting to close parentheses, =SUM(A1:A10 without a closing ) throws an error.
• Misreading error codes, #VALUE! means wrong data type. #REF! means a referenced cell is gone. #DIV/0! means you’re dividing by zero. These aren’t mysterious; they’re Excel telling you exactly what went wrong.
• Confusing commas and colons, A1, A10 references two separate cells. A1:A10 references the entire range.

Pro Tip: Press Ctrl+~ to toggle between formula view and result view across all cells at once.

How to Insert Formulas in Excel for an Entire Column

This is one of the most Googled Excel commands for a reason: applying a formula to thousands of rows shouldn’t take thousands of clicks. Here’s how to do it fast:

1. Double-click the fill handle (the tiny green square at a cell’s bottom-right corner), and Excel auto-fills down to match adjacent data.
2. Drag the fill handle manually if auto-fill stops at the wrong row.
3. Select the range first, then press Ctrl+Enter. This fills every selected cell simultaneously.
4. Format as a Table (Ctrl+T). Once your data is in a Table, new formulas automatically expand as you add rows. This is easily the most underused trick in Excel.

One critical warning: if your formula uses A1 instead of $A$1 for a fixed reference, it will shift as you fill down. Know when to lock your references.

Basic Formulas in Excel — The 15 You Actually Need

Basic Excel formulas don’t require a computer science degree. These 15 cover roughly 80% of real-world spreadsheet work. Master them, and you’ll look like a pro in any office setting.

1. SUM() — Your Most-Used Formula

The sum formula in Excel adds up a range of numbers. Simple? Yes. Indispensable? Absolutely.

Syntax: =SUM(B2:B10)

If you’re trying to sum numbers in an Excel spreadsheet, use formulas for everything you want to total (e.g., sales, expenses, hours worked, etc.). 

Pro Tip: Use the shortcut Alt + = to insert an automatic SUM Formula RIGHT now. The SUM function is your go-to Formula to provide a running total. 

2. MIN() and MAX()

These find the smallest and largest values in a range.

Syntax: =MIN(C2:C50) / =MAX(C2:C50)

You will find real value in locating the vendor with the lowest price, identifying the day of the highest sales, or determining the student with the lowest score in an exam.  Pair either with IF() later, and you unlock conditional comparisons.

3. AVERAGE()

Calculates the arithmetic mean of a range.

Syntax: =AVERAGE(D2:D12)

True to its nature, AVERAGE will ignore blank cells but will include zero-value cells. If your dataset has blank cells that actually represent a zero value, you’ll have an erroneous average. To resolve this, replace any blank cells with a zero before finding the average. 

4. COUNT(), COUNTA(), and COUNTIF()

COUNT() will count only those cells in your dataset that contain numeric values. 

COUNTA() will include all non-blank cells, regardless of their type.

COUNTIF() will include only those cells in your dataset that fit the specific criteria you provide. 

Syntax: =COUNTIF(A2:A100, “Completed”)

The trio above forms the basic foundation of all tracking dashboards (completed tasks, filled forms, and active clients). 

Pro Tip: Use COUNTBLANK() to instantly audit incomplete data entries in any column.

5. POWER()

Calculates the power of a number: =POWER(2,10) will equal 1024.

A shortcut to this is using the ^ operator the same way: =2^10. You can also use it in situations where you’ve built a compound interest/finding interest for science types of things (where clarity of figures and data is more important than space). 

6. CEILING() and FLOOR()

CEILING rounds up to the nearest multiple. FLOOR rounds down.

Syntax: =CEILING(14.3, 5) → returns 15

Common real-world examples: pricing models (always round up to the next $0.50) and keeping track of time (always round to the nearest 15-minute block). These functions aren’t given enough credit as basic functions of Excel! 

7. CONCAT()

Joins text strings together. Replaced the older CONCATENATE() function.

Syntax: =CONCAT(A2, ” “, B2) → “Jane Doe”

The & operator does the same thing: =A2&” “&B2. Use CONCAT for readability in complex formulas. Essential for building full names, addresses, or unique product IDs from split columns.

8. TRIM()

Removes every extra space — leading, trailing, and double spaces between words.

Syntax: =TRIM(” John Smith “) → “John Smith”

If your VLOOKUP keeps failing even though the data looks correct, run TRIM on both columns first. Invisible spaces are the silent killer of lookup formulas.

9. REPLACE() and SUBSTITUTE()

Both swap text — but they work differently.

• REPLACE() targets a position: =REPLACE(A1, 1, 3, “XXX”)
• SUBSTITUTE() targets a value: =SUBSTITUTE(A1, “old”, “new”)

Use SUBSTITUTE when you know what to replace. Use REPLACE when you know where it sits. Mixing them up is one of the most common Excel formula explanation mistakes beginners make.

10. LEFT(), RIGHT(), and MID()

Extract characters from a text string.

• LEFT(“Houston, TX”, 7) → “Houston”
• RIGHT(“Report_2024”, 4) → “2024”
• MID(“AB-1234-CD”, 4, 4) → “1234”

These are essential for parsing codes, pulling years from strings, or breaking apart messy imported data.

11. UPPER(), LOWER(), and PROPER()

Convert text case without retyping anything.

• =UPPER(“john”) → “JOHN”
• =LOWER(“JANE”) → “jane”
• =PROPER(“john doe”) → “John Doe”

Run PROPER on any name column imported from a CRM or form and watch chaos become clean data in seconds.

12. NOW() and TODAY()

TODAY() returns today’s date. NOW() returns today’s date and current time.

Important caveat: Both recalculate every time the spreadsheet opens or refreshes. If you need a static timestamp, paste the value using Ctrl+Shift+; instead.

13. DATEDIF()

Calculates the difference between two dates in days, months, or years.

Syntax: =DATEDIF(B2, TODAY(), “Y”) → returns age in years

Here’s what no tutorial mentions: DATEDIF is an undocumented function. It won’t appear in Excel’s autocomplete. Type it manually, and it works perfectly, but Microsoft officially removed it from documentation years ago. It still functions in all modern Excel versions.

14. VLOOKUP() and HLOOKUP()

VLOOKUP searches a table vertically (by column). HLOOKUP searches horizontally (by row).

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

Always use FALSE as the last argument unless you’re working with sorted data and want approximate matches. Using TRUE on unsorted data returns wrong results — silently, without any error.

Heads up: Microsoft 365 users should explore XLOOKUP(), it’s the modern replacement and fixes most of VLOOKUP’s limitations without the column-index headache.

15. IF() — The Gateway Formula

Syntax: =IF(C2>=60, “Pass”, “Fail”)

The IF() function returns one value when a condition is true and another when it’s false. It’s the entry point to logical thinking in Excel. Once you nest multiple IFs, or graduate to IFS(), you can build decision engines inside a single cell.

This is also the formula that makes formula-based conditional formatting possible, which turns raw data into color-coded visual dashboards.

Excel Cheat Sheet: Quick Reference Table

Formula	What It Does	Example
SUM()	Adds a range	=SUM(A1:A10)
AVERAGE()	Finds the mean	=AVERAGE(B1:B10)
IF()	Logical condition	=IF(A1>10,”Yes”,”No”)
VLOOKUP()	Vertical search	=VLOOKUP(A1,D:F,2,FALSE)
TRIM()	Removes extra spaces	=TRIM(A1)
CONCAT()	Joins text	=CONCAT(A1,” “,B1)
TODAY()	Returns today’s date	=TODAY()
COUNTIF()	Conditional count	=COUNTIF(A:A,”Done”)

Save this as your personal Excel cheat sheet, or better yet, bookmark this page.

Other Kinds of Formulas in Excel

Formulas Using Operators

Beyond functions, Excel supports four operator types:

• Arithmetic: +, -, *, /, ^
• Comparison: >, <, >=, <=, <>
• Text: & for joining strings
• Reference: : for ranges

Excel follows the standard order of operations, multiplication before addition. Use parentheses to override it: =(A1+B1)*C1 vs. =A1+B1*C1 produces very different results.

Array Formulas

Array formulas process multiple values in one shot. In Microsoft 365, dynamic arrays work automatically, =SORT(), =FILTER(), and =UNIQUE() are game-changers that spill results across multiple cells without any special keystrokes.

In older Excel versions, press Ctrl+Shift+Enter instead of just Enter to activate array behavior.

Formula-Based Conditional Formatting

This is where Excel spreadsheet formulas meet visual design. Go to Home → Conditional Formatting → New Rule → “Use a formula to determine which cells to format.”Example: =$C2>1000 highlights every row where column C exceeds 1,000. One formula, instant visual clarity across hundreds of rows.

Pros and Cons of Learning Excel Formulas

Pros:

• Eliminates manual calculation errors instantly
• Scales from 10 rows to 10,000 with zero added effort
• Transferable across industries and job roles
• Free with most Microsoft 365 subscriptions

Cons:

• VLOOKUP and nested IFs have a real learning curve
• Formula errors can cascade silently if you’re not careful
• Excel isn’t the best tool for real-time collaboration (Google Sheets wins there)
• Some advanced functions, like DATEDIF, are undocumented and fragile

Excel vs. Google Sheets: Which Should Beginners Learn First?

Feature	Excel	Google Sheets
Offline access	✅ Full	❌ Limited
Collaboration	❌ Clunky	✅ Real-time
Advanced formulas	✅ Superior	✅ Mostly same
Cost	Paid (M365)	Free
Job market demand	✅ Higher	Growing

Verdict: Learn Excel first. The core formulas for Excel spreadsheet work identically in Google Sheets, so you’re not locked in. But Excel proficiency carries more weight in hiring conversations.

Common Mistakes to Avoid

• Using commas instead of colons in ranges, A1, A10 ≠ , A1:A10
• Forgetting absolute references when filling formulas down a column
• Trusting VLOOKUP with TRUE on unsorted data, always use FALSE
• Not trimming data before lookups, invisible spaces break everything
• Nesting too many IFs, use IFS() or SWITCH() instead for cleaner logic

Pro Tip: Press F2 on any cell to enter edit mode and visually trace formula references with color-coded highlights.

Upskilling Your Team in Excel

If you manage a team, individual Excel training pays off fast. One analyst saving two hours per week across a 10-person team recovers 1,040 hours annually. At a modest $25/hour, that’s $26,000 in recovered productivity per year, from basic formula literacy alone.

Recommended platforms: Microsoft Learn, Coursera, and LinkedIn Learning all offer structured Excel paths from beginner to advanced. The MOS certification adds credibility for client-facing or analyst roles.

FAQs

Conclusion

Basic Excel formulas aren’t optional knowledge anymore; they’re table stakes for almost every office job on the planet. The 15 functions covered here aren’t arbitrary. They’re the ones that appear in real work, real interviews, and real business problems every single day.

Start with SUM, AVERAGE, and IF. Get comfortable with VLOOKUP and TRIM. Then layer in the text and date functions as your needs grow. You don’t need to memorize 500 commands. You need to genuinely understand 15 and use them until they feel automatic.

Open Excel right now. Pick one formula. Build something real with it. That’s how this actually sticks.