By /

⭐ Excel Functions Complete Guide: From Basics to Advanced Use

Contents

Your Comprehensive Reference to Mastering Formulas, Logic, Lookup, Text, Math, Error Handling, and More

Excel functions are the backbone of data analysis, business reporting, automation, and decision-making. While Excel offers a powerful environment for storing and managing data, its true strength lies in how functions transform raw numbers into meaningful results.

This guide serves as the pillar article for everything related to Excel functions—from basic formulas and arithmetic, to advanced logical functions, lookup operations, text manipulation, dynamic arrays, and error handling techniques. Whether you’re a beginner learning SUM and IF, or an experienced analyst building nested formulas with XLOOKUP and FILTER, this guide will help you understand Excel functions deeply and use them with confidence.

1. Introduction: Why Excel Functions Matter

Functions enable Excel to:

  • calculate numbers
  • process data
  • analyze trends
  • automate tasks
  • validate entries
  • clean text
  • organize workflow
  • support decision-making

Without functions, Excel would simply be a grid of cells.
With functions, Excel becomes a calculator, a database, and an analysis tool.

Mastering Excel functions gives you:

  • speed
  • accuracy
  • scalability
  • flexibility
  • reduced manual work

This guide covers everything you need to build formulas with confidence.

2. Understanding Excel Formula Structure

Every Excel function begins with =

2.1 Formula Pattern

=FUNCTION_NAME(argument1, argument2, ...)

2.2 Operators Combine Functions

  • arithmetic: +, -, *, /, ^
  • comparison: =, <, >, <=, >=, <>
  • text: &

2.3 Order of Evaluation

Excel follows:

  1. Parentheses
  2. Exponents
  3. Multiplication/Division
  4. Addition/Subtraction

Recommended Articles

3. Cell References: Relative, Absolute, and Mixed

References control how formulas behave when copied.

3.1 Relative Reference

A1
Changes when copied.

3.2 Absolute Reference

$A$1
Does not change.

3.3 Mixed Reference

A$1 or $A1
Useful for tables, charts, or 2D lookups.

3.4 Why Referencing Matters

Incorrect references cause:

  • wrong totals
  • incorrect lookup results
  • broken calculations

Recommended Articles

4. Basic Arithmetic Functions & Operators

Arithmetic is the foundation of all formulas.

4.1 Basic Operations

=A1 + B1
=A1 - B1
=A1 * B1
=A1 / B1
=A1 ^ 2

4.2 SUM for Multiple Values

=SUM(A1:A10)

4.3 AutoSum Shortcut

Alt + =

Recommended Articles

5. Working with SUM, AVERAGE, COUNT, MAX, MIN

These functions appear in nearly every Excel file.

5.1 SUM

Adds values.

5.2 AVERAGE

Calculates mean.

5.3 COUNT / COUNTA

Counts numeric or non-blank entries.

5.4 MAX / MIN

Find highest or lowest values.

5.5 AVERAGEIF / COUNTIF

Conditional calculations.

Recommended Articles

6. Logical Functions: IF, AND, OR, NOT

Logic controls decision-making.

6.1 IF

=IF(A1>50,"Pass","Fail")

6.2 AND

=AND(A1>0, A1<100)

6.3 OR

=OR(A1="A", A1="B")

6.4 NOT

=NOT(A1="OK")

6.5 Combine Logic

=IF(AND(A1>=70,B1>=70),"OK","NG")

Recommended Articles

7. Nested IF vs. IFS: Choosing the Right Logic Structure

7.1 Nested IF

=IF(A1>=80,"A",IF(A1>=70,"B",IF(A1>=60,"C","D")))

7.2 IFS

=IFS(
A1>=80,"A",
A1>=70,"B",
A1>=60,"C",
TRUE,"D"
)

7.3 When to Use Which

  • IFS → cleaner logic
  • Nested IF → more control in complex cases

Recommended Articles

8. Lookup Functions: VLOOKUP, HLOOKUP, INDEX, MATCH

Lookup functions are essential for retrieving data.

8.1 VLOOKUP

=VLOOKUP(A2, B2:D10, 2, FALSE)

Limitations

  • must search left-to-right
  • column index can break
  • slower in large files

8.2 INDEX + MATCH

=INDEX(C2:C10, MATCH(A2, A2:A10, 0))

Advantages

  • lookup left or right
  • stable structure
  • faster

Recommended Articles

9. XLOOKUP: The Modern Replacement for VLOOKUP

Excel’s most powerful lookup function.

Syntax

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

Benefits

  • lookup in any direction
  • optional default value
  • supports arrays
  • simpler than INDEX/MATCH
  • more reliable

Example

=XLOOKUP(A2, A:A, B:B, "Not found")

Recommended Articles

10. FILTER, SORT, UNIQUE: Dynamic Array Functions

Dynamic arrays changed Excel permanently.

10.1 FILTER

=FILTER(A2:C100, C2:C100="Tokyo")

10.2 SORT

=SORT(A2:B20, 2, TRUE)

