Home

First Formula: SUM

The SUM formula is one of the simplest and most commonly used formulas in Excel. It is used to calculate the total (sum) of a range of numbers.

How to Apply SUM:

Purpose: To add up the numbers in the "Salary" column.
Formula: =SUM(E2:E4)
- E2:E4 refers to the range of cells in the "Salary" column (50000, 65000, 80000).

Step-by-Step Application:

Click on an empty cell (e.g., E5) below the "Salary" column.
Type the formula: =SUM(E2:E4).
Press Enter.
Excel will calculate the total of 50000 + 65000 + 80000 = 195000

Second Formula: AVERAGE

The AVERAGE formula calculates the mean (average) of a range of numbers.

How to Apply AVERAGE:

Purpose: To find the average salary in the "Salary" column.
Formula: =AVERAGE(E2:E4)
- E2:E4 refers to the range of cells in the "Salary" column (50000, 65000, 80000).

Step-by-Step Application:

Click on an empty cell (e.g., E6) below the "Salary" column.
Type the formula: =AVERAGE(E2:E4).
Press Enter.
Excel will calculate the average: (50000 + 65000 + 80000) ÷ 3 = 65000.

Third Formula: IF

The IF formula is a logical function that checks a condition and returns different values based on whether the condition is TRUE or FALSE.

How to Apply IF:

Purpose: To check if the "Performance Score" is above 80% and return "Excellent" if true, otherwise "Needs Improvement."
Formula: =IF(G2>80%, "Excellent", "Needs Improvement")
- G2 refers to the "Performance Score" of the first employee.
- >80% checks if the score is greater than 80%.
- "Excellent" is the value returned if the condition is TRUE.
- "Needs Improvement" is the value returned if the condition is FALSE.

Step-by-Step Application:

Go to the column next to "Performance Score" (e.g., H2).
Type the formula: =IF(G2>80%, "Excellent", "Needs Improvement").
Press Enter.
Drag the formula down to apply it to other rows (H3, H4, etc.).
The result will display:
- Row 2: "Excellent" (92% > 80%).
- Row 3: "Needs Improvement" (76% < 80%).

Fourth Formula: VLOOKUP

The VLOOKUP formula is used to search for a value in the first column of a table and return a value in the same row from another column.

How to Apply VLOOKUP:

Purpose: To find the "Department" of an employee using their "ID."
Formula: =VLOOKUP(1, A2:G4, 6, FALSE)
- 1: The value to look up (ID = 1).
- A2:G4: The table range to search within.
- 6: The column number to return the value from (Department column is the 6th column in the range).
- FALSE: Ensures an exact match.

Step-by-Step Application:

In an empty cell (e.g., I1), type: Employee Department.
In the cell below it (e.g., I2), type the formula: =VLOOKUP(1, A2:G4, 6, FALSE).
Press Enter.
Excel will return Sales because ID 1 corresponds to the "Sales" department.

Fifth Formula: CONCATENATE or CONCAT

The CONCATENATE (or CONCAT in newer versions of Excel) formula is used to join two or more pieces of text into one.

How to Apply CONCATENATE/CONCAT:

Purpose: To combine the "Name" and "Department" into a single string, such as "John Doe - Sales."
Formula: =CONCATENATE(B2, " - ", F2)
- B2: Refers to the "Name" column.
- " - ": Adds a separator between the name and department.
- F2: Refers to the "Department" column.

Step-by-Step Application:

In a new column (e.g., J1), type: Name and Department.
In the cell below it (e.g., J2), type the formula: =CONCATENATE(B2, " - ", F2).
Press Enter.
Drag the formula down to apply it to other rows (J3, J4, etc.).
The results will be:
- Row 2: John Doe - Sales
- Row 3: Jane Smith - HR
- Row 4: Alan Brown - IT

Sixth Formula: COUNTIF

The COUNTIF formula counts the number of cells in a range that meet a specific condition.

How to Apply COUNTIF:

