Excel Formula List (40+ Useful Formulas to Master Excel)

In this Excel tutorial, you will get a list of useful Excel formulas for your daily use while working. We’ll use relevant practical datasets to give you a better understanding.

Let’s see an example. In the following image, you see some students with their marks in English. We have calculated the total marks, average marks, total no. of students, and difference between the highest and lowest marks using some of the Excel formulas.

In this article, we’ll cover fundamental operations like adding, subtracting, multiplying, and dividing cells, along with summing and counting using different formulas in Excel. You’ll learn how to work with text, concatenate cells, and perform calculations involving dates and times. The article will show you how to use formulas for calculating subtotals, and ranges, and implementing conditional and nested formulas. Additionally, it’ll show you the use of lookup formulas for efficient data searching. You’ll learn how to calculate percentages, ratios, and rounding numbers in Excel. Last but not least, you can generate random numbers, convert units, and incorporate serial number formulas through this article.

⏷What is an Excel Formula?
⏷Apply Formula
⏷Add and Subtract
⏷Multiply
⏷Divide
⏷Sum
  ⏵AutoSum
  ⏵Sum Columns
  ⏵Sum Based on Criteria
⏷Count
  ⏵Count Cells
  ⏵Count Unique Values
  ⏵Count Based on Criteria
⏷Use Average Formula
  ⏵Average
  ⏵Running Average
  ⏵Moving Average
  ⏵Weighted Average
⏷Range Formula
⏷Subtotals
⏷Concatenate
  ⏵Multiple Cells
  ⏵Combine Text and Number
⏷Calculate Percentages
  ⏵Percentage
  ⏵Percentage Change
⏷Ratio
⏷Rounding
  ⏵Round Up Decimals
  ⏵Round to Nearest 5
⏷Math Formula
  ⏵Finding Root
  ⏵Multiplying Matrix
  ⏵Checking Even or Odd
⏷Use Text Formula
  ⏵Finding Text in a Cell
  ⏵Changing Case
  ⏵Removing Space
  ⏵Extracting Text
  ⏵Truncating Text
⏷Date and Time Formula
⏷Use Date and Time Functions
  ⏵Calculating Age
  ⏵Getting First Day of Month
  ⏵Days between Dates
  ⏵Calculating Time
⏷Use Conditional Formulas
⏷Use Nested Formula
⏷Use Lookup Formula
  ⏵Wildcard
  ⏵INDEX and MATCH
⏷Randomize
  ⏵Random Numbers
  ⏵Random Value from a Selection
⏷Unit Conversion
  ⏵Inches to Feet
  ⏵Kg to Lbs
⏷Serial Number Formula
⏷Some Keyboard Shortcuts for Formulas

What Is Excel Formula?

Excel formula is an expression that acts on a cell or range of cells and produces results on another cell or multiple cells. You can perform different calculations easily with Excel formulas. You can add, subtract, multiply, divide, and do other calculations through Excel formulas.

How to Apply Formula in Excel?

You can apply a formula in Excel by typing an equal sign (=) first in a cell and then typing the desired formula. Then you can put direct values in the formula as the arguments or you can use cell references as the arguments. After typing the formula, press Enter to get the desired result.

We have a dataset with some student’s marks in 3 subjects. We want to get the total marks of each student in these 3 subjects. So, we have to add the marks of these 3 subjects. We can use the SUM function here.

Follow these steps:

Step 1: Select cell F6 and type the equal sign. Then, type sum. Excel will suggest all the available formulas related to the sum.

Step 2: We’ll use the SUM function here. So, we double-click on the SUM option and choose the cells from C6 to E6. You can also type the full formula.

Step 3: Press Enter and you’ll get the output (total marks of the first student). You’ll see the Fill Handle icon located at the right bottom corner of the cell.

Step 4: Double-click on the Fill Handle icon or drag it down with a mouse to get the result for all students.

1. How to Add and Subtract in Excel?

