Basic Functions:
1. SUM: Adds up a range of cells.
o Example: =SUM(A1:A10)
2. AVERAGE: Calculates the average of a range of cells.
o Example: =AVERAGE(A1:A10)
3. MIN: Returns the smallest value in a range of cells.
o Example: =MIN(A1:A10)
4. MAX: Returns the largest value in a range of cells.
o Example: =MAX(A1:A10)
5. COUNT: Counts the number of cells in a range that contain
numbers.
o Example: =COUNT(A1:A10)
6. IF: Performs a conditional operation based on a specified condition.
o Example: =IF(A1 > 10, "Yes", "No")
Text Functions:
1. CONCATENATE: Joins multiple text strings into one.
o Example: =CONCATENATE(A1, " ", B1)
2. LEFT, RIGHT, MID: Extracts characters from a text string.
o Example: =LEFT(A1, 5) extracts the leftmost 5 characters from
cell A1.
3. LEN: Returns the length of a text string.
o Example: =LEN(A1)
4. TRIM: Removes leading and trailing spaces from text.
o Example: =TRIM(A1)
Date and Time Functions:
1. TODAY: Returns the current date.
o Example: =TODAY()
2. DATE: Creates a date from year, month, and day values.
o Example: =DATE(2024, 7, 24)
3. DATEDIF: Calculates the difference between two dates in various
units (days, months, years).
o Example: =DATEDIF(A1, B1, "d")
4. EOMONTH: Returns the last day of the month before or after a
specified number of months.
o Example: =EOMONTH(A1, 3)
Statistical Functions:
1. STDEV: Calculates the standard deviation based on a sample of
numbers.
o Example: =STDEV(A1:A10)
2. VAR: Estimates the variance based on a sample of numbers.
o Example: =VAR(A1:A10)
3. CORREL: Calculates the correlation coefficient between two data
sets.
o Example: =CORREL(A1:A10, B1:B10)
Lookup and Reference Functions:
1. VLOOKUP: Searches for a value in the first column of a table and
returns a value in the same row from another column.
o Example: =VLOOKUP(A1, Table1, 2, FALSE)
2. HLOOKUP: Searches for a value in the first row of a table and
returns a value in the same column from another row.
o Example: =HLOOKUP(A1, Table1, 2, FALSE)
3. INDEX: Returns the value of a cell in a specified row and column of
a table or range.
o Example: =INDEX(Table1, 3, 2)
4. MATCH: Searches for a specified value in a range and returns the
relative position of that item.
o Example: =MATCH(A1, Table1, 0)
Logical Functions:
1. IF: Checks whether a condition is met, and returns one value if true
and another if false.
o Example: =IF(A1 > 10, "Yes", "No")
2. AND, OR: Checks multiple conditions and returns true if all
conditions are met (AND) or if any condition is met (OR).
o Example: =AND(A1 > 5, B1 < 10)
Financial Functions:
1. PMT: Calculates the payment for a loan based on constant
payments and a constant interest rate.
o Example: =PMT(0.05/12, 5*12, -25000)
2. PV: Returns the present value of an investment based on a series of
periodic cash flows and a discount rate.
o Example: =PV(0.1, 5, -2000)
Engineering Functions:
1. FV: Calculates the future value of an investment based on a series
of periodic cash flows and a constant interest rate.
o Example: =FV(0.05, 10, -100, -1000)
2. NPV: Calculates the net present value of an investment based on a
series of periodic cash flows and a discount rate.
o Example: =NPV(0.1, A1:A5)
Navigation Shortcuts:
Ctrl + Arrow Keys: Move to the edge of the current data region
(left, right, up, down).
Ctrl + Home: Move to the beginning of the worksheet.
Ctrl + End: Move to the last cell of the data region.
Ctrl + Page Up / Page Down: Move between worksheet tabs.
Ctrl + G: Go to a specific cell or range.
Ctrl + Backspace: Move to the active cell's precedent cell.
Ctrl + F6: Switch between multiple Excel workbooks.
Selection Shortcuts:
Shift + Arrow Keys: Extend the selection by one cell.
Ctrl + Shift + Arrow Keys: Extend the selection to the edge of the
current data region.
Ctrl + Space: Select the entire column.
Shift + Space: Select the entire row.
Ctrl + A: Select the entire worksheet.
Editing Shortcuts:
Ctrl + C: Copy the selected cells.
Ctrl + X: Cut the selected cells.
Ctrl + V: Paste copied or cut cells.
Ctrl + Z: Undo the last action.
Ctrl + Y: Redo the last undone action.
F2: Edit the active cell.
Ctrl + D: Fill down from the cell above.
Ctrl + R: Fill right from the cell to the left.
**Ctrl + -: Delete the selected column or row.
Ctrl + Shift + +: Insert a new column or row.
Ctrl + ': Copy the value from the cell above the active cell into the
cell or the formula bar.
Formatting Shortcuts:
Ctrl + B: Apply or remove bold formatting.
Ctrl + I: Apply or remove italic formatting.
Ctrl + U: Apply or remove underline formatting.
Ctrl + 1: Open the Format Cells dialog box.
Alt + H + B: Apply a bottom border to the selected cell(s).
Alt + H + F + C: Format cells dialog box.
1. Math and Trigonometry:
SUM: Adds all the numbers in a range of cells.
AVERAGE: Calculates the average of a range of cells.
MIN: Returns the smallest value in a range of cells.
MAX: Returns the largest value in a range of cells.
COUNT: Counts the number of cells that contain numbers.
COUNTA: Counts the number of cells that are not empty.
COUNTIF: Counts the number of cells that meet a criterion.
SUMIF: Adds the cells specified by a given condition.
SUMIFS: Adds the cells in a range that meet multiple criteria.
AVERAGEIF: Returns the average of cells that meet a single
condition.
AVERAGEIFS: Returns the average of cells that meet multiple
criteria.
ROUND: Rounds a number to a specified number of digits.
RAND: Returns a random number between 0 and 1.
RANDBETWEEN: Returns a random number between two specified
numbers.
TRUNC: Truncates a number to an integer.
ABS: Returns the absolute value of a number.
SQRT: Returns the square root of a number.
POWER: Returns the result of a number raised to a power.
MOD: Returns the remainder of a division operation.
CEILING: Rounds a number up, to the nearest multiple of
significance.
FLOOR: Rounds a number down, to the nearest multiple of
significance.
PI: Returns the value of pi, π (3.14159265358979).
EXP: Returns e raised to the power of a given number.
LOG: Returns the logarithm of a number to a specified base.
LN: Returns the natural logarithm of a number.
DEGREES: Converts radians to degrees.
RADIANS: Converts degrees to radians.
SIN, COS, TAN: Returns the sine, cosine, or tangent of an angle.
ASIN, ACOS, ATAN: Returns the inverse sine, cosine, or tangent of
a number.
2. Text Functions:
CONCATENATE: Joins two or more text strings into one string.
LEFT, RIGHT, MID: Extracts characters from a text string.
LEN: Returns the length of a text string.
LOWER, UPPER: Converts text to lowercase or uppercase.
PROPER: Capitalizes the first letter of each word in a text string.
TRIM: Removes spaces from text.
SUBSTITUTE: Substitutes new text for old text in a text string.
FIND: Finds one text string within another (case-sensitive).
SEARCH: Finds one text string within another (not case-sensitive).
TEXT: Formats a number and converts it to text.
VALUE: Converts a text argument to a number.
REPLACE: Replaces characters within text.
REPT: Repeats text a given number of times.
3. Date and Time Functions:
TODAY: Returns the current date.
NOW: Returns the current date and time.
DATE: Returns the serial number of a specified date.
TIME: Returns the serial number of a specified time.
YEAR, MONTH, DAY: Returns the year, month, or day of a date.
HOUR, MINUTE, SECOND: Returns the hour, minute, or second of
a time.
DATEDIF: Calculates the difference between two dates.
EOMONTH: Returns the last day of the month.
4. Logical Functions:
IF: Returns one value if a condition is true and another value if false.
AND, OR: Returns true if all arguments are true (AND), or if any
argument is true (OR).
NOT: Reverses the logical value of its argument.
IFERROR: Returns a value you specify if a formula evaluates to an
error; otherwise, it returns the result of the formula.
5. Lookup and Reference Functions:
VLOOKUP, HLOOKUP: Searches for a value in a range and returns
a value in the same row (VLOOKUP) or column (HLOOKUP).
INDEX: Returns the value of a cell in a specific row and column of a
range.
MATCH: Searches for a specified value in a range and returns the
relative position of that item.
OFFSET: Returns a reference offset from a starting cell with a
specified number of rows and columns.
6. Statistical Functions:
STDEV, STDEVP: Estimates the standard deviation based on a
sample (STDEV) or population (STDEVP).
VAR, VARP: Estimates the variance based on a sample (VAR) or
population (VARP).
CORREL: Returns the correlation coefficient between two data sets.
COVAR: Returns the covariance, the average of the products of
deviations for each data point pair in two data sets.
7. Financial Functions:
PMT: Calculates the payment for a loan based on constant
payments and a constant interest rate.
PV: Returns the present value of an investment based on a series of
periodic cash flows and a discount rate.
FV: Calculates the future value of an investment based on a series
of periodic cash flows and a constant interest rate.
NPV: Calculates the net present value of an investment based on a
series of periodic cash flows and a discount rate.
IRR: Returns the internal rate of return for a series of cash flows.
RATE: Calculates the interest rate per period of an annuity.
NPER: Returns the number of periods for an investment based on
periodic, constant payments and a constant interest rate.
8. Engineering Functions:
DEGREES, RADIANS: Converts angles from degrees to radians or
radians to degrees.
CONVERT: Converts a number from one measurement system to
another.
IMAGINARY, IMREAL: Returns the imaginary or real coefficient of a
complex number.
BIN2DEC, BIN2HEX, BIN2OCT: Converts binary numbers to
decimal, hexadecimal, or octal.
DEC2BIN, DEC2HEX, DEC2OCT: Converts decimal numbers to
binary, hexadecimal, or octal.
HEX2BIN, HEX2DEC, HEX2OCT: Converts hexadecimal numbers
to binary, decimal, or octal.
OCT2BIN, OCT2DEC, OCT2HEX: Converts octal numbers to binary,
decimal, or hexadecimal.
