Top 15 Basic Excel Formulas You Must Know (Beginner’s Guide with Examples)

Top 15 Basic Excel Formulas Everyone Should Know (With Examples)

Introduction

Microsoft Excel is a powerful spreadsheet tool that millions use to organize data, perform calculations, and create reports. Whether you’re a student managing grades, a business owner tracking sales, or an office worker analyzing budgets, Excel makes your work faster and easier. The secret to Excel’s power lies in its formulas—commands that automate calculations, find specific data, or clean up information. Learning these formulas is like having a toolkit that solves data problems quickly.

This article covers 15 essential Excel formulas that everyone should know. They’re simple to use, even for beginners, and can save you hours of manual work. For each formula, we’ll explain what it does, how to write it, and when to use it. To make things clear, we’ve included an example table for each formula, styled to look like an Excel spreadsheet, showing sample data, the formula used, and the result. These formulas will help you add numbers, find averages, search data, and more, making your spreadsheets more efficient and professional.

Let’s get started with the formulas. Each section below dives into a formula with a detailed explanation, practical use cases, and an Excel-like table to show it in action.

1. SUM – Add Values

The SUM formula is one of the most popular Excel formulas because it’s so simple and useful. It adds up all the numbers in a range of cells, saving you from manually calculating totals. Whether you’re summing up sales, expenses, or scores, SUM gets the job done quickly. It’s a must-know for anyone using Excel.

Syntax: =SUM(range)

What it does: Adds all numbers in the specified range of cells.

Use Case: Imagine you’re a store manager tracking daily sales for a week. You have sales amounts in cells A1 to A5, and you want the total. SUM calculates it instantly. It’s also great for budgeting, totaling hours worked, or summing up test scores in a classroom. You can use it for personal finance, like adding up monthly expenses, or in business to calculate total revenue.

Tips: SUM can handle multiple ranges, like =SUM(A1:A5, B1:B5), to add numbers from different parts of your spreadsheet. It ignores empty cells and text, so you don’t need to clean your data beforehand. If you need to sum only specific values (e.g., sales above $100), consider combining SUM with other formulas like IF.

Example Table

Formula	Example Data	Formula Used	Result
SUM	A1:A3 = [100, 200, 300]	=SUM(A1:A3)	600

In this Excel-like table, the SUM formula adds 100, 200, and 300 to give a total of 600. This could represent total sales for three days in a store.

2. AVERAGE – Calculate Mean

The AVERAGE formula finds the mean (or average) of a range of numbers. It’s perfect for understanding the typical value in a dataset, like average test scores, monthly sales, or daily temperatures. Instead of adding numbers and dividing by the count, AVERAGE does it all in one step, saving you time.

Syntax: =AVERAGE(range)

What it does: Adds up numbers in a range and divides by the count of numbers.

Use Case: Suppose you’re a teacher calculating the average score for a class test. If student scores are in cells B1 to B5, AVERAGE gives you the class average instantly. Businesses use it to find average daily sales, while scientists might use it for average experimental results. It’s versatile for any situation where you need a quick summary of numbers.

Tips: AVERAGE ignores empty cells and text, so your data doesn’t need to be perfect. If you want to exclude zeros or specific values, combine AVERAGE with IF or other formulas. For weighted averages, you may need to use SUMPRODUCT instead.

Example Table

Formula	Example Data	Formula Used	Result
AVERAGE	B1:B3 = [80, 90, 100]	=AVERAGE(B1:B3)	90

In this table, AVERAGE adds 80, 90, and 100 (total = 270) and divides by 3 to get 90. This could be the average score for a student’s three tests.

3. COUNT – Count Numeric Values

The COUNT formula counts how many cells in a range contain numbers. It’s useful for checking how many numeric entries you have, like sales transactions, test scores, or completed tasks. It skips text, empty cells, and errors, focusing only on numbers.

Syntax: =COUNT(range)

What it does: Counts cells in a range that contain numbers.

Use Case: If you’re managing a project and tracking task completion times in cells C1 to C10, COUNT tells you how many tasks have recorded times. Teachers can use it to count submitted test scores, or retailers can count the number of sales entries. It’s a quick way to verify data completeness.

Tips: Use COUNT when you only want to count numbers. For text or other data, use COUNTA (see below). If you need to count specific numbers (e.g., scores above 70), try COUNTIF instead.

Example Table

Formula	Example Data	Formula Used	Result
COUNT	C1:C4 = [10, "Text", 20, empty]	=COUNT(C1:C4)	2

This table shows COUNT finding two cells with numbers (10 and 20), ignoring the text and empty cell.

📘 Also Read: Basic Accounting Principles Explained for Beginners

4. COUNTA – Count Non-Empty Cells

COUNTA counts all non-empty cells in a range, whether they contain numbers, text, or errors. It’s perfect for checking how much data has been entered, like survey responses, inventory items, or customer names.

Syntax: =COUNTA(range)

What it does: Counts all cells in a range that are not empty.