Purpose: To count the number of employees with a "Performance Score" greater than 80%.
Formula: =COUNTIF(G2:G4, ">80%")
- G2:G4: The range of cells in the "Performance Score" column.
- ">80%": The condition to count only scores greater than 80%.

Step-by-Step Application:

In an empty cell (e.g., K1), type: High Performers Count.
In the cell below it (e.g., K2), type the formula: =COUNTIF(G2:G4, ">80%").
Press Enter.
Excel will return 2 because two employees (92% and 85%) have performance scores greater than 80%.

How to Apply LEFT:

Purpose: To extract the first three letters of each employee's name in the "Name" column.
Formula: =LEFT(B2, 3)
- B2: Refers to the "Name" column.
- 3: Indicates the number of characters to extract from the left.

Step-by-Step Application:

In a new column (e.g., L1), type: Name Initials.
In the cell below it (e.g., L2), type the formula: =LEFT(B2, 3).
Press Enter.
Drag the formula down to apply it to other rows (L3, L4, etc.).
The results will be:
- Row 2: Joh (from "John Doe").
- Row 3: Jan (from "Jane Smith").
- Row 4: Ala (from "Alan Brown").

Eighth Formula: LEN

The LEN formula calculates the total number of characters in a text string, including spaces.

How to Apply LEN:

Purpose: To count the number of characters in each employee's name in the "Name" column.
Formula: =LEN(B2)
- B2: Refers to the "Name" column.

Step-by-Step Application:

In a new column (e.g., M1), type: Name Length.
In the cell below it (e.g., M2), type the formula: =LEN(B2).
Press Enter.
Drag the formula down to apply it to other rows (M3, M4, etc.).
The results will be:
- Row 2: 8 (John Doe has 8 characters including the space).
- Row 3: 10 (Jane Smith has 10 characters including the space).
- Row 4: 10 (Alan Brown has 10 characters including the space).

Ninth Formula: TRIM

The TRIM formula removes all extra spaces from a text string, leaving only single spaces between words.

How to Apply TRIM:

Purpose: To clean up text by removing unnecessary spaces from names in the "Name" column.
Formula: =TRIM(B2)
- B2: Refers to the "Name" column.

Step-by-Step Application:

In a new column (e.g., N1), type: Cleaned Name.
In the cell below it (e.g., N2), type the formula: =TRIM(B2).
Press Enter.
Drag the formula down to apply it to other rows (N3, N4, etc.).
The results will show the cleaned names:
- For example, if there were extra spaces around "John Doe", the TRIM formula would return "John Doe" without extra spaces.

Tenth Formula: PROPER

The PROPER formula capitalizes the first letter of each word in a text string while converting the remaining letters to lowercase.

How to Apply PROPER:

Purpose: To format names in the "Name" column so that each word starts with a capital letter.
Formula: =PROPER(B2)
- B2: Refers to the "Name" column.

Step-by-Step Application:

In a new column (e.g., O1), type: Proper Case Name.
In the cell below it (e.g., O2), type the formula: =PROPER(B2).
Press Enter.
Drag the formula down to apply it to other rows (O3, O4, etc.).
The results will show:
- Row 2: John Doe (if "john doe" was entered).
- Row 3: Jane Smith (if "JANE SMITH" was entered).
- Row 4: Alan Brown (if "aLaN bRoWn" was entered).

Eleventh Formula: NOW

The NOW formula returns the current date and time based on your system's clock.

How to Apply NOW:

Purpose: To display the current date and time in the sheet, useful for tracking when the data was updated.
Formula: =NOW()

Step-by-Step Application:

In an empty cell (e.g., P1), type: Current Date & Time.
In the cell below it (e.g., P2), type the formula: =NOW().
Press Enter.
The result will show the current date and time in the format DD/MM/YYYY HH:MM:SS (e.g., 03/12/2024 14:30:45).

Twelfth Formula: ROUND

The ROUND formula is used to round numbers to a specified number of digits.

How to Apply ROUND:

Purpose: To round the "Salary" column values to the nearest thousand for better readability.
Formula: =ROUND(E2, -3)
- E2: Refers to the "Salary" of the first employee.
- -3: Indicates rounding to the nearest thousand (negative numbers for left of the decimal).

