Interactive Excel Function Academy
All 500+ Excel formulas, live interactive spreadsheets, financial modeling tracks, graded exercises, and solution files by Jawahar Pandiarajan are now 100% Free & Open Access!
10 Master Modules
Curated Learning Tracks
500+ Formulas
100% Free & Unlocked
Interactive Simulators
Real Excel Calculation Engine
MOS Master
Jawahar Pandiarajan
Google AdSense Responsive Unit (horizontal)
Publisher ID: ca-pub-7444400132160337. Ad unit ready for live ad serving upon Google approval.
Course Catalog & Lessons (151)
Select any function below to launch the live interactive spreadsheet simulator
=XLOOKUP() Interactive Masterclass & Practice Simulator
Searches a range or an array for a match and returns the corresponding item from a second range or array.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=VLOOKUP() Interactive Masterclass & Practice Simulator
Looks for a value in the leftmost column of a table and returns a value in the same row from a column you specify.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=HLOOKUP() Interactive Masterclass & Practice Simulator
Searches for a value in the top row of a table or an array of values, and returns a value in the same column from a row you specify.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])=INDEX() Interactive Masterclass & Practice Simulator
Returns a value or reference of the cell at the intersection of a particular row and column in a given range.
=INDEX(array, row_num, [column_num])=MATCH() Interactive Masterclass & Practice Simulator
Returns the relative position of an item in an array that matches a specified value in a specified order.
=MATCH(lookup_value, lookup_array, [match_type])=XMATCH() Interactive Masterclass & Practice Simulator
Returns the relative position of an item in an array or range of cells. Defaults to exact match.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])=CHOOSE() Interactive Masterclass & Practice Simulator
Uses index_num to return a value from the list of value arguments.
=CHOOSE(index_num, value1, [value2], ...)=CHOOSECOLS() Interactive Masterclass & Practice Simulator
Returns the specified columns from an array.
=CHOOSECOLS(array, col_num1, [col_num2], ...)=CHOOSEROWS() Interactive Masterclass & Practice Simulator
Returns the specified rows from an array.
=CHOOSEROWS(array, row_num1, [row_num2], ...)=DROP() Interactive Masterclass & Practice Simulator
Excludes a specified number of rows or columns from the start or end of an array.
=DROP(array, rows, [columns])=TAKE() Interactive Masterclass & Practice Simulator
Returns a specified number of contiguous rows or columns from the start or end of an array.
=TAKE(array, rows, [columns])=EXPAND() Interactive Masterclass & Practice Simulator
Expands or pads an array to specified row and column dimensions.
=EXPAND(array, rows, [columns], [pad_with])=VSTACK() Interactive Masterclass & Practice Simulator
Appends arrays vertically and in sequence to return a larger combined array.
=VSTACK(array1, [array2], ...)=HSTACK() Interactive Masterclass & Practice Simulator
Appends arrays horizontally and in sequence to return a larger array.
=HSTACK(array1, [array2], ...)=TOCOL() Interactive Masterclass & Practice Simulator
Returns the array in a single column.
=TOCOL(array, [ignore], [scan_by_column])=TOROW() Interactive Masterclass & Practice Simulator
Returns the array in a single row.
=TOROW(array, [ignore], [scan_by_column])=WRAPCOLS() Interactive Masterclass & Practice Simulator
Wraps the provided row or column of values by columns after a specified number of elements.
=WRAPCOLS(vector, wrap_count, [pad_with])=WRAPROWS() Interactive Masterclass & Practice Simulator
Wraps the provided row or column of values by rows after a specified number of elements.
=WRAPROWS(vector, wrap_count, [pad_with])=OFFSET() Interactive Masterclass & Practice Simulator
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
=OFFSET(reference, rows, cols, [height], [width])=INDIRECT() Interactive Masterclass & Practice Simulator
Returns the reference specified by a text string.
=INDIRECT(ref_text, [a1])=ADDRESS() Interactive Masterclass & Practice Simulator
Creates a cell address as text, given specified row and column numbers.
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])=TRANSPOSE() Interactive Masterclass & Practice Simulator
Returns a vertical range of cells as a horizontal range, or vice versa.
=TRANSPOSE(array)=HYPERLINK() Interactive Masterclass & Practice Simulator
Creates a shortcut or jump that opens a document stored on your hard drive, a network server, or on the internet.
=HYPERLINK(link_location, [friendly_name])=FORMULATEXT() Interactive Masterclass & Practice Simulator
Returns the formula at a given reference as text.
=FORMULATEXT(reference)=ROW() Interactive Masterclass & Practice Simulator
Returns the row number of a reference.
=ROW([reference])=COLUMN() Interactive Masterclass & Practice Simulator
Returns the column number of a reference.
=COLUMN([reference])=ROWS() Interactive Masterclass & Practice Simulator
Returns the number of rows in a reference or array.
=ROWS(array)=COLUMNS() Interactive Masterclass & Practice Simulator
Returns the number of columns in a reference or array.
=COLUMNS(array)=UNIQUE() Interactive Masterclass & Practice Simulator
Returns a list of unique values in a list or range.
=UNIQUE(array, [by_col], [exactly_once])=FILTER() Interactive Masterclass & Practice Simulator
Filters a range or array based on boolean criteria.
=FILTER(array, include, [if_empty])=SORT() Interactive Masterclass & Practice Simulator
Sorts the contents of a range or array.
=SORT(array, [sort_index], [sort_order], [by_col])=SORTBY() Interactive Masterclass & Practice Simulator
Sorts the contents of a range or array based on the values in a corresponding range or array.
=SORTBY(array, by_array1, [sort_order1], ...)=SEQUENCE() Interactive Masterclass & Practice Simulator
Generates a list of sequential numbers in an array, such as 1, 2, 3, 4.
=SEQUENCE(rows, [columns], [start], [step])=RANDARRAY() Interactive Masterclass & Practice Simulator
Returns an array of random numbers between 0 and 1, or between specified integers.
=RANDARRAY([rows], [columns], [min], [max], [whole_number])=IF() Interactive Masterclass & Practice Simulator
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
=IF(logical_test, [value_if_true], [value_if_false])=IFS() Interactive Masterclass & Practice Simulator
Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)=AND() Interactive Masterclass & Practice Simulator
Returns TRUE if all of its arguments are TRUE; returns FALSE if one or more arguments are FALSE.
=AND(logical1, [logical2], ...)=OR() Interactive Masterclass & Practice Simulator
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
=OR(logical1, [logical2], ...)=NOT() Interactive Masterclass & Practice Simulator
Reverses the value of its argument. Returns TRUE for FALSE and FALSE for TRUE.
=NOT(logical)=XOR() Interactive Masterclass & Practice Simulator
Returns a logical exclusive OR of all arguments.
=XOR(logical1, [logical2], ...)=SWITCH() Interactive Masterclass & Practice Simulator
Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
=SWITCH(expression, val1, result1, [val2, result2], ..., [default])=IFERROR() Interactive Masterclass & Practice Simulator
Returns value_if_error if expression is an error and the value of the expression itself otherwise.
=IFERROR(value, value_if_error)=IFNA() Interactive Masterclass & Practice Simulator
Returns the value you specify if the formula returns the #N/A error value; otherwise returns formula result.
=IFNA(value, value_if_na)=LET() Interactive Masterclass & Practice Simulator
Assigns names to calculation results, allowing storing intermediate calculations, values, or defining names inside a formula.
=LET(name1, name_value1, calculation_or_name2, ...)=LAMBDA() Interactive Masterclass & Practice Simulator
Creates custom, reusable functions and calls them by a friendly name.
=LAMBDA([parameter1, parameter2, ...], calculation)=SUM() Interactive Masterclass & Practice Simulator
Adds all the numbers in a range of cells.
=SUM(number1, [number2], ...)=SUMIF() Interactive Masterclass & Practice Simulator
Adds the cells specified by a given criteria.
=SUMIF(range, criteria, [sum_range])=SUMIFS() Interactive Masterclass & Practice Simulator
Adds the cells in a range that meet multiple criteria.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=SUMPRODUCT() Interactive Masterclass & Practice Simulator
Multiplies corresponding components in the given arrays, and returns the sum of those products.
=SUMPRODUCT(array1, [array2], [array3], ...)=PRODUCT() Interactive Masterclass & Practice Simulator
Multiplies all the numbers given as arguments and returns the product.
=PRODUCT(number1, [number2], ...)=ABS() Interactive Masterclass & Practice Simulator
Returns the absolute value of a number (number without its sign).
=ABS(number)=ROUND() Interactive Masterclass & Practice Simulator
Rounds a number to a specified number of digits.
=ROUND(number, num_digits)=ROUNDUP() Interactive Masterclass & Practice Simulator
Rounds a number up, away from zero.
=ROUNDUP(number, num_digits)=ROUNDDOWN() Interactive Masterclass & Practice Simulator
Rounds a number down, toward zero.
=ROUNDDOWN(number, num_digits)=MROUND() Interactive Masterclass & Practice Simulator
Returns a number rounded to the desired multiple.
=MROUND(number, multiple)=CEILING() Interactive Masterclass & Practice Simulator
Rounds a number up, to the nearest integer or to the nearest multiple of significance.
=CEILING(number, significance)=FLOOR() Interactive Masterclass & Practice Simulator
Rounds a number down, toward zero, to the nearest multiple of significance.
=FLOOR(number, significance)=INT() Interactive Masterclass & Practice Simulator
Rounds a number down to the nearest integer.
=INT(number)=TRUNC() Interactive Masterclass & Practice Simulator
Truncates a number to an integer by removing the fractional part of the number.
=TRUNC(number, [num_digits])=MOD() Interactive Masterclass & Practice Simulator
Returns the remainder after number is divided by divisor.
=MOD(number, divisor)=POWER() Interactive Masterclass & Practice Simulator
Returns the result of a number raised to a power.
=POWER(number, power)=SQRT() Interactive Masterclass & Practice Simulator
Returns a positive square root.
=SQRT(number)=EXP() Interactive Masterclass & Practice Simulator
Returns e raised to the power of a given number.
=EXP(number)=LN() Interactive Masterclass & Practice Simulator
Returns the natural logarithm of a number.
=LN(number)=LOG() Interactive Masterclass & Practice Simulator
Returns the logarithm of a number to the base you specify.
=LOG(number, [base])=LOG10() Interactive Masterclass & Practice Simulator
Returns the base-10 logarithm of a number.
=LOG10(number)=PI() Interactive Masterclass & Practice Simulator
Returns the value of pi: 3.14159265358979, accurate to 15 digits.
=PI()=SUBTOTAL() Interactive Masterclass & Practice Simulator
Returns a subtotal in a list or database.
=SUBTOTAL(function_num, ref1, [ref2], ...)=AGGREGATE() Interactive Masterclass & Practice Simulator
Returns an aggregate in a list or database with options to ignore hidden rows and error values.
=AGGREGATE(function_num, options, ref1, [ref2], ...)=RAND() Interactive Masterclass & Practice Simulator
Returns an evenly distributed random real number greater than or equal to 0 and less than 1.
=RAND()=RANDBETWEEN() Interactive Masterclass & Practice Simulator
Returns a random integer number between the numbers you specify.
=RANDBETWEEN(bottom, top)=COUNT() Interactive Masterclass & Practice Simulator
Counts the number of cells that contain numbers.
=COUNT(value1, [value2], ...)=COUNTA() Interactive Masterclass & Practice Simulator
Counts the number of cells that are not empty.
=COUNTA(value1, [value2], ...)=COUNTBLANK() Interactive Masterclass & Practice Simulator
Counts empty cells in a specified range of cells.
=COUNTBLANK(range)=COUNTIF() Interactive Masterclass & Practice Simulator
Counts the number of cells within a range that meet the given condition.
=COUNTIF(range, criteria)=AVERAGE() Interactive Masterclass & Practice Simulator
Returns the average (arithmetic mean) of the arguments.
=AVERAGE(number1, [number2], ...)=AVERAGEIF() Interactive Masterclass & Practice Simulator
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.
=AVERAGEIF(range, criteria, [average_range])=AVERAGEIFS() Interactive Masterclass & Practice Simulator
Returns the average (arithmetic mean) of all cells that meet multiple conditions.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=MEDIAN() Interactive Masterclass & Practice Simulator
Returns the median, or the number in the middle of the set of given numbers.
=MEDIAN(number1, [number2], ...)=MODE() Interactive Masterclass & Practice Simulator
Returns the most frequently occurring, or repetitive, value in an array or range of data.
=MODE(number1, [number2], ...)=MIN() Interactive Masterclass & Practice Simulator
Returns the smallest number in a set of values.
=MIN(number1, [number2], ...)=MAX() Interactive Masterclass & Practice Simulator
Returns the largest value in a set of values.
=MAX(number1, [number2], ...)=MINIFS() Interactive Masterclass & Practice Simulator
Returns the minimum value among cells specified by a given set of conditions or criteria.
=MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=MAXIFS() Interactive Masterclass & Practice Simulator
Returns the maximum value among cells specified by a given set of conditions or criteria.
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=LARGE() Interactive Masterclass & Practice Simulator
Returns the k-th largest value in a data set.
=LARGE(array, k)=SMALL() Interactive Masterclass & Practice Simulator
Returns the k-th smallest value in a data set.
=SMALL(array, k)=RANK() Interactive Masterclass & Practice Simulator
Returns the rank of a number in a list of numbers.
=RANK(number, ref, [order])=PERCENTILE() Interactive Masterclass & Practice Simulator
Returns the k-th percentile of values in a range.
=PERCENTILE(array, k)=STDEV() Interactive Masterclass & Practice Simulator
Estimates standard deviation based on a sample.
=STDEV(number1, [number2], ...)=VAR() Interactive Masterclass & Practice Simulator
Estimates variance based on a sample.
=VAR(number1, [number2], ...)=TEXTSPLIT() Interactive Masterclass & Practice Simulator
Splits text strings across columns and/or rows using delimiters.
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])=TEXTJOIN() Interactive Masterclass & Practice Simulator
Combines the text from multiple ranges and/or strings, and includes a delimiter you specify.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)=CONCAT() Interactive Masterclass & Practice Simulator
Combines the text from multiple ranges and/or strings, but does not provide delimiter or ignore_empty arguments.
=CONCAT(text1, [text2], ...)=LEFT() Interactive Masterclass & Practice Simulator
Returns the specified number of characters from the start of a text string.
=LEFT(text, [num_chars])=RIGHT() Interactive Masterclass & Practice Simulator
Returns the specified number of characters from the end of a text string.
=RIGHT(text, [num_chars])=MID() Interactive Masterclass & Practice Simulator
Returns a specific number of characters from a text string, starting at the position you specify.
=MID(text, start_num, num_chars)=LEN() Interactive Masterclass & Practice Simulator
Returns the number of characters in a text string.
=LEN(text)=FIND() Interactive Masterclass & Practice Simulator
Locates one text string within a second text string, and returns the number of the starting position (Case-Sensitive).
=FIND(find_text, within_text, [start_num])=SEARCH() Interactive Masterclass & Practice Simulator
Locates one text string within a second text string (Not Case-Sensitive, supports * and ? wildcards).
=SEARCH(find_text, within_text, [start_num])=REPLACE() Interactive Masterclass & Practice Simulator
Replaces part of a text string, based on the number of characters you specify, with a different text string.
=REPLACE(old_text, start_num, num_chars, new_text)=SUBSTITUTE() Interactive Masterclass & Practice Simulator
Substitutes new_text for old_text in a text string.
=SUBSTITUTE(text, old_text, new_text, [instance_num])=TRIM() Interactive Masterclass & Practice Simulator
Removes all spaces from text except for single spaces between words.
=TRIM(text)=PROPER() Interactive Masterclass & Practice Simulator
Capitalizes the first letter in each word of a text string, and converts all other letters to lowercase.
=PROPER(text)=UPPER() Interactive Masterclass & Practice Simulator
Converts text to uppercase.
=UPPER(text)=LOWER() Interactive Masterclass & Practice Simulator
Converts all uppercase letters in a text string to lowercase.
=LOWER(text)=EXACT() Interactive Masterclass & Practice Simulator
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise (Case-Sensitive).
=EXACT(text1, text2)=TEXT() Interactive Masterclass & Practice Simulator
Converts a value to text in a specific number format (e.g. "$#,##0.00" or "YYYY-MM-DD").
=TEXT(value, format_text)=VALUE() Interactive Masterclass & Practice Simulator
Converts a text string that represents a number to a number.
=VALUE(text)=TEXTBEFORE() Interactive Masterclass & Practice Simulator
Returns text that occurs before a given character or string.
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])=TEXTAFTER() Interactive Masterclass & Practice Simulator
Returns text that occurs after a given character or string.
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])=TODAY() Interactive Masterclass & Practice Simulator
Returns the serial number of the current date.
=TODAY()=NOW() Interactive Masterclass & Practice Simulator
Returns the serial number of the current date and time.
=NOW()=DATE() Interactive Masterclass & Practice Simulator
Returns the sequential serial number that represents a particular date.
=DATE(year, month, day)=TIME() Interactive Masterclass & Practice Simulator
Returns the decimal number for a particular time.
=TIME(hour, minute, second)=YEAR() Interactive Masterclass & Practice Simulator
Returns the year corresponding to a date as a four-digit integer.
=YEAR(serial_number)=MONTH() Interactive Masterclass & Practice Simulator
Returns the month of a date represented by a serial number (1 to 12).
=MONTH(serial_number)=DAY() Interactive Masterclass & Practice Simulator
Returns the day of the month (integer from 1 to 31).
=DAY(serial_number)=EDATE() Interactive Masterclass & Practice Simulator
Returns the serial number that represents the date that is the indicated number of months before or after a start date.
=EDATE(start_date, months)=EOMONTH() Interactive Masterclass & Practice Simulator
Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
=EOMONTH(start_date, months)=NETWORKDAYS() Interactive Masterclass & Practice Simulator
Returns the number of whole working days between start_date and end_date (excluding weekends and optional holidays).
=NETWORKDAYS(start_date, end_date, [holidays])=WORKDAY() Interactive Masterclass & Practice Simulator
Returns a date that is the indicated number of working days before or after a date.
=WORKDAY(start_date, days, [holidays])=DAYS() Interactive Masterclass & Practice Simulator
Returns the number of days between two dates.
=DAYS(end_date, start_date)=DATEDIF() Interactive Masterclass & Practice Simulator
Calculates the number of days, months, or years between two dates.
=DATEDIF(start_date, end_date, unit)=WEEKDAY() Interactive Masterclass & Practice Simulator
Returns the day of the week corresponding to a date (1 for Sunday to 7 for Saturday by default).
=WEEKDAY(serial_number, [return_type])=WEEKNUM() Interactive Masterclass & Practice Simulator
Returns the week number of a specific date.
=WEEKNUM(serial_number, [return_type])=PMT() Interactive Masterclass & Practice Simulator
Calculates the payment for a loan based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])=PV() Interactive Masterclass & Practice Simulator
Returns the present value of an investment: the total amount that a series of future payments is worth now.
=PV(rate, nper, pmt, [fv], [type])=FV() Interactive Masterclass & Practice Simulator
Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
=FV(rate, nper, pmt, [pv], [type])=NPV() Interactive Masterclass & Practice Simulator
Calculates the net present value of an investment by using a discount rate and a series of future payments and income.
=NPV(rate, value1, [value2], ...)=IRR() Interactive Masterclass & Practice Simulator
Returns the internal rate of return for a series of cash flows.
=IRR(values, [guess])=XNPV() Interactive Masterclass & Practice Simulator
Returns the net present value for a schedule of cash flows that is not necessarily periodic.
=XNPV(rate, values, dates)=XIRR() Interactive Masterclass & Practice Simulator
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
=XIRR(values, dates, [guess])=RATE() Interactive Masterclass & Practice Simulator
Returns the interest rate per period of an annuity.
=RATE(nper, pmt, pv, [fv], [type], [guess])=NPER() Interactive Masterclass & Practice Simulator
Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
=NPER(rate, pmt, pv, [fv], [type])=SLN() Interactive Masterclass & Practice Simulator
Returns the straight-line depreciation of an asset for one period.
=SLN(cost, salvage, life)=ISBLANK() Interactive Masterclass & Practice Simulator
Returns TRUE if the value refers to an empty cell.
=ISBLANK(value)=ISNUMBER() Interactive Masterclass & Practice Simulator
Returns TRUE if the value is a number.
=ISNUMBER(value)=ISTEXT() Interactive Masterclass & Practice Simulator
Returns TRUE if the value is text.
=ISTEXT(value)=ISNONTEXT() Interactive Masterclass & Practice Simulator
Returns TRUE if the value is not text (blank cells are nontext).
=ISNONTEXT(value)=ISLOGICAL() Interactive Masterclass & Practice Simulator
Returns TRUE if the value is a logical value (TRUE or FALSE).
=ISLOGICAL(value)=ISERROR() Interactive Masterclass & Practice Simulator
Returns TRUE if the value is any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
=ISERROR(value)=ISERR() Interactive Masterclass & Practice Simulator
Returns TRUE if the value is any error value except #N/A.
=ISERR(value)=ISNA() Interactive Masterclass & Practice Simulator
Returns TRUE if the value is the #N/A (value not available) error value.
=ISNA(value)=ISFORMULA() Interactive Masterclass & Practice Simulator
Returns TRUE if there is a reference to a cell that contains a formula.
=ISFORMULA(reference)=ISODD() Interactive Masterclass & Practice Simulator
Returns TRUE if the number is odd.
=ISODD(number)=ISEVEN() Interactive Masterclass & Practice Simulator
Returns TRUE if the number is even.
=ISEVEN(number)=TYPE() Interactive Masterclass & Practice Simulator
Returns an integer representing the data type of a value: 1 = number, 2 = text, 4 = logical, 16 = error, 64 = array.
=TYPE(value)=DSUM() Interactive Masterclass & Practice Simulator
Adds the numbers in a column of records in a list or database that match conditions you specify.
=DSUM(database, field, criteria)=DCOUNT() Interactive Masterclass & Practice Simulator
Counts the cells that contain numbers in a column of records in a database that match conditions you specify.
=DCOUNT(database, field, criteria)=DAVERAGE() Interactive Masterclass & Practice Simulator
Averages the values in a column of a list or database that match conditions you specify.
=DAVERAGE(database, field, criteria)=DGET() Interactive Masterclass & Practice Simulator
Extracts from a database a single record that matches conditions you specify.
=DGET(database, field, criteria)Earn Your Excel Function Master Certificate
Complete the interactive exercises across the 7 course modules to unlock and generate your personalized Certificate of Completion.
Google AdSense Responsive Unit (auto)
Publisher ID: ca-pub-7444400132160337. Ad unit ready for live ad serving upon Google approval.