Introduction
Excel enables users to perform a wide range of functions, from simple arithmetic operations to complex data analysis, provided there are related formulas in the software. Such functions help to reduce work burdens, increase productivity, and make data more easily accessible. This reference guide is presented to detail , step by step , the various popular functions of Excel, categorized according to groups, to help understand how one can utilize the full potential of the application in any type of data work.
Arithmetic and Math Functions
SUM () – It gives the sum of the cell values for the selected range, as the name itself suggests.
AVERAGE () – This function emphasizes the calculation of the mean value of the selected range of cell values.
COUNT () – It counts the overall number of cells in a range that hold a number.
SUBTOTAL () – Subtotal () is returned by the subtotal function as the subtotal of a database.
COUNTA () – Count the number of nonblank cells.
MIN () – Find the smallest value
MAX () – Find the largest value
PRODUCT () – Multiply a range of numbers
SQRT () – Calculate the square root
ABS () – Returns the absolute value.
Logical Functions
IF () – If the condition holds true, it returns one value; otherwise, it returns another value.
AND () – TRUE only when all of its arguments are true.
OR () – If any of the arguments are TRUE then returns TRUE.
NOT () – flip the logical value.
IFERROR () – Returns an alternative value if a formula returns an error.
Lookup and Reference Functions
VLOOKUP () – Looks for any value down the first column of any range.
HLOOKUP () – Searches for a value to the left in the top row of any range.
INDEX () – Returns a value in a specified cell in a specified row and column.
MATCH () – Searches for a value within a range, returning the relative position of that cell.
LOOKUP () – This function looks up for a value in a one-row or one-column range.
OFFSET () – Returns a reference to a range offset from a cell.
INDIRECT () – Returns a reference to a cell or range given as text.
Text Functions
CONCAT / CONCATENATE: Concatenates text strings.
LEFT: Retrieves a specified number of characters from the left side of a text.
RIGHT: Pulls several characters out of a text from the right side.
MID: Retrieves characters internally between two characters within a text.
LEN: Returns a numeric value specifying how many characters are in a text.
SEARCH: Determines if one text is inside another (case-insensitive).
FIND: Searches for one text string within another. This function is case-sensitive.
TRIM: Deletes extra spaces in a text string.
UPPER: Converts the text to uppercase.
LOWER: Converts the text to lowercase.
PROPER: This capitalizes the first letter of every word.
Date and Time Functions
TODAY: Returns today’s date.
NOW: Returns today’s date and time.
YEAR: extracts the year from any date.
MONTH: extracts the month from any date.
DAY: extracts the day from any date.
HOUR, MINUTE, SECOND: Return part of a time.
DATE: Builds a date by using the year, month, and day.
DATEDIF: Returns the difference between two dates.
NETWORKDAYS: Returns the number of whole workdays between two dates.
EOMONTH: Returns last day of month.
Financial Functions
PV: Gives the current value of a present investment.
FV: returns future value of investment.
NPV: Computation of the net present value.
PMT: Computes periodic payment for a loan.
RATE: This returns interest rate per period of annuity.
IRR: This function calculates the internal rate of return.
XNPV: It returns net present value for irregular cash flows.
XIRR: This is the function to calculate the rate of return with irregular cash flows.
Statistical Functions
STDEV: Returns the standard deviation.
VAR: Returns the variance.
MEDIAN: Finds the median.
MODE: Finds the mode.
RANK: It will generate the rank of a number in the list.
LARGE: nth largest Return the nth largest value.
SMALL: It generates the nth smallest number.
PERCENTILE: Calculates the value of the nth percentile of values.