Understanding Excel Formulas & Functions

Microsoft Excel is more than a dataentry tool; its a powerful calculation engine. Knowing the right formulas and functions can turn a simple spreadsheet into an analytical powerhouse. This guide covers the fundamentals, popular categories, and useful tips for everyday Excel users.

1. Basics of an Excel Formula

Every formula in Excel starts with an equals sign (=). After the sign, you can combine:

Operators +, -, *, /, ^ (exponent)
Cell references A1, B$2, $C3
Functions predefined calculations such as SUM() or VLOOKUP()
Constants numbers or text placed directly in the formula

Simple Example

=A2+B2*10

This adds the value in A2 to ten times the value in B2. Parentheses can be used to control the order of operations:

=(A2+B2)*10

2. Common Categories of Functions

2.1 Math & Trig

Function	Purpose	Example
`SUM(range)`	Adds all numbers in a range	`=SUM(A1:A10)`
`AVERAGE(range)`	Calculates the mean	`=AVERAGE(B1:B5)`
`MIN(range)`	Smallest value	`=MIN(C1:C20)`
`MAX(range)`	Largest value	`=MAX(D1:D20)`
`ROUND(number, num_digits)`	Rounds to a specified number of digits	`=ROUND(E2,2)`
`POWER(number, power)`	Raises a number to a power	`=POWER(F2,3)`

2.2 Text

Function	Purpose	Example
`CONCATENATE(text1, text2, )`	Joins several strings	`=CONCATENATE(A2," ",B2)`
`LEFT(text, [num_chars])`	Returns the leftmost characters	`=LEFT(C2,3)`
`RIGHT(text, [num_chars])`	Returns the rightmost characters	`=RIGHT(D2,2)`
`MID(text, start_num, num_chars)`	Extracts a substring	`=MID(E2,5,4)`
`LEN(text)`	Length of a string	`=LEN(F2)`
`TRIM(text)`	Removes extra spaces	`=TRIM(G2)`

2.3 Logical

Logical functions help you test conditions and return different results.

=IF(A2>100,"High","Low")

Other useful logical functions:

AND(condition1, condition2,)
OR(condition1, condition2,)
NOT(logical)
IFS(logical_test1, value_if_true1, ) modern replacement for nested IFs

2.4 Lookup & Reference

These functions search for data in tables.

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

Excel now encourages XLOOKUP because its more flexible:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Other lookup helpers:

HLOOKUP horizontal lookup
INDEX & MATCH powerful combination for dynamic ranges
LOOKUP legacy function

2.5 Date & Time

Function	Purpose	Example
`TODAY()`	Current date	`=TODAY()`
`NOW()`	Current date & time	`=NOW()`
`DATE(year, month, day)`	Creates a date value	`=DATE(2026,6,6)`
`YEAR(date)`	Extracts year	`=YEAR(A2)`
`MONTH(date)`	Extracts month	`=MONTH(A2)`
`DAY(date)`	Extracts day	`=DAY(A2)`
`NETWORKDAYS(start, end, [holidays])`	Working days between two dates	`=NETWORKDAYS(B2,C2)`
`EDATE(start_date, months)`	Date after a number of months	`=EDATE(D2,3)`

3. Building Complex Formulas

Complex calculations are usually a nesting of several functions. The key is to keep the logic clear.

3.1 Example: ConditionalSum

Sum sales for a specific region only if the sales amount exceeds $1,000.

=SUMIFS(Sales!$C$2:$C$1000, Sales!$A$2:$A$1000, "North", Sales!$C$2:$C$1000, ">1000")

3.2 Example: Dynamic Ranking

Rank employees by performance score, ignoring ties.

=RANK.EQ(B2,$B$2:$B$20,0)

3.3 Example: Extracting a Domain from an Email

=RIGHT(A2, LEN(A2)-FIND("@",A2))

This finds the position of @ and returns everything to the right of it.

4. Tips for Working with Formulas

Use absolute references ($A$1) when a cell must stay constant while copying a formula.
Break long formulas into helper columns it improves readability and debugging.
Use Ctrl + ` (grave accent) to toggle the display of formulas versus results.
Leverage named ranges for clarity, e.g., =SUM(Sales) instead of =SUM(A2:A100).
Employ errorhandling functions like IFERROR(value, value_if_error) to hide #DIV/0! or #N/A messages.
Test with sample data before applying a formula to a large dataset.

5. Frequently Asked Questions

Q: Why does my `VLOOKUP` return #N/A?

A: Common reasons include:

The lookup value does not exist in the first column of the table array.
You used an exact match (FALSE) but the data contains extra spaces wrap the lookup column with TRIM().
The range is not locked with absolute references, causing it to shift when copied.

Q: How can I calculate a running total?

Place this in cell B2 and copy down:

=B2 + IFERROR(B1,0)

Or use the table feature and enable Total Row.

Q: What is the difference between `COUNT` and `COUNTA`?

COUNT counts only numeric entries, while COUNTA counts any nonblank cell (numbers, text, logical values, errors).

6. Resources for Further Learning

Microsofts official Excel Support site
Chandoo.org tutorials ranging from beginner to advanced
ExcelJet quick reference for functions and formulas
Books: Excel Formulas by John Walkenbach and Power Query for Excel by Chris Webb
Online courses on platforms such as Coursera, Udemy, and LinkedIn Learning

Understanding and mastering Excel formulas is a journey. Start with the basics, experiment with real data, and gradually explore advanced functions. With practice, youll unlock the full analytical power of Excel and turn raw data into actionable insight.

Reference Files For Excel Formulas And Functions

Screenshoot