In this section, you’ll learn how to add and subtract in Excel using cell references as the formula and the plus and minus operators together.

We have a dataset with some students’ marks in a subject that is divided into 3 sections (Theory, Practical and Negative). We have to add the theory and practical section’s marks and subtract the negative marks to obtain the total marks of each student.

So. we’ll put the cell references as the formula and then use the plus operator to add the theory and practical section’s marks and put the minus operator to subtract the negative marks.

Steps:

• Put the following formula in cell F6 and press Enter to get the total marks of the first student:

• Double-click the Fill Handle icon or drag it down with a mouse to get the result for all students.

Read More: Excel GST Formula

2. How to Multiply in Excel?

In this part, you’ll learn how to multiply in Excel using cell references as the formula and the multiplication (*) operator between them.

We have a dataset with some products and their unit prices and quantity sold. We have to multiply the unit price with the quantity sold to obtain the sales of each product.

So. we’ll put the cell references as the formula and then use the multiplication operator to get the sales of each product.

Steps:

• Insert the following formula in cell E6 to get the sales of the first product and then use the Fill Handle icon for all the remaining cells to copy the formula:

3. How to Divide in Excel?

Here, you’ll learn how to divide in Excel using cell references as the formula and the division (/) operator between them.

We’ll use the same dataset as before. Now, we’ll determine the quantity sold of each product by dividing sales by unit price.

• Now the formula in cell E6 will be:

Read More: Margin Formula Excel

4. How to Sum in Excel?

In this section, we will show you how you can sum using the AutoSum feature; how to sum columns using the SUM function, and sum based on criteria using the SUMIFS function.

4.1 Using AutoSum Feature

Here, you’ll learn how to use the AutoSum feature to sum in Excel.

The AutoSum feature in Excel makes it very convenient to sum all selected cells together with a single click. This feature uses the SUM function to sum cells. However, the advantage is that you don’t have to write the SUM function manually and repeatedly for each row and column. You can sum up several rows and columns with a single click.

We have the following dataset with some employees and their sales in 3 different months. We want to get the sales in the employee-wise sales column. So, we have to sum all the 3 months sales in each row. We’ll use the AutoSum feature to get all the results with a single click.

Steps:

• Select all cells F6:F15. Go to the Home tab => Select the AutoSum option in the Editing group of commands.

• You’ll get all the outputs together.

4.2 Sum Columns

In this part, you’ll learn how to sum columns in Excel using the SUM function.

We have the same employee sales dataset. But now we’ll calculate the total month-wise sales. So, we have to sum all the columns one by one.

Steps:

• Write the following formula in cell C16 to get the total sales of April of all employees:

• Then drag the Fill Handle icon to the right side for the 2 remaining months (May and June).

4.3 Sum Based on Criteria

In this section, you’ll learn how to sum based on criteria in Excel. For a single criterion, use the SUMIF function. Here in this example, you will learn to sum based on multiple criteria using the SUMIFS function.

We have a dataset having brand names, devices, models, and their prices. We want the total price based on the two criteria below.

Criteria 1: Brand (Omicorn)
Criteria 2: Device (Notebook)

So, we’ll use the SUMIFS function here.

• The formula which we put in cell C21 is:

Read More: Excel Dividend Formula

5. How to Count in Excel?

In this section, you’ll learn how you can count cells using COUNT, COUNTA, and COUNTBLANK functions; how to count unique values using COUNTA and UNIQUE functions; and count based on criteria using the COUNTIFS function.

5.1 Count Cells

Here, you’ll learn how to count cells using COUNT, COUNTA and COUNTBLANK functions. For cells having numbers and dates use the COUNT function. For cells having numerical values, texts, and formulas but not empty cells, use the COUNTA function. You can count blank cells using the COUNTBLANK function.

We have a dataset with values like number, text, date, and empty cells. We’ll see how the 3 functions count cells based on these values.

• To count numerical values, use this formula:

• To count numerical values, texts, and formulas, use this:

• To count blank cells, use this formula:

5.2 Count Unique Values

In this section, we’ll show you how to count unique values in Excel by combining the COUNTA and UNIQUE functions.

We have a dataset having brand names, devices, models, and their prices. We want to count the unique brands and unique devices separately. So, we’ll use the COUNTA and UNIQUE functions together.

• Type the formula in cell D18 to count the unique brands:

• Put the formula in cell D20 to count the unique devices:

5.3 Count Based on Criteria

Here, you’ll learn how to count based on criteria in Excel. For a single criterion, use the COUNTIF function in Excel. And, for multiple criteria use the COUNTIFS function. Here, in this example, we will count cells based on multiple criteria using COUNTIFS function.

We have the same dataset. Now, we want the total count based on the criteria below.

Criteria 1: Brand (Omicorn)
Criteria 2: Device (Notebook)

• The formula is:

Read More: How to Calculate Discount in Excel

6. How to Use Average Formula in Excel?

Average means adding up all the numbers in a group and then dividing the summation by how many numbers are there. The basic formula for calculating the average is:

In this section, you’ll learn how to calculate average, running average, and moving average using the AVERAGE function; and how to calculate weighted average using the SUMPRODUCT and SUM functions.

6.1 Calculate Average

In this method, we will show you how to calculate average in Excel, using the AVERAGE function.

We have the following dataset with some employees and their sales in 3 different months. We want to get the average sales of each employee in average sales column. So. we’ll use the AVERAGE function.

Steps:

• Insert the following formula in cell F6 to get the average sales of the first employee and then use the Fill Handle icon for all the remaining cells to copy the formula:

6.2 Running Average

Here, you’ll learn how to calculate the running average in Excel using the AVERAGE function.

A running average is a type of average that is continually updated as new data points become available. This method provides a more dynamic and responsive way to track changes in the average value over time.

We have the following dataset with months and no. of visitors on the Exceldemy forum. We want to calculate the running average of the no. of visitors in each month starting from the second month. So, we’ll use the AVERAGE function with mixed cell reference.

Steps:

• Write the following formula in cell D7 and press Enter to get the running average of the first two months:

• Double-click the Fill Handle icon or drag it down with a mouse to get the result for all months.

6.3 Moving Average

In this section, you’ll learn how to calculate the moving average in Excel using the AVERAGE function.

A moving average is a type of average that continually updates the average as new data points are added or old data points are removed.

We have the same dataset as before. Now, we want to calculate the 3 points moving average of the no. of visitors. The 3-point moving average means that it will require 3 values to calculate this average. So, we’ll use the AVERAGE function here.

Steps:

• Type the following formula in cell D8 to get the 3-point moving average of the no. of visitors and then use the Fill Handle icon for all the remaining cells:

6.4 Weighted Average

In this part, you’ll learn how to calculate the weighted average in Excel using the SUMPRODUCT and SUM functions.

The weighted average is an average in which each data point is assigned a weight based on its relative importance in the overall set.

I have the following dataset of a student’s marks in some subjects and the weights assigned to each subject. Now, I want to calculate the weighted average marks. So, I’ll use the SUMPRODUCT function to get the sum of all marks multiplied by its weight. The SUM function will give the sum of all weights. Then I’ll divide the output obtained from the SUMPRODUCT function by the SUM function output to get the weighted average marks.

• The formula to calculate the weighted average marks is:

7. Range Formula in Excel

In this section, you’ll learn how to use range formulas (the MAX and the MIN functions) in Excel to calculate range.

The range is the difference between the maximum and minimum values in a dataset.

We have the following dataset where we have some products and their sales in 3 different months. We want to get the difference between the highest and lowest sales in each month. So, we’ll subtract the MIN formula from the MAX formula.

Steps:

• Put the following formula in cell C16 to get the difference between the highest and lowest sales in April:

• Then drag the Fill Handle icon to the right side for May and June.