Step-by-Step Application:

In a new column (e.g., P1), type: Rounded Salary.
In the cell below it (e.g., P2), type the formula: =ROUND(E2, -3).
Press Enter.
Drag the formula down to apply it to other rows (P3, P4, etc.).
The results will show:
- Row 2: 50000 (already rounded).
- Row 3: 65000 (already rounded).
- Row 4: 80000 (already rounded).

Thirteenth Formula: TEXT

The TEXT formula is used to format numbers, dates, and other values into a specific format as text.

How to Apply TEXT:

Purpose: To format the "Joining Date" column into a more readable format, such as DD-MMM-YYYY (e.g., 01-Jan-2020).
Formula: =TEXT(D2, "DD-MMM-YYYY")
- D2: Refers to the "Joining Date" column.
- "DD-MMM-YYYY": Specifies the desired date format.

Step-by-Step Application:

In a new column (e.g., Q1), type: Formatted Joining Date.
In the cell below it (e.g., Q2), type the formula: =TEXT(D2, "DD-MMM-YYYY").
Press Enter.
Drag the formula down to apply it to other rows (Q3, Q4, etc.).
The results will show:
- Row 2: 01-Jan-2020 (from 01-01-2020).
- Row 3: 15-Jul-2018 (from 15-07-2018).
- Row 4: 23-Mar-2015 (from 23-03-2015).

Fourteenth Formula: RANK

The RANK formula is used to determine the rank of a number within a list, either in ascending or descending order.

How to Apply RANK:

Purpose: To rank employees based on their "Salary" in descending order (highest salary = rank 1).
Formula: =RANK(E2, E$2:E$4, 0)
- E2: Refers to the "Salary" of the first employee.
- E$2:E$4: Refers to the range of all salaries to rank.
- 0: Specifies descending order (use 1 for ascending).

Step-by-Step Application:

In a new column (e.g., R1), type: Salary Rank.
In the cell below it (e.g., R2), type the formula: =RANK(E2, E$2:E$4, 0).
Press Enter.
Drag the formula down to apply it to other rows (R3, R4, etc.).
The results will show:
- Row 2: 3 (50000 is the 3rd highest salary).
- Row 3: 2 (65000 is the 2nd highest salary).
- Row 4: 1 (80000 is the highest salary).

Fifteenth Formula

How to Apply PMT for Different Individuals:

We’ll extend the table to include three new columns:

Loan Amount: The loan amount each individual plans to take.
Annual Interest Rate: The interest rate for each loan.
Loan Duration (Years): The duration of the loan in years.

Step-by-Step Setup:

Add three new columns to the table:
- Loan Amount (Column S): Enter loan amounts for each person (e.g., 500000, 700000, 300000).
- Interest Rate (Column T): Enter the annual interest rate for each person (e.g., 7%, 6.5%, 8%).
- Loan Duration (Years) (Column U): Enter the loan duration in years for each person (e.g., 10, 15, 5).
Add a column for the Monthly Payment (Column V) and use the following formula:
- Formula: =PMT(T2/12, U2*12, -S2)
  - T2: Refers to the annual interest rate for the individual.
  - /12: Converts the annual rate to a monthly rate.
  - U2*12: Converts the duration in years to the total number of monthly payments.
  - -S2: Refers to the loan amount (negative to represent cash outflow).

Sixteenth Formula: INDEX

The INDEX formula is used to return the value of a cell at a specific row and column in a given range.

How to Apply INDEX:

Purpose: To retrieve specific data from the table, such as the "Name" of the employee in the second row of the table.
Formula: =INDEX(B2:B4, 2)
- B2:B4: The range of cells in the "Name" column.
- 2: Refers to the row number within the selected range.

Step-by-Step Application:

In a new column (e.g., X1), type: Employee by Row.
In the cell below it (e.g., X2), type the formula: =INDEX(B2:B4, 2).
Press Enter.
The result will show:
- Jane Smith because she is the second entry in the "Name" column.