Use Case: If you’re collecting feedback forms, COUNTA counts all responses, even if they’re text like “Great” or “Poor.” Retailers can use it to count inventory entries, and HR managers can count employee records. It’s a great way to ensure you’re not missing data.

Tips: COUNTA counts everything except empty cells. To count only text, subtract COUNT from COUNTA. For specific conditions, use COUNTIFS.

Example Table

Formula	Example Data	Formula Used	Result
COUNTA	A1:A4 = [10, "Yes", "No", empty]	=COUNTA(A1:A4)	3

In this table, COUNTA counts three non-empty cells (10, "Yes", "No"), ignoring the empty cell.

5. IF – Logical Condition

The IF formula checks a condition and returns one value if true and another if false. It’s like making a decision in Excel, such as labeling sales as “High” or “Low” based on a threshold.

Syntax: =IF(condition, value_if_true, value_if_false)

What it does: Tests a condition and returns different results based on whether it’s true or false.

Use Case: As a sales manager, you might want to categorize daily sales in cell A1 as “High” if over $100, otherwise “Low.” IF does this automatically. Teachers can use it to assign pass/fail grades, and project managers can flag overdue tasks.

Tips: Nest multiple IFs for complex decisions, but keep it simple to avoid errors. Combine with AND or OR for advanced logic.

Example Table

Formula	Example Data	Formula Used	Result
IF	B1 = 150	=IF(B1>100, "High", "Low")	High

In this table, since B1 (150) is greater than 100, IF returns “High.”

💡 Must Read: How to Create a Ledger in Excel (With Example)

6. IFERROR – Handle Errors

IFERROR catches errors in calculations and replaces them with a custom value, making your spreadsheet look cleaner. It’s great for handling issues like dividing by zero.

Syntax: =IFERROR(formula, value_if_error)

What it does: Runs a formula and returns a custom value if an error occurs.

Use Case: If you’re calculating profit margins by dividing profit by revenue, some cells might cause errors (e.g., dividing by zero). IFERROR can display “Error” instead of #DIV/0!, keeping your spreadsheet professional.

Tips: Test your formula without IFERROR first to ensure it works. Use sparingly to avoid hiding critical errors.

Example Table

Formula	Example Data	Formula Used	Result
IFERROR	A1 = 10, B1 = 0	=IFERROR(A1/B1, "Error")	Error

In this table, dividing 10 by 0 would cause an error, but IFERROR returns “Error” instead.

7. VLOOKUP – Search Vertically

VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. It’s like looking up a name in a phone book.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What it does: Finds a value in the first column and returns a value from a specified column.

Use Case: If you have a product list with IDs in column A and prices in column B, VLOOKUP can find a product’s price by its ID. It’s ideal for inventory, customer databases, or price lists.

Tips: Use FALSE for exact matches and TRUE for approximate matches. Sort the first column if using TRUE.

Example Table

Formula	Example Data	Formula Used	Result
VLOOKUP	A2:B3 = [101, $50; 102, $75]	=VLOOKUP(101, A2:B3, 2, FALSE)	$50

This table shows VLOOKUP finding product ID 101 and returning its price, $50.

8. HLOOKUP – Search Horizontally

HLOOKUP is like VLOOKUP but searches horizontally across the top row of a table and returns a value from a row below.

Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

What it does: Finds a value in the top row and returns a value from a specified row.

Use Case: If you have monthly sales data with months in the top row, HLOOKUP can find sales for a specific month. It’s great for time-based data like budgets or sales reports.

Tips: Use FALSE for exact matches. Sort the top row for approximate matches.

Example Table

Formula	Example Data	Formula Used	Result
HLOOKUP	A1:C2 = [Jan, Feb, Mar; 200, 300, 400]	=HLOOKUP("Feb", A1:C2, 2, FALSE)	300

In this table, HLOOKUP finds “Feb” in the top row and returns 300 from the second row.

9. INDEX – Returns Value at Specific Row and Column

INDEX returns a value from a specific row and column in a range. It’s like picking a specific cell from a grid of data.

Syntax: =INDEX(range, row_num, column_num)

What it does: Retrieves a value at the intersection of a row and column.

Use Case: If you have a table of student scores, INDEX can pull a specific score from a row and column. It’s useful for dynamic reports or dashboards where you need precise data points.

Tips: Combine INDEX with MATCH for flexible lookups that work in any direction.

Example Table

Formula	Example Data	Formula Used	Result
INDEX	A2:C2 = [Math, 85, Pass]	=INDEX(A2:C2, 1, 2)	85

This table shows INDEX returning 85, the value in the first row and second column of A2:C2.

10. MATCH – Returns Position

MATCH finds the position of a value in a range. It’s often used with INDEX to create powerful, flexible lookups.

Syntax: =MATCH(lookup_value, lookup_array, [match_type])

What it does: Returns the relative position of a value in a range.

Use Case: If you’re looking for a product ID in a list, MATCH tells you its position. This is useful for cross-referencing data or building dynamic reports.