8. Subtotals in Excel

In this method, you’ll learn how to calculate subtotals in Excel by using the SUBTOTAL function.

Let’s say we have a dataset with some products, unit price, quantity sold and their sales values. There are 3 common products. We want to calculate the subtotal sales of those 3 products one by one and then their grand total sales.

So, we have to make the dataset like below by sorting the same products together. Then use the SUBTOTAL function to calculate the subtotal sales of those 3 products and then their grand total sales.

Steps:

• Insert the following formula in cell E9 to get the subtotal sales of the Air Conditioner:

The first argument of the SUBTOTAL function is function_num. This argument denotes the function we want to use in our calculation. We have used 9 because 9 denotes the SUM function.

• Similarly for Monitor in cell E13:

• For Battery in cell E17:

• Write the following formula in cell E19 to get the grand total sales of all products:

9. Concatenate in Excel

In this section, you’ll learn how to concatenate multiple cells using the CONCATENATE function; and how to combine text and number using the ampersand operator (&).

Example 1: Concatenate Multiple Cells

Here, you’ll learn how to concatenate multiple cells in Excel using the CONCATENATE function.

Below you can see a dataset with some people’s first names and last names in 2 separate cells in 2 columns. We want to concatenate the values from these 2 cells to obtain the full name. So, we’ll use the CONCATENATE function.

Steps:

• Type the following formula in cell D6 and press Enter to get the full name by joining the first and the last name with a space:

• Double-click the Fill Handle icon or drag it down with a mouse to get the result for all people.

Example 2: Combine Text and Number

In this part, you’ll learn how to combine text and number using cell references as the formula and the ampersand operator (&) between them.

We have another dataset with some employees’ names and their serial numbers. We want to join this text and number to form unique IDs for all the employees. So, we’ll use the cell references as the formula and the ampersand operator to combine them.

Steps:

• Put the following formula in cell D6 to combine serial no. and name and then use the Fill Handle icon for all the remaining cells:

10. How to Calculate Percentages in Excel?

In this section, you’ll learn how to calculate percentages in Excel; and how to calculate percentage change in Excel.

The percentage of a part can be calculated by dividing it by the total value and then multiplying the result by 100 or formatting the result with the Percent Style command.

Example 1: Calculate Percentages

Here, you’ll learn how to calculate percentages in Excel.

I have a dataset with individual expense categories and their amounts. Here, we have calculated the total expense using the SUM function. And, we want to find the individual category expense in % of the total expense. So, I’ll divide each expense category by the total expense and then multiply the result by 100 to get the percentage.

Steps:

• Insert the following formula in cell D8 to get the Loan Payment expense in % of the total expense:

• Then drag the Fill Handle icon down to obtain percentages for all the category expenses.

Example 2: Calculate Percentage Change

In this method, we’ll show you how to calculate the percentage change in Excel.

We have a dataset of some products and their old and new prices. We want to calculate the percentage change of the old prices. So, we’ll subtract the old price from the new price first. Then divide the result by the old price and multiply by 100 to get the percentage change.

• Use the following formula to get the percentage change:

11. Ratio in Excel

Here, we’ll show you how to calculate the ratio in Excel using the ROUND function and the ampersand operator (&).

Suppose, there is a dataset having some departments of a company. We have a number of male and female workers in those departments. We want to get the male-female ratio in each department. So,  we’ll divide the two values first. Then use the ROUND function to round the result up to certain decimal places. Finally, use the ampersand operators (&) to join the output with a ratio sign (:) and 1.

Steps:

• Write the following formula in cell E6 to get the male-female ratio in the Executive department:

• Then drag the Fill Handle icon down to obtain the male-female ratios for all the departments.

Read More: Payroll Formula in Excel

12. Rounding in Excel

In this section, you’ll learn how to round up numbers to decimal places using the ROUNDUP function; and how to round numbers to the nearest 5 using CEILING.MATH function.

12.1 Round Up Decimals