10.3 UNIQUE

=UNIQUE(A2:A100)

Use Cases

  • generate unique lists
  • sort tables
  • filtered dashboards
  • dependent dropdowns

Recommended Articles

11. Text Functions: CONCAT, TEXTJOIN, LEFT, RIGHT, MID

Text processing is crucial for:

  • names
  • addresses
  • IDs
  • codes
  • file names
  • product SKUs

11.1 CONCAT

=CONCAT(A1," ",B1)

11.2 TEXTJOIN

=TEXTJOIN(", ", TRUE, A1:A5)

11.3 LEFT / RIGHT / MID

=LEFT(A1,3)
=RIGHT(A1,5)
=MID(A1,2,4)

Recommended Articles

12. Extracting Text Based on Conditions

Often used for:

  • extracting names
  • filtering codes
  • cleaning data

12.1 FIND & SEARCH

Case-sensitive vs. insensitive.

12.2 Example

=MID(A1, SEARCH("-", A1)+1, 99)

Recommended Articles

13. Text Cleanup Functions: TRIM, CLEAN, SUBSTITUTE

13.1 TRIM

Removes extra spaces.

13.2 CLEAN

Removes non-printing characters.

13.3 SUBSTITUTE

=SUBSTITUTE(A1,"-","")

Useful for:

  • IDs
  • phone numbers
  • data imports

Recommended Articles

14. Date & Time Functions

Common Functions

  • TODAY()
  • NOW()
  • DATE()
  • YEAR()
  • MONTH()
  • DAY()
  • TEXT()

Example

=DATE(YEAR(A1), MONTH(A1)+1, 1)

Recommended Articles

15. Percentage & Ratio Calculations

Percentages are commonly used in:

  • growth rates
  • contribution ratios
  • distribution analysis

Examples

=A1/B1
=(A1-B1)/B1

Recommended Articles

16. Cell Address & Position Functions

Useful for:

  • dynamic ranges
  • automation
  • advanced dashboards

16.1 ADDRESS

=ADDRESS(3,5)

16.2 ROW / COLUMN

=ROW(A5)
=COLUMN(D2)

16.3 INDEX for Positions

=INDEX(A:A, MATCH("Target", A:A, 0))

17. Random Functions: RAND, RANDBETWEEN

Useful for simulations and test data.

17.1 RAND

0–1 random number.

17.2 RANDBETWEEN

=RANDBETWEEN(1,100)

Tips

  • volatile functions recalculate constantly
  • consider using static values for final results

Recommended Articles

18. Error Handling: IFERROR, ISERROR, ISNA

Error handling keeps formulas clean.

18.1 IFERROR

=IFERROR(A1/B1, "Error")

18.2 ISNA

Check for #N/A.

18.3 ISERROR

Check for all errors.

Recommended Articles

19. Creating Reusable Function Templates

Create formula templates for:

  • monthly reports
  • department sheets
  • dashboards
  • repetitive calculations

Store templates in:

  • hidden sheets
  • sample files
  • separate template workbooks

Recommended Articles

20. Combining Functions for Complex Formulas

Examples:

20.1 IF + AND + OR

=IF(AND(A1>50, B1<100), "OK", "NG")

20.2 TEXTJOIN + FILTER

=TEXTJOIN(", ", TRUE, FILTER(A1:A100, B1:B100="Yes"))

20.3 XLOOKUP + IFERROR

=IFERROR(XLOOKUP(E1, A:A, B:B), "Not found")

20.4 INDEX + MATCH + MATCH (2D lookup)

=INDEX(B2:F10, MATCH(H1,A2:A10,0), MATCH(H2,B1:F1,0))

Recommended Articles

21. Troubleshooting Formula Errors

#DIV/0!

Divide by zero.

#REF!

Deleted cell reference.

#VALUE!

Wrong type.

#N/A

Lookup mismatch.

#NAME?

Function misspelled.

#NUM!

Invalid numeric calculation.

Recommended Articles

22. Best Practices for Writing Reliable Formulas

  • keep formulas simple
  • use comments
  • avoid volatile functions
  • use named ranges
  • break formulas into smaller parts
  • avoid unnecessary nesting
  • test with sample data
  • format numbers properly
  • document logic

23. Recommended Internal Links

Related Posts

How to Calculate Percentages in Excel

How to Calculate Percentages in Excel (Complete Guide with Examples)ContentsHow to Calculate Percentages in Excel (Complete Guide with Examples)✅ What Is a Percentage in Excel?✅ Basic Percentage Formula✅ How to…

Powered by
►
Necessary cookies enable essential site features like secure log-ins and consent preference adjustments. They do not store personal data.
None
►
Functional cookies support features like content sharing on social media, collecting feedback, and enabling third-party tools.
None
►
Analytical cookies track visitor interactions, providing insights on metrics like visitor count, bounce rate, and traffic sources.
None
►
Advertisement cookies deliver personalized ads based on your previous visits and analyze the effectiveness of ad campaigns.
None
Powered by
Scroll to Top