The previous posts covered the Excel interface and core functionalities. This post looks at Excel functions.
Functions are predefined formulas that perform a specific task, saving us time and effort. Everything we pass inside a function is an argument.
Get Lakshmi Priyadarshni Krishnamurthy’s stories in your inbox
Join Medium for free to get updates from this writer.
Knowing how these formulas work helps understand how functions in programming languages like R and Python work, as they essentially perform the same tasks (and much more).
➕Math & Statistics Functions
These functions are fundamental for any data analysis and will help us quickly get insights from our numbers.
SUM, COUNT, AVERAGE, MIN, MAX
These are the most basic and frequently used functions.
They help us find the sum, count (number of non-blank cells), average, minimum, and maximum value within a range of cells.
Tip: - COUNT works only with numbers. - COUNTA counts any cell that’s not empty, including text. - COUNTBLANK counts empty cells.
Quick Trick: The AutoSum button is a fast way to use many common functions. - The main button defaults to SUM, but its dropdown provides quick access to other functions. - We can also use the shortcut ALT + = to quickly insert a SUM formula.
2. SUMIF, COUNTIF, AVERAGEIF
These are the conditional versions of the functions above. They perform an operation only if a certain condition is met.
Example: =SUMIF(D1:D5, “>50”) would sum up all the numbers in the range D1:D5 that are greater than 50.
To input multiple conditions, use SUMIFS, COUNTIFS, or AVERAGEIFS.
Wildcards: When we need to set a condition for text, we can use wildcards. - * (asterisk):Matches any number of characters. “*a” would find any word that ends with the letter “a”. - ? (question mark): Matches exactly one character. “a?c” would match “abc” but not “abac”.
3. LARGE and SMALL
These functions are great for finding the “nth” largest/ smallest value in a dataset.
Example: =LARGE(A1:A100, 3) would find the third-largest value in the range.
4. ROUND
These functions are used to manage decimals.
ROUND(number, num_digits): Rounds a number to a specified number of digits. Example: ROUND(3.333, 1) rounds off to 3.3.
ROUNDUP: Rounds a number up, away from zero. Example: ROUNDUP(5.4) rounds to 6
ROUNDDOWN: Rounds a number down, toward zero. Example: ROUNDDOWN(5.8) rounds to 5
🔤Text Functions
These functions are perfect for manipulating and cleaning up text data.
TRIM and CONCAT
TRIM() removes extra spaces from a text string. It gets rid of leading spaces and any extra spaces between words.
CONCAT() (or the older CONCATENATE()) joins text from different cells or text strings together. We can also use the ampersand symbol & as a shortcut for concatenation.
2. LEFT, RIGHT, MID, and LEN
LEFT(text, num_chars): Extracts a specified number of characters from the beginning of a text string.
RIGHT(text, num_chars): Extracts a specified number of characters from the end of a text string.
MID(text, start_num, num_chars): Extracts a specified number of characters from the middle of a text string, starting at a given position.
LEN(text): Counts the number of characters in a text string.
3. UPPER, LOWER, PROPER
These functions are used to change the case of text.
UPPER() makes all letters uppercase.
LOWER() makes all letters lowercase.
PROPER() capitalizes the first letter of each word.
💭Logical Functions
These functions help us make decisions and perform checks in our formulas.
IF, AND, OR, NOT
IF(logical_test, value_if_true, value_if_false): Runs a logical test and returns one value if the test is true and another if it’s false.
AND() and OR() are often used within an IF statement to check for multiple conditions. AND requires all conditions to be true, while OR requires only one to be true.
For multiple, nested IF conditions, the IFS() function is a better option.
🔍Lookups & Reference Functions
These are some of the most powerful and useful functions in Excel. They help us search for data in large tables.
VLOOKUP & HLOOKUP
VLOOKUP searches for a value vertically in the first column of a table and returns a corresponding value from a specified column on the matched row in the right.
HLOOKUP does the same thing, but it searches horizontally across the table and returns the value from the matched column.
Common issues with VLOOKUP and HLOOKUP: It can only look from left to right (or in case of HLOOKUP, top to bottom). Plus it only finds the first match, and can break if we insert/ delete columns.
2. INDEX-MATCH
This is a more flexible and powerful alternative to VLOOKUP. It’s a combination of two separate functions:
MATCH() finds the position (row number or column number) of a value in a list.
INDEX() returns a value from a specified cell within a range, using a row and column number.
By nesting MATCH inside INDEX, we can perform lookups in any direction and avoid the limitations of VLOOKUP.
Example: =index(B15:G20, match(A4, table2[column2_name]), 3) — finds the row number using match and gets the matching value - array → the lookup range (here: B15:G20) - row_num → the row to pick (here: MATCH(A4, table2[column2_name])) - column_num → column inside the array to return (defaults to 1 if we skip it) (we can use match here as well)
3. XLOOKUP
The modern replacement for VLOOKUP, HLOOKUP, and INDEX-MATCH. It’s simpler and more flexible.
XLOOKUP(lookup_value, lookup_array, return_array): It looks for a value in one column and returns a corresponding value from another column, regardless of its position.
Tip: XLOOKUP is more robust and less prone to errors than its predecessors.
Example: E.g.- XLOOKUP(F6, “A:A”, “B:B”, “error”)
What the above formula does- - Takes the value in F6 - Looks up column A and finds a match - Returns the value from B for the matched row - If nothing is found, it gives the error message
Tip: To get the second or later occurrences, we can use XLOOKUP’s optional arguments. The key is to change the search_mode.
⏲️Other Useful Functions
Date & Time Functions:
TODAY(): Returns the current date.
NOW(): Returns the current date and time.
EDATE(start_date, months): Returns the date that is a specified number of months before or after a start date. For example, =EDATE(“2025–08–28”, 3) returns “11/28/2025”.
EOMONTH(start_date, months): Returns the last day of the month a specified number of months before or after a start date.
DATEDIF(start_date, end_date, unit): Calculates the difference between two dates in days, months, or years.
TRANSPOSE(): Converts a range of cells from rows to columns, or vice versa.
⚠️Common Errors to Watch For
#N/A: The lookup didn’t find a matching value.
#REF!: The formula refers to a cell that has been deleted or is invalid.
#VALUE!: The wrong type of argument was used in the formula (e.g., trying to add text).
#DIV/0!: A number is being divided by zero.
Tip: IFERROR(): Wraps around a formula to handle errors gracefully. Instead of displaying a messy error message like #N/A, we can specify a custom message like “Not Found”.
This guide covers the most useful functions in Excel. The next post covers advanced topics like Pivot Tables, Macros, Power Query, and Power Pivot.