Here, you’ll learn how to round up numbers to decimal places using the ROUNDUP function in Excel.

We have some decimal numbers (both positive and negative). We want to round them up to 3, 2, and 1 decimal places. We’ll use the ROUNDUP function here. Let’s see how you can do this.

Steps:

• Type the following formula in cell C7 and press Enter to get the first number rounded up to 3 decimal places:

• Double-click the Fill Handle icon or drag it down with a mouse to round up all numbers to 3 decimal places.

• Again locate the Fill Handle icon at the bottom corner of cell C16 and drag it right side with a mouse to round up all numbers to 2 and 1 decimal places.

12.2 Round to Nearest 5

In this part, you’ll learn how to round numbers to the nearest 5 using the CEILING.MATH function in Excel.

Note: The CEILING.MATH function is available from Excel 2013 or later versions.

We have the same dataset as before. We’ll use the CEILING.MATH function to round these numbers to the nearest 5.

Steps:

• Put the following formula in cell C6 to round the first number to the nearest 5:

• Then drag the Fill Handle icon down to round all numbers to the nearest 5.

13. Math Formula in Excel

In this example, we’ll show you how to find roots using the SQRT function; how to multiply matrix using the MMULT function; and how to check even or odd numbers using the ISEVEN function.

13.1 How to Find Root

Here, you’ll learn how to find roots using the SQRT function in Excel.

You’ll get the square root of any positive numbers using this function. On the other hand, only the imaginary number can be used to find the square roots of negative values. So, this function won’t work for negative numbers and will give a #NUM! error.

Below I have a list of numbers (both positive and negative). I’ll calculate the square root of these numbers using the SQRT function.

• The formula to put in cell C6 is:

13.2 How to Multiply Matrix

In this section, you’ll learn how to multiply the matrix using the MMULT function in Excel.

But this function only returns the matrix product of two arrays. Both arrays must only contain numbers, and the number of rows in array2 and the number of columns in array1 must be the same.

We have a dataset with 2 matrices, Matrix A and B. Both have 3 rows and 3 columns. Now, we’ll multiply these 2 matrices using the MMULT function.

Steps:

• Insert the following formula in cell B11 and press Enter to get the product of 2 matrices:

13.3 Check Even or Odd

In this method, you’ll learn how to check even or odd using the ISEVEN function in Excel.

We have a list of numbers here. To check even or odd, we’ll use the combination of IF and ISEVEN functions.

The ISEVEN function will check whether the number is even or not. It is the logical_test argument of the IF function. When the logical_test argument is true, the IF function will return “Even”. When the logical_test argument is false, the IF function will return “Odd”.

Steps:

• Write the following formula in cell C6 to check whether the number is even or odd:

• Then drag the Fill Handle icon down to check all the numbers.

Read More: Excel Sales Formula

14. How to Use Excel Text Formulas?

In this section, you’ll learn how to find text in a cell using the SEARCH function; how to change case using UPPER, LOWER, and PROPER  functions; how to remove unnecessary space using the TRIM function; how to extract text using LEFT, RIGHT, and MID functions; and how to truncate text using REPLACE function in Excel.

14.1 Find Text in a Cell

Here, you’ll learn how to find text in a cell using the SEARCH function in Excel. This function gives the starting position of the desired text from the left side of the main text.

Suppose, you have a list of email IDs and you want to find Gmail among those email IDs. You can use the SEARCH function to find the starting position of Gmail from the left side of an email ID.

Then, the ISNUMBER function will check whether the position is a number or not. It is the logical_test argument of the IF function. Finally, the IF function will return “Yes”, when the logical_test argument is true, and “No” when the logical_test argument is false.

• So, the formula is:

14.2 Change Case

In this method, you’ll learn how to change the case of texts to upper case using the UPPER function, lower case using the LOWER function, and title case using the PROPER function.

We have a list of names and you can see that the cases of these texts are not alright. We’ll show you how to change cases of texts using the above-mentioned 3 functions.

