Excel Formula Guide Tips, Examples & Best Practices

Basic Concepts

Excel formulas always start with an equal sign (=). Anything after the equal sign is interpreted as a calculation, not plain text.

=A1+B1          'Adds the values in A1 and B1=SUM(C1:C10)    'Adds all numbers in the range C1:C10

Key points to remember:

• Formulas can reference cells, ranges, constants, functions, or a combination of all three.
• Relative references (e.g., A1) change when copied to another cell. Absolute references (e.g., $A$1) stay fixed.
• Mixed references ($A1 or A$1) lock either the column or the row.

Operators & Order of Operations

Excel supports arithmetic, comparison and text concatenation operators.

Operator	Name	Example
+	Addition	=5+3
-	Subtraction	=10-2
*	Multiplication	=4*7
/	Division	=20/4
^	Exponent	=2^3
&	Concatenate text	=A2 & " " & B2
=	Equal to	=A1=B1
<>	Not equal	=A1<>B1
>	Greater than	=A1>B1
<	Less than	=A1<B1

Excel follows the standard PEMDAS rule (Parentheses, Exponents, Multiplication/Division, Addition/Subtraction). Use parentheses to force the desired order.

= (A1+B1) * C1   'Multiplication happens after the sum= A1 + B1 * C1    'Multiplication happens before addition

Common Functions

Mathematical

• SUM(range) Adds all numbers.
• AVERAGE(range) Returns the arithmetic mean.
• MIN(range) / MAX(range) Smallest / largest values.
• ROUND(number, num_digits) Rounds to a specified number of digits.
• INT(number) Returns the integer portion.

Statistical

• COUNT(range) Counts cells containing numbers.
• COUNTA(range) Counts nonempty cells.
• COUNTIF(range, criteria) Counts cells that meet a condition.
• MEDIAN(range) Returns the median value.

Logical

• IF(logical_test, value_if_true, value_if_false)
• AND(logical1, ) and OR(logical1, )
• NOT(logical)

Example of a nested IF:

=IF(A1<50, "Fail",    IF(A1<70, "Pass",    IF(A1<90, "Merit", "Distinction")))

Working with Text

Text functions help you clean, split, or combine strings.

• CONCATENATE(text1, text2,) Joins strings (now replaced by CONCAT and the & operator).
• LEFT(text, [num_chars]) Returns the leftmost characters.
• RIGHT(text, [num_chars]) Returns the rightmost characters.
• MID(text, start_num, num_chars) Extracts a substring.
• LEN(text) Length of a string.
• TRIM(text) Removes leading and trailing spaces.
• UPPER(text), LOWER(text), PROPER(text) Change case.
• SEARCH(find_text, within_text, [start_num]) Caseinsensitive locate.
• FIND(find_text, within_text, [start_num]) Casesensitive locate.

Combining text with numbers:

= "Total: $" & TEXT(SUM(B2:B10), "0.00")

Lookup & Reference

Finding data in tables is a core Excel skill. The two most used functions are VLOOKUP and INDEX/MATCH. Newer versions also provide XLOOKUP.

VLOOKUP

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

Example fetch a price for a product code:

=VLOOKUP(A2, Products!$A$2:$D$100, 3, FALSE)

INDEX / MATCH

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Why prefer this combo?

• Works when the lookup column is not the leftmost column.
• Better performance on large data sets.

XLOOKUP (Excel 365 / 2019+)

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

It replaces both VLOOKUP and HLOOKUP and adds builtin error handling.

Date & Time Calculations

Excel stores dates as serial numbers (1 = Jan11900). Time is stored as a fraction of a day.

• DATE(year, month, day) Creates a date.
• TIME(hour, minute, second) Creates a time.
• NOW() Current date & time.
• TODAY() Current date only.
• YEAR(date), MONTH(date), DAY(date) Extract parts.
• EDATE(start_date, months) Adds months.
• DAYS(end_date, start_date) Number of days between two dates.

Calculate days left until a deadline:

=MAX(0, DATEDIF(TODAY(), B2, "d"))

Error Handling

Formulas often return errors such as #DIV/0! or #N/A. Use the following functions to manage them.

• IFERROR(value, value_if_error) Returns the second argument when the first results in any error.
• IFNA(value, value_if_na) Specific for #N/A errors.

Example safe division:

=IFERROR(A1/B1, "N/A")

Advanced Techniques

Array Formulas (Dynamic Arrays)

Modern Excel automatically spills results of functions that return multiple values.

=SORT(FILTER(A2:C100, B2:B100="North"))

This single formula filters rows for the North region and sorts them alphabetically.

LET Function

Assign names to calculation steps, improving readability and performance.

=LET(        total, SUM(D2:D20),        tax, total*0.07,        total+tax    )

Lambda Functions

Define custom reusable functions directly in the workbook.

=LAMBDA(x, y, x^2 + y^2)(3,4)   'Returns 25

Conditional Formatting with Formulas

Use a formula to highlight cells that meet complex criteria. Example: Highlight dates that are past due.

=AND(ISNUMBER(A2), A2<TODAY())

Productivity Tips

• Name ranges (FormulasName Manager) to make formulas selfdescribing, e.g., =SUM(Sales_Q1).
• Press F4 after selecting a reference to toggle absolute/relative modes quickly.
• Use Ctrl+` (grave accent) to toggle the display of formulas on the sheet.
• Leverage AutoSum and Quick Analysis for oneclick totals, percentages, and charts.
• When editing a long formula, press Alt+Enter to insert line breaks for readability.
• Document complex formulas with a comment (rightclick Insert Comment) so collaborators understand the logic.

Conclusion

Mastering Excel formulas empowers you to turn raw data into actionable insight. Start with the basicsoperators, simple functions, and proper referencingthen explore lookup functions, date arithmetic, and modern dynamic array capabilities. With practice and the productivity shortcuts above, youll create robust, maintainable spreadsheets that save time and reduce errors.