Top 101 Excel Formulas

This guide provides an overview of Excel Formulas and functions from the ground up. Learn the basics through useful examples.

We divided the article into multiple sections. All sections explain the basic formulas, furthermore, you can find more advanced examples. Before taking a deep dive, here are a few must-have articles about named ranges and logical operators.

Microsoft Excel formulas help you to count, analyze, filter, convert, summarize and transform data to boost productivity and speed up your work.

Table of contents:

Formulas and Functions

There are two ways to perform calculations in Excel; using Formulas and Functions. But, first, it’s time to clarify their differences.

What is a Formula? Excel Formulas are math statements that can perform calculations and manipulate the values of other cells. Also, it can test conditions and frequently uses logical operators.

Let’s see a simple example: =B1 + B2 + B3 + B4 finds the sum of the cell values between cell B1 and cell B4.

What is a function? Functions are built-in presets, so there are hard-coded Excel formulas. With its help, you can avoid manual data entry, and it has small, user-friendly names.

For example, the =SUM(B1:B4) formula uses the SUM function to sum all cell B1 to B4 values.

101 Excel Formulas and Functions

For Excel newbies, we have a piece of good news. First, please read our article about the basics. We provide an easy-to-understand overview. Then, start your adventure!

COUNT

The COUNT function counts the total number of cells in a range that contains numbers. In the example below, you want to count the numbers of cells in the range B2:B6.

count function

Use the following formula:

=COUNT(B2:B6)

Let us see how to apply the COUNT formula in various situations.

COUNT Formulas

The main goal of count functions in Excel is to count cells and values. In addition, you can use multiple criteria to create advanced calculations.

Count cells that are greater than or cells less than a value

formula to count cells that are greater than or cells less than a value 02

To count the number of cells where the values are greater than a given number, you can use the COUNTIF function.

The function uses two arguments: range and criteria; the generic formula is:

=COUNTIF(range, criteria)

You can use a named range instead of using B2:B8 cell references. In the example, the range B2:B8 represents a range of cells that contain numbers. Criteria represent the number value (in cell F2) above which you want to count. Here is another example.

Count cells between two numbers

Sometimes we need to use more than one criteria. In this case, the COUNTIF function can help count the number of cells between upper and lower limits.

COUNTIFS formula - count cells between two number 03

Here is the general formula:

=COUNTIFS(criteria_range, criteria1, criteria_range2, criteria2….)

The criteria_range represents a range of cells that contain numbers. In the example, cell E6 has the lower limit, and we will use the value in cell E2 as an upper limit of the numbers you want to count.

In the example, use the formula in E4:

=COUNTIFS(B2:B8,">"&E1,B2:B8,"<"&E2)

Count blank or non-blank cells in a range

Formula to Count blank or non-blank cells in a range 04

Use the COUNTBLANK function to count the number of blank cells in a range.

The formula in cell F4 is:

=COUNTBLANK(B2:B8) 

We have only two empty cells in the range B2:B8, so the COUNTBLANK formula returns 2.

If you want to count cells that are not blank in a range, use the COUNTA function. In the example, the formula in cell F5 gets the number of the non-blank cells.

=COUNTA(B5:B9)

After evaluating the formula, the result is that the range contains five non-blank cells.

Count cells that contain text

Count cells that contain text

In the example, you want to count only these cells that contain text values. Therefore, the generic formula combines the COUNTIF function with the ISTEXT function.

Create a new column and apply the following formula in cell C3:

=ISTEXT(C3:C9)

ISTEXT checks whether a value is a text and returns TRUE or FALSE. In the example, we want to count cells in the range C3:C9 that contain text values. We added various data types, text, numbers, dates, and formula errors. Copy the formula down.

Enter the formula in cell F3:

=COUNTIF(C3:C9, TRUE) 

The formula uses the COUNTIF function and counts the number of cells in a range that match the criteria. In the example, we have three cells that contain text values.

To count the non-blank cells in a range, use FALSE as the second argument.