• Type the following formula in cell C6 to change the case to upper case:

• Use the following formula in cell D6 to change the case to the title case:

• And for lower case in cell E6:

14.3 Remove Space

Here, you’ll learn how to remove unnecessary space between words, before words, and afterwords, using the TRIM function in Excel.

Here, we have a dataset of some names having 2 parts (first and last name). And there are unnecessary spaces between first and last names. There are also spaces before and after these names. So. we’ll use the TRIM function to remove those spaces.

Steps:

• Put the following formula in cell C6 to remove spaces from the name:

• Then drag the Fill Handle icon down to remove spaces from all names.

14.4 Extract Text

In this section, you’ll learn how to extract text from the left side using the LEFT function, from the right side using the RIGHT function, and from the middle using the MID function in Excel.

We have a dataset with some products and their codes. The product codes are in 3 parts separated by hyphens. We want to extract the first, middle, and last parts of these codes. So, we’ll use the above-mentioned functions one by one.

• To extract text from the left side (first part of the code), use this formula:

• To extract text from the middle (middle part of the code), use this formula:

• For extracting text from the right side (last part of the code):

14.5 Truncate Text

Here, you’ll learn how to truncate text using the REPLACE function in Excel.

Truncating text is the process of shortening or cutting off a piece of text.

We have a list of unique IDs of some employees of a company. The IDs are made with serial numbers and employee’s names. We want to truncate the IDs and replace them with the names of the employees. So, we’ll use the REPLACE function.

• The formula given below will replace the unique IDs with the employee’s names:

15. Date and Time Formula in Excel

In Excel, there are several functions and formulas related to date and time that allow you to perform various operations and calculations involving dates and times. Some of them are TODAY, DATE, YEAR, MONTH, DAY, NOW functions. These functions are used for various purposes, such as calculating durations between two dates, extracting components (year, month, day, etc.) from a date or time, and performing date and time calculations.

We’ll see how these functions calculate date and time with the following dataset.

• To get today’s date only, use any of these formulas:

• You can also put the date of your choice using this formula:

• To get today’s date and time together, use this formula:

Read More: Excel Scoring Formula

16. How to Use Date and Time Functions in Excel?

In this part, you’ll learn how to use date and time functions to calculate age, the first day of month, and days between dates and time.

16.1 Calculate Age

In this section, you will learn to calculate age from Today’s date using the DATEDIF function in Excel.

Suppose, you have a list of birthdays of some people. And you want to calculate their age based on today. Then you can use the DATEDIF function.

We’ll take the birthday as the starting date and use the TODAY function to get today’s date as the ending date. We’ll use the DATEDIF function 3 times to calculate years, months, and days separately and connect them with the ampersand operator.

Steps:

• Insert the following formula in cell D6 and press Enter to get the age of the first person:

• Double-click the Fill Handle icon or drag it down with a mouse to get the result for all people.

16.2 Get First Day of the Month

Here, you’ll learn how to get the first day of the month using the EOMONTH function in Excel.

We have a list of some employees and their joining month which is in Date format. We want to know the first day of their joining month.

So, we’ll use the EOMONTH function to get the serial number for the last day of the previous month before the joining date. Then, we’ll add 1 and get the serial number for the first day of the joining month. Finally, change the format of cells to get the first day of the month.

Steps:

• Type the following formula in cell D6 and press Enter to get the serial number for the first day of the joining month:

• Double-click the Fill Handle icon or drag it down with a mouse to get the result for all employees.

• Select all cells D6:D15 => Right-click with the mouse to open the Context menu => Locate Format Cells

• Click on the Format Cells option => Format Cells window will open => Go to Number tab => Choose Custom under Category: menu => Type or choose dddd.

Tips: The keyboard shortcut for opening the Format Cells window is Ctrl + 1.

• Click the OK button to apply the format. You’ll get the first day of the joining month.

