This comprehensive Excel functions reference guide covers essential Math & Trig, Statistical, Text, and Lookup functions. Master these Excel formulas to streamline your data analysis, automate calculations, and boost your productivity in spreadsheet management.
| Math & Trig Functions |
Description |
|---|---|
| ABS | Returns the absolute value of a number. |
| AGGREGATE | Returns one of many different aggregates from a list or database |
| Rounding Functions | |
| CEILING.MATH | Returns a Number Rounded away from Zero to the Nearest Multiple of significance |
| FLOOR.MATH | Rounds a number down toward zero to the nearest multiple of significance |
| INT | Rounds a number down to the nearest integer |
| EVEN | Rounds a decimal number away from zero to the nearest even integer |
| ODD | Rounds a decimal number away from zero to the nearest odd integer |
| ROUND | Rounds a number to a specified number of digits |
| ROUNDDOWN | Always rounds a number down |
| ROUNDUP | Always rounds a number up |
| MROUND | Rounds a value to a desired multiple |
| TRUNC | Truncates a number by removing some or all of the decimal portion of a number |
| MOD | Returns the remainder of a division problem |
| GCD | Greatest Common Divisior (Returns the greatest common divisor of two or more integers) |
| LCM | Least Common Multiple (Returns the least common multiple of integers) |
| POWER | Returns the result of a number raised to a power |
| RANDBETWEEN | Generates a Random number between two specified numbers |
| SIGN | Determines the sign of a number then returns a 1 if the number is positive, a zero (0) if the number is 0 and a -1 if the number is negative |
| SUMSQ | Returns the Sum of the Squares |
| PRODUCT | Multiplies all of the numbers given for arguments |
| SUMPRODUCT | Returns the sum of the multiplications of values in corresponding ranges |
| SQRT | Returns the square root of a number |
| SUBTOTAL | Returns the subtotal of the numbers in a column from a list or database |
| SUM | Sums a series of values |
| SUMIF | Used to sum values provided that they meet a condition that you specify |
| SUMIFS | Used to sum values only if multiple conditions are met |
|
Statistical Functions |
|
| Count Functions | |
| COUNT | Counts the number of cells in a range that contain numbers |
| COUNTBLANK | Counts only those cells in a range that are empty |
| COUNTA | Counts the Number of Nonblank cells |
| COUNTIF | Returns a Number Rounded away from Zero to the Nearest Multiple of significance |
| COUNTIFS | Rounds a number down toward zero to the nearest multiple of significance |
| Frequency and Rank Functions | |
| FREQUENCY | Number of times values appear within a range of values |
| RANK.EQ | Returns the rank of a number in a list of numbers |
| RANK.AVG | Returns the rank of a number in a list of numbers. If more than one value has the same rank, the average rank is returned. |
| PERCENTRANK.INC | Returns the rank of a value in a data set as a percentage (0 to 100 inclusive) of the data set |
| PERCENTRANK.EXC | Returns the rank of a value in a data set as a percentage (0 to 100 exclusive) of the data set |
| PERCENTILE.INC | Given an array of values, it returns the value that is at the percentile you specify. Can find values anywhere from the 0 percentile to the 100-percentile inclusive. |
| PERCENTILE.EXC | Given an array of values, it returns the value that is at the percentile you specify. Can find values anywhere from the 0 percentile to the 100-percentile exclusive. |
| Measures of Central Tendency | |
| AVERAGE | Returns the Average of its arguments from only those cells that contain numeric data |
| AVERAGEA | Returns the Average of its arguments. It includes Numeric and Nonnumeric cells. It does not include Blanks. |
| AVERAGEIF | Averages values that meet a single criterion |
| AVERAGEIFS | Averages values that meet multiple criteria |
| MEDIAN | The median is the the middle number of a group of numbers; half the numbers have values greater than the median, and half the numbers have values less than the median |
| MODE.SNGL | Finds the Mode which is the most frequently occuring number in a set of numbers |
| MODE.MULT | Returns a vertical array of the most frequently occuring, or repetivitive values in an array or range of data. |
| Size Functions | |
| MIN | Returns the smallest value from a set of values |
| MAX | Returns the largest value from a set of values |
| LARGE | Returns the nth largest value from a set of values |
| SMALL | Returns the nth smallest value from a set of values |
| Variance Functions | |
| VAR.S | Estimates variance based on a sample of the entire population |
| VAR.P | Calculates variance based on the entire population |
| Standard Deviation | |
| STDEV.S | Standard deviation based on a sample of the entire population |
| STDEV.P | Standard deviation based on the entire population |
| Trend Line Functions | |
| SLOPE | Measures the rate of Change in a line |
| INTERCEPT | The Point where a lince crosses the Y-axis |
| TREND | Use this function when you have a series of known X and Y coordinates and you want to follow the pattern of that line to new X-coordinates that you specify. Excel will return the y_coordinates for the new X-coordinates |
| FORECAST | Predicts a future value based on existing values |
| Text Functions | |
| CONCATENATE | Used to join as many as 255 text strings into a single text string |
| LEN | Returns the number of characters in a string |
| Alignment Functions | |
| LEFT | Returns the number of characters you specify from the left side of a string. |
| MID | Returns the number of characters you specify from a string starting at the position you specify |
| RIGHT | Returns the number of characters you specify from the right side of a string |
| Change the Case of a String | |
| LOWER | Converts a string to all lowercase letters |
| UPPER | Converts a string to all uppercase letters |
| PROPER | Capitalizes the first letter in a string and all other letters that follow any character other than a letter. All other characters in the string are converted to lowercase letters |
| Find a String within another String | |
| FIND | Returns the starting position of where one string is found in another string. It is case sensitive. |
| SEARCH | Returns the starting position of where one string is found in another string. It is not case sensitive. You can use wildcards in the search string. |
| Replace Part of a String with another String | |
| REPLACE | Replaces part of a text string with a different text string |
| SUBSTITUTE | Replaces existing text with new text. |
|
Lookup & Reference Functions |
|
| ADDRESS | Returns the address of a cell in a worksheet, given specified row and column numbers. |
| AREAS | Returns the number of areas in a reference. The reference can include a single cell, a range of contiguous cells, or a range of non-contiguous cells. |
| CHOOSE | Determines which value from a list of values is to be retrieved based on its position in the list |
| Column and Row Functions | |
| COLUMN | Returns the column number from a reference |
| COLUMNS | Returns the number of columns in an array or reference |
| ROW | Returns the row number of a reference |
| ROWS | Returns the number of rows in an array or reference |
| OFFSET | Returns a reference to a range that is a given number of rows and columns from a given reference |
| TRANSPOSE | Converts a vertical range of cells to a horizontal range, or vice versa |
| Search Functions | |
| INDEX | Used to retrieve the value from a single row or the value from any two-dimensional range. It doesn't use a lookup value it just retrieves the value from the specified location. |
| LOOKUP | Searches a single row or column for a value and then returns a value from the same position in another single row or column range. |
| HLOOKUP | Horizontal Lookup. Uses a lookup value to search the top row of a table or array for a matching value then returns the value from the same column as the row you specify. |
| VLOOKUP | Looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify |
| MATCH | Returns the relative position of an item in an array that matches a specified value in a specified order |
Understanding Excel Functions: A Complete Guide
Excel functions are powerful tools that transform raw data into actionable insights. Whether you’re managing budgets, analyzing sales data, or tracking project metrics, understanding these formulas is essential for efficient spreadsheet work. This reference guide organizes Excel’s most commonly used functions into four main categories: Math & Trig, Statistical, Text, and Lookup & Reference functions.
Why Excel Functions Matter
Excel functions automate calculations that would otherwise require manual computation. Instead of calculating totals, averages, or conditional sums by hand, functions perform these operations instantly and accurately. They save time, reduce errors, and enable complex data analysis that would be impractical to perform manually.
Math & Trigonometry Functions
Mathematical functions form the foundation of spreadsheet calculations. From basic operations like SUM and PRODUCT to specialized rounding functions like CEILING.MATH and FLOOR.MATH, these formulas handle numerical data with precision. The rounding functions are particularly useful when working with financial data that requires specific decimal places or when you need to round to nearest multiples for pricing strategies.