=COUNTIF(C3:C9, FALSE)

Count cells that contain even or odd numbers

Count cells that contain even or odd numbers

You can combine the SUMPRODUCT function with the MOD function to count cells that contain odd or even numbers.

The formula in cell E3:

=SUMPRODUCT(--(MOD(numbers,2)=1))

This formula returns 5 because there are five odd numbers in the range B3:B8.

Explanation: First, the SUMPRODUCT tests on an array using one (or more) criteria. After that, count the results. In the example, we are running a test for an odd number, which uses the MOD function:

MOD(numbers,2)=1

This part of the formula gets the remainder value after division by 2. The MOD function will return a remainder of 1 for odd numbers and gets 0 for even numbers.

Count items in a group with a percentage breakdown

Count items in a group with a percentage breakdown

In the example, we will use the COUNTIF and COUNTA functions in one formula to create a percentage breakdown by risk level. The goal is to split the different risk levels into three categories.

Risk is a named range that refers to B3:B13. We use a simple COUNTIF formula in cell F3 to count the number of incidents.

=COUNTIF(risk, E3)

Copy the formula down, and it will return the count of each risk level. Now, take a look at the formula in G3:

=COUNTIF(risk,E3)/COUNTA(risk)

The left part splits the risk levels into three categories, and the right part of the formula counts all cells in a range using the COUNTA function. You have only one thing left: apply the percentage number format in column G.

More COUNT-based examples:

SUM

SUM is the most used, must-have function in some basic Excel formulas. For example, this formula below summarizes values from a selection of columns or rows that contain numbers.

basic sum formula example

SUM Formulas

Here is a list of some useful Excel formulas based on sum:

Sum if the cell value greater than or less than

sum-if-cell-value-greater-than-or-less-than

You can use the SUMIF function to sum cells greater than a given value. In this example, we want to sum values greater than $1200.

Apply the formula in cell F3:

=SUMIF(sales,">1200")

Good to know that the formula supports all logical operators as criteria. If you want to find and sum values where the sales are less than $1400, simply apply the “<$1400” as the criteria.

Don’t forget to use the double-quote before and after the criteria, else you will get an error.

=SUMIF(sales,"<1400")

Sum values between two numbers

Excel formula to sum values between two numbers

If you want to sum cell values between two numbers (criteria), SUMIF can help.

The formula in cell E6:

=SUMIFS(sales,sales,">1200",sales,"<1400")

The formula uses a named range, “sales” for cells C3:C13, and sums all cell values in column C if the numbers are greater than 1200 and less than 1400.

The “between” criteria is a special relationship. It contains the AND logical operator. In the example, we find the values in column C, which is matching to all of our criteria:

  • Criteria 1 = sales, “>1200”
  • Criteria 2 = sales, “<1400”

The sum range is the range C3:C13. The formula uses these criteria and sums all sales greater than 1200 and less than 1400.

Sum Top n values in a range

SUMPRODUCT formula Sum Top n values in a range

Use a formula that combines the SUMPRODUCT and LARGE functions to sum the top N values in a range.

To get the nth value in a range, use the formula below:

=LARGE(range, n)

In the formula, “sales” (the first argument) is a range of cells that contain numeric values, and N represents the nth value. But we need the sum of the top n values, not the nth value.

We can use the second argument as an array. Then, instead of a single number value, the LARGE function will return an array.

The formula finds the 1st, 2nd, and 3rd largest value in the sales range:

=LARGE(sales, {1,2,3}

In the example, the array contains the following values: {92,84,73}. Finally, SUMPRODUCT sums all numbers in the result array and returns 249.

=SUMPRODUCT(LARGE(sales,{1,2,3}))

If you want to sum the top 7 values, append the array like this: {1, 2, 3, 4, 5, 6, 7}

Sum every nth row

sum every nth row or column

To sum every nth row, you can use a formula with SUMPRODUCT, MOD, and ROW functions.

=SUMPRODUCT(--(MOD(ROW(rnum)-ROW(B3)+1,E3)=0), values)

We use a named range, “rnum” which refers to range B3:B14.

Take a look inside the formula. The ROW function gets a set of row numbers for the range, which looks like this: {1,2,3,4,5,6,7,8,9,10,11,12}

After that, the MOD function returns the remainder for each row number divided by N.

Use a double-negative (–) to convert TRUE and FALSE to ones and zeros. The output array looks like the following: {0,1,0,1,0,1,0,1,0,1,0,1}

For example, if N=2, the MOD function will return the array below:

SUMPRODUCT, MOD, ROW Excel formula

Where the value equals 1, SUMPRODUCT multiplies and then sums the arrays’ values. In the example, the total for every second row is:

=(10 + 22 + 16 + 22 +32 + 70) = 172

This method works fine when you want to sum every nth column; you need to replace the ROW function with COLUMN.

Sum if a date is greater than

Sum formula if a date is greater than

SUMIF function gets the answer if you want to sum sales for a given period. In the example, you want to add a date and find the dates greater than the start date.

The formula in cell G2:

=SUMIF(date,">"&DATE(2022,8,20), sales)

In the formula, “date” and “sales” are named ranges and sum the sales in column D when the date in column C is greater than Aug 20, 2022.

Good to know that you can use other logical operators (equal to, greater than or equal to, less than, etc.) So, you can change the criteria easily.

We are using Excel’s DATE function. With its help, it is easy to split dates into separate values. Add an ampersand to join the logical operator to date (&).

Additional resources:

AVERAGE

Use the AVERAGE function to calculate the average of numbers in a range of cells.

Formula example:

=AVERAGE(B3:B8) shows a simple average of six numbers in a range.

basic average function example

Tip: the AVERAGE function ignores logical values, empty cells, and cells that contain any text.

Average formulas

The following examples will explain how to use various Excel formulas with AVERAGE to calculate top or bottom scores and values in a given range.

Average of top 5 scores

calculate average of top 5 scores

Combine the AVERAGE and LARGE functions to calculate each group’s average of the top 5 scores.

In the example above, enter the following formula in cell K3, then copy it down:

=AVERAGE(LARGE(C3:H3,{1,2,3,4,5}))

The LARGE(C3:H3,1) formula returns the highest value in a given range. Using the same logic, LARGE(C3:H3) returns the second largest value, and so on.

Our example will use an array constant {1, 2, 3, 4, 5} as a second argument of the LARGE function. The returned array will include the highest five values.

In cell K3, LARGE(C3:H3, {1,2,3,4,5}) returns the following array:

{10, 8, 7, 4, 3}

The AVERAGE function will use the return array as an argument and gets the average of the highest five values:

=AVERAGE({10, 8, 7, 4, 3} = 6.4

Calculate the average of the last 3 value

Excel formulas - Average of last 3 values

In the next example, we want to calculate the average of the last three values in a range. To do that, we will use three functions in a single formula.

The formula below uses the AVERAGE, COUNT, and OFFSET functions:

=AVERAGE(OFFSET(C2,COUNT(C2:C11),0,-3))

We have nine numeric values in column C. Using these values; First, OFFSET will use the C2 cell as a starting point and offsets nine rows until the end of the range, C11. After that, steps three cells up to create the starting point of the new range, cell C9.

Finally, OFFSET returns the range C9:C11 to the AVERAGE function, which calculates the average of values in that range.

Average sales between Monday and Friday

average sales on working days

We will use two criteria in the example to calculate the average sales on working days, from Monday to Friday. The AVERAGEIFS function can handle multiple criteria, the formula in cell A4:

=AVERAGEIFS(amount,days,">=1",days,"<=5")

The formula uses two named ranges:

  • amount = D3:D9
  • days = C3:C9

First, create a helper column in column C. For example, the WEEKDAY function helps you extract an integer value from a date. The question is how to identify if the date is a working day.

The formula in cell C3:

=WEEKDAY(B3,11)

Configure the second argument (return_type) and use 11 as an output format. Using this method, Monday will be 1, Tuesday returns with 2, and so on.

In the example, the AVERAGEIFS formula will use two criteria to calculate the average sales on working days:

  • days >= 1
  • days <= 5

Finally, we have the proper criteria; you can build the final formula:

=AVERAGEIFS(amount,days,">=1",days,"<=5")

The average sales are $1755 on the working days.

IF

The IF function has superb usability in Microsoft Excel. After the basic example, we will show you more advanced examples.

IF Formulas

Basic IF formula example

The IF function runs a logical test and returns the TRUE (=1) or FALSE (=0) output.

basic IF example

The formula in cell G3:

=IF(B3>C3, TRUE, FALSE)

checks if the value at B3 is greater than the value in cell C3. If the logic is true, the result is 1. If not, 0.

Use a description to make the formula easy to readable: =IF(B4>C4, “B3 > C3”, “C3 < B3”). Because the B3>C3, the formula returns with the text: “B3 > C3”. To write the greater value in a cell, use the formula:

=IF(B3>C3, B3, C3)

In this case, the result is 65 in cell E6.

Extract name If the cell contains a partial match

Formula to Extract name If the cell contains a partial match

The following formula example will show how to use the IF, SEARCH, and ISNUMBERS functions together to extract a name if the cell contains a partial match.

The IF function provides versatile formula, but it has no wildcard support. So, if you want to use it for testing text values, we need to append the formula with the SEARCH and ISNUMBER functions.

To extract names that contain “excelkid”, apply the formula in cell C3:

=IF(ISNUMBER(SEARCH("excelkid",B5)),B5,"")

First, the formula checks the content of the B5 cell. After that, uses a logical test: IF the cell contains the “excelkid” string, it will return the complete email address.

If no partial match is found, we “create” a blank cell, using “” as the last argument.

Nested IF function example using IFS

Nested IF function example using IFS

You can use the IFS function with multiple criteria instead of multiple (nested) IF functions. IFS checks all criteria and returns the first result where the test is TRUE.

The big advantage is that the IFS-based formula is shorter, providing better readability. In the example above, we create categories (bands) to identify the bonus grade for all sales.

Enter the following formula in D3, then copy it down:

=IFS(C3<8000,"No Bonus",C3<10000,"C1",C3<12000,"C2",C3<=13000,"C3",C3>13000,"C4")

The formula must test the lowest sales first and get the TRUE if the sales are in the given category. For example, C3= $12754. The expression will check the criteria in the following order:

IF(C3<3000) ->FALSE, the test jumps to the next criteria and tests cell C3 again. The first matching value (if the test returns with TRUE) is the “C3 bonus category”.

IF and logical operators (multiple criteria)

if and logical operators

You can use the IF function to test multiple conditions using the common logical operators (AND, OR).

In the example, enter the formula below in cell E3:

=IF(AND(D3>10000,OR(C3="West",C3="North")),"Match","-")

If the sales are greater than $10000 and the region is either “West” or “North”, the formula returns “Match”. In other cases, the “-” formula will use a “-” string as an output.

Following the best practice, evaluate the formula from the inside out:

=AND(D3>10000,OR(C3="West",C3="North")),"Match","-")

The expression will return TRUE only if the value in cell D3 is greater than $10000 AND the Region in C3 is either “North” or “West”. When the logical test gets a TRUE result, the IF formula returns “Match”. Else returns the “-” string.

Check if a cell is not blank

excel formulas to check if cell is not blank

As a great replacement for the ISBLANK function, you can use a simple IF formula with logical criteria if you want to check blank or non-blank cells in a range.

Use the formula in cell E3; it is easy to test the blank cells:

=IF(D3<>"","Not blank","blank")

Explanation: If the value in cell D3 is not equal to””, the result is TRUE and returns with a “Not blank” text string. Therefore, the third (optional) argument of the IF function (values if false) is “blank”.

MIN and MAX

If you are working with Excel formulas, you definitely will find the minimum or maximum values in a range. For this purpose, use the MIN and MAX functions. If you have multiple criteria, use the MINIFS and MAXIFS functions.

MIN and MAX Formulas

Find the minimum or maximum value in a range

excel formulas to find the minimum or maximum value in a range

Use the MIN function to find the minimum value in a range. For example, here is the formula to get the minimum value in the range B3:B12.

=MIN(B3:B12)

The MIN function uses multiple arguments. For example, you can use multiple ranges to find the minimum value. The function skips the text values and empty cells and uses only values.

Sometimes we need to find the maximum value in a range; in this case, we use the MAX function. Using the range mentioned above, use the formula below to find the maximum value:

=MAX(B3:B12)

MINIFS and MAXIFS functions

MINIFS and MAXIFS functions

You can use the MINIFS function to get the minimum value in a range and have one or more additional criteria.

In the example, we find the minimum value where the values are greater than 500.

=MINIFS(B3:C12,B3:C12,">500")

Use the MAXIFS function to get the maximum value in a range and have one or more additional criteria.

=MAXIFS(B3:C12,B3:C12,"<800")

In the example, we find the maximum value where the values are less than 800.

DATE and TIME

This section will demonstrate the basic and advanced usage of Excel formulas that uses DATE and TIME functions.

Date and Time Formulas

Calculate days between two dates

You can calculate the remaining days between two dates without using any Excel function. For example, the formula below subtracts the earlier date from the later date.

=end_date - start_date

In the example shown, enter in D5 the following:

=C5-B5
Calculate days between two dates with DAY and EOMONTH

Calculate the days in the given month

You can use a custom formula and combine the EOMONTH with the DAY functions to get the number of days in a selected month.

In the picture above, we have dates in column F. So then, the formula in cell G3 is:

=DAY(EOMONTH(F3,0))

EOMONTH returns the serial number of the last day of the month, before or after the specified number of months. The second argument is 0, so it will return the last day of the month for a given date using a date serial output.

The DAY function converts the date serial to a number value and returns the day using a number format between 28 and 31.

Display the current date and time

display date and time

Sometimes it can be useful to insert the current date into your Workbook. Use the TODAY function to return with the current date.

TODAY and TIME functions do not use arguments, and you can enter the formula with empty parentheses:

=TODAY()

You can use the NOW function if you need to display the current date and time in a worksheet.

=NOW()

Tip: Excel will recalculate the cell at every opening, so if you want to keep it static, use a copy and paste it as a value.

Get the age from a birthdate

get age from birthdate formula

You can extract the age from a birthdate using a formula that uses the TODAY and DATEDIF functions.

In the example, use the formula below and copy it down:

=DATEDIF(C3,TODAY(),"y")

The result (age) always will be up-to-date because Excel will recalculate the TODAY() parts of the formula.

DATEDIF is a hidden (you can’t find it in Excel’s built-in function list) compatibility function but useful and supports all Excel versions. It is a useful function for calculating the period between two dates in years, months, and days.

TEXT

Using the above-listed Excel formulas, you can dive deeply into the world of text transformation, conversion, and extraction.

TEXT Formulas

Text conversion formulas

text conversion formulas - UPPER PROPER LOWER

Excel has more built-in functions for text conversion purposes. For example, the B3 cell contains the following text in the example: “Data is beautiful”.

All functions use a single argument, the cell containing the string you want to transform.

The UPPER function converts a string to all uppercase letters.

=UPPER(B3)

The PROPER function converts a string to proper case, the first letter in each word to uppercase, and all other letters to lowercase.

=PROPER(B3)

The LOWER function converts all letters in a text string to lowercase.

=LOWER(B3)

Capitalize the first letter of a string

capitalize first letter

Excel does not contain a built-in function to capitalize the first letter of a single word or a large string. However, you can create a custom formula that uses the LEFT, MID, and LEN functions.

Enter the following formula in cell C3:

=UPPER(LEFT(B3))&MID(B3,2,LEN(B3))

Evaluate the left part:

=UPPER(LEFT(B3))

capitalize the first letter, in the example, “D”

=MID(B3,2,LEN(B3))

extracts the remaining characters, in the example “ata is beautiful

We use a simple ampersand to concatenate the two parts of the formula; the result: “Data is beautiful.”

Compare two strings

formula to compare two strings

You can use the EXACT function to compare two strings. The function uses a case-sensitive way and checks the differences between upper and lower characters. In case of a match, the following formula returns a TRUE or FALSE result.

Enter cell D3:

=EXACT(B3, C3)

In non-case sensitive comparison, simple use the equal logical operator:

=B3=C3

will return TRUE.

Count total words in a cell

count total words in a cell

Here is the generic formula to count the total words in a cell:

=LEN(TRIM(B3))-LEN(SUBSTITUTE(B3," ",""))+1

Explanation: Excel does not have native support to count words. We will use the SUBSTITUTE, LEN, and TRIM functions to construct a custom formula.

The LEN function counts the total number of characters in the cell with and without spaces, then uses the difference to figure out the word count.

To simplify the formula, think about it:

Total word count = (count of spaces) + 1

The first expression counts all characters in cell B3, then remove the extra spaces.

=LEN(TRIM(B3))

The nested TRIM function removes the first and last spaces and cleans the spaces between words.

=SUBSTITUTE(B3," "," ")

The SUBSTITUTE function replaces all spaces with a zero-length string, “”.

Get the first word from a cell

get the first word from a cell

To extract the first word from a cell that contains text, use the generic formula:

=LEFT(B4,FIND(" ",B4)-1)

Explanation: The FIND function gets the position of the first space character in the text. The LEFT function will use the result (-1) as a num_chars argument and extracts all characters from the beginning until the “text position-1.”

If the cell contains a single word, we need to handle errors using the IFERROR function. The formula works simply: in case of error; the expression returns the original text:

=IFERROR(LEFT(B4,FIND(" ",B4)-1),B4)

Extract the last word from a cell

get last word from a cell

The formula demonstrated above in cell range C3:C9 extracts the last word from an adjacent cell in column B.

=TRIM(RIGHT(SUBSTITUTE(B3, " ", REPT(" ", 300)), 300))

Look under the hood! For example, if your words are longer than 300 characters, change each instance to 300. Using the REPT function, repeat the specific text (space) character 300 times.

=REPT(" ", 300)

This snippet will return 300 joined space characters. After that, the SUBSTITUTE function replaces each blank in the cell value with 300 blanks.

=SUBSTITUTE(B3," ",REPT(" ",300))

Use the RIGHT function to extract 300 characters from the right.

=RIGHT(SUBSTITUTE(B3," ",REPT(" ",300)), 300)

Finally, remove all leading and trailing spaces using the TRIM function.

=TRIM(RIGHT(SUBSTITUTE(B3, " ", REPT(" ", 300)), 300))

Combine text using TEXTJOIN

textjoin formula example

In Excel, concatenating multiple text values is a daily routine. TEXTJOIN concatenates values using a delimiter (or without it).

Arguments:

  • delimiter
  • ignore_empty
  • text1

Examples:

=TEXTJOIN(" ", TRUE, B3:B11)

will return “The Sun always shines on tv” and ignore the blank cells.

=TEXTJOIN(" “, FALSE, B3:B11) 

will return “The Sun always shines on tv” and concatenate the blank cells too.

More examples:

LOOKUP

Excel Formulas may use powerful lookup functions to find the lookup value in an array. This section will introduce you to the most used methods.

LOOKUP Formulas

INDEX function example

index funcion example

You can use the INDEX function to get a value in a section of specified rows and columns. In the example, get the second item in the fourth column.

=INDEX(C4:F10,2,4)

The function uses two required and one optional argument:

  • The array is the range or array from which to retrieve values.
  • A row number is a number from which rows get a value.
  • The column number is optional at which to retrieve a value.

MATCH function example

match formula example

Use the MATCH function to return the position of a value in a range or an array. The function supports exact matches, approximate matches, and wildcards.

In the example, use the formula below to get the position of the lookup value in the range C3:C9:

= MATCH(E3,C3:C9,0)

The formula returns with 4 because the position of “garlic” is 4 in the range C3:C9.

The function uses two required and one optional argument.

  • lookup_value is the value that we search in the array
  • lookup_array is a range of cells where we find the lookup value
  • match_type is an optional argument (exact match or next smallest = 1, exact match = 0; exact match or next largest = -1)

INDEX and MATCH example

index and match functions

In the example, we have Product 5 in position 5. Therefore, we use the MATCH function to find “Product 5” (H5) in row 5 and give the position to INDEX.

The formula in H6 is:

=INDEX(C4:F10,MATCH(H5,C4:C10,0),3)

INDEX will use the MATCH-based formula as the second argument.

Lookup formula to calculate the nth largest value

get the nth largest value 39

We love XLOOKUP; the function makes your life easier. For example, you can use it to return the name of the nth largest value or simply the nth largest value in a range.

=XLOOKUP(LARGE(sales, E3), sales, name)

Sales (C3:C9) and name (B3:B9) are named ranges.

Arguments:

  • Lookup value = E3
  • Lookup array = LARGE(sales, E3)
  • Return array = name

LARGE returns the largest value in a range, $7930. XLOOKUP will use the result as a lookup value:

=XLOOKUP($7930 ,sales ,name)

You can replace the lookup values to get the name for the 2nd and 3rd largest amounts.

FILTER

With the help of the FILTER function, you can write smart Excel formulas to filter and extract data from a range.

FILTER formulas

Basic FILTER example

basic filter function example

In the first example, we will show you how to use the FILTER function to find and extract all matching records.

The formula below extracts all records from a list where the region = “North”.

=FILTER(B3:E9,E3:E9=H2,"not found")

The formula uses a logical test and finds the corresponding item(s) in an array. If FILTER has a match in the array, return all records. We use the if_not_found argument to handle errors in case no matching record is found.

Extract matching records in two list

extract matching records

You can use the FILTER function combined with the COUNTIF function to compare two lists and extract the matching records to a new array.

As usual, we are using named ranges for the better readability:

  • list1 = B3:B12
  • list2 = C3:C10

The formula in cell E3:

=FILTER(list1,COUNTIF(list2,list1))

In the example, we find the list2 items in the list1. COUNTIF count all records in the list2. So, the function arguments look like this:

  • range = list2
  • criteria = list1

The result is an array that contains six matching records, which spill into the range E3:E8. Try to update your data; the function will recalculate the matching values.

Extract the first N or last N records

excel formula to filter first 5 and last 5 values

We use the FILTER function in the example to extract the first five records to a new array.

Enter the formula in cell D3:

=INDEX(FILTER(list1,list1<>""),SEQUENCE(5,1,1,1))

Evaluate each part of the formula:

The SEQUENCE(5,1,1,1) return an array: {1, 2, 3, 4, 5}

Now filter the non-blank cells:

=FILTER(data,data<>"")

Finally, the INDEX extracts the array’s 1st, 2nd, 3rd, 4th and 5th values.

To extract the last 5 non-blank values, use the formula below:

=INDEX(FILTER(data,data<>""),SORT(SEQUENCE(3,1,SUM(--(data<>"")),-1)))

RANK

RANK formulas

How to rank scores

rank formula basic example

The RANK function is the fastest way to rank a set of numeric values in ascending or descending order.

In the example, we use a named range, result (C3:C12); the formula in D3 is:

=RANK(C3, result) or =RANK(C3, result,0)

The function uses the third argument to decide the order. If you do not use this optional argument, the formula gets the values from the highest to the lowest (order = 0). To create a rank where the lowest value is the first, use the “1” as an argument.

We want to create a rank using the “highest-first” method; it is not necessary to use the third argument; it is “0” by default.

Rank values by month

excel formulas to display rank by month

Now we take a deep dive into an exciting challenge. The goal is to create a ranked list based on the monthly sales.

First, rank values by month:

=LARGE(IF(TEXT(date,"mmmm")=G$2,sales),$F3)

Evaluate the formula: use the TEXT function to convert dates to months using the “mmmm” format.

The LARGE function returns the nth largest value. For example, cell G3 returns 2691; this is the first largest value for August. Copy the formula down to fill the table.

To get the list that shows the names, not the values, use the formula below:

=INDEX(name,MATCH(1,(amount=G3)*(TEXT(date,"mmmm")=G$7),0))

ROUND

We have many options to build Excel formulas for rounding purposes. In this section, you can find various examples that use the ROUND and their related functions.

Formula examples

Basic ROUND Example

basic round formula examples

The ROUND function rounds numbers to N digits, where N is an integer. In the example, we want to round 3.35 to one decimal place.

The function has two required arguments:

  • the number that you want to round.
  • num_digits are the number of digits

Formula:

=ROUND(3.35,1)

The result is 3.4.

Round numbers up and down

roundup formula

Use the ROUNDUP function to round a number up, away from zero.

Example:

=ROUNDUP(B3, C3)

Look at cell D4! The formula rounds the value in cell B4 up to 2 digits and returns 8.54.

=ROUNDUP(B4,2)

The ROUNDDOWN function rounds a number down. For example, if you need to round the 8.53349 down to 2 digits, use the formula below:

=ROUNDDOWN(B4, C4)

In this case, the result is 8.53.

Get the integer or decimal part of a number

Get the integer or decimal part of a number

Use the TRUNC function to truncate a decimal part of a number and leave only the integer part.

Generic formula:

=TRUNC(B3)

TRUNC cuts off the decimal part of the number by default. But it provides a second argument to control the precision. We want to extract only the integer part in the example, so use the first argument only.

If you want to extract the decimal part of a number, use the formula below. The formula subtracts the decimal part of the number and strips the integer part.

=B3-TRUNC(B3)

Round a number to the nearest multiple

mround formula to get the nearest multiple

Use the MROUND function to return a number rounded to the desired multiple. In the example, the formula in D3 is:

=MROUND(B3, C3)

Explanation: The MROUND(B3, C3) round the number in the C3 cell to the nearest multiple based on the cell value in C3 (2). The result in D3 is $112.00 because $ 112.00 is the nearest multiple of 2 to 112.21

Round time to nearest 10 minutes

Round time to nearest 15 minutes

Sometimes we need to create a schedule in Microsoft Excel. In the example, we will use the MROUND function to round the time to the nearest 10-minute interval.

=MROUND(B3, "0:10")

For example, if you want to apply “0:10” as the multiple, Excel will convert 0:10 to 0.006944. This is the corresponding decimal value, and Excel uses it for further calculations.

=10/(60*24) =0.006944

Formula to round to the nearest 1 hour:

=MROUND(time,"1:00")

Best practices for using Excel Formulas

Toggle between relative and absolute reference. First, select the cell that contains the formula and use the F2 shortcut to enter the cell edit mode. Then, press F4 to switch between the reference types.

Apply autocomplete formula function. First, type an equal sign (=) and type the first character of the function. Excel shows a dynamic list of built-in functions. Next, select the function that you want to use.

excel formulas autocomplete

Use Function ScreenTips. Type an equal sign and enter the function name. To add arguments, type opening parenthesis. A screen tip will appear and show your formula’s corresponding argument.

Useful resources to learn Excel Formula Basics

Finally, if you want to learn more about Excel Formulas, we recommend you the related articles below:

Additional resources: