Scribd
Upload
12 views48 pages

Essential Excel Functions Guide

The document provides a comprehensive overview of various Excel functions, including their syntax, examples, and results. Key functions covered include SUM, MAX, MIN, AVERAGE, COUNT, and various conditional counting and summation functions. Additionally, it includes text manipulation, date functions, lookup functions, and formatting functions.

Uploaded by

Sachin Agarwal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
12 views48 pages

Essential Excel Functions Guide

The document provides a comprehensive overview of various Excel functions, including their syntax, examples, and results. Key functions covered include SUM, MAX, MIN, AVERAGE, COUNT, and various conditional counting and summation functions. Additionally, it includes text manipulation, date functions, lookup functions, and formatting functions.

Uploaded by

Sachin Agarwal
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

SUM

• Syntax: =SUM(A1:A3)
• Example: 10,20,30
• Result: 60
• Adds numbers together
MAX
• Syntax: =MAX(A1:A3)
• Example: 10,20,30
• Result: 30
• Returns highest value
MIN
• Syntax: =MIN(A1:A3)
• Example: 10,20,30
• Result: 10
• Returns lowest value
AVERAGE
• Syntax: =AVERAGE(A1:A3)
• Example: 10,20,30
• Result: 20
• Returns average
COUNT
• Syntax: =COUNT(A1:A5)
• Counts numeric cells
COUNTA
• Syntax: =COUNTA(A1:A5)
• Counts non-empty cells
COUNTBLANK
• Syntax: =COUNTBLANK(A1:A5)
• Counts empty cells
COUNTIF
• Syntax: =COUNTIF(A1:A5,">10")
• Counts matching condition
COUNTIFS
• Syntax: =COUNTIFS(A:A,">10",B:B,"Yes")
• Multiple conditions
SUMIF
• Syntax: =SUMIF(A:A,">10",B:B)
• Conditional sum
SUMIFS
• Syntax: =SUMIFS(C:C,A:A,">10",B:B,"Yes")
• Multiple conditions sum
SUBSTITUTE
• Syntax: =SUBSTITUTE(A1,"old","new")
• Replaces text
SUBTOTAL
• Syntax: =SUBTOTAL(9,A1:A10)
• Ignores filtered rows
TRIM
• Syntax: =TRIM(A1)
• Removes extra spaces
TODAY
• Syntax: =TODAY()
• Returns today’s date
NOW
• Syntax: =NOW()
• Returns current date & time
ROUND
• Syntax: =ROUND(12.345,2)
• Rounds number
ROUNDUP
• Syntax: =ROUNDUP(12.341,2)
• Always rounds up
ROUNDDOWN
• Syntax: =ROUNDDOWN(12.349,2)
• Always rounds down
IF
• Syntax: =IF(A1>50,"Pass","Fail")
• Logical test
IFERROR
• Syntax: =IFERROR(A1/B1,0)
• Handles errors
AND
• Syntax: =AND(A1>10,B1<5)
• All conditions true
OR
• Syntax: =OR(A1>10,B1<5)
• Any condition true
SWITCH
• Syntax: =SWITCH(A1,1,"One",2,"Two")
• Multiple IF
DAY
• Syntax: =DAY(A1)
• Returns day
MONTH
• Syntax: =MONTH(A1)
• Returns month
YEAR
• Syntax: =YEAR(A1)
• Returns year
DATEDIF
• Syntax: =DATEDIF(A1,B1,"Y")
• Date difference
VLOOKUP
• Syntax: =VLOOKUP(A1,A:B,2,0)
• Vertical lookup
HLOOKUP
• Syntax: =HLOOKUP(A1,A1:D2,2,0)
• Horizontal lookup
XLOOKUP
• Syntax: =XLOOKUP(A1,A:A,B:B)
• Modern lookup
INDEX
• Syntax: =INDEX(A1:C5,2,3)
• Returns value
MATCH
• Syntax: =MATCH(50,A:A,0)
• Finds position
INDEX+MATCH
• Syntax: =INDEX(B:B,MATCH(A1,A:A,0))
• Flexible lookup
UPPER
• Syntax: =UPPER(A1)
• Uppercase
LOWER
• Syntax: =LOWER(A1)
• Lowercase
PROPER
• Syntax: =PROPER(A1)
• Title case
LEFT
• Syntax: =LEFT(A1,4)
• Left characters
RIGHT
• Syntax: =RIGHT(A1,2)
• Right characters
MID
• Syntax: =MID(A1,2,3)
• Middle text
LEN
• Syntax: =LEN(A1)
• Text length
INDIRECT
• Syntax: =INDIRECT("A1")
• Reference as text
OFFSET
• Syntax: =OFFSET(A1,1,1)
• Dynamic reference
FIND
• Syntax: =FIND("@",A1)
• Find position
RANK
• Syntax: =RANK(A1,A:A)
• Ranking
TEXTJOIN
• Syntax: =TEXTJOIN(",",TRUE,A1:A3)
• Join text
UNIQUE
• Syntax: =UNIQUE(A1:A10)
• Unique values
FILTER
• Syntax: =FILTER(A1:C10,B1:B10="Yes")
• Filtered data

You might also like