Introduction: Formula Fundamentals

Before diving into specific functions, let's cover the basics that apply to all formulas.

Formula Structure

Every formula starts with an equals sign (=). After that, you can use:

• Values: Numbers (=5+3), text (="Hello")
• Cell References: =A1+B1
• Functions: =SUM(A1:A10)
• Operators: +, -, *, /, ^, &

Reference Types

• Relative: A1 – Changes when copied
• Absolute: $A$1 – Stays fixed when copied
• Mixed: $A1 or A$1 – Partially fixed

Use F4 to toggle between reference types while editing.

Mathematical Functions

These functions perform calculations on numbers.

SUM – Add Numbers

=SUM(A1:A10)           // Sum a range
=SUM(A1,B1,C1)         // Sum specific cells
=SUM(A:A)              // Sum entire column

SUMIF / SUMIFS – Conditional Sum

=SUMIF(range, criteria, sum_range)
=SUMIF(A:A, "North", B:B)          // Sum B where A is "North"
=SUMIF(A:A, ">100")                // Sum values > 100

=SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)
=SUMIFS(C:C, A:A, "North", B:B, ">1000")  // Multiple conditions

AVERAGE – Calculate Mean

=AVERAGE(A1:A10)
=AVERAGEIF(A:A, ">0", B:B)         // Average with condition
=AVERAGEIFS(C:C, A:A, "North", B:B, ">0")  // Multiple conditions

COUNT Functions

=COUNT(A:A)            // Count numbers
=COUNTA(A:A)           // Count non-empty cells
=COUNTBLANK(A:A)       // Count empty cells
=COUNTIF(A:A, "Apple") // Count specific value
=COUNTIFS(A:A, "North", B:B, ">1000")  // Multiple conditions

ROUND Functions

=ROUND(A1, 2)          // Round to 2 decimal places
=ROUNDUP(A1, 0)        // Always round up
=ROUNDDOWN(A1, 0)      // Always round down
=MROUND(A1, 5)         // Round to nearest 5
=CEILING(A1, 10)       // Round up to nearest 10
=FLOOR(A1, 10)         // Round down to nearest 10

Other Math Functions

=ABS(A1)               // Absolute value
=MOD(A1, 3)            // Remainder (A1 divided by 3)
=POWER(A1, 2)          // Or =A1^2 for square
=SQRT(A1)              // Square root
=PRODUCT(A1:A5)        // Multiply all values
=QUOTIENT(A1, B1)      // Integer division

Text Functions

Functions for manipulating and extracting text.

Combining Text

=CONCAT(A1, " ", B1)         // Join text
=TEXTJOIN(", ", TRUE, A1:A5) // Join with delimiter, skip blanks
=A1 & " " & B1               // Concatenate with &

Extracting Text

=LEFT(A1, 5)           // First 5 characters
=RIGHT(A1, 3)          // Last 3 characters
=MID(A1, 3, 5)         // 5 characters starting at position 3

Finding and Replacing

=FIND("@", A1)         // Position of @ (case-sensitive)
=SEARCH("apple", A1)   // Position (case-insensitive)
=SUBSTITUTE(A1, "old", "new")        // Replace text
=REPLACE(A1, 3, 5, "NEW")            // Replace by position

Text Transformation

=UPPER(A1)             // UPPERCASE
=LOWER(A1)             // lowercase
=PROPER(A1)            // Title Case
=TRIM(A1)              // Remove extra spaces
=CLEAN(A1)             // Remove non-printable characters

Text Information

=LEN(A1)               // Character count
=EXACT(A1, B1)         // Case-sensitive comparison
=REPT(A1, 3)           // Repeat text 3 times

Number Formatting

=TEXT(A1, "0.00")                    // Format as text
=TEXT(A1, "$#,##0.00")               // Currency format
=TEXT(A1, "MM/DD/YYYY")              // Date format
=VALUE(A1)                           // Text to number

Lookup & Reference Functions

The workhorses of data retrieval.

XLOOKUP (Recommended)

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

=XLOOKUP(A1, B:B, C:C)                    // Basic lookup
=XLOOKUP(A1, B:B, C:C, "Not found")       // With error handling
=XLOOKUP(A1, B:B, C:E)                    // Return multiple columns

VLOOKUP (Legacy)

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

=VLOOKUP(A1, B:D, 3, FALSE)              // Exact match
=VLOOKUP(A1, B:D, 3, TRUE)               // Approximate match

INDEX + MATCH (Flexible)

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

=INDEX(C:C, MATCH(A1, B:B, 0))           // Basic lookup
=INDEX(C:C, MATCH(A1&B1, A:A&B:B, 0))    // Multiple criteria

Other Reference Functions

=INDIRECT("A" & B1)           // Create reference from text
=OFFSET(A1, 2, 3)             // Reference 2 rows down, 3 columns right
=CHOOSE(A1, "Mon", "Tue", "Wed")  // Choose from list by index
=ROW(A1)                      // Return row number
=COLUMN(A1)                   // Return column number

Date & Time Functions

Working with dates and times.

Current Date/Time

=TODAY()               // Current date
=NOW()                 // Current date and time

Extracting Components

