Top 10 Excel interview questions with answers:
1. What are the different types of cell references in Excel?
Solution:
1. Relative Reference: Changes when copied (e.g., A1).
2. Absolute Reference: Remains constant when copied (e.g., $A$1).
3. Mixed Reference: Partly absolute and partly relative (e.g., $A1 or A$1).
2. How do you remove duplicates in Excel?
Solution:
1. Select the data range.
2. Go to Data > Remove Duplicates.
3. Choose the columns to check for duplicates and click OK.
3. What is the difference between COUNT, COUNTA, and COUNTIF?
Solution:
COUNT: Counts numeric values.
COUNTA: Counts all non-empty cells (numbers, text, etc.).
COUNTIF: Counts cells based on a condition.
Example:
=COUNT(A1:A10) // Count numbers
=COUNTA(A1:A10) // Count all non-empty cells
=COUNTIF(A1:A10, ">50") // Count numbers > 50
4. What are pivot tables, and why are they used?
Solution:
Pivot tables summarize and analyze large datasets, allowing dynamic filtering and aggregation (e.g., sum, average, count) without altering the original data.
5. How do you protect a worksheet in Excel?
Solution:
1. Go to Review > Protect Sheet.
2. Set a password and select allowed actions (e.g., selecting cells).
3. Click OK to apply.
6. What is the difference between VLOOKUP and HLOOKUP?
Solution:
VLOOKUP: Searches for a value vertically in the leftmost column.
HLOOKUP: Searches for a value horizontally in the topmost row.
Example:
=VLOOKUP(101, A2:D10, 2, FALSE) // Find data for 101 vertically
=HLOOKUP("Jan", A1:Z2, 2, FALSE) // Find data for "Jan" horizontally.
7. What is conditional formatting in Excel?
Solution:
Conditional formatting highlights cells based on rules.
Steps:
1. Select cells.
2. Go to Home > Conditional Formatting.
3. Choose a rule (e.g., values greater than 50) and apply formatting.
8. How do you find duplicates using a formula in Excel?
Solution:
Use the COUNTIF function:
=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")
9. How do you use the IF function?
Solution:
The IF function performs logical tests and returns a value based on the result.
=IF(A1 > 50, "Pass", "Fail") // Returns "Pass" if A1 > 50, else "Fail"
10. What is the purpose of the TEXT function?
Solution:
The TEXT function formats numbers and dates into specified text formats.
Example:
=TEXT(A1, "DD-MMM-YYYY") // Converts date to "22-Nov-2024"
=TEXT(1234.56, "$#,##0.00") // Formats number as "$1,234.56"
Like for more β€οΈ
1. What are the different types of cell references in Excel?
Solution:
1. Relative Reference: Changes when copied (e.g., A1).
2. Absolute Reference: Remains constant when copied (e.g., $A$1).
3. Mixed Reference: Partly absolute and partly relative (e.g., $A1 or A$1).
2. How do you remove duplicates in Excel?
Solution:
1. Select the data range.
2. Go to Data > Remove Duplicates.
3. Choose the columns to check for duplicates and click OK.
3. What is the difference between COUNT, COUNTA, and COUNTIF?
Solution:
COUNT: Counts numeric values.
COUNTA: Counts all non-empty cells (numbers, text, etc.).
COUNTIF: Counts cells based on a condition.
Example:
=COUNT(A1:A10) // Count numbers
=COUNTA(A1:A10) // Count all non-empty cells
=COUNTIF(A1:A10, ">50") // Count numbers > 50
4. What are pivot tables, and why are they used?
Solution:
Pivot tables summarize and analyze large datasets, allowing dynamic filtering and aggregation (e.g., sum, average, count) without altering the original data.
5. How do you protect a worksheet in Excel?
Solution:
1. Go to Review > Protect Sheet.
2. Set a password and select allowed actions (e.g., selecting cells).
3. Click OK to apply.
6. What is the difference between VLOOKUP and HLOOKUP?
Solution:
VLOOKUP: Searches for a value vertically in the leftmost column.
HLOOKUP: Searches for a value horizontally in the topmost row.
Example:
=VLOOKUP(101, A2:D10, 2, FALSE) // Find data for 101 vertically
=HLOOKUP("Jan", A1:Z2, 2, FALSE) // Find data for "Jan" horizontally.
7. What is conditional formatting in Excel?
Solution:
Conditional formatting highlights cells based on rules.
Steps:
1. Select cells.
2. Go to Home > Conditional Formatting.
3. Choose a rule (e.g., values greater than 50) and apply formatting.
8. How do you find duplicates using a formula in Excel?
Solution:
Use the COUNTIF function:
=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")
9. How do you use the IF function?
Solution:
The IF function performs logical tests and returns a value based on the result.
=IF(A1 > 50, "Pass", "Fail") // Returns "Pass" if A1 > 50, else "Fail"
10. What is the purpose of the TEXT function?
Solution:
The TEXT function formats numbers and dates into specified text formats.
Example:
=TEXT(A1, "DD-MMM-YYYY") // Converts date to "22-Nov-2024"
=TEXT(1234.56, "$#,##0.00") // Formats number as "$1,234.56"
Like for more β€οΈ
β€1π1π1
π Excel Basics #7 β Conditional Formatting
Conditional Formatting automatically changes the appearance of cells based on rules or conditions. It helps you quickly identify trends, outliers, duplicates, and important values.
π What is Conditional Formatting?
Instead of manually highlighting data, Excel formats cells automatically when they meet a specific condition.
Go to: Home β Conditional Formatting
π Common Rules
β Highlight Cells Rules
β’ Greater Than
β’ Less Than
β’ Between
β’ Equal To
β’ Text That Contains
β’ A Date Occurring
β’ Duplicate Values
Example:
Highlight all sales greater than 10000.
π Top/Bottom Rules
β’ Top 10 Items
β’ Top 10%
β’ Bottom 10 Items
β’ Bottom 10%
β’ Above Average
β’ Below Average
Example:
Highlight the top 5 highest-performing employees.
π Data Bars
Display colored bars inside cells to compare values visually.
Example: Higher sales β Longer bar | Lower sales β Shorter bar
Perfect for comparing performance without creating charts.
π Color Scales
Apply gradient colors based on values.
Example:
β’ Green β Highest values
β’ Yellow β Medium values
β’ Red β Lowest values
Useful for marks, revenue, or KPI analysis.
π Icon Sets
Display icons based on cell values.
Examples:
β’ π’ Green Arrow β High
β’ π‘ Yellow Arrow β Medium
β’ π΄ Red Arrow β Low
Great for dashboards and performance reports.
π Custom Formula Rule
You can create your own formatting rules using formulas.
Example: =A2>100
This highlights cells where the value is greater than 100.
π‘ Real-World Examples
β’ Highlight overdue payment dates
β’ Find duplicate Employee IDs
β’ Identify sales below the monthly target
β’ Highlight the top 10% of performers
β’ Track project status using colors
β Best Practices
β’ Use colors consistently (e.g., Green = Good, Red = Attention)
β’ Don't overuse multiple formatting rules
β’ Keep formatting simple and easy to understand
β’ Review and manage rules regularly to avoid conflicts
Double Tap β€οΈ For More
Conditional Formatting automatically changes the appearance of cells based on rules or conditions. It helps you quickly identify trends, outliers, duplicates, and important values.
π What is Conditional Formatting?
Instead of manually highlighting data, Excel formats cells automatically when they meet a specific condition.
Go to: Home β Conditional Formatting
π Common Rules
β Highlight Cells Rules
β’ Greater Than
β’ Less Than
β’ Between
β’ Equal To
β’ Text That Contains
β’ A Date Occurring
β’ Duplicate Values
Example:
Highlight all sales greater than 10000.
π Top/Bottom Rules
β’ Top 10 Items
β’ Top 10%
β’ Bottom 10 Items
β’ Bottom 10%
β’ Above Average
β’ Below Average
Example:
Highlight the top 5 highest-performing employees.
π Data Bars
Display colored bars inside cells to compare values visually.
Example: Higher sales β Longer bar | Lower sales β Shorter bar
Perfect for comparing performance without creating charts.
π Color Scales
Apply gradient colors based on values.
Example:
β’ Green β Highest values
β’ Yellow β Medium values
β’ Red β Lowest values
Useful for marks, revenue, or KPI analysis.
π Icon Sets
Display icons based on cell values.
Examples:
β’ π’ Green Arrow β High
β’ π‘ Yellow Arrow β Medium
β’ π΄ Red Arrow β Low
Great for dashboards and performance reports.
π Custom Formula Rule
You can create your own formatting rules using formulas.
Example: =A2>100
This highlights cells where the value is greater than 100.
π‘ Real-World Examples
β’ Highlight overdue payment dates
β’ Find duplicate Employee IDs
β’ Identify sales below the monthly target
β’ Highlight the top 10% of performers
β’ Track project status using colors
β Best Practices
β’ Use colors consistently (e.g., Green = Good, Red = Attention)
β’ Don't overuse multiple formatting rules
β’ Keep formatting simple and easy to understand
β’ Review and manage rules regularly to avoid conflicts
Double Tap β€οΈ For More
β€1π1π1
π Excel Basics #8 β Cell Styles & Themes
Cell Styles and Themes help you create professional-looking worksheets with a consistent design. Instead of formatting each cell manually, you can apply predefined styles with a single click.
π What are Cell Styles?
Cell Styles are pre-designed formatting combinations that include:
β’ Font
β’ Font Size
β’ Font Color
β’ Fill Color
β’ Borders
β’ Number Format
Go to:
Home β Cell Styles
π Common Built-in Styles
β’ Title
β’ Heading 1
β’ Heading 2
β’ Total
β’ Good
β’ Bad
β’ Neutral
β’ Input
β’ Output
β’ Warning
π‘ Example:
β’ Use Heading 1 for report titles.
β’ Use Total for the final total row.
β’ Use Good to highlight completed tasks.
β’ Use Warning for values that need attention.
π What are Themes?
A Theme applies a consistent look to your entire workbook by changing:
β’ Colors
β’ Fonts
β’ Effects
Go to:
Page Layout β Themes
When you apply a new theme, all compatible formatting updates automatically.
π Theme Components
β’ Colors β Changes the workbook's color palette.
β’ Fonts β Updates heading and body fonts.
β’ Effects β Modifies shapes and SmartArt styles.
π Why Use Cell Styles & Themes?
β’ Create professional reports quickly.
β’ Maintain consistent formatting across worksheets.
β’ Save time by avoiding repetitive formatting.
β’ Improve readability and presentation.
π‘ Real-World Example
A sales dashboard might use:
β’ Blue Heading style for titles.
β’ Green "Good" style for achieved targets.
β’ Red "Bad" style for missed targets.
β’ One theme across all worksheets for a consistent look.
β Best Practices
β’ Use one theme throughout the workbook.
β’ Apply styles consistently to similar data.
β’ Avoid using too many different fonts or colors.
β’ Let Cell Styles handle formatting instead of manually changing every cell.
Mastering Cell Styles and Themes will make your Excel reports look clean, consistent, and professional.
Double Tap β€οΈ For More
Cell Styles and Themes help you create professional-looking worksheets with a consistent design. Instead of formatting each cell manually, you can apply predefined styles with a single click.
π What are Cell Styles?
Cell Styles are pre-designed formatting combinations that include:
β’ Font
β’ Font Size
β’ Font Color
β’ Fill Color
β’ Borders
β’ Number Format
Go to:
Home β Cell Styles
π Common Built-in Styles
β’ Title
β’ Heading 1
β’ Heading 2
β’ Total
β’ Good
β’ Bad
β’ Neutral
β’ Input
β’ Output
β’ Warning
π‘ Example:
β’ Use Heading 1 for report titles.
β’ Use Total for the final total row.
β’ Use Good to highlight completed tasks.
β’ Use Warning for values that need attention.
π What are Themes?
A Theme applies a consistent look to your entire workbook by changing:
β’ Colors
β’ Fonts
β’ Effects
Go to:
Page Layout β Themes
When you apply a new theme, all compatible formatting updates automatically.
π Theme Components
β’ Colors β Changes the workbook's color palette.
β’ Fonts β Updates heading and body fonts.
β’ Effects β Modifies shapes and SmartArt styles.
π Why Use Cell Styles & Themes?
β’ Create professional reports quickly.
β’ Maintain consistent formatting across worksheets.
β’ Save time by avoiding repetitive formatting.
β’ Improve readability and presentation.
π‘ Real-World Example
A sales dashboard might use:
β’ Blue Heading style for titles.
β’ Green "Good" style for achieved targets.
β’ Red "Bad" style for missed targets.
β’ One theme across all worksheets for a consistent look.
β Best Practices
β’ Use one theme throughout the workbook.
β’ Apply styles consistently to similar data.
β’ Avoid using too many different fonts or colors.
β’ Let Cell Styles handle formatting instead of manually changing every cell.
Mastering Cell Styles and Themes will make your Excel reports look clean, consistent, and professional.
Double Tap β€οΈ For More
β‘1π₯1π1
π Excel Basics #9 β Your First Formula in Excel
Formulas are the heart of Excel. They allow you to perform calculations automatically instead of doing them manually.
π What is a Formula?
A formula is an expression that tells Excel to calculate a result.
Every formula in Excel must start with an equal sign "=".
Example:
"=10+20"
Result: 30
π Using Cell References
Instead of typing numbers directly, you can refer to cells.
Example:
A2: 100
B2: 50
C2: "=A2+B2"
Result in C2: 150
If you change A2 from 100 to 200, the result automatically updates to 250.
This is why using cell references is better than typing values directly.
π Basic Arithmetic Operators
Excel supports the following operators:
β’ "+" β Addition
β’ "-" β Subtraction
β’ "_" β Multiplication
β’ "/" β Division
β’ "^" β Power (Exponent)
Examples:
β’ "=A2+B2" β Add values
β’ "=A2-B2" β Subtract values
β’ "=A2B2" β Multiply values
β’ "=A2/B2" β Divide values
β’ "=A2Β²" β Square the value in A2
π Order of Operations
Excel follows the standard mathematical order:
1. Parentheses "()"
2. Exponents "^"
3. Multiplication & Division "_" "/"
4. Addition & Subtraction "+" "-"
Example:
"=10+5β"
Result: 20
Because multiplication is performed first.
To change the order:
"=(10+5)β"
Result: 30
π‘ Real-World Example
Product: Laptop
Price B2: 50000
Quantity C2: 2
Total D2: "=B2C2"
Result: 100000
Whenever the Price or Quantity changes, the Total updates automatically.
β Best Practices
β’ Always start formulas with "=".
β’ Use cell references instead of hardcoding values.
β’ Use parentheses for complex calculations.
β’ Double-check formulas before copying them to other cells.
Mastering formulas is the first step toward becoming an Excel expert.
Double Tap β€οΈ For More
Formulas are the heart of Excel. They allow you to perform calculations automatically instead of doing them manually.
π What is a Formula?
A formula is an expression that tells Excel to calculate a result.
Every formula in Excel must start with an equal sign "=".
Example:
"=10+20"
Result: 30
π Using Cell References
Instead of typing numbers directly, you can refer to cells.
Example:
A2: 100
B2: 50
C2: "=A2+B2"
Result in C2: 150
If you change A2 from 100 to 200, the result automatically updates to 250.
This is why using cell references is better than typing values directly.
π Basic Arithmetic Operators
Excel supports the following operators:
β’ "+" β Addition
β’ "-" β Subtraction
β’ "_" β Multiplication
β’ "/" β Division
β’ "^" β Power (Exponent)
Examples:
β’ "=A2+B2" β Add values
β’ "=A2-B2" β Subtract values
β’ "=A2B2" β Multiply values
β’ "=A2/B2" β Divide values
β’ "=A2Β²" β Square the value in A2
π Order of Operations
Excel follows the standard mathematical order:
1. Parentheses "()"
2. Exponents "^"
3. Multiplication & Division "_" "/"
4. Addition & Subtraction "+" "-"
Example:
"=10+5β"
Result: 20
Because multiplication is performed first.
To change the order:
"=(10+5)β"
Result: 30
π‘ Real-World Example
Product: Laptop
Price B2: 50000
Quantity C2: 2
Total D2: "=B2C2"
Result: 100000
Whenever the Price or Quantity changes, the Total updates automatically.
β Best Practices
β’ Always start formulas with "=".
β’ Use cell references instead of hardcoding values.
β’ Use parentheses for complex calculations.
β’ Double-check formulas before copying them to other cells.
Mastering formulas is the first step toward becoming an Excel expert.
Double Tap β€οΈ For More
β€1π₯1π1π1
β
Excel Scenario-Based Questions for Interview & Practice π§ π
π Scenario 31
Question: Your manager asks you to return "Yes" if an Employee ID exists in the master list; otherwise, return "No". How would you do it?
Answer: Use IF() with COUNTIF().
Example:
=IF(COUNTIF(Master!A:A,A2)>0,"Yes","No")
_Why it works:_ COUNTIF checks if the ID appears at least once. If >0 then "Yes".
π Scenario 32
Question: You need to calculate the total sales between two specific dates. How would you do it?
Answer: Use SUMIFS().
Example:
=SUMIFS(B:B,A:A,">="&E2,A:A,"<="&F2)
Where E2 is the start date and F2 is the end date.
_Pro tip:_ Make sure column A is actually formatted as dates, not text.
π Scenario 33
Question: Your dataset has several blank rows, and you need to remove them quickly. What should you do?
Answer:
Home β Find & Select β Go To Special β Blanks β Right-click β Delete β Entire Row.
_Alternative:_ Filter for blanks and delete, or use Power Query to remove empty rows.
π Scenario 34
Question: You need to calculate the percentage of total sales contributed by each product. How would you do it?
Answer: Divide each product's sales by the total sales.
Example:
=B2/SUM(B2:B100)
Format the result as a Percentage.
_Tip:_ The $ locks the range so you can drag the formula down.
π Scenario 35
Question: You have multiple worksheets with the same structure, and your manager wants a combined summary. How do you do it?
Answer: Use Power Query (Data β Get Data β Combine Queries) or use Consolidate (Data β Consolidate) to merge and summarize the data from multiple sheets.
_Why Power Query wins:_ It auto-updates when new sheets/data are added.
π¬ Double Tap β₯οΈ For More!
π Scenario 31
Question: Your manager asks you to return "Yes" if an Employee ID exists in the master list; otherwise, return "No". How would you do it?
Answer: Use IF() with COUNTIF().
Example:
=IF(COUNTIF(Master!A:A,A2)>0,"Yes","No")
_Why it works:_ COUNTIF checks if the ID appears at least once. If >0 then "Yes".
π Scenario 32
Question: You need to calculate the total sales between two specific dates. How would you do it?
Answer: Use SUMIFS().
Example:
=SUMIFS(B:B,A:A,">="&E2,A:A,"<="&F2)
Where E2 is the start date and F2 is the end date.
_Pro tip:_ Make sure column A is actually formatted as dates, not text.
π Scenario 33
Question: Your dataset has several blank rows, and you need to remove them quickly. What should you do?
Answer:
Home β Find & Select β Go To Special β Blanks β Right-click β Delete β Entire Row.
_Alternative:_ Filter for blanks and delete, or use Power Query to remove empty rows.
π Scenario 34
Question: You need to calculate the percentage of total sales contributed by each product. How would you do it?
Answer: Divide each product's sales by the total sales.
Example:
=B2/SUM(B2:B100)
Format the result as a Percentage.
_Tip:_ The $ locks the range so you can drag the formula down.
π Scenario 35
Question: You have multiple worksheets with the same structure, and your manager wants a combined summary. How do you do it?
Answer: Use Power Query (Data β Get Data β Combine Queries) or use Consolidate (Data β Consolidate) to merge and summarize the data from multiple sheets.
_Why Power Query wins:_ It auto-updates when new sheets/data are added.
π¬ Double Tap β₯οΈ For More!
β€1π₯1π1π€1π1
π Excel Basics #10 β SUM() Function
The SUM() function is one of the most commonly used Excel functions. It quickly adds numbers without writing long formulas.
π What is the SUM() Function?
SUM() adds the values in one or more cells.
Syntax:
=SUM(number1, [number2], ...)
You can use numbers, cell references, or ranges.
π Example 1 β Add Individual Numbers
Formula:
=SUM(10,20,30)
Result: 60
π Example 2 β Add Cell Values
A
100
200
300
Formula:
=SUM(A2,A3,A4)
Result: 600
π Example 3 β Add a Range
Instead of selecting each cell individually, use a range.
Formula:
=SUM(A2:A10)
This adds all values from A2 to A10.
π Example 4 β Add Multiple Ranges
Formula:
=SUM(A2:A5,C2:C5)
This adds both ranges together.
π Real-World Example
Product Sales
Laptop 50000
Mouse 5000
Keyboard 8000
Monitor 25000
Formula:
=SUM(B2:B5)
Result: 88000
π AutoSum Shortcut
Excel can automatically insert the SUM() function.
Go to: Home β AutoSum (β)
Or use the shortcut: Alt + =
Excel automatically selects the nearby range.
π Common Mistakes
β’ Including blank cells unintentionally.
β’ Selecting the wrong range.
β’ Typing numbers as text (they won't be added).
β Best Practices
β’ Use ranges (A2:A100) instead of listing individual cells.
β’ Double-check the selected range before pressing Enter.
β’ Use AutoSum (Alt + =) to save time.
β’ Keep related numbers together in one column or row for easier calculations.
Double Tap β€οΈ For More
The SUM() function is one of the most commonly used Excel functions. It quickly adds numbers without writing long formulas.
π What is the SUM() Function?
SUM() adds the values in one or more cells.
Syntax:
=SUM(number1, [number2], ...)
You can use numbers, cell references, or ranges.
π Example 1 β Add Individual Numbers
Formula:
=SUM(10,20,30)
Result: 60
π Example 2 β Add Cell Values
A
100
200
300
Formula:
=SUM(A2,A3,A4)
Result: 600
π Example 3 β Add a Range
Instead of selecting each cell individually, use a range.
Formula:
=SUM(A2:A10)
This adds all values from A2 to A10.
π Example 4 β Add Multiple Ranges
Formula:
=SUM(A2:A5,C2:C5)
This adds both ranges together.
π Real-World Example
Product Sales
Laptop 50000
Mouse 5000
Keyboard 8000
Monitor 25000
Formula:
=SUM(B2:B5)
Result: 88000
π AutoSum Shortcut
Excel can automatically insert the SUM() function.
Go to: Home β AutoSum (β)
Or use the shortcut: Alt + =
Excel automatically selects the nearby range.
π Common Mistakes
β’ Including blank cells unintentionally.
β’ Selecting the wrong range.
β’ Typing numbers as text (they won't be added).
β Best Practices
β’ Use ranges (A2:A100) instead of listing individual cells.
β’ Double-check the selected range before pressing Enter.
β’ Use AutoSum (Alt + =) to save time.
β’ Keep related numbers together in one column or row for easier calculations.
Double Tap β€οΈ For More
β€1π₯1π€©1π1
π Excel Basics #12 β MIN() & MAX() Functions
The "MIN()" and "MAX()" functions help you quickly find the smallest and largest values in a dataset. They are commonly used in reports, dashboards, and data analysis.
π What is the MIN() Function?
The "MIN()" function returns the smallest numeric value in a range.
Syntax:
=MIN(number1, [number2], ...)
Example:
A
45
82
19
67
Formula:
=MIN(A2:A5)
Result:
19
π What is the MAX() Function?
The "MAX()" function returns the largest numeric value in a range.
Syntax:
=MAX(number1, [number2], ...)
Using the same data:
Formula:
=MAX(A2:A5)
Result:
82
π Real-World Example
Employee | Sales
Rahul | 45000
Priya | 62000
Amit | 51000
Neha | 70000
Formula to find the highest sales:
=MAX(B2:B5)
Result: 70000
Formula to find the lowest sales:
=MIN(B2:B5)
Result: 45000
π Things to Remember
β’ Blank cells are ignored.
β’ Text values are ignored.
β’ Zero ("0") is treated as a valid number.
π Related Functions
β’ =LARGE(range, k) β Returns the k-th largest value.
Example: =LARGE(A2:A10,2) Returns the 2nd largest value.
β’ =SMALL(range, k) β Returns the k-th smallest value.
Example: =SMALL(A2:A10,3) Returns the 3rd smallest value.
π Common Mistakes
β’ Selecting the wrong range.
β’ Expecting text values to be included.
β’ Forgetting that zero is considered a number.
β Best Practices
β’ Use "MIN()" and "MAX()" to identify the lowest and highest values quickly.
β’ Combine them with Conditional Formatting to highlight extreme values.
β’ Use "LARGE()" and "SMALL()" when you need rankings instead of just the highest or lowest value.
β’ Always verify your data range before applying the functions.
The "MIN()" and "MAX()" functions are essential for finding key insights in any dataset.
Double Tap β€οΈ For More
The "MIN()" and "MAX()" functions help you quickly find the smallest and largest values in a dataset. They are commonly used in reports, dashboards, and data analysis.
π What is the MIN() Function?
The "MIN()" function returns the smallest numeric value in a range.
Syntax:
=MIN(number1, [number2], ...)
Example:
A
45
82
19
67
Formula:
=MIN(A2:A5)
Result:
19
π What is the MAX() Function?
The "MAX()" function returns the largest numeric value in a range.
Syntax:
=MAX(number1, [number2], ...)
Using the same data:
Formula:
=MAX(A2:A5)
Result:
82
π Real-World Example
Employee | Sales
Rahul | 45000
Priya | 62000
Amit | 51000
Neha | 70000
Formula to find the highest sales:
=MAX(B2:B5)
Result: 70000
Formula to find the lowest sales:
=MIN(B2:B5)
Result: 45000
π Things to Remember
β’ Blank cells are ignored.
β’ Text values are ignored.
β’ Zero ("0") is treated as a valid number.
π Related Functions
β’ =LARGE(range, k) β Returns the k-th largest value.
Example: =LARGE(A2:A10,2) Returns the 2nd largest value.
β’ =SMALL(range, k) β Returns the k-th smallest value.
Example: =SMALL(A2:A10,3) Returns the 3rd smallest value.
π Common Mistakes
β’ Selecting the wrong range.
β’ Expecting text values to be included.
β’ Forgetting that zero is considered a number.
β Best Practices
β’ Use "MIN()" and "MAX()" to identify the lowest and highest values quickly.
β’ Combine them with Conditional Formatting to highlight extreme values.
β’ Use "LARGE()" and "SMALL()" when you need rankings instead of just the highest or lowest value.
β’ Always verify your data range before applying the functions.
The "MIN()" and "MAX()" functions are essential for finding key insights in any dataset.
Double Tap β€οΈ For More
β€1π1π₯1π€©1π1
π Excel Basics #13 β COUNT(), COUNTA() & COUNTBLANK() Functions
When working with large datasets, you often need to know how many cells contain numbers, how many are non-empty, or how many are blank. Excel provides three simple functions for this.
π 1. COUNT() Function
The "COUNT()" function counts only cells containing numeric values.
Syntax:
=COUNT(value1, [value2], ...)
Example:
A
100
250
Rahul
500
(Blank)
Formula:
=COUNT(A2:A6)
Result: 3
Only numeric values are counted.
π 2. COUNTA() Function
The "COUNTA()" function counts all non-empty cells, including:
β’ Numbers
β’ Text
β’ Dates
β’ Logical values (TRUE/FALSE)
Syntax:
=COUNTA(value1, [value2], ...)
Using the same data:
Formula:
=COUNTA(A2:A6)
Result: 4
Everything except the blank cell is counted.
π 3. COUNTBLANK() Function
The "COUNTBLANK()" function counts empty cells in a range.
Syntax:
=COUNTBLANK(range)
Formula:
=COUNTBLANK(A2:A6)
Result: 1
π Real-World Example
Employee | Sales
Rahul | 50000
Priya | 62000
Amit |
Neha | 70000
β’ =COUNT(B2:B5) β 3 (numeric sales values)
β’ =COUNTA(A2:A5) β 4 (employee names)
β’ =COUNTBLANK(B2:B5) β 1 (missing sales value)
π When to Use Each Function
β’ COUNT() β Count only numbers.
β’ COUNTA() β Count all filled cells.
β’ COUNTBLANK() β Count empty cells.
π Common Mistakes
β’ Using COUNT() to count text values.
β’ Assuming COUNTA() ignores text β it doesn't.
β’ Forgetting that a cell containing a formula is not considered blank, even if it displays an empty string ("").
β Best Practices
β’ Use COUNT() for numeric datasets.
β’ Use COUNTA() to check how many records have data.
β’ Use COUNTBLANK() to identify missing values before analysis.
β’ Combine these functions with charts and Pivot Tables to monitor data quality.
These three functions are essential for validating and analyzing data in Excel.
Double Tap β€οΈ For More
When working with large datasets, you often need to know how many cells contain numbers, how many are non-empty, or how many are blank. Excel provides three simple functions for this.
π 1. COUNT() Function
The "COUNT()" function counts only cells containing numeric values.
Syntax:
=COUNT(value1, [value2], ...)
Example:
A
100
250
Rahul
500
(Blank)
Formula:
=COUNT(A2:A6)
Result: 3
Only numeric values are counted.
π 2. COUNTA() Function
The "COUNTA()" function counts all non-empty cells, including:
β’ Numbers
β’ Text
β’ Dates
β’ Logical values (TRUE/FALSE)
Syntax:
=COUNTA(value1, [value2], ...)
Using the same data:
Formula:
=COUNTA(A2:A6)
Result: 4
Everything except the blank cell is counted.
π 3. COUNTBLANK() Function
The "COUNTBLANK()" function counts empty cells in a range.
Syntax:
=COUNTBLANK(range)
Formula:
=COUNTBLANK(A2:A6)
Result: 1
π Real-World Example
Employee | Sales
Rahul | 50000
Priya | 62000
Amit |
Neha | 70000
β’ =COUNT(B2:B5) β 3 (numeric sales values)
β’ =COUNTA(A2:A5) β 4 (employee names)
β’ =COUNTBLANK(B2:B5) β 1 (missing sales value)
π When to Use Each Function
β’ COUNT() β Count only numbers.
β’ COUNTA() β Count all filled cells.
β’ COUNTBLANK() β Count empty cells.
π Common Mistakes
β’ Using COUNT() to count text values.
β’ Assuming COUNTA() ignores text β it doesn't.
β’ Forgetting that a cell containing a formula is not considered blank, even if it displays an empty string ("").
β Best Practices
β’ Use COUNT() for numeric datasets.
β’ Use COUNTA() to check how many records have data.
β’ Use COUNTBLANK() to identify missing values before analysis.
β’ Combine these functions with charts and Pivot Tables to monitor data quality.
These three functions are essential for validating and analyzing data in Excel.
Double Tap β€οΈ For More
β€1π₯1π1π1
This media is not supported in your browser
VIEW IN TELEGRAM
β€2π₯1π€©1π1π1
*Important Shortcut Keys*
Ctrl + N β New Document
Ctrl + O β Open
Ctrl + S β Save
Ctrl + P β Print
Ctrl + C β Copy
Ctrl + X β Cut
Ctrl + V β Paste
Ctrl + Z β Undo
Ctrl + Y β Redo
Ctrl + A β Select All
Ctrl + B β Bold
Ctrl + I β Italic
Ctrl + U β Underline
Ctrl + F β Find
Ctrl + N β New Document
Ctrl + O β Open
Ctrl + S β Save
Ctrl + P β Print
Ctrl + C β Copy
Ctrl + X β Cut
Ctrl + V β Paste
Ctrl + Z β Undo
Ctrl + Y β Redo
Ctrl + A β Select All
Ctrl + B β Bold
Ctrl + I β Italic
Ctrl + U β Underline
Ctrl + F β Find
β€1π₯1π1
π Excel Formulas β Part 1
Letβs start with the most important Excel formulas for beginners.
1οΈβ£ SUM
Adds numbers together.
=SUM(A2:A10)
Example: Calculate total sales from A2 to A10.
2οΈβ£ AVERAGE
Calculates the average.
=AVERAGE(B2:B10)
Example: Find the average marks of students.
3οΈβ£ COUNT
Counts cells containing numbers.
=COUNT(A2:A10)
Example: Count how many numeric values are present.
4οΈβ£ COUNTA
Counts non-empty cells.
=COUNTA(A2:A10)
Example: Count how many customers have been entered.
5οΈβ£ MAX
Returns the largest value.
=MAX(C2:C10)
Example: Find the highest sales value.
6οΈβ£ MIN
Returns the smallest value.
=MIN(C2:C10)
Example: Find the lowest sales value.
7οΈβ£ COUNTBLANK
Counts empty cells.
=COUNTBLANK(A2:A10)
Example: Find how many records are missing data.
8οΈβ£ PRODUCT
Multiplies numbers together.
=PRODUCT(A2:A5)
Example: Multiply multiple values without writing A2_A3_A4**A5.
π‘ Quick Practice
Suppose column B contains:
100, 250, 300, 150, 200
Try to find:
1. Total β SUM
2. Average β AVERAGE
3. Highest value β MAX
4. Lowest value β MIN
5. Number of values β COUNT
Double Tap β€οΈ For Part-2
Letβs start with the most important Excel formulas for beginners.
1οΈβ£ SUM
Adds numbers together.
=SUM(A2:A10)
Example: Calculate total sales from A2 to A10.
2οΈβ£ AVERAGE
Calculates the average.
=AVERAGE(B2:B10)
Example: Find the average marks of students.
3οΈβ£ COUNT
Counts cells containing numbers.
=COUNT(A2:A10)
Example: Count how many numeric values are present.
4οΈβ£ COUNTA
Counts non-empty cells.
=COUNTA(A2:A10)
Example: Count how many customers have been entered.
5οΈβ£ MAX
Returns the largest value.
=MAX(C2:C10)
Example: Find the highest sales value.
6οΈβ£ MIN
Returns the smallest value.
=MIN(C2:C10)
Example: Find the lowest sales value.
7οΈβ£ COUNTBLANK
Counts empty cells.
=COUNTBLANK(A2:A10)
Example: Find how many records are missing data.
8οΈβ£ PRODUCT
Multiplies numbers together.
=PRODUCT(A2:A5)
Example: Multiply multiple values without writing A2_A3_A4**A5.
π‘ Quick Practice
Suppose column B contains:
100, 250, 300, 150, 200
Try to find:
1. Total β SUM
2. Average β AVERAGE
3. Highest value β MAX
4. Lowest value β MIN
5. Number of values β COUNT
Double Tap β€οΈ For Part-2
β€2π1π₯1π€©1π1
π Excel Formulas β Part 2
This part focuses on logical formulas β formulas that help Excel make decisions based on conditions.
1οΈβ£ IF
Returns one value when a condition is TRUE and another when it is FALSE.
=IF(B2>=50,"Pass","Fail")
Example: If marks are 50 or more β Pass, otherwise β Fail.
2οΈβ£ IFS
Checks multiple conditions without using deeply nested IFs.
=IFS(B2>=90,"A",B2>=75,"B",B2>=50,"C",TRUE,"Fail")
Example:
90+ β A
75β89 β B
50β74 β C
Below 50 β Fail
3οΈβ£ AND
Returns TRUE only when all conditions are TRUE.
=AND(B2>=50,C2>=75)
Example: Check whether a student scored at least 50 in one subject and 75 in another.
4οΈβ£ OR
Returns TRUE when at least one condition is TRUE.
=OR(B2>=90,C2>=90)
Example: Check whether a student scored 90 or more in either subject.
5οΈβ£ NOT
Reverses a logical result.
=NOT(B2="Completed")
If B2 contains Completed, the result is FALSE.
π₯ Combine Logical Functions
You can combine them to create more useful formulas.
=IF(AND(B2>=50,C2>=50),"Pass","Fail")
This checks whether the student passed both subjects.
Another example:
=IF(OR(B2="Yes",C2="Yes"),"Eligible","Not Eligible")
π‘ Quick Practice
Student Marks 1 Marks 2
Rahul 75 82
Priya 45 90
Amit 60 40
Neha 95 88
Try creating formulas to:
1. Check whether both marks are β₯ 50 β AND
2. Check whether either mark is β₯ 90 β OR
3. Return Pass/Fail β IF
4. Categorize students based on marks β IFS
Double Tap β€οΈ For Part-3
This part focuses on logical formulas β formulas that help Excel make decisions based on conditions.
1οΈβ£ IF
Returns one value when a condition is TRUE and another when it is FALSE.
=IF(B2>=50,"Pass","Fail")
Example: If marks are 50 or more β Pass, otherwise β Fail.
2οΈβ£ IFS
Checks multiple conditions without using deeply nested IFs.
=IFS(B2>=90,"A",B2>=75,"B",B2>=50,"C",TRUE,"Fail")
Example:
90+ β A
75β89 β B
50β74 β C
Below 50 β Fail
3οΈβ£ AND
Returns TRUE only when all conditions are TRUE.
=AND(B2>=50,C2>=75)
Example: Check whether a student scored at least 50 in one subject and 75 in another.
4οΈβ£ OR
Returns TRUE when at least one condition is TRUE.
=OR(B2>=90,C2>=90)
Example: Check whether a student scored 90 or more in either subject.
5οΈβ£ NOT
Reverses a logical result.
=NOT(B2="Completed")
If B2 contains Completed, the result is FALSE.
π₯ Combine Logical Functions
You can combine them to create more useful formulas.
=IF(AND(B2>=50,C2>=50),"Pass","Fail")
This checks whether the student passed both subjects.
Another example:
=IF(OR(B2="Yes",C2="Yes"),"Eligible","Not Eligible")
π‘ Quick Practice
Student Marks 1 Marks 2
Rahul 75 82
Priya 45 90
Amit 60 40
Neha 95 88
Try creating formulas to:
1. Check whether both marks are β₯ 50 β AND
2. Check whether either mark is β₯ 90 β OR
3. Return Pass/Fail β IF
4. Categorize students based on marks β IFS
Double Tap β€οΈ For Part-3
β€1π1π₯1π1π1
π Excel Formulas β Part 3
Conditional Calculations
This part focuses on conditional calculations β extremely useful when working with real-world datasets.
1οΈβ£ SUMIF
Adds values that meet one condition.
=SUMIF(A2:A10,"Sales",B2:B10)
Example: Add sales only for rows where the category is Sales.
2οΈβ£ SUMIFS
Adds values based on multiple conditions.
=SUMIFS(C2:C10,A2:A10,"East",B2:B10,"Laptop")
Example: Calculate laptop sales only for the East region.
Syntax:
=SUMIFS(sum_range, criteriaα΅£ange1, criteria1, criteriaα΅£ange2, criteria2)
3οΈβ£ COUNTIF
Counts cells that meet one condition.
=COUNTIF(B2:B10,">=50")
Example: Count how many students scored 50 or more.
4οΈβ£ COUNTIFS
Counts cells/rows that meet multiple conditions.
=COUNTIFS(A2:A10,"East",B2:B10,">=100")
Example: Count transactions from the East region where sales are at least 100.
5οΈβ£ AVERAGEIF
Calculates an average based on one condition.
=AVERAGEIF(A2:A10,"Sales",B2:B10)
Example: Calculate the average sales for the Sales category.
6οΈβ£ AVERAGEIFS
Calculates an average based on multiple conditions.
=AVERAGEIFS(C2:C10,A2:A10,"East",B2:B10,"Laptop")
Example: Calculate the average laptop sales in the East region.
π‘ Sample Data
Region | Product | Sales
East | Laptop | 500
West | Mouse | 200
East | Laptop | 700
South | Keyboard | 300
East | Mouse | 250
Try calculating:
1. Total East sales β SUMIF
2. Total East Laptop sales β SUMIFS
3. Number of transactions above 300 β COUNTIF
4. Number of East transactions β COUNTIF
5. Number of East Laptop transactions β COUNTIFS
6. Average East sales β AVERAGEIF
7. Average East Laptop sales β AVERAGEIFS
π§ Remember
β’ IF β Make a decision
β’ SUMIF β Add based on a condition
β’ SUMIFS β Add based on multiple conditions
β’ COUNTIF β Count based on a condition
β’ COUNTIFS β Count based on multiple conditions
β’ AVERAGEIF β Average based on a condition
β’ AVERAGEIFS β Average based on multiple conditions
Double Tap β€οΈ For Part-4
Conditional Calculations
This part focuses on conditional calculations β extremely useful when working with real-world datasets.
1οΈβ£ SUMIF
Adds values that meet one condition.
=SUMIF(A2:A10,"Sales",B2:B10)
Example: Add sales only for rows where the category is Sales.
2οΈβ£ SUMIFS
Adds values based on multiple conditions.
=SUMIFS(C2:C10,A2:A10,"East",B2:B10,"Laptop")
Example: Calculate laptop sales only for the East region.
Syntax:
=SUMIFS(sum_range, criteriaα΅£ange1, criteria1, criteriaα΅£ange2, criteria2)
3οΈβ£ COUNTIF
Counts cells that meet one condition.
=COUNTIF(B2:B10,">=50")
Example: Count how many students scored 50 or more.
4οΈβ£ COUNTIFS
Counts cells/rows that meet multiple conditions.
=COUNTIFS(A2:A10,"East",B2:B10,">=100")
Example: Count transactions from the East region where sales are at least 100.
5οΈβ£ AVERAGEIF
Calculates an average based on one condition.
=AVERAGEIF(A2:A10,"Sales",B2:B10)
Example: Calculate the average sales for the Sales category.
6οΈβ£ AVERAGEIFS
Calculates an average based on multiple conditions.
=AVERAGEIFS(C2:C10,A2:A10,"East",B2:B10,"Laptop")
Example: Calculate the average laptop sales in the East region.
π‘ Sample Data
Region | Product | Sales
East | Laptop | 500
West | Mouse | 200
East | Laptop | 700
South | Keyboard | 300
East | Mouse | 250
Try calculating:
1. Total East sales β SUMIF
2. Total East Laptop sales β SUMIFS
3. Number of transactions above 300 β COUNTIF
4. Number of East transactions β COUNTIF
5. Number of East Laptop transactions β COUNTIFS
6. Average East sales β AVERAGEIF
7. Average East Laptop sales β AVERAGEIFS
π§ Remember
β’ IF β Make a decision
β’ SUMIF β Add based on a condition
β’ SUMIFS β Add based on multiple conditions
β’ COUNTIF β Count based on a condition
β’ COUNTIFS β Count based on multiple conditions
β’ AVERAGEIF β Average based on a condition
β’ AVERAGEIFS β Average based on multiple conditions
Double Tap β€οΈ For Part-4
β€2π1π₯1π€©1π1π1
*π Master Microsoft Excel :*
The Excel Tree π
|
|ββ *Basics*
| βββ Workbook / Worksheet
| βββ Rows & Columns
| βββ Cells & Ranges
|
|ββ *Data Entry & Formatting*
| βββ Text / Numbers / Dates
| βββ Cell Formatting (bold, color, borders)
| βββ Conditional Formatting
| βββ Cell Styles & Themes
|
|ββ *Formulas & Functions*
| βββ
| βββ
| βββ
| βββ
| βββ
|
|ββ *Charts & Graphs*
| βββ Bar / Line / Pie / Column
| βββ Combo Charts
| βββ Sparklines
|
|ββ *Data Tools*
| βββ Data Validation
| βββ Remove Duplicates
| βββ Text to Columns
| βββ Flash Fill
|
|ββ *Sorting & Filtering*
| βββ AutoFilter
| βββ Custom Sort
| βββ Advanced Filter
|
|ββ *Pivot Tables & Pivot Charts*
| βββ Summarize large data
| βββ Drag & drop interface
| βββ Slicers for filtering
|
|ββ *Tables & Named Ranges*
| βββ Excel Tables (Insert > Table)
| βββ Named Ranges for easy reference
|
|ββ *Date & Time Functions*
| βββ
| βββ
| βββ
|
|ββ *Text Functions*
| βββ
| βββ
| βββ
|
|ββ *Logical & Lookup Functions*
| βββ
| βββ
| βββ
|
|ββ *Keyboard Shortcuts*
| βββ Ctrl + Arrow β Jump
| βββ Ctrl + Shift + L β Filter
| βββ F2 β Edit Cell
|
|ββ *Macros & Automation*
| βββ Record Macros
| βββ VBA (Visual Basic for Applications)
|
|ββ *Data Analysis Tools*
| βββ Goal Seek
| βββ Solver
| βββ What-If Analysis
|
|ββ *Best Practices*
| βββ Use tables for dynamic data
| βββ Use comments & named ranges
| βββ Avoid merged cells in data tables
|
|ββ END __
π¬ *Double Tap β€οΈ if this helped you!*
The Excel Tree π
|
|ββ *Basics*
| βββ Workbook / Worksheet
| βββ Rows & Columns
| βββ Cells & Ranges
|
|ββ *Data Entry & Formatting*
| βββ Text / Numbers / Dates
| βββ Cell Formatting (bold, color, borders)
| βββ Conditional Formatting
| βββ Cell Styles & Themes
|
|ββ *Formulas & Functions*
| βββ
=SUM(), =AVERAGE() | βββ
=IF(), =AND(), =OR() | βββ
=VLOOKUP() / =HLOOKUP() / =XLOOKUP() | βββ
=INDEX() / =MATCH() | βββ
=COUNT(), =COUNTA(), =COUNTIF() |
|ββ *Charts & Graphs*
| βββ Bar / Line / Pie / Column
| βββ Combo Charts
| βββ Sparklines
|
|ββ *Data Tools*
| βββ Data Validation
| βββ Remove Duplicates
| βββ Text to Columns
| βββ Flash Fill
|
|ββ *Sorting & Filtering*
| βββ AutoFilter
| βββ Custom Sort
| βββ Advanced Filter
|
|ββ *Pivot Tables & Pivot Charts*
| βββ Summarize large data
| βββ Drag & drop interface
| βββ Slicers for filtering
|
|ββ *Tables & Named Ranges*
| βββ Excel Tables (Insert > Table)
| βββ Named Ranges for easy reference
|
|ββ *Date & Time Functions*
| βββ
=TODAY(), =NOW() | βββ
=DATEDIF(), =EDATE() | βββ
=TEXT() for formatting |
|ββ *Text Functions*
| βββ
=LEFT(), =RIGHT(), =MID()| βββ
=LEN(), =FIND(), =SEARCH() | βββ
=CONCAT() / =TEXTJOIN() |
|ββ *Logical & Lookup Functions*
| βββ
=IFERROR() | βββ
=CHOOSE() | βββ
=SWITCH() |
|ββ *Keyboard Shortcuts*
| βββ Ctrl + Arrow β Jump
| βββ Ctrl + Shift + L β Filter
| βββ F2 β Edit Cell
|
|ββ *Macros & Automation*
| βββ Record Macros
| βββ VBA (Visual Basic for Applications)
|
|ββ *Data Analysis Tools*
| βββ Goal Seek
| βββ Solver
| βββ What-If Analysis
|
|ββ *Best Practices*
| βββ Use tables for dynamic data
| βββ Use comments & named ranges
| βββ Avoid merged cells in data tables
|
|ββ END __
π¬ *Double Tap β€οΈ if this helped you!*
β€2π₯°1π€©1π1π€1
π Excel Formulas β Part 4
This part focuses on Lookup formulas β essential when you need to find information from another table.
1οΈβ£ XLOOKUP
Searches for a value and returns the corresponding result.
=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")
Example: Find an employee's department using their Employee ID.
Why it's useful:
β’ Can look left or right
β’ Exact match by default
β’ Can return a custom result when nothing is found
2οΈβ£ VLOOKUP
Searches vertically in the first column of a table.
=VLOOKUP(A2,E2:G10,3,FALSE)
Example: Find a product's price using its Product ID.
Remember: FALSE β Exact match
TRUE β Approximate match
3οΈβ£ HLOOKUP
Searches horizontally across the first row of a table.
=HLOOKUP(B1,B2:F5,4,FALSE)
Useful when your lookup values are arranged horizontally.
4οΈβ£ INDEX
Returns a value from a specific position in a range.
=INDEX(B2:B10,4)
Example: Return the 4th value from the range B2:B10.
5οΈβ£ MATCH
Finds the position of a value within a range.
=MATCH("Laptop",A2:A10,0)
0 means you want an exact match.
6οΈβ£ INDEX + MATCH
A powerful combination for lookups.
=INDEX(C2:C10,MATCH(A2,A2:A10,0))
Here:
MATCH β Finds the position
INDEX β Returns the value from that position
π‘ Sample Data
Product ID Product Price
P101 Laptop 50000
P102 Mouse 800
P103 Keyboard 1500
P104 Monitor 12000
P105 Headphones 2500
Try finding the Price for Product ID P103 using:
1. XLOOKUP
2. VLOOKUP
3. INDEX + MATCH
π§ Double Tap β€οΈ For More
This part focuses on Lookup formulas β essential when you need to find information from another table.
1οΈβ£ XLOOKUP
Searches for a value and returns the corresponding result.
=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")
Example: Find an employee's department using their Employee ID.
Why it's useful:
β’ Can look left or right
β’ Exact match by default
β’ Can return a custom result when nothing is found
2οΈβ£ VLOOKUP
Searches vertically in the first column of a table.
=VLOOKUP(A2,E2:G10,3,FALSE)
Example: Find a product's price using its Product ID.
Remember: FALSE β Exact match
TRUE β Approximate match
3οΈβ£ HLOOKUP
Searches horizontally across the first row of a table.
=HLOOKUP(B1,B2:F5,4,FALSE)
Useful when your lookup values are arranged horizontally.
4οΈβ£ INDEX
Returns a value from a specific position in a range.
=INDEX(B2:B10,4)
Example: Return the 4th value from the range B2:B10.
5οΈβ£ MATCH
Finds the position of a value within a range.
=MATCH("Laptop",A2:A10,0)
0 means you want an exact match.
6οΈβ£ INDEX + MATCH
A powerful combination for lookups.
=INDEX(C2:C10,MATCH(A2,A2:A10,0))
Here:
MATCH β Finds the position
INDEX β Returns the value from that position
π‘ Sample Data
Product ID Product Price
P101 Laptop 50000
P102 Mouse 800
P103 Keyboard 1500
P104 Monitor 12000
P105 Headphones 2500
Try finding the Price for Product ID P103 using:
1. XLOOKUP
2. VLOOKUP
3. INDEX + MATCH
π§ Double Tap β€οΈ For More
β€4π€©1
π Excel Formulas β Part 5
This part focuses on Text Functions β essential for cleaning, extracting, and combining text in Excel.
1οΈβ£ LEFT
Extracts characters from the beginning of a text.
=LEFT(A2,5)
Example:
INDIA123 β INDIA
2οΈβ£ RIGHT
Extracts characters from the end of a text.
=RIGHT(A2,3)
Example:
INV123 β 123
3οΈβ£ MID
Extracts characters from the middle of a text.
=MID(A2,4,5)
Example:
EMP-12345 β 12345
4οΈβ£ LEN
Counts the number of characters in a text.
=LEN(A2)
Example:
Excel β 5
Spaces are also counted.
5οΈβ£ TRIM
Removes unnecessary spaces from text.
=TRIM(A2)
Example:
" John Smith " β "John Smith"
Very useful when cleaning imported data.
6οΈβ£ UPPER
Converts text to uppercase.
=UPPER(A2)
excel β EXCEL
7οΈβ£ LOWER
Converts text to lowercase.
=LOWER(A2)
EXCEL β excel
8οΈβ£ PROPER
Capitalizes the first letter of each word.
=PROPER(A2)
john smith β John Smith
9οΈβ£ CONCAT
Combines text from multiple cells.
=CONCAT(A2," ",B2)
Example:
A2 = John
B2 = Smith
Result β John Smith
π TEXTJOIN
Combines multiple values using a delimiter.
=TEXTJOIN(", ",TRUE,A2:A5)
Example:
SQL, Excel, Power BI, Tableau
The TRUE tells Excel to ignore empty cells.
π§ Quick Reference
LEFT β Extract from beginning
RIGHT β Extract from end
MID β Extract from middle
LEN β Count characters
TRIM β Remove extra spaces
UPPER β Convert to uppercase
LOWER β Convert to lowercase
PROPER β Capitalize words
CONCAT β Combine text
TEXTJOIN β Combine text with a separator
π‘ Practice
Suppose:
A2 = " john smith "
Try creating formulas to:
1. Remove extra spaces β TRIM
2. Convert to uppercase β UPPER
3. Convert to lowercase β LOWER
4. Capitalize properly β PROPER
5. Count characters β LEN
β€οΈ Double Tap & React For Part 6!
This part focuses on Text Functions β essential for cleaning, extracting, and combining text in Excel.
1οΈβ£ LEFT
Extracts characters from the beginning of a text.
=LEFT(A2,5)
Example:
INDIA123 β INDIA
2οΈβ£ RIGHT
Extracts characters from the end of a text.
=RIGHT(A2,3)
Example:
INV123 β 123
3οΈβ£ MID
Extracts characters from the middle of a text.
=MID(A2,4,5)
Example:
EMP-12345 β 12345
4οΈβ£ LEN
Counts the number of characters in a text.
=LEN(A2)
Example:
Excel β 5
Spaces are also counted.
5οΈβ£ TRIM
Removes unnecessary spaces from text.
=TRIM(A2)
Example:
" John Smith " β "John Smith"
Very useful when cleaning imported data.
6οΈβ£ UPPER
Converts text to uppercase.
=UPPER(A2)
excel β EXCEL
7οΈβ£ LOWER
Converts text to lowercase.
=LOWER(A2)
EXCEL β excel
8οΈβ£ PROPER
Capitalizes the first letter of each word.
=PROPER(A2)
john smith β John Smith
9οΈβ£ CONCAT
Combines text from multiple cells.
=CONCAT(A2," ",B2)
Example:
A2 = John
B2 = Smith
Result β John Smith
π TEXTJOIN
Combines multiple values using a delimiter.
=TEXTJOIN(", ",TRUE,A2:A5)
Example:
SQL, Excel, Power BI, Tableau
The TRUE tells Excel to ignore empty cells.
π§ Quick Reference
LEFT β Extract from beginning
RIGHT β Extract from end
MID β Extract from middle
LEN β Count characters
TRIM β Remove extra spaces
UPPER β Convert to uppercase
LOWER β Convert to lowercase
PROPER β Capitalize words
CONCAT β Combine text
TEXTJOIN β Combine text with a separator
π‘ Practice
Suppose:
A2 = " john smith "
Try creating formulas to:
1. Remove extra spaces β TRIM
2. Convert to uppercase β UPPER
3. Convert to lowercase β LOWER
4. Capitalize properly β PROPER
5. Count characters β LEN
β€οΈ Double Tap & React For Part 6!
β€4π1π₯1π1
π Excel Formulas β Part 6
This part focuses on Date & Time formulas β essential for reporting, deadlines, ageing analysis, trends, and time-based calculations.
1οΈβ£ TODAY
Returns the current date.
=TODAY()
β’ Example: Automatically display today's date.
2οΈβ£ NOW
Returns the current date and time.
=NOW()
β’ Example: Track when a workbook was last recalculated.
3οΈβ£ DATE
Creates a date from year, month, and day.
=DATE(2026,9,24)
β’ Result: 24-Sep-2026
4οΈβ£ YEAR
Extracts the year from a date.
=YEAR(A2)
β’ Example: 24-Sep-2026 β 2026
5οΈβ£ MONTH
Extracts the month number.
=MONTH(A2)
β’ Example: 24-Sep-2026 β 9
6οΈβ£ DAY
Extracts the day of the month.
=DAY(A2)
β’ Example: 24-Sep-2026 β 24
7οΈβ£ EOMONTH
Returns the last day of a month.
=EOMONTH(A2,0)
β’ If A2 is 24-Sep-2026 β Result: 30-Sep-2026
β’ You can also move between months:
β’ =EOMONTH(A2,1) β Last day of next month
8οΈβ£ DATEDIF
Calculates the difference between two dates.
=DATEDIF(A2,B2,"Y")
β’ Returns the number of complete years.
Other units:
β’ "Y" β Years
β’ "M" β Months
β’ "D" β Days
Example:
β’ =DATEDIF(A2,B2,"D") β Number of days between the dates.
9οΈβ£ DAYS
Returns the number of days between two dates.
=DAYS(B2,A2)
β’ Example:
β’ Start Date β 01-Sep-2026
β’ End Date β 24-Sep-2026
β’ Result β 23
π NETWORKDAYS
Calculates the number of working days between two dates, excluding weekends.
=NETWORKDAYS(A2,B2)
β’ You can also exclude holidays:
β’ =NETWORKDAYS(A2,B2,E2:E10)
π§ Quick Reference
β’ TODAY() β Current date
β’ NOW() β Current date + time
β’ DATE() β Create a date
β’ YEAR() β Extract year
β’ MONTH() β Extract month
β’ DAY() β Extract day
β’ EOMONTH() β Last day of month
β’ DATEDIF() β Difference between dates
β’ DAYS() β Number of days between dates
β’ NETWORKDAYS() β Working days between dates
π‘ Practice
β’ Employee β Joining Date β End Date
β’ Rahul β 10-Jan-2022 β 24-Sep-2026
β’ Priya β 15-Mar-2023 β 24-Sep-2026
β’ Amit β 20-Jul-2024 β 24-Sep-2026
β’ Neha β 05-Feb-2025 β 24-Sep-2026
β€οΈ Double Tap & React For Part 7!
This part focuses on Date & Time formulas β essential for reporting, deadlines, ageing analysis, trends, and time-based calculations.
1οΈβ£ TODAY
Returns the current date.
=TODAY()
β’ Example: Automatically display today's date.
2οΈβ£ NOW
Returns the current date and time.
=NOW()
β’ Example: Track when a workbook was last recalculated.
3οΈβ£ DATE
Creates a date from year, month, and day.
=DATE(2026,9,24)
β’ Result: 24-Sep-2026
4οΈβ£ YEAR
Extracts the year from a date.
=YEAR(A2)
β’ Example: 24-Sep-2026 β 2026
5οΈβ£ MONTH
Extracts the month number.
=MONTH(A2)
β’ Example: 24-Sep-2026 β 9
6οΈβ£ DAY
Extracts the day of the month.
=DAY(A2)
β’ Example: 24-Sep-2026 β 24
7οΈβ£ EOMONTH
Returns the last day of a month.
=EOMONTH(A2,0)
β’ If A2 is 24-Sep-2026 β Result: 30-Sep-2026
β’ You can also move between months:
β’ =EOMONTH(A2,1) β Last day of next month
8οΈβ£ DATEDIF
Calculates the difference between two dates.
=DATEDIF(A2,B2,"Y")
β’ Returns the number of complete years.
Other units:
β’ "Y" β Years
β’ "M" β Months
β’ "D" β Days
Example:
β’ =DATEDIF(A2,B2,"D") β Number of days between the dates.
9οΈβ£ DAYS
Returns the number of days between two dates.
=DAYS(B2,A2)
β’ Example:
β’ Start Date β 01-Sep-2026
β’ End Date β 24-Sep-2026
β’ Result β 23
π NETWORKDAYS
Calculates the number of working days between two dates, excluding weekends.
=NETWORKDAYS(A2,B2)
β’ You can also exclude holidays:
β’ =NETWORKDAYS(A2,B2,E2:E10)
π§ Quick Reference
β’ TODAY() β Current date
β’ NOW() β Current date + time
β’ DATE() β Create a date
β’ YEAR() β Extract year
β’ MONTH() β Extract month
β’ DAY() β Extract day
β’ EOMONTH() β Last day of month
β’ DATEDIF() β Difference between dates
β’ DAYS() β Number of days between dates
β’ NETWORKDAYS() β Working days between dates
π‘ Practice
β’ Employee β Joining Date β End Date
β’ Rahul β 10-Jan-2022 β 24-Sep-2026
β’ Priya β 15-Mar-2023 β 24-Sep-2026
β’ Amit β 20-Jul-2024 β 24-Sep-2026
β’ Neha β 05-Feb-2025 β 24-Sep-2026
β€οΈ Double Tap & React For Part 7!
β€2π1π₯1π1
π Excel Formulas β Part 6
This part focuses on Date & Time formulas β essential for reporting, deadlines, ageing analysis, trends, and time-based calculations.
1οΈβ£ TODAY
Returns the current date.
=TODAY()
β’ Example: Automatically display today's date.
2οΈβ£ NOW
Returns the current date and time.
=NOW()
β’ Example: Track when a workbook was last recalculated.
3οΈβ£ DATE
Creates a date from year, month, and day.
=DATE(2026,9,24)
β’ Result: 24-Sep-2026
4οΈβ£ YEAR
Extracts the year from a date.
=YEAR(A2)
β’ Example: 24-Sep-2026 β 2026
5οΈβ£ MONTH
Extracts the month number.
=MONTH(A2)
β’ Example: 24-Sep-2026 β 9
6οΈβ£ DAY
Extracts the day of the month.
=DAY(A2)
β’ Example: 24-Sep-2026 β 24
7οΈβ£ EOMONTH
Returns the last day of a month.
=EOMONTH(A2,0)
β’ If A2 is 24-Sep-2026 β Result: 30-Sep-2026
β’ You can also move between months:
β’ =EOMONTH(A2,1) β Last day of next month
8οΈβ£ DATEDIF
Calculates the difference between two dates.
=DATEDIF(A2,B2,"Y")
β’ Returns the number of complete years.
Other units:
β’ "Y" β Years
β’ "M" β Months
β’ "D" β Days
Example:
β’ =DATEDIF(A2,B2,"D") β Number of days between the dates.
9οΈβ£ DAYS
Returns the number of days between two dates.
=DAYS(B2,A2)
β’ Example:
β’ Start Date β 01-Sep-2026
β’ End Date β 24-Sep-2026
β’ Result β 23
π NETWORKDAYS
Calculates the number of working days between two dates, excluding weekends.
=NETWORKDAYS(A2,B2)
β’ You can also exclude holidays:
β’ =NETWORKDAYS(A2,B2,E2:E10)
π§ Quick Reference
β’ TODAY() β Current date
β’ NOW() β Current date + time
β’ DATE() β Create a date
β’ YEAR() β Extract year
β’ MONTH() β Extract month
β’ DAY() β Extract day
β’ EOMONTH() β Last day of month
β’ DATEDIF() β Difference between dates
β’ DAYS() β Number of days between dates
β’ NETWORKDAYS() β Working days between dates
π‘ Practice
β’ Employee β Joining Date β End Date
β’ Rahul β 10-Jan-2022 β 24-Sep-2026
β’ Priya β 15-Mar-2023 β 24-Sep-2026
β’ Amit β 20-Jul-2024 β 24-Sep-2026
β’ Neha β 05-Feb-2025 β 24-Sep-2026
β€οΈ Double Tap & React For Part 7!
This part focuses on Date & Time formulas β essential for reporting, deadlines, ageing analysis, trends, and time-based calculations.
1οΈβ£ TODAY
Returns the current date.
=TODAY()
β’ Example: Automatically display today's date.
2οΈβ£ NOW
Returns the current date and time.
=NOW()
β’ Example: Track when a workbook was last recalculated.
3οΈβ£ DATE
Creates a date from year, month, and day.
=DATE(2026,9,24)
β’ Result: 24-Sep-2026
4οΈβ£ YEAR
Extracts the year from a date.
=YEAR(A2)
β’ Example: 24-Sep-2026 β 2026
5οΈβ£ MONTH
Extracts the month number.
=MONTH(A2)
β’ Example: 24-Sep-2026 β 9
6οΈβ£ DAY
Extracts the day of the month.
=DAY(A2)
β’ Example: 24-Sep-2026 β 24
7οΈβ£ EOMONTH
Returns the last day of a month.
=EOMONTH(A2,0)
β’ If A2 is 24-Sep-2026 β Result: 30-Sep-2026
β’ You can also move between months:
β’ =EOMONTH(A2,1) β Last day of next month
8οΈβ£ DATEDIF
Calculates the difference between two dates.
=DATEDIF(A2,B2,"Y")
β’ Returns the number of complete years.
Other units:
β’ "Y" β Years
β’ "M" β Months
β’ "D" β Days
Example:
β’ =DATEDIF(A2,B2,"D") β Number of days between the dates.
9οΈβ£ DAYS
Returns the number of days between two dates.
=DAYS(B2,A2)
β’ Example:
β’ Start Date β 01-Sep-2026
β’ End Date β 24-Sep-2026
β’ Result β 23
π NETWORKDAYS
Calculates the number of working days between two dates, excluding weekends.
=NETWORKDAYS(A2,B2)
β’ You can also exclude holidays:
β’ =NETWORKDAYS(A2,B2,E2:E10)
π§ Quick Reference
β’ TODAY() β Current date
β’ NOW() β Current date + time
β’ DATE() β Create a date
β’ YEAR() β Extract year
β’ MONTH() β Extract month
β’ DAY() β Extract day
β’ EOMONTH() β Last day of month
β’ DATEDIF() β Difference between dates
β’ DAYS() β Number of days between dates
β’ NETWORKDAYS() β Working days between dates
π‘ Practice
β’ Employee β Joining Date β End Date
β’ Rahul β 10-Jan-2022 β 24-Sep-2026
β’ Priya β 15-Mar-2023 β 24-Sep-2026
β’ Amit β 20-Jul-2024 β 24-Sep-2026
β’ Neha β 05-Feb-2025 β 24-Sep-2026
β€οΈ Double Tap & React For Part 7!
β€4π1π1π1
π Excel Formulas β Part 8
This part focuses on Advanced Calculation Functions β useful for working with filtered data, multiple calculations, and large datasets.
1οΈβ£ SUBTOTAL
Performs calculations while respecting filtered or hidden rows depending on the function number.
=SUBTOTAL(9,B2:B100)
Here, 9 means SUM.
Common function numbers:
1 β AVERAGE
2 β COUNT
3 β COUNTA
9 β SUM
4 β MAX
5 β MIN
π‘ Very useful when working with filtered tables.
2οΈβ£ AGGREGATE
Performs calculations while allowing you to ignore errors, hidden rows, or nested subtotals.
=AGGREGATE(9,5,B2:B100)
Here:
9 β SUM
5 β Ignore hidden rows
It supports functions such as:
β’ AVERAGE
β’ COUNT
β’ MAX
β’ MIN
β’ SUM
β’ LARGE
β’ SMALL
3οΈβ£ SUMPRODUCT
Multiplies corresponding values and then adds the results.
=SUMPRODUCT(B2:B10,C2:C10)
Example:
Product Price Quantity
Laptop 50000 2
Mouse 800 5
Keyboard 1500 3
=SUMPRODUCT(B2:B4,C2:C4)
This calculates:
50000Γ2 + 800Γ5 + 1500Γ3
Result β 107,500
π‘ Extremely useful for weighted calculations and business analysis.
4οΈβ£ LARGE
Returns the nth largest value.
=LARGE(B2:B10,1)
β Largest value
=LARGE(B2:B10,2)
β 2nd largest value
5οΈβ£ SMALL
Returns the nth smallest value.
=SMALL(B2:B10,1)
β Smallest value
=SMALL(B2:B10,2)
β 2nd smallest value
6οΈβ£ RANK.EQ
Returns the rank of a number within a dataset.
=RANK.EQ(B2,$B$2:$B$10,0)
0 β Highest value gets rank 1
1 β Lowest value gets rank 1
Example:
Sales = 95000
Rank β 2
7οΈβ£ SUMPRODUCT + Conditions
SUMPRODUCT can also perform conditional calculations.
=SUMPRODUCT((A2:A10="East")*(B2:B10))
This calculates the total of values in column B where the region is East.
π‘ Useful when you need flexible calculations without creating helper columns.
π§ Quick Reference
SUBTOTAL β Calculations that work well with filtered data
AGGREGATE β Advanced calculations with options to ignore certain values
SUMPRODUCT β Multiply and sum corresponding values
LARGE β nth largest value
SMALL β nth smallest value
RANK.EQ β Rank values
SUMPRODUCT + Conditions β Flexible conditional calculations
π‘ Practice
Using a sales dataset, try to calculate:
1.
Total visible sales β SUBTOTAL
2.
Average visible sales β SUBTOTAL
3.
Sum while ignoring hidden rows β AGGREGATE
4.
Total revenue from Price Γ Quantity β SUMPRODUCT
5.
3rd highest sale β LARGE
6.
2nd lowest sale β SMALL
7.
Rank each salesperson β RANK.EQ
8.
Total East-region sales β SUMPRODUCT
π₯ Double Tap β€οΈ For More
This part focuses on Advanced Calculation Functions β useful for working with filtered data, multiple calculations, and large datasets.
1οΈβ£ SUBTOTAL
Performs calculations while respecting filtered or hidden rows depending on the function number.
=SUBTOTAL(9,B2:B100)
Here, 9 means SUM.
Common function numbers:
1 β AVERAGE
2 β COUNT
3 β COUNTA
9 β SUM
4 β MAX
5 β MIN
π‘ Very useful when working with filtered tables.
2οΈβ£ AGGREGATE
Performs calculations while allowing you to ignore errors, hidden rows, or nested subtotals.
=AGGREGATE(9,5,B2:B100)
Here:
9 β SUM
5 β Ignore hidden rows
It supports functions such as:
β’ AVERAGE
β’ COUNT
β’ MAX
β’ MIN
β’ SUM
β’ LARGE
β’ SMALL
3οΈβ£ SUMPRODUCT
Multiplies corresponding values and then adds the results.
=SUMPRODUCT(B2:B10,C2:C10)
Example:
Product Price Quantity
Laptop 50000 2
Mouse 800 5
Keyboard 1500 3
=SUMPRODUCT(B2:B4,C2:C4)
This calculates:
50000Γ2 + 800Γ5 + 1500Γ3
Result β 107,500
π‘ Extremely useful for weighted calculations and business analysis.
4οΈβ£ LARGE
Returns the nth largest value.
=LARGE(B2:B10,1)
β Largest value
=LARGE(B2:B10,2)
β 2nd largest value
5οΈβ£ SMALL
Returns the nth smallest value.
=SMALL(B2:B10,1)
β Smallest value
=SMALL(B2:B10,2)
β 2nd smallest value
6οΈβ£ RANK.EQ
Returns the rank of a number within a dataset.
=RANK.EQ(B2,$B$2:$B$10,0)
0 β Highest value gets rank 1
1 β Lowest value gets rank 1
Example:
Sales = 95000
Rank β 2
7οΈβ£ SUMPRODUCT + Conditions
SUMPRODUCT can also perform conditional calculations.
=SUMPRODUCT((A2:A10="East")*(B2:B10))
This calculates the total of values in column B where the region is East.
π‘ Useful when you need flexible calculations without creating helper columns.
π§ Quick Reference
SUBTOTAL β Calculations that work well with filtered data
AGGREGATE β Advanced calculations with options to ignore certain values
SUMPRODUCT β Multiply and sum corresponding values
LARGE β nth largest value
SMALL β nth smallest value
RANK.EQ β Rank values
SUMPRODUCT + Conditions β Flexible conditional calculations
π‘ Practice
Using a sales dataset, try to calculate:
1.
Total visible sales β SUBTOTAL
2.
Average visible sales β SUBTOTAL
3.
Sum while ignoring hidden rows β AGGREGATE
4.
Total revenue from Price Γ Quantity β SUMPRODUCT
5.
3rd highest sale β LARGE
6.
2nd lowest sale β SMALL
7.
Rank each salesperson β RANK.EQ
8.
Total East-region sales β SUMPRODUCT
π₯ Double Tap β€οΈ For More
β€2π1π₯1π1π1