✅ 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!

📊 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

📊 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

📊 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

*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

📊 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

📊 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

📊 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

*📊 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*
| ├── =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!*

📊 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

📊 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!

📊 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!

📊 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!

📊 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

📊 Excel Shortcuts — Part 1

Master these basic shortcuts first. They will save time every day when working with Excel.

🟢 File & Workbook Shortcuts

1️⃣ Ctrl + N → Create a new workbook

2️⃣ Ctrl + O → Open a workbook

3️⃣ Ctrl + S → Save the workbook

4️⃣ Ctrl + Shift + S → Save As

5️⃣ Ctrl + P → Print

6️⃣ Ctrl + W → Close the current workbook

🔵 Editing Shortcuts

7️⃣ Ctrl + C → Copy

8️⃣ Ctrl + X → Cut

9️⃣ Ctrl + V → Paste

🔟 Ctrl + Z → Undo

1️⃣1️⃣ Ctrl + Y → Redo / Repeat

1️⃣2️⃣ F2 → Edit the active cell

1️⃣3️⃣ Delete → Clear the contents of selected cells

🟣 Find & Select

1️⃣4️⃣ Ctrl + F → Find

1️⃣5️⃣ Ctrl + H → Find & Replace

1️⃣6️⃣ Ctrl + A → Select the current data region / all data

1️⃣7️⃣ Esc → Cancel the current action or entry

💡 Double Tap ❤️ For More

📊 Excel Shortcuts — Part 2

This part focuses on Navigation & Selection shortcuts — especially useful when working with large datasets

🟢 Navigation Shortcuts

1️⃣ Arrow Keys → Move one cell

2️⃣ Ctrl + Arrow Key → Jump to the edge of a data region

3️⃣ Home → Move to the beginning of the row

4️⃣ Ctrl + Home → Go to the beginning of the worksheet

5️⃣ Ctrl + End → Go to the last used cell

6️⃣ Page Up → Move one screen up

7️⃣ Page Down → Move one screen down

8️⃣ Alt + Page Up → Move one screen left

9️⃣ Alt + Page Down → Move one screen right

🔟 F5 → Open Go To 

🔵 Selection Shortcuts

1️⃣1️⃣ Shift + Arrow Key → Extend selection by one cell

1️⃣2️⃣ Ctrl + Shift + Arrow Key → Select data up to the edge of a data region

1️⃣3️⃣ Ctrl + Space → Select the entire column

1️⃣4️⃣ Shift + Space → Select the entire row

1️⃣5️⃣ Ctrl + A → Select the current data region / all data

1️⃣6️⃣ Ctrl + Shift + Space → Select the entire worksheet

💡 Real-World Example
Imagine you have 50,000 rows of sales data.

Instead of scrolling manually:

Ctrl + ↓ → Jump to the bottom of the data

Ctrl + ↑ → Jump back to the top

Ctrl + Shift + ↓ → Select data down to the end

Ctrl + Space → Select the entire column

Shift + Space → Select the entire row 

🧠 Double Tap ❤️ For More

📊 Excel Shortcuts — Part 3

This part focuses on Formatting Shortcuts — quickly format cells, numbers, rows, and columns without repeatedly using the ribbon.

🟢 Basic Formatting

1️⃣ Ctrl + B → Bold

2️⃣ Ctrl + I → Italic

3️⃣ Ctrl + U → Underline

4️⃣ Ctrl + 1 → Open Format Cells dialog box

5️⃣ Ctrl + 5 → Apply / remove strikethrough

🔵 Number Formatting

6️⃣ Ctrl + Shift + ~ → General format

7️⃣ Ctrl + Shift + $ → Currency format

8️⃣ Ctrl + Shift + % → Percentage format

9️⃣ Ctrl + Shift + # → Date format

🔟 Ctrl + Shift + @ → Time format

1️⃣1️⃣ Ctrl + Shift + ! → Number format with commas and two decimal places

🟣 Rows & Columns

1️⃣2️⃣ Ctrl + Shift + + → Insert cells, rows, or columns

1️⃣3️⃣ Ctrl + - → Delete selected cells, rows, or columns

1️⃣4️⃣ Alt + H + O + A → AutoFit row height

1️⃣5️⃣ Alt + H + O + I → AutoFit column width

🟠 Useful Formatting Actions

1️⃣6️⃣ Alt + H + H → Open Fill Color menu

1️⃣7️⃣ Alt + H + FC → Open Font Color menu

1️⃣8️⃣ Ctrl + Shift + & → Apply border

1️⃣9️⃣ Ctrl + Shift + _ → Remove border

2️⃣0️⃣ Ctrl + E → Center-align text

💡 Real-World Example

Suppose you receive a raw sales report.

Instead of manually formatting everything:

➡️ Select the header → Ctrl + B

➡️ Format sales as currency → Ctrl + Shift + $

➡️ Format percentages → Ctrl + Shift + %

➡️ AutoFit columns → Alt + H + O + I

➡️ Insert a new column → Ctrl + Shift + +

➡️ Open detailed formatting options → Ctrl + 1

🧠 Double Tap ❤️ For More