Skip to content
Half Stack Python

Excel: Formulas Reference Guide

Table of Contents

Section titled “Table of Contents”

Excel Formulas

Section titled “Excel Formulas”

Useful Excel formulas and functions for data manipulation, analysis, and automation.

Text Functions

Section titled “Text Functions”

Check if Substring Exists in String

Section titled “Check if Substring Exists in String”
=IF(ISNUMBER(SEARCH("ccc", A1)), "TRUE", "FALSE")
  • SEARCH is case-insensitive
  • Returns position if found, error if not
  • ISNUMBER checks if SEARCH succeeded
  • Use FIND for case-sensitive search

Concatenate Strings

Section titled “Concatenate Strings”
=CONCAT(A1, " ", B1)

Alternative methods:

=A1 & " " & B1
=TEXTJOIN(" ", TRUE, A1:B1)

Extract Substrings

Section titled “Extract Substrings”
# Left N characters
=LEFT(A1, 5)


# Right N characters
=RIGHT(A1, 3)


# Middle substring
=MID(A1, 3, 5)  # Start at position 3, length 5

Case Conversion

Section titled “Case Conversion”
=UPPER(A1)   # Convert to uppercase
=LOWER(A1)   # Convert to lowercase
=PROPER(A1)  # Capitalize first letter of each word

Trim and Clean

Section titled “Trim and Clean”
=TRIM(A1)     # Remove extra spaces
=CLEAN(A1)    # Remove non-printable characters

Lookup Functions

Section titled “Lookup Functions”

VLOOKUP

Section titled “VLOOKUP”
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

=VLOOKUP(A2, D:E, 2, FALSE)
  • Searches for A2 in column D
  • Returns value from column E (2nd column)
  • FALSE = exact match

XLOOKUP (Modern Alternative)

Section titled “XLOOKUP (Modern Alternative)”
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Example:

=XLOOKUP(A2, D:D, E:E, "Not Found")

INDEX + MATCH

Section titled “INDEX + MATCH”
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example:

=INDEX(E:E, MATCH(A2, D:D, 0))

Conditional Functions

Section titled “Conditional Functions”

IF Statement

Section titled “IF Statement”
=IF(condition, value_if_true, value_if_false)

Examples:

=IF(A1 > 100, "High", "Low")
=IF(AND(A1 > 50, B1 < 100), "Valid", "Invalid")
=IF(OR(A1 = "Yes", B1 = "Yes"), "Approved", "Denied")

Nested IF

Section titled “Nested IF”
=IF(A1 >= 90, "A", IF(A1 >= 80, "B", IF(A1 >= 70, "C", "F")))

IFS (Multiple Conditions)

Section titled “IFS (Multiple Conditions)”
=IFS(A1 >= 90, "A", A1 >= 80, "B", A1 >= 70, "C", TRUE, "F")

SWITCH

Section titled “SWITCH”
=SWITCH(expression, value1, result1, value2, result2, default)

Example:

=SWITCH(A1, "Red", 1, "Blue", 2, "Green", 3, 0)

Logical Functions

Section titled “Logical Functions”

AND, OR, NOT

Section titled “AND, OR, NOT”
=AND(A1 > 10, B1 < 20)
=OR(A1 = "Yes", B1 = "Yes")
=NOT(A1 = "No")

ISBLANK, ISERROR

Section titled “ISBLANK, ISERROR”
=IF(ISBLANK(A1), "Empty", A1)
=IFERROR(A1/B1, "Error")

Date and Time Functions

Section titled “Date and Time Functions”

Current Date and Time

Section titled “Current Date and Time”
=TODAY()      # Current date
=NOW()        # Current date and time

Date Arithmetic

Section titled “Date Arithmetic”
=DATE(2024, 12, 31)           # Create date
=YEAR(A1)                     # Extract year
=MONTH(A1)                    # Extract month
=DAY(A1)                      # Extract day
=EOMONTH(A1, 0)              # Last day of month
=WORKDAY(A1, 5)              # 5 workdays from A1
=NETWORKDAYS(A1, B1)         # Workdays between dates

Date Difference

Section titled “Date Difference”
=DATEDIF(A1, B1, "D")  # Days
=DATEDIF(A1, B1, "M")  # Months
=DATEDIF(A1, B1, "Y")  # Years

Mathematical Functions

Section titled “Mathematical Functions”

Basic Math

Section titled “Basic Math”
=SUM(A1:A10)
=AVERAGE(A1:A10)
=MIN(A1:A10)
=MAX(A1:A10)
=COUNT(A1:A10)      # Count numbers
=COUNTA(A1:A10)     # Count non-empty cells

Conditional Aggregation

Section titled “Conditional Aggregation”
=SUMIF(range, criteria, [sum_range])
=COUNTIF(range, criteria)
=AVERAGEIF(range, criteria, [average_range])