Advanced Use Case:

To retrieve data from a table with multiple rows and columns, use both row and column numbers:

Formula: =INDEX(A2:G4, 2, 3)
- A2:G4: The entire table range.
- 2: Refers to the second row.
- 3: Refers to the third column (in this case, "Age").

This will return the "Age" of the second employee (32 in the example).

Pairing with INDEX:

You can combine MATCH with INDEX to automate data retrieval:

Example: If "Jane Smith" is in position 2, use that position to get her salary:
- =INDEX(E2:E4, MATCH("Jane Smith", B2:B4, 0))
- This will fetch the salary associated with "Jane Smith" without manual lookups.

Eighteenth Formula: IFERROR

The IFERROR formula is used to handle errors gracefully. Instead of showing error messages like #N/A, #DIV/0!, or others, it displays a custom message or performs an alternative action.

How to Apply IFERROR:

Purpose: To prevent the #N/A error in the MATCH formula when the value isn’t found.
Formula: =IFERROR(MATCH("Jane Smith", B2:B4, 0), "Not Found")
- MATCH("Jane Smith", B2:B4, 0): Tries to find "Jane Smith" in the range.
- "Not Found": The value to return if an error occurs.

Step-by-Step Application:

In a new column (e.g., Z1), type: Error-Handled Match.
In the cell below it (e.g., Z2), type the formula: =IFERROR(MATCH("Jane Smith", B2:B4, 0), "Not Found").
Press Enter.
The result will show:
- 2 if "Jane Smith" is found in the second row.
- "Not Found" if "Jane Smith" isn’t in the range.

Use Cases:

Preventing Error Messages in Reports:
- Display user-friendly messages instead of errors, improving the readability of dashboards or reports.
Data Validation:
- Check for missing data and flag errors with a custom message.
Combining with Other Formulas:
- Use with VLOOKUP, INDEX, or any formula prone to errors.

Nineteenth Formula: COUNTIFS

The COUNTIFS formula counts the number of rows that meet multiple conditions in a dataset.

How to Apply COUNTIFS:

Purpose: To count the number of employees with a "Performance Score" above 80% and in the "Sales" department.
Formula: =COUNTIFS(G2:G4, ">80%", F2:F4, "Sales")
- G2:G4: Range of "Performance Score."
- ">80%": Condition for the performance score.
- F2:F4: Range of "Department."
- "Sales": Condition for the department.

Step-by-Step Application:

In a new column (e.g., AA1), type: High Performers in Sales.
In the cell below it (e.g., AA2), type the formula: =COUNTIFS(G2:G4, ">80%", F2:F4, "Sales").
Press Enter.
The result will show:
- 1 if only one employee meets both conditions.
- 0 if no employees meet both conditions.

Twentieth Formula: SUMIFS

The SUMIFS formula calculates the sum of values that meet multiple conditions.

How to Apply SUMIFS:

Purpose: To calculate the total salary of employees with a "Performance Score" above 80% and in the "Sales" department.
Formula: =SUMIFS(E2:E4, G2:G4, ">80%", F2:F4, "Sales")
- E2:E4: Range of "Salary" to sum.
- G2:G4: Range of "Performance Score."
- ">80%": Condition for the performance score.
- F2:F4: Range of "Department."
- "Sales": Condition for the department.

Step-by-Step Application:

In a new column (e.g., AB1), type: Total Salary of High Performers in Sales.
In the cell below it (e.g., AB2), type the formula: =SUMIFS(E2:E4, G2:G4, ">80%", F2:F4, "Sales").
Press Enter.
The result will show:
- The sum of salaries for employees who meet both conditions.

Use Cases:

Financial Analysis:
- Calculate total expenses, revenues, or salaries based on multiple criteria.
Performance Evaluation:
- Sum up bonuses or salaries for employees meeting performance benchmarks.
Sales and Marketing:
- Add up revenues for specific products in selected regions or periods.

Twenty-First Formula: SUBTOTAL

