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.

**Note:**We have used**Excel for Microsoft 365**to prepare 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:

`=C6+D6-E6`

- 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:

`=D6*C6`

## 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:

`=D6/C6`

**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.

*The keyboard shortcut of the*

**AutoSum**feature is**Alt**+**=**.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:

`=SUM(C6:C15)`

- 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:

`=SUMIFS(E6:E16,B6:B16,C18,C6:C16,C19)`

**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:

`=COUNT(B6:B13)`

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

`=COUNTA(B6:B13)`

- To count blank cells, use this formula:

`=COUNTBLANK(B6:B13)`

### 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.

**Note:**The**UNIQUE**function is only available in**Excel 2021**and**Microsoft 365**versions.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:

`=COUNTA(UNIQUE(B6:B16))`

- Put the formula in cell
**D20**to count the unique devices:

`=COUNTA(UNIQUE(C6:C16))`

### 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:

`=COUNTIFS(B6:B16,C18,C6:C16,C19)`

**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:

`Average = Sum of All Values / Number of Values`

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:

`=AVERAGE(C6:E6)`

### 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:

`=AVERAGE($C$6:C7)`

- 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:

`=AVERAGE(C6:C8)`

### 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:

`=SUMPRODUCT(C6:C10,D6:D10)/SUM(D6:D10)`

## 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:

`=MAX(C6:C15)-MIN(C6:C15)`

- 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:

`=SUBTOTAL(9,E6:E8)`

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**:

`=SUBTOTAL(9,E10:E12)`

- For Battery in cell
**E17**:

`=SUBTOTAL(9,E14:E16)`

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

`=SUBTOTAL(9,E6:E17)`

## 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:

`=CONCATENATE(B6, " ", C6)`

- 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:

`=B6&"-"&C6`

## 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:

`=C8/$C$5*100`

- 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:

`=(D6-C6)/C6*100`

## 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:

`=ROUND(C6/D6,3)&":"&1`

- 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:

`=ROUNDUP($B7,C$6)`

- 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:

`=CEILING.MATH(B6,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:

`=SQRT(B6)`

### 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:

`=MMULT(B6:D8,F6:H8)`

### 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:

`=IF(ISEVEN(B6),"Even","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:

`=IF(ISNUMBER(SEARCH($C$5,B6)),"Yes","No")`

### 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:

`=UPPER(B6)`

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

`=PROPER(B6)`

- And for lower case in cell
**E6**:

`=LOWER(B6)`

### 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:

`=TRIM(B6)`

- 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:

`=LEFT(C6,4)`

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

`=MID(C6,6,4)`

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

`=RIGHT(C6,3)`

### 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:

`=REPLACE(B6,1,4,"")`

## 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:

`=TODAY()`

`=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))`

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

`=DATE(2023,11,16)`

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

`=NOW()`

**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:

`=DATEDIF(C6,TODAY(),"Y")&" Years, "&DATEDIF(C6,TODAY(),"YM")&" Months, "&DATEDIF(C6,TODAY(),"MD")&" Days"`

- 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:

`=EOMONTH(C6,-1)+1`

- 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:

`=DAYS(D6,C6)`

- 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:

`=D6-C6`

- 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:

`=IF(C10<=$C$6,$B$6,$B$7)`

- 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:

`=IF(C13<61,$C$6,IF(C13<71,$C$7,IF(C13<81,$C$8,IF(C13<91,$C$9,$C$10))))`

- 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:

`=VLOOKUP(B23,B6:D20,3,FALSE)`

`=VLOOKUP(B24,B6:D20,3,FALSE)`

`=VLOOKUP(B25,B6:D20,3,FALSE)`

`=VLOOKUP(B26,B6:D20,3,FALSE)`

### 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:

`=INDEX($B$6:$D$20,MATCH($B$23,$B$6:$B$20,0),MATCH(C22,$B$5:$D$5,0))`

- 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:

`=RAND()`

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

`=RANDBETWEEN(B7,C7)`

`=RANDBETWEEN(B8,C8)`

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

`=RANDARRAY(B6,C6,D6,E6,TRUE)`

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

`=RANDARRAY(B6,C6,D6,E6,FALSE)`

### 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:

`=INDEX($B$6:$B$20,RANDBETWEEN(1,ROWS($B$6:$B$20)),1)`

- 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:

`=CONVERT(C6,"in","ft")`

- 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:

`=CONVERT(C6,"kg","lbm")`

- 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:

`=ROW()-ROW($B$5)`

- 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.

Keyboard Shortcuts/Features | Tasks |
---|---|

F4 | Toggle between absolute, relative, and mixed references |

Ctrl + ~ | Show all formulas on the sheet |

F2 | Edit a formula |

Select a formula and press F9 | Debug formulas |

Fill Handle tool | Copy a formula to all cells in a column or row |

Ctrl + C | Copy cells with the formula |

Ctrl + V | Paste cells with the formula |

Shift + F10 + V | Paste Special (Values Only) |

**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

- Timesheet Formula in Excel
- How to Lookup in Excel
- Use INDEX and MATCH Functions in Excel
- Leave Calculation Formula in Excel
- Bmi Formula in Excel
- Calculate Salary in Excel
- If Date Formula in Excel
- Excel If Time Between Range
- Count Number of Columns in Excel
- Excel Most Frequent Value
- Selling Price Formula Excel

**<< Go Back to Learn Excel**