16.3 Days Between Dates

In this method, you’ll learn how to calculate the number of days between 2 dates in Excel using the DAYS function.

Suppose, you have some employees’ joining date and resigning date in a project. You want to know the number of working days of each employee. You can use the DAYS function here.

Steps:

• Write the following formula in cell E6 to calculate the working days of the first employee:

• Then drag the Fill Handle icon down to calculate the working days of all employees.

16.4 Calculate Time

Here, you’ll learn how to calculate time by subtracting one time from another time in Excel.

We have a similar kind of dataset but now we have entry time and exit time of employees. We want to calculate the working hours. So, we’ll subtract the entry time from the exit time and then change the cell format to Custom to obtain the working hours.

Steps:

• Put the following formula in cell E6 and press Enter to calculate the working hours of the first employee:

• Double-click the Fill Handle icon or drag it down with a mouse to calculate the working hours of all employees.

You’ll see AM after working hours because the cell is in Time format. When we subtract one time from another time, the resulting cell automatically becomes Time formatted cell. Now, we’ll change the format to Custom.

• Select all cells E6:E15 => Right-click with the mouse to open the Context menu => Locate Format Cells

• Click on the Format Cells option => Format Cells window will open => Go to Number tab => Choose Custom under Category: menu => Type or choose h:mm:ss.

• Click the OK button to apply the format. You’ll get the working hours for all the employees.

17. How to Use Conditional Formulas in Excel?

In this section, you’ll learn how to use conditional formulas in Excel to perform a logical test. When the logical test is true, it will return a value. When the logical test is false, it will return another value.

Let’s say you have a dataset with some students and their project submission date. You want to make a comment based on the submission date.
If the submission date is on or before 11 Dec, you want to comment On Time.

If the submission date is after 11 Dec, you want to comment Late.

In this case, you can use the IF function. Let’s see how.

Steps:

• Insert the following formula in cell D10 to make a comment for the first student:

• Then drag the Fill Handle icon down to make comments for all.

18. How to Use Nested Formula in Excel?

In this method, you’ll learn how to use nested formulas in Excel. A nested formula is when you use one function as an argument inside another function. It’s like putting a function inside another function to perform complex calculations.

Below we have a student’s marksheet in Math. We also have the marks range for 5 grades. Now, we want to assign a grade to each student. So, we’ll apply the nested IF formula here.

Steps:

• Write the following formula in cell D13 and press Enter to assign a grade to the first student:

• Double-click the Fill Handle icon or drag it down with a mouse to assign grade to all.

Read More: Excel Overtime Formula

19. How to Use Lookup Formulas in Excel?

In this section, you’ll learn how to use lookup formulas in Excel to search for a specific value in a range of cells and return a corresponding value based on certain criteria. These formulas are useful for finding and extracting information from large datasets. Here, we are showing two lookup formulas in Excel for:

• Partial matching with VLOOKUP function and wildcard
• Exact matching with INDEX and MATCH functions

19.1 Wildcard

Here, you’ll learn how to use wildcard (asterisk and question mark) with the VLOOKUP function to do partial matching in Excel.

The asterisk wildcard (*) finds any number of characters after a text, before a text, and both before and after a text. The question mark wildcard (?) finds only the number of characters based on the number of question marks. If there is a single question mark wildcard, it’ll search for a single character.

We have an employee database with names, departments, and designations. You can see 4 lookup values (names) here. Here, I put the asterisk wildcard after a name, before a name, and both before and after a name. We have also put the question mark wildcard 3 times between a name.

Now, we’ll partially search for these values in the database and extract the matching designation using the VLOOKUP function.

• Use the following formulas to do partial matching based on wildcard:

19.2 INDEX and MATCH

In this method, you’ll learn how to use the INDEX and MATCH functions to do exact matching in Excel.

We have the same employee database. Now, we’ll search for a name in the database and extract the department and designation of that person using the INDEX–MATCH combination.

Steps:

• Type the following formula in cell C23 to get the department based on the name:

• Then drag the Fill Handle icon to the right side for designation.

Read More: Excel Debt Calculation Formula

20. Randomize in Excel

In this method, you’ll learn how to generate random numbers using the RAND, RANDBETWEEN, and RANDARRAY functions; and how to generate random values from a selection using the combination of INDEX, RANDBETWEEN, and ROWS functions in Excel.

20.1 Generate Random Numbers

Here, you’ll learn how to generate random numbers between 0 to 1 using the RAND function; how to generate random numbers between any given values using the RANDBETWEEN function; and how to generate an array of random numbers using the RANDARRAY function.

We have a dataset with lower and upper limits. We’ll see how these functions generate random numbers.

• To generate random numbers between 0 to 1, use this formula:

• To generate random numbers between lower and upper limits, use these formulas:

• Use the following formula to generate an array of random integers based on no. of rows, no. of columns, lower limit, and upper limit:

• Use the following formula to generate an array of random decimals based on no.of rows, no. of columns, lower limit, and upper limit:

20.2 Generate Random Value from a Selection

In this part, you’ll learn how to generate a random value from a selection using the combination of INDEX, RANDBETWEEN, and ROWS functions in Excel.

We have an employee database with IDs, names, and departments. We want to generate a random selection of 3 IDs from all. The purpose can be a lottery.

The ROWS function will give all the row numbers of IDs. Then, the RANDBETWEEN function generates a random row number from all row numbers. Finally, the INDEX function will extract that random ID based on the row number.

Steps:

• Put the following formula in cell B23 to generate a random ID:

• Then drag the Fill Handle icon down to generate 2 more random IDs.

Read More: Ageing Formula in Excel

21. Unit Conversion in Excel

In this section, you’ll learn how to convert inches to feet and kg to lbs using the CONVERT function in Excel.

Example 1: Convert Inches to Feet

Here, you’ll learn how to convert inches to feet using the CONVERT function with “in” and “ft” as unit arguments.

We have some student’s heights in inches. We’ll convert them to feet using the CONVERT function.

Steps:

• Insert the following formula in cell D6 to convert the height of the first student from inches to feet:

• Then drag the Fill Handle icon down to convert all remaining values.

Example 2: Convert Kg to Lbs

In this method, you’ll learn how to convert kg to lbs using the CONVERT function with “kg” and “lbm” as unit arguments.

We have some student’s weights in kg. We’ll convert them to lbs using the CONVERT function.

Steps:

• Write the following formula in cell D6 to convert the weight of the first student from kg to lbs:

• Then use the Fill Handle icon to convert all remaining values.

22. Serial Number Formula in Excel

In this part, you’ll learn how to get serial numbers in Excel using a function. You can obtain serial numbers in various ways in Excel. But here, we’ll show you one example using the ROW function.

Below, we have a dataset with some employees’ names and departments. We want to put serial numbers in column B. So, we’ll use the ROW function.

Steps:

• Type the following formula in cell B6 and press Enter to get the first serial number:

• Double-click the Fill Handle icon or drag it down to get all serial numbers.

Some Keyboard Shortcuts and Features That Will Save Your Time While Working with Formulas

Below is a list of some keyboard shortcuts and features that are time-saving when you work with formulas.

Download Practice Workbook

This is how you can use this formula list in Excel for your related tasks. You can add, subtract, multiply, and divide using Excel formulas. You can sum and count cells using different formulas. The average formula can be used to calculate different types of averages. We have shown the calculation of subtotal, percentages, and ratios using several formulas. We have also shown the use of range formula, math formula, text formula, date and time formula, conditional formula, nested formula, lookup formula, and serial number formula. You can concatenate cells, round numbers, generate random numbers, and convert units by following this article. If you know any other methods or face any problems that we have not discussed in this post, please share them with us in the comment box.

Excel Formula List: Knowledge Hub

<< Go Back to Learn Excel