The SUBTOTAL formula is used to perform calculations (like SUM, AVERAGE, COUNT, etc.) on a filtered range of data. It is dynamic and updates based on visible rows when filters are applied.

How to Apply SUBTOTAL:

Purpose: To calculate the total salary of employees visible after applying filters.
Formula: =SUBTOTAL(9, E2:E4)
- 9: Represents the function for SUM (other codes are available for different calculations).
- E2:E4: The range of the "Salary" column.

Step-by-Step Application:

Apply a filter to your table:
- Highlight the table, go to the "Data" tab, and click "Filter."
- Use filters to show only specific rows (e.g., filter by Department or Performance Score).
In an empty cell (e.g., AC1), type: Filtered Salary Total.
In the cell below it (e.g., AC2), type the formula: =SUBTOTAL(9, E2:E4).
Press Enter.
The result will dynamically adjust based on the visible rows after filtering.

Use Cases:

Dynamic Summaries:
- Instantly update totals for filtered data, such as filtered departments or performance groups.
Flexible Reporting:
- Use SUBTOTAL in dashboards to display changing totals as filters are applied.
Avoiding Hidden Rows:
- Unlike SUM, SUBTOTAL ignores rows hidden by filters, making it more accurate for filtered datasets.

Twenty-Second Formula: UNIQUE

The UNIQUE formula extracts unique values from a range, eliminating duplicates.

Formula-Based Workaround (Array Formula):

Use an array formula for unique values:

Type the formula:
- =IF(COUNTIF($F$2:F2, F2)=1, F2, "")
- Drag it down to populate unique values.
Filter out blanks manually if needed.

How to Apply UNIQUE:

Purpose: To list all unique departments from the "Department" column.
Formula: =UNIQUE(F2:F4)
- F2:F4: The range of the "Department" column.

Step-by-Step Application:

In a new column (e.g., AD1), type: Unique Departments.
In the cell below it (e.g., AD2), type the formula: =UNIQUE(F2:F4).
Press Enter.
The result will display the unique departments:
- Sales
- HR
- IT

Twenty-Fourth Formula: OR

The OR formula is a logical function used to check if at least one condition is TRUE. It returns TRUE if any condition is true, otherwise FALSE.

How to Apply OR:

Purpose: To check if an employee has a "Performance Score" above 80% or is in the "Sales" department.
Formula: =OR(G2>80%, F2="Sales")
- G2>80%: Checks if the performance score is greater than 80%.
- F2="Sales": Checks if the department is "Sales".

Step-by-Step Application:

In a new column (e.g., AF1), type: High Performance or Sales?.
In the cell below it (e.g., AF2), type the formula: =OR(G2>80%, F2="Sales").
Press Enter.
Drag the formula down to apply it to other rows (AF3, AF4, etc.).
The results will show:
- TRUE if at least one of the conditions is met (e.g., performance score > 80% or being in the Sales department).
- FALSE if neither condition is met.

Twenty-Fifth Formula: TEXTJOIN

The TEXTJOIN formula combines multiple text values into one string, separated by a delimiter (e.g., a comma, space, or custom character). It is particularly useful when you want to combine text while ignoring blank cells.

How to Apply TEXTJOIN:

Purpose: To combine employee names and departments into a single string, separated by commas.
Formula: =TEXTJOIN(", ", TRUE, B2, F2)
- ", ": Specifies the delimiter (comma and space in this case).
- TRUE: Ignores blank cells.
- B2, F2: Refers to the cells containing the employee name and department.

Step-by-Step Application:

In a new column (e.g., AG1), type: Name and Department Combined.
In the cell below it (e.g., AG2), type the formula: =TEXTJOIN(", ", TRUE, B2, F2).
Press Enter.
Drag the formula down to apply it to other rows (AG3, AG4, etc.).
The results will show:
- Row 2: John Doe, Sales
- Row 3: Jane Smith, HR
- Row 4: Alan Brown, IT

BWR6P4VT

OTR ID - 123000007161056 & Fortuner@1

FVT SUBJECT - CHEMISTRY

FVT COLOUR - RED