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

If you have two columns with first as with names A, B, C, D, E and other column with names C, A, B, E, D

Scenario

Sheet1 (or Column 1): A, B, C, D, E
Sheet2 (or Column 2): C, A, B, E, D

You want to rearrange either sheet to match the order of the other.

✅ Method: Use MATCH and INDEX to Rearrange

Assume:

Column A (A1:A5) has names from Sheet1: A, B, C, D, E
Column B (B1:B5) has names from Sheet2: C, A, B, E, D

You want to reorder Column A to match Column B, or vice versa.

🧩 To reorder Column A to match Column B (in Column C):

Use this formula in C1, then drag down to C5:

=INDEX(A$1:A$5, MATCH(B1, A$1:A$5, 0))

This formula tells Excel: “Find the position of the name in B1 inside A1:A5, and return the corresponding value from A1:A5.”

📍Alternate: Rearranging Column B to Match Column A (in Column D):

If instead you want to rearrange Column B to match Column A, use:

excel

=INDEX(B$1:B$5, MATCH(A1, B$1:B$5, 0))

✅ Formula to Identify Extras

Assume:

Column A has the full list: A, B, C, D, E
Column B has a partial list: D, B, A, C

🧮 Step 1: Use a formula in Column C

In cell C1, enter:

=IF(COUNTIF(B:B, A1)=0, "Extra", "")

Then drag it down to match all names in Column A.

🧮 Step 2: Count the Extras

In another cell (e.g., D1), enter:

=COUNTIF(C:C, "Extra")

This will give you the number of names in Column A that are not in Column B.

✅ Formula to Identify Extras

Assume:

Column A has the full list: A, B, C, D, E
Column B has a partial list: D, B, A, C

🧮 Step 1: Use a formula in Column C

In cell C1, enter:

=IF(COUNTIF(B:B, A1)=0, "Extra", "")

🔍 Explanation:

A1 → This is the value you're checking (a name from the first column).
COUNTIF(B:B, A1) → This counts how many times the value from A1 appears anywhere in Column B.

For E, COUNTIF(B:B, E) = 0 → Output: "Extra"

🎯 Logic Flow:

If the count is 0, it means the name in A1 does not exist in Column B → it's "Extra".
If the count is more than 0, the name does exist in Column B → it's not extra, so it returns an empty string "".

Then drag it down to match all names in Column A.

🧮 Step 2: Count the Extras

In another cell (e.g., D1), enter:

=COUNTIF(C:C, "Extra")

This will give you the number of names in Column A that are not in Column B.

Names in Second column if found or not found from first column

Suppose in first column we have five names A,B,C,D,E

Suppose in second column we have five names D, B, A, C

then in third column whether second column is in first or not then formula

whether each name in the second column exists in the first column, and show a result (e.g., "Found" / "Not Found") in the third column.

✅ Formula in Column C:

Place this formula in C1 and drag down:

=IF(COUNTIF(A:A, B1)>0, "Found", "Not Found")

🔍 What It Does:

COUNTIF(A:A, B1) → Checks how many times the value from B1 appears in Column A.
If the count is greater than 0, that means it's present → "Found".
If it's 0, it means it's not there → "Not Found".

What You Should Do:

Open Excel.
Copy and paste the sample data above into a new worksheet.
Make sure there are no blank columns or rows, and column headers are in row 1.

✅ Step 2: Insert the Pivot Table

🧭 Follow These Steps:

Click anywhere inside your data table (e.g., click cell A1 or any cell with data).
Go to the Insert tab on the Ribbon.
Click PivotTable.
In the pop-up window:
- Your data range (e.g., A1:D6) should already be selected.
- Choose:
  - New Worksheet (recommended), or
  - Existing Worksheet if you want it on the same sheet.
Click OK.

✅ Step 3: Set Up the Pivot Table Fields

This is where we define what we want to analyze.

🧩 Do This:

You should see a pane on the right called PivotTable Fields. It lists your column headers:

Date
Region
Product
Sales

Now follow these steps to build your Pivot Table:

🎯 Goal: Total Sales by Region and Product

1. Drag Region to the Rows area

This will list each region (East, West) vertically.

2. Drag Product to the Columns area

This will list each product (Laptop, Phone, Tablet) across the top.

3. Drag Sales to the Values area

It should default to Sum of Sales — showing the total sales per region and product.

✅ Step 4: Add Filters (Optional but Powerful)

Filters let you interactively control what data is shown in the Pivot Table.

🧭 To Add a Filter:

Let’s say you want to filter by Date.

In the PivotTable Fields pane:
Drag Date to the Filters area.

Now, above your Pivot Table, you’ll see a dropdown menu for Date.

🔍 What You Can Do:

Click the Date filter.
Choose a specific date (e.g., only see sales from 01/01/2025).
The Pivot Table will update instantly to reflect that selection.

✅ Step 5: Add a Slicer (Interactive Filter Control)

A slicer is a visual, clickable filter that makes your Pivot Table look like a mini dashboard.

🧭 Steps to Add a Slicer:

Click anywhere inside your Pivot Table.
Go to the PivotTable Analyze tab (in some versions it's just Analyze).
Click Insert Slicer.
In the pop-up window, check one or more fields to use as slicers. For example:
- ✅ Region
- ✅ Product
Click OK.

You'll now see clickable boxes (slicers) for Region and Product next to your Pivot Table.

🎯 How to Use the Slicer:

Click “East” to show only East region sales.
Hold Ctrl and click multiple options (e.g., East + West).
Click the filter icon in the slicer to clear filters.

BWR6P4VT

OTR ID - 123000007161056 & Fortuner@1

FVT SUBJECT - CHEMISTRY

FVT COLOUR - RED