Tips: Use 0 for exact matches. Combine with INDEX for lookups that VLOOKUP can’t handle.

Example Table

Formula	Example Data	Formula Used	Result
MATCH	A1:A3 = [10, 20, 30]	=MATCH(20, A1:A3, 0)	2

In this table, MATCH finds that 20 is in the second position in A1:A3.

11. CONCATENATE / CONCAT – Join Text

CONCATENATE (or CONCAT in newer Excel versions) combines text from multiple cells into one. It’s great for creating full names, addresses, or custom labels.

Syntax: =CONCATENATE(text1, text2, ...) or =CONCAT(text1, text2, ...)

What it does: Joins text strings together.

Use Case: If you have first names in one column and last names in another, CONCAT creates full names. It’s also useful for generating email addresses or product codes.

Tips: Add spaces or commas (e.g., " ") to separate text. CONCAT is more flexible than CONCATENATE in newer Excel versions.

Example Table

Formula	Example Data	Formula Used	Result
CONCAT	A1 = "John", B1 = "Doe"	=CONCAT(A1, " ", B1)	John Doe

This table shows CONCAT joining “John” and “Doe” with a space to create “John Doe.”

12. LEFT / RIGHT / MID – Extract Text

LEFT, RIGHT, and MID extract specific parts of text from a cell. They’re perfect for pulling out pieces like area codes, initials, or product IDs from longer text strings.

Syntax:

• =LEFT(text, num_chars) – Gets characters from the start.
• =RIGHT(text, num_chars) – Gets characters from the end.
• =MID(text, start_num, num_chars) – Gets characters from the middle.

What it does: Extracts a specified number of characters from a text string.

Use Case: If you have phone numbers, LEFT can extract area codes, RIGHT can get the last four digits, and MID can pull a specific section. These are great for cleaning data or preparing reports.

Tips: Combine with LEN to calculate how many characters to extract dynamically.

Example Table

Formula	Example Data	Formula Used	Result
LEFT	A1 = "HelloWorld"	=LEFT(A1, 5)	Hello

In this table, LEFT extracts the first five characters (“Hello”) from “HelloWorld.”

13. LEN – Count Characters

LEN counts the total number of characters in a cell, including spaces. It’s useful for checking the length of text entries, like names, codes, or descriptions.

Syntax: =LEN(text)

What it does: Returns the number of characters in a text string.

Use Case: If you’re preparing data for a system that requires specific text lengths (e.g., usernames or product codes), LEN helps verify compliance. It’s also great for data validation or analyzing text length.

Tips: Use LEN with LEFT, RIGHT, or MID to extract text dynamically. For example, combine with LEFT to get all but the last character.

Example Table

Formula	Example Data	Formula Used	Result
LEN	A1 = "Excel"	=LEN(A1)	5

This table shows LEN counting five characters in “Excel.”

14. NOW / TODAY – Date and Time

NOW and TODAY insert the current date and time into your spreadsheet. They’re great for tracking when data was entered or creating time-sensitive reports.

Syntax:

• =NOW() – Shows current date and time.
• =TODAY() – Shows current date only.

What it does: Returns the current date and/or time, updating automatically.

Use Case: If you’re logging when orders are placed or tasks are completed, NOW and TODAY add timestamps. They’re useful for calculating deadlines or tracking project progress.

Tips: These formulas update every time the spreadsheet recalculates. To keep a static date, copy and paste as values.

Example Table

Formula	Example Data	Formula Used	Result
TODAY	Current date: 7/19/2025	=TODAY()	7/19/2025

In this table, TODAY returns the current date, 7/19/2025.

15. TRIM – Remove Extra Spaces

TRIM cleans up text by removing extra spaces, leaving only single spaces between words. It’s perfect for fixing messy data from imports or user inputs.

Syntax: =TRIM(text)

What it does: Removes all spaces except single spaces between words.

Use Case: If you’re importing customer names or addresses, TRIM ensures they look neat. For example, “ John Doe ” becomes “John Doe.” It’s essential for clean data in reports or databases.

Tips: Use TRIM before text formulas like CONCAT or VLOOKUP to ensure clean data. It doesn’t remove non-breaking spaces, so additional cleanup may be needed for some imports.

Example Table

Formula	Example Data	Formula Used	Result
TRIM	A1 = " Hello World "	=TRIM(A1)	Hello World

This table shows TRIM removing extra spaces, turning “ Hello World ” into “Hello World.”

Conclusion

These 15 Excel formulas are your go-to tools for working smarter with data. From adding numbers with SUM to cleaning text with TRIM, they solve common problems and make your spreadsheets more efficient. The Excel-like tables above show how each formula works in real scenarios, so you can apply them to your own data. Practice these formulas, and you’ll be an Excel pro in no time!

Styling Note: This article uses the Aptos Display font for a modern look. In Excel, apply this font by selecting cells and choosing “Aptos Display” from the font menu. If unavailable, use Calibri or Arial for a similar effect.