Examples:

=SUMIF(A:A, ">100", B:B)
=COUNTIF(A:A, "Yes")
=AVERAGEIF(A:A, "<>0")

Multiple Criteria

Section titled “Multiple Criteria”
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

Example:

=SUMIFS(C:C, A:A, "Product A", B:B, ">100")

Rounding

Section titled “Rounding”
=ROUND(A1, 2)      # Round to 2 decimal places
=ROUNDUP(A1, 0)    # Round up to integer
=ROUNDDOWN(A1, 0)  # Round down to integer
=CEILING(A1, 5)    # Round up to nearest 5
=FLOOR(A1, 5)      # Round down to nearest 5

Statistical Functions

Section titled “Statistical Functions”

Common Statistics

Section titled “Common Statistics”
=MEDIAN(A1:A10)
=MODE.SNGL(A1:A10)     # Most common value
=STDEV.S(A1:A10)       # Standard deviation (sample)
=STDEV.P(A1:A10)       # Standard deviation (population)
=VAR.S(A1:A10)         # Variance (sample)

Percentiles and Quartiles

Section titled “Percentiles and Quartiles”
=PERCENTILE.INC(A1:A10, 0.95)  # 95th percentile
=QUARTILE.INC(A1:A10, 1)       # First quartile

Array Formulas

Section titled “Array Formulas”

FILTER (Dynamic Arrays)

Section titled “FILTER (Dynamic Arrays)”
=FILTER(array, include, [if_empty])

Example:

=FILTER(A2:C100, B2:B100 > 100, "No results")

SORT

Section titled “SORT”
=SORT(array, [sort_index], [sort_order], [by_col])

Example:

=SORT(A2:C100, 2, -1)  # Sort by 2nd column, descending

UNIQUE

Section titled “UNIQUE”
=UNIQUE(array, [by_col], [exactly_once])

Example:

=UNIQUE(A2:A100)

Data Cleaning

Section titled “Data Cleaning”

Remove Duplicates

Section titled “Remove Duplicates”
=UNIQUE(A1:A100)

Find and Replace

Section titled “Find and Replace”
=SUBSTITUTE(text, old_text, new_text, [instance_num])

Example:

=SUBSTITUTE(A1, "old", "new")
=SUBSTITUTE(A1, " ", "", )  # Remove all spaces

Split Text

Section titled “Split Text”
=TEXTSPLIT(text, col_delimiter, [row_delimiter])

Example:

=TEXTSPLIT(A1, ",")  # Split by comma

Legacy method:

=LEFT(A1, FIND(",", A1) - 1)  # Before comma
=MID(A1, FIND(",", A1) + 1, 999)  # After comma

Advanced Techniques

Section titled “Advanced Techniques”

Dynamic Named Ranges

Section titled “Dynamic Named Ranges”
=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)

Array Constants

Section titled “Array Constants”
=SUM(A1:A10 * B1:B10)  # Element-wise multiplication

Nested Functions

Section titled “Nested Functions”
=IFERROR(VLOOKUP(A1, D:E, 2, FALSE), "Not Found")
=SUMPRODUCT((A1:A10 > 100) * (B1:B10 < 50) * C1:C10)

Custom Sorting with INDEX

Section titled “Custom Sorting with INDEX”
=INDEX(A:A, MATCH(SMALL(B:B, ROW()), B:B, 0))

Tips and Tricks

Section titled “Tips and Tricks”

Absolute vs Relative References

Section titled “Absolute vs Relative References”
A1    # Relative (both row and column change)
$A$1  # Absolute (neither changes)
$A1   # Mixed (column fixed, row changes)
A$1   # Mixed (row fixed, column changes)

Named Ranges

Section titled “Named Ranges”

Instead of:

=SUM(Sheet1!$A$1:$A$100)

Define name “SalesData” for A1:A100, then use:

=SUM(SalesData)

Error Handling

Section titled “Error Handling”
=IFERROR(formula, value_if_error)
=IFNA(formula, value_if_na)

Array Formula Entry

Section titled “Array Formula Entry”
  • Excel 365: Formulas automatically spill
  • Older Excel: Press Ctrl+Shift+Enter

Common Patterns

Section titled “Common Patterns”

Running Total

Section titled “Running Total”
=SUM($A$2:A2)  # Copy down for cumulative sum

Rank Values

Section titled “Rank Values”
=RANK(A2, $A$2:$A$100, 0)  # 0 = descending

Remove Non-Numeric Characters

Section titled “Remove Non-Numeric Characters”
=SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW($1:$99), 1)) * ROW($1:$99), 0), ROW($1:$99)) + 1, 1) * 10^ROW($1:$99)/10)

Simpler with REGEX (if available):

=REGEXEXTRACT(A1, "\d+")

Resources

Section titled “Resources”