1. Name of A to Z MS Excel functions and their use? How to use ?
Here is an A-to-Z list of commonly used Excel functions, their purposes, and examples of how to use them:
A
ABS: Returns the absolute value of a number.
Usage: =ABS(-5) → 5
AVERAGE: Calculates the average of a range of numbers.
Usage: =AVERAGE(A1:A5)
B
BAHTTEXT: Converts a number to Thai text.
Usage: =BAHTTEXT(123) → Outputs the number in Thai currency format.
C
CEILING: Rounds a number up to the nearest multiple of a specified value.
Usage: =CEILING(2.5, 1) → 3
CONCAT: Combines the text from multiple cells.
Usage: =CONCAT(A1, " ", B1) → Joins values with a space.
D
DATE: Creates a date from year, month, and day.
Usage: =DATE(2023, 12, 25) → 25-Dec-2023
DAY: Extracts the day from a date.
Usage: =DAY(A1) → Extracts the day value from the date in cell A1.
E
EXACT: Compares two text strings and returns TRUE if they are identical.
Usage: =EXACT("Excel", "excel") → FALSE
EXP: Returns the exponential value of a number.
Usage: =EXP(1) → 2.71828
F
FILTER: Filters a range based on specified criteria.
Usage: =FILTER(A1:A10, B1:B10="Yes")
FLOOR: Rounds a number down to the nearest multiple.
Usage: =FLOOR(2.7, 1) → 2
G
GCD: Returns the greatest common divisor of two numbers.
Usage: =GCD(24, 36) → 12
H
HLOOKUP: Looks for a value in the top row of a table and returns a value in the same column from another row.
Usage: =HLOOKUP("Item", A1:Z10, 2, FALSE)
I
IF: Performs a logical test and returns one value for TRUE and another for FALSE.
Usage: =IF(A1>10, "Yes", "No")
INDEX: Returns the value of a cell at a specified row and column.
Usage: =INDEX(A1:C10, 2, 3) → Value in 2nd row, 3rd column.
J
JOIN: Combines text strings from an array (available in VBA).
Usage: JOIN(delimiter, text) (via macros).
K
KURT: Returns the kurtosis of a data set (a measure of tails in data distribution).
Usage: =KURT(A1:A10)
L
LEFT: Extracts a specific number of characters from the start of a text string.
Usage: =LEFT("Excel", 2) → Ex
LEN: Returns the length of a text string.
Usage: =LEN("Excel") → 5
M
MATCH: Returns the relative position of a value in a range.
Usage: =MATCH(25, A1:A10, 0) → Position of 25.
MID: Extracts text from the middle of a string.
Usage: =MID("Excel", 2, 3) → xce
N
NOW: Returns the current date and time.
Usage: =NOW()
O
OR: Returns TRUE if at least one condition is TRUE.
Usage: =OR(A1>5, B1<10)
P
PMT: Calculates the payment for a loan based on constant payments and interest rates.
Usage: =PMT(5%/12, 60, -10000)
PROPER: Capitalizes the first letter of each word in a text string.
Usage: =PROPER("hello world") → Hello World
Q
QUOTIENT: Returns the integer portion of a division.
Usage: =QUOTIENT(5, 2) → 2
R
RAND: Generates a random number between 0 and 1.
Usage: =RAND()
ROUND: Rounds a number to a specified number of digits.
Usage: =ROUND(2.567, 2) → 2.57
S
SUM: Adds numbers in a range.
Usage: =SUM(A1:A10)
SUBSTITUTE: Replaces text in a string.
Usage: =SUBSTITUTE("Hello World", "World", "Excel")
T
TEXT: Formats a number and converts it to text.
Usage: =TEXT(12345, "0.00") → 12345.00
TRIM: Removes extra spaces from text.
Usage: =TRIM(" Excel ")
U
UPPER: Converts text to uppercase.
Usage: =UPPER("excel") → EXCEL
V
VLOOKUP: Searches for a value in the first column and returns a value in the same row from another column.
Usage: =VLOOKUP(1001, A1:D10, 3, FALSE)
W
WORKDAY: Returns a date before or after a specified number of working days.
Usage: =WORKDAY(TODAY(), 10)
X
XLOOKUP: Looks for a value in a range and returns the corresponding value (more flexible than VLOOKUP).
Usage: =XLOOKUP(3, A1:A10, B1:B10)
Y
YEAR: Extracts the year from a date.
Usage: =YEAR(A1) → Returns the year of the date in cell A1.
Z
ZTEST: Returns the one-tailed probability-value of a z-test.
Usage: =ZTEST(A1:A10, 10)
Detailed explanation
Below are detailed explanations with examples of some commonly used Excel functions from A to Z:
A
ABS (Absolute Value)
Description: Returns the absolute (non-negative) value of a number.
Syntax: =ABS(number)
Example:
=ABS(-10) → 10
Use case: If you want to ignore negative signs in calculations, such as when calculating deviations.
AVERAGE
Description: Calculates the arithmetic mean of numbers.
Syntax: =AVERAGE(number1, [number2], ...)
Example:
=AVERAGE(10, 20, 30) → 20
Use case: Calculate the average of student scores or monthly expenses.
C
CONCAT
Description: Combines text from multiple cells into one.
Syntax: =CONCAT(text1, [text2], ...)
Example:
=CONCAT(A1, " ", B1) → Joins text in A1 and B1 with a space.
Alternative: Use TEXTJOIN for combining with delimiters
CEILING
Description: Rounds a number up to the nearest multiple of a specified value.
Syntax: =CEILING(number, significance)
Example:
=CEILING(5.3, 2) → 6
Use case: Round up amounts to the nearest dollar or nearest interval (e.g., 5 cents).
F
FILTER
Description: Filters a range of data based on criteria.
Syntax: =FILTER(array, include, [if_empty])
Example:
=FILTER(A1:A10, B1:B10="Yes")
Filters values in A1:A10 where B1:B10 contains "Yes".
Use case: Extract rows matching criteria (e.g., sales greater than $500).
FLOOR
Description: Rounds a number down to the nearest multiple of a specified value.
Syntax: =FLOOR(number, significance)
Example:
=FLOOR(5.7, 2) → 4
IF
IF (Conditional Statement)
Description: Performs a logical test and returns a value based on whether the condition is TRUE or FALSE.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Example:
=IF(A1>50, "Pass", "Fail")
If the value in A1 is greater than 50, it returns "Pass"; otherwise, "Fail".
Use case: Grading systems, assigning categories based on sales, etc.
INDEX
INDEX (Find Value by Position)
Description: Returns the value of a cell at a specified row and column in a range.
Syntax: =INDEX(array, row_num, [column_num])
Example:
=INDEX(A1:C10, 2, 3)
Returns the value in the 2nd row and 3rd column of the range A1:C10.
Use case: Look up values in structured data.
LEN
LEN (Length of Text)
Description: Returns the total number of characters in a text string, including spaces.
Syntax: =LEN(text)
Example:
=LEN("Excel Functions") → 15
Use case: Validate input lengths, such as phone numbers or IDs.
MATCH
MATCH (Find Position of a Value)
Description: Returns the position of a value in a range.
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
Example:
=MATCH(50, A1:A10, 0) → Finds the exact match of 50 in A1:A10.
Use case: Identify the position of data for dynamic calculations.
SUM
SUM (Add Numbers)
Description: Adds all numbers in a range or list.
Syntax: =SUM(number1, [number2], ...)
Example:
=SUM(A1:A10) → Adds all values in A1:A10.
Use case: Quickly calculate totals.
TEXT
TEXT (Format Numbers as Text)
Description: Converts a number into a specific format.
Syntax: =TEXT(value, format_text)
Example:
=TEXT(1234.567, "$#,##0.00") → $1,234.57
Use case: Format numbers for reports, such as currency, percentages, or dates.
VLOOKUP
VLOOKUP (Vertical Lookup)
Description: Searches for a value in the first column and returns a value in the same row from another column.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
=VLOOKUP(101, A1:D10, 2, FALSE)
Finds 101 in the first column of A1:D10 and returns the corresponding value from the 2nd column.
Use case: Search for product details, employee names, etc.
XLOOKUP
XLOOKUP (Flexible Lookup)
Description: Searches for a value in a range and returns the corresponding value from another range.
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example:
=XLOOKUP(3, A1:A10, B1:B10, "Not Found")
Searches for 3 in A1:A10 and returns the corresponding value from B1:B10.
Use case: A more versatile replacement for VLOOKUP and HLOOKUP.
Post a Comment