=YEAR(A1)              // Year from date
=MONTH(A1)             // Month (1-12)
=DAY(A1)               // Day of month
=WEEKDAY(A1)           // Day of week (1-7)
=WEEKNUM(A1)           // Week number
=HOUR(A1)              // Hour from time
=MINUTE(A1)            // Minute from time
=SECOND(A1)            // Second from time

Creating Dates

=DATE(2024, 12, 25)    // Create date from parts
=TIME(14, 30, 0)       // Create time (2:30 PM)
=DATEVALUE("1/15/2024")  // Text to date
=TIMEVALUE("2:30 PM")    // Text to time

Date Calculations

=EDATE(A1, 3)          // Add 3 months
=EOMONTH(A1, 0)        // End of current month
=EOMONTH(A1, 1)        // End of next month
=WORKDAY(A1, 10)       // Add 10 working days
=NETWORKDAYS(A1, B1)   // Working days between dates
=DATEDIF(A1, B1, "Y")  // Years between dates
=DATEDIF(A1, B1, "M")  // Months between dates
=DATEDIF(A1, B1, "D")  // Days between dates

Logical Functions

Decision-making formulas.

IF – Basic Condition

=IF(condition, value_if_true, value_if_false)

=IF(A1>100, "High", "Low")
=IF(A1="", "Empty", A1)

Nested IF

=IF(A1>100, "High", IF(A1>50, "Medium", "Low"))

IFS – Multiple Conditions (Excel 2019+)

=IFS(A1>100, "High", A1>50, "Medium", TRUE, "Low")

AND, OR, NOT

=AND(A1>0, B1>0)       // Both conditions true
=OR(A1>100, B1>100)    // Either condition true
=NOT(A1>100)           // Inverts TRUE/FALSE

=IF(AND(A1>0, B1>0), "Both positive", "Not both positive")

SWITCH (Excel 2019+)

=SWITCH(A1, 1, "One", 2, "Two", 3, "Three", "Other")

XOR – Exclusive OR

=XOR(A1>10, B1>10)     // TRUE if exactly one is true

Statistical Functions

For data analysis.

Central Tendency

=AVERAGE(A:A)          // Mean
=MEDIAN(A:A)           // Median (middle value)
=MODE.SNGL(A:A)        // Most common value
=MODE.MULT(A:A)        // All modes (array formula)

Dispersion

=STDEV.S(A:A)          // Sample standard deviation
=STDEV.P(A:A)          // Population standard deviation
=VAR.S(A:A)            // Sample variance
=VAR.P(A:A)            // Population variance

Ranking

=MAX(A:A)              // Largest value
=MIN(A:A)              // Smallest value
=LARGE(A:A, 2)         // 2nd largest
=SMALL(A:A, 3)         // 3rd smallest
=RANK.EQ(A1, A:A)      // Rank of value
=PERCENTILE.INC(A:A, 0.9)  // 90th percentile
=QUARTILE.INC(A:A, 1)      // 1st quartile

Array Formulas

Work with multiple values at once.

Dynamic Arrays (Excel 365)

=UNIQUE(A:A)                   // Unique values
=SORT(A:A)                     // Sort ascending
=SORT(A:A, 1, -1)              // Sort descending
=FILTER(A:D, B:B="North")      // Filter rows
=SEQUENCE(10)                  // Numbers 1-10
=SEQUENCE(5, 3)                // 5 rows × 3 columns
=RANDARRAY(5, 3)               // Random numbers

FILTER Examples

=FILTER(A:D, B:B>1000)         // Single condition
=FILTER(A:D, (B:B="North")*(C:C>1000))  // AND (multiply)
=FILTER(A:D, (B:B="North")+(B:B="South"))  // OR (add)
=FILTER(A:D, B:B>1000, "No results")  // With fallback

Legacy Array Formulas (Ctrl+Shift+Enter)

{=SUM(A:A*B:B)}               // Sum of products
{=AVERAGE(IF(A:A>0, B:B))}    // Conditional average

Error Handling

Manage formula errors gracefully.

IFERROR

=IFERROR(A1/B1, 0)             // Return 0 if error
=IFERROR(VLOOKUP(...), "Not found")

IFNA

=IFNA(XLOOKUP(...), "No match")  // Handle #N/A only

Error Checking

=ISERROR(A1)           // TRUE if any error
=ISNA(A1)              // TRUE if #N/A
=ISNUMBER(A1)          // TRUE if number
=ISTEXT(A1)            // TRUE if text
=ISBLANK(A1)           // TRUE if empty

Common Errors

Formula Best Practices

1. Use Named Ranges

Instead of:

=SUMIF($A$2:$A$1000, "North", $C$2:$C$1000)

Create named ranges and use:

=SUMIF(Region, "North", Sales)

2. Break Complex Formulas

Instead of one massive formula, use helper columns:

• Easier to debug
• Easier to understand
• Better performance

3. Avoid Volatile Functions When Possible

These recalculate every time anything changes:

• NOW(), TODAY()
• RAND(), RANDBETWEEN()
• OFFSET(), INDIRECT()

4. Use Tables

Convert ranges to Tables (Ctrl + T):

• Structured references ([@Column])
• Auto-expanding ranges
• Easier maintenance

5. Document Your Formulas

Use cell comments or a documentation sheet to explain complex logic.

6. Test Edge Cases

Always test with:

• Empty cells
• Zero values
• Negative numbers
• Text where numbers expected
• Very large numbers