📚 Complete Excel Formulas Guide
Master Excel with our comprehensive guide to the most important formulas and functions. Each formula includes syntax, examples, and practical use cases.
💡 Pro Tip: You can generate any of these formulas instantly using our AI Formula Generator!
Math & Statistical Functions
SUM
Syntax:
=SUM(number1, number2, ...)Description:
Adds up all numbers in a range or list of numbers
Example:
=SUM(A1:A10)Use Case:
Calculate total sales, expenses, or any numerical totals
Use SUM to quickly add up columns or rows of numbers
AVERAGE
Syntax:
=AVERAGE(number1, number2, ...)Description:
Calculates the arithmetic mean of a group of numbers
Example:
=AVERAGE(B1:B20)Use Case:
Find average scores, temperatures, or performance metrics
Automatically excludes empty cells and text from calculation
COUNT
Syntax:
=COUNT(value1, value2, ...)Description:
Counts the number of cells that contain numbers
Example:
=COUNT(A1:A50)Use Case:
Count how many numeric entries are in a range
Only counts cells with numbers, not text or empty cells
MAX
Syntax:
=MAX(number1, number2, ...)Description:
Returns the largest value in a set of values
Example:
=MAX(C1:C100)Use Case:
Find highest score, maximum temperature, or peak sales
Useful for finding the best performance in datasets
MIN
Syntax:
=MIN(number1, number2, ...)Description:
Returns the smallest value in a set of values
Example:
=MIN(D1:D100)Use Case:
Find lowest score, minimum temperature, or bottom sales
Great for identifying worst performance or minimum requirements
Lookup & Reference Functions
VLOOKUP
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)Description:
Searches for a value in the first column and returns a value in the same row from specified column
Example:
=VLOOKUP(E2, A:C, 3, FALSE)Use Case:
Find product prices, employee details, or any table lookup
Use FALSE for exact match, TRUE for approximate match
HLOOKUP
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)Description:
Like VLOOKUP but searches horizontally in the first row
Example:
=HLOOKUP(F2, A1:Z5, 3, FALSE)Use Case:
Look up data in horizontal tables or matrices
Less common than VLOOKUP but useful for horizontal data layouts
INDEX
Syntax:
=INDEX(array, row_num, column_num)Description:
Returns the value at a specific row and column intersection
Example:
=INDEX(A1:C10, 5, 2)Use Case:
Get specific values from tables when you know the position
More flexible than VLOOKUP, can look left or right
MATCH
Syntax:
=MATCH(lookup_value, lookup_array, match_type)Description:
Returns the position of an item in an array
Example:
=MATCH('Apple', A1:A10, 0)Use Case:
Find the position of items in lists
Often combined with INDEX for powerful lookup formulas
Logical Functions
IF
Syntax:
=IF(logical_test, value_if_true, value_if_false)Description:
Returns one value if condition is true, another if false
Example:
=IF(A1>100, 'High', 'Low')Use Case:
Grade classifications, status indicators, conditional calculations
Can be nested for multiple conditions, but consider IFS for clarity
AND
Syntax:
=AND(logical1, logical2, ...)Description:
Returns TRUE only if all conditions are true
Example:
=AND(A1>50, B1<100)Use Case:
Check if multiple criteria are met simultaneously
Often used inside IF functions for complex conditions
OR
Syntax:
=OR(logical1, logical2, ...)Description:
Returns TRUE if any condition is true
Example:
=OR(A1='Yes', B1='Maybe')Use Case:
Check if at least one of several conditions is met
Useful for flexible conditional logic
NOT
Syntax:
=NOT(logical)Description:
Reverses the logic of its argument
Example:
=NOT(A1='Complete')Use Case:
Find items that don't match a specific criteria
Useful for finding exceptions or opposite conditions
Text Functions
CONCATENATE
Syntax:
=CONCATENATE(text1, text2, ...)Description:
Joins two or more text strings into one string
Example:
=CONCATENATE(A1, ' ', B1)Use Case:
Combine first and last names, create full addresses
The & operator can also be used: A1&' '&B1
LEFT
Syntax:
=LEFT(text, num_chars)Description:
Returns specified number of characters from the start of text
Example:
=LEFT(A1, 3)Use Case:
Extract area codes, prefixes, or beginning portions of text
Great for extracting consistent patterns from the left side
RIGHT
Syntax:
=RIGHT(text, num_chars)Description:
Returns specified number of characters from the end of text
Example:
=RIGHT(A1, 4)Use Case:
Extract file extensions, suffixes, or ending portions
Useful for extracting data from the right side of text
MID
Syntax:
=MID(text, start_num, num_chars)Description:
Returns characters from the middle of text
Example:
=MID(A1, 3, 5)Use Case:
Extract middle portions like account numbers or codes
Perfect for extracting specific portions from fixed-format text
LEN
Syntax:
=LEN(text)Description:
Returns the number of characters in text
Example:
=LEN(A1)Use Case:
Check text length for validation or formatting
Useful for data validation and quality checks
Date & Time Functions
TODAY
Syntax:
=TODAY()Description:
Returns the current date
Example:
=TODAY()Use Case:
Date stamps, age calculations, days until deadline
Updates automatically when file is opened or recalculated
NOW
Syntax:
=NOW()Description:
Returns the current date and time
Example:
=NOW()Use Case:
Timestamp creation, time tracking, real-time calculations
Updates continuously, use with caution in time-sensitive calculations
DATE
Syntax:
=DATE(year, month, day)Description:
Creates a date from year, month, and day values
Example:
=DATE(2024, 12, 25)Use Case:
Build dates from separate components, date calculations
Useful when date parts are in separate cells
DATEDIF
Syntax:
=DATEDIF(start_date, end_date, unit)Description:
Calculates the difference between two dates
Example:
=DATEDIF(A1, B1, 'Y')Use Case:
Calculate age, years of service, project duration
Units: 'Y' for years, 'M' for months, 'D' for days
Conditional Functions
COUNTIF
Syntax:
=COUNTIF(range, criteria)Description:
Counts cells that meet a specific condition
Example:
=COUNTIF(A:A, 'Sales')Use Case:
Count specific items, track occurrences, data analysis
Use wildcards * and ? for partial matches
SUMIF
Syntax:
=SUMIF(range, criteria, sum_range)Description:
Sums cells that meet a specific condition
Example:
=SUMIF(A:A, 'Sales', B:B)Use Case:
Sum values based on categories or conditions
Sum_range is optional if same as range
AVERAGEIF
Syntax:
=AVERAGEIF(range, criteria, average_range)Description:
Averages cells that meet a specific condition
Example:
=AVERAGEIF(A:A, '>100', B:B)Use Case:
Average performance by category or above/below thresholds
Great for conditional statistical analysis
COUNTIFS
Syntax:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)Description:
Counts cells that meet multiple conditions
Example:
=COUNTIFS(A:A, 'Sales', B:B, '>1000')Use Case:
Complex counting with multiple criteria
More powerful than COUNTIF for complex conditions
Ready to Generate Custom Formulas?
Use our AI-powered generator to create any Excel formula from plain English descriptions
🚀 Try Formula Generator