*📊 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
📊 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
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
❤2👍1😍1🏆1
📊 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.
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.
❤3👍1😍1🏆1🤗1
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
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
❤3👍1😍1
📊 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
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
❤2👍1🔥1😁1
📊 Excel Shortcuts — Part 5
This part focuses on Tables, Filters & Data Analysis shortcuts — essential when working with large datasets.
🟢 Tables
1️⃣ Ctrl + T → Convert a data range into an Excel Table
2️⃣ Ctrl + Shift + L → Turn filters on/off
3️⃣ Alt + ↓ → Open the filter/dropdown menu
4️⃣ Ctrl + A → Select the current table/data region
🔵 Filters & Data
5️⃣ Alt + ↓ → Open a cell's dropdown list
6️⃣ Alt + A + S + A → Sort A to Z
7️⃣ Alt + A + S + D → Sort Z to A
8️⃣ Alt + A + C → Clear filters
9️⃣ Ctrl + Shift + L → Quickly add/remove AutoFilter
🟣 Table Navigation
🔟 Ctrl + → → Jump to the edge of the table/data
1️⃣1️⃣ Ctrl + ↓ → Jump to the bottom of the data
1️⃣2️⃣ Ctrl + Shift + → → Select data toward the right
1️⃣3️⃣ Ctrl + Shift + ↓ → Select data toward the bottom
🟠 Insert & Analyze
1️⃣4️⃣ Alt + N + V → Insert a PivotTable
1️⃣5️⃣ Alt + N + C → Insert a chart
1️⃣6️⃣ Alt + = → Quickly calculate a total
1️⃣7️⃣ Ctrl + F → Find specific data
1️⃣8️⃣ Ctrl + H → Find and replace data
💡 Real-World Example
Imagine you have 50,000 sales records.
Instead of manually working through the dataset:
➡️ Ctrl + T → Convert it into a table
➡️ Ctrl + Shift + L → Enable filters
➡️ Alt + ↓ → Open a filter
➡️ Sort the data → A–Z / Z–A
➡️ Alt + N + V → Create a PivotTable
➡️ Alt + N + C → Create a chart
➡️ Ctrl + F → Find a specific customer or product
🧠 Quick Practice
Take any dataset and try:
Ctrl + T → Create a table
Ctrl + Shift + L → Add filters
Alt + ↓ → Open a filter
Ctrl + Shift + ↓ → Select data downward
Alt + N + V → Create a PivotTable
Ctrl + F → Find a value
🔥 Double Tap ❤️ For More
This part focuses on Tables, Filters & Data Analysis shortcuts — essential when working with large datasets.
🟢 Tables
1️⃣ Ctrl + T → Convert a data range into an Excel Table
2️⃣ Ctrl + Shift + L → Turn filters on/off
3️⃣ Alt + ↓ → Open the filter/dropdown menu
4️⃣ Ctrl + A → Select the current table/data region
🔵 Filters & Data
5️⃣ Alt + ↓ → Open a cell's dropdown list
6️⃣ Alt + A + S + A → Sort A to Z
7️⃣ Alt + A + S + D → Sort Z to A
8️⃣ Alt + A + C → Clear filters
9️⃣ Ctrl + Shift + L → Quickly add/remove AutoFilter
🟣 Table Navigation
🔟 Ctrl + → → Jump to the edge of the table/data
1️⃣1️⃣ Ctrl + ↓ → Jump to the bottom of the data
1️⃣2️⃣ Ctrl + Shift + → → Select data toward the right
1️⃣3️⃣ Ctrl + Shift + ↓ → Select data toward the bottom
🟠 Insert & Analyze
1️⃣4️⃣ Alt + N + V → Insert a PivotTable
1️⃣5️⃣ Alt + N + C → Insert a chart
1️⃣6️⃣ Alt + = → Quickly calculate a total
1️⃣7️⃣ Ctrl + F → Find specific data
1️⃣8️⃣ Ctrl + H → Find and replace data
💡 Real-World Example
Imagine you have 50,000 sales records.
Instead of manually working through the dataset:
➡️ Ctrl + T → Convert it into a table
➡️ Ctrl + Shift + L → Enable filters
➡️ Alt + ↓ → Open a filter
➡️ Sort the data → A–Z / Z–A
➡️ Alt + N + V → Create a PivotTable
➡️ Alt + N + C → Create a chart
➡️ Ctrl + F → Find a specific customer or product
🧠 Quick Practice
Take any dataset and try:
Ctrl + T → Create a table
Ctrl + Shift + L → Add filters
Alt + ↓ → Open a filter
Ctrl + Shift + ↓ → Select data downward
Alt + N + V → Create a PivotTable
Ctrl + F → Find a value
🔥 Double Tap ❤️ For More
❤1👍1😁1😍1
Hey guys,
Today, I’m covering some Excel interview questions that often pop up in data analyst roles 👇👇
1. What are the most common functions used in Excel for data analysis?
- SUM(): Adds up values in a range.
- AVERAGE(): Finds the mean of a range of numbers.
- VLOOKUP() / XLOOKUP(): Searches for a value in a table and returns a related value.
- INDEX-MATCH: A more flexible alternative to VLOOKUP, allowing lookups in any direction.
- IF(): Performs logical tests and returns one value if TRUE, another if FALSE.
- COUNTIF(): Counts the number of cells that meet a specific condition.
- PivotTables: For summarizing, analyzing, and exploring large datasets.
2. What is the difference between VLOOKUP and XLOOKUP?
- VLOOKUP is an older function used to find data in a vertical column and return a value from another column to the right.
Example:
=VLOOKUP("A2", B2:D10, 3, FALSE)
- XLOOKUP is more powerful, offering the flexibility to search both vertically and horizontally, and it doesn’t require the lookup value to be in the first column.
Example:
=XLOOKUP(A2, B2:B10, C2:C10)
Tip: Explain the limitations of VLOOKUP (like not being able to search left or needing sorted data for approximate matches) and how XLOOKUP overcomes them.
3. How do you create a PivotTable in Excel, and why is it useful?
A PivotTable allows you to summarize large amounts of data quickly. Here’s how to create one:
1. Select your data.
2. Go to the Insert tab and click on PivotTable.
3. Choose where to place the PivotTable.
4. Drag and drop fields into the Rows, Columns, Values, and Filters sections.
4. What is conditional formatting, and how do you use it?
Conditional formatting is used to change the appearance of cells based on their content. It helps highlight trends, patterns, and outliers.
For example, to highlight cells greater than 1000:
1. Select the range of cells.
2. Go to the Home tab, click on Conditional Formatting.
3. Choose Highlight Cell Rules > Greater Than and enter 1000.
4. Choose a format (e.g., cell color) to apply.
5. How do you handle large datasets in Excel without slowing it down?
Here are some strategies to improve efficiency:
- Turn off automatic calculations: Use manual recalculation to prevent Excel from recalculating formulas every time you make a change.
File > Options > Formulas > Calculation Options > Manual
- Use fewer volatile functions: Functions like NOW(), TODAY(), and INDIRECT() recalculate every time a change is made.
- Use tables instead of ranges: Structured references in tables are more efficient.
- Split large datasets: If feasible, split your data across multiple sheets or workbooks.
- Remove unnecessary formatting: Too much formatting can bloat file size and slow down processing.
6. How do you use Excel for data cleaning?
Data cleaning is one of the first and most important steps in data analysis, and Excel provides multiple ways to do this:
- Remove duplicates: Easily eliminate duplicate entries.
- Text to Columns: Split data in one column into multiple columns (e.g., splitting full names into first and last names).
- TRIM(): Remove extra spaces from text.
- FIND() and SUBSTITUTE(): For locating and replacing specific characters or substrings.
7. What are some advanced Excel functions you’ve used for data analysis?
Aside from the basics, some advanced Excel functions you might mention include:
- ARRAYFORMULA(): Allows multiple calculations to be performed at once.
- OFFSET(): Returns a range that is offset from a starting point.
- FORECAST(): Predicts future values based on historical data.
- POWER QUERY: For data extraction, transformation, and loading (ETL) tasks.
Today, I’m covering some Excel interview questions that often pop up in data analyst roles 👇👇
1. What are the most common functions used in Excel for data analysis?
- SUM(): Adds up values in a range.
- AVERAGE(): Finds the mean of a range of numbers.
- VLOOKUP() / XLOOKUP(): Searches for a value in a table and returns a related value.
- INDEX-MATCH: A more flexible alternative to VLOOKUP, allowing lookups in any direction.
- IF(): Performs logical tests and returns one value if TRUE, another if FALSE.
- COUNTIF(): Counts the number of cells that meet a specific condition.
- PivotTables: For summarizing, analyzing, and exploring large datasets.
2. What is the difference between VLOOKUP and XLOOKUP?
- VLOOKUP is an older function used to find data in a vertical column and return a value from another column to the right.
Example:
=VLOOKUP("A2", B2:D10, 3, FALSE)
- XLOOKUP is more powerful, offering the flexibility to search both vertically and horizontally, and it doesn’t require the lookup value to be in the first column.
Example:
=XLOOKUP(A2, B2:B10, C2:C10)
Tip: Explain the limitations of VLOOKUP (like not being able to search left or needing sorted data for approximate matches) and how XLOOKUP overcomes them.
3. How do you create a PivotTable in Excel, and why is it useful?
A PivotTable allows you to summarize large amounts of data quickly. Here’s how to create one:
1. Select your data.
2. Go to the Insert tab and click on PivotTable.
3. Choose where to place the PivotTable.
4. Drag and drop fields into the Rows, Columns, Values, and Filters sections.
4. What is conditional formatting, and how do you use it?
Conditional formatting is used to change the appearance of cells based on their content. It helps highlight trends, patterns, and outliers.
For example, to highlight cells greater than 1000:
1. Select the range of cells.
2. Go to the Home tab, click on Conditional Formatting.
3. Choose Highlight Cell Rules > Greater Than and enter 1000.
4. Choose a format (e.g., cell color) to apply.
5. How do you handle large datasets in Excel without slowing it down?
Here are some strategies to improve efficiency:
- Turn off automatic calculations: Use manual recalculation to prevent Excel from recalculating formulas every time you make a change.
File > Options > Formulas > Calculation Options > Manual
- Use fewer volatile functions: Functions like NOW(), TODAY(), and INDIRECT() recalculate every time a change is made.
- Use tables instead of ranges: Structured references in tables are more efficient.
- Split large datasets: If feasible, split your data across multiple sheets or workbooks.
- Remove unnecessary formatting: Too much formatting can bloat file size and slow down processing.
6. How do you use Excel for data cleaning?
Data cleaning is one of the first and most important steps in data analysis, and Excel provides multiple ways to do this:
- Remove duplicates: Easily eliminate duplicate entries.
- Text to Columns: Split data in one column into multiple columns (e.g., splitting full names into first and last names).
- TRIM(): Remove extra spaces from text.
- FIND() and SUBSTITUTE(): For locating and replacing specific characters or substrings.
7. What are some advanced Excel functions you’ve used for data analysis?
Aside from the basics, some advanced Excel functions you might mention include:
- ARRAYFORMULA(): Allows multiple calculations to be performed at once.
- OFFSET(): Returns a range that is offset from a starting point.
- FORECAST(): Predicts future values based on historical data.
- POWER QUERY: For data extraction, transformation, and loading (ETL) tasks.
❤2👍1😁1😍1
📊 Excel Shortcuts — Part 6
This part focuses on Worksheet & Sheet Management shortcuts — useful when working with multiple sheets and large workbooks.
🟢 Worksheet Navigation
1️⃣ Ctrl + Page Up → Move to the previous worksheet
2️⃣ Ctrl + Page Down → Move to the next worksheet
3️⃣ Ctrl + Home → Go to the beginning of the active worksheet
4️⃣ Ctrl + End → Go to the last used cell
🔵 Working With Sheets
5️⃣ Shift + F11 → Insert a new worksheet
6️⃣ Alt + O + H + R → Rename the active worksheet
7️⃣ Alt + O + H + D → Delete the active worksheet
8️⃣ Alt + O + H + U → Unhide a worksheet
9️⃣ Alt + O + H + H → Hide the active worksheet
🟣 Moving & Copying Sheets
🔟 Alt + O + H + M → Move or copy a worksheet
1️⃣1️⃣ Ctrl + Page Up → Navigate to the previous sheet
1️⃣2️⃣ Ctrl + Page Down → Navigate to the next sheet
🟠 Workbook Navigation
1️⃣3️⃣ Ctrl + Tab → Switch between open workbooks/windows
1️⃣4️⃣ Ctrl + F6 → Switch between workbook windows
1️⃣5️⃣ Alt + Tab → Switch between open applications
💡 Real-World Example
Imagine your workbook contains:
📄 Sales
📄 Customers
📄 Products
📄 Dashboard
📄 Raw Data
Instead of clicking each sheet with your mouse:
➡️ Ctrl + Page Down → Move to the next sheet
➡️ Ctrl + Page Up → Move back
➡️ Shift + F11 → Create a new sheet
➡️ Rename the sheet using the keyboard
➡️ Hide unnecessary sheets when preparing a report
🔥 Double Tap ❤️ For More
This part focuses on Worksheet & Sheet Management shortcuts — useful when working with multiple sheets and large workbooks.
🟢 Worksheet Navigation
1️⃣ Ctrl + Page Up → Move to the previous worksheet
2️⃣ Ctrl + Page Down → Move to the next worksheet
3️⃣ Ctrl + Home → Go to the beginning of the active worksheet
4️⃣ Ctrl + End → Go to the last used cell
🔵 Working With Sheets
5️⃣ Shift + F11 → Insert a new worksheet
6️⃣ Alt + O + H + R → Rename the active worksheet
7️⃣ Alt + O + H + D → Delete the active worksheet
8️⃣ Alt + O + H + U → Unhide a worksheet
9️⃣ Alt + O + H + H → Hide the active worksheet
🟣 Moving & Copying Sheets
🔟 Alt + O + H + M → Move or copy a worksheet
1️⃣1️⃣ Ctrl + Page Up → Navigate to the previous sheet
1️⃣2️⃣ Ctrl + Page Down → Navigate to the next sheet
🟠 Workbook Navigation
1️⃣3️⃣ Ctrl + Tab → Switch between open workbooks/windows
1️⃣4️⃣ Ctrl + F6 → Switch between workbook windows
1️⃣5️⃣ Alt + Tab → Switch between open applications
💡 Real-World Example
Imagine your workbook contains:
📄 Sales
📄 Customers
📄 Products
📄 Dashboard
📄 Raw Data
Instead of clicking each sheet with your mouse:
➡️ Ctrl + Page Down → Move to the next sheet
➡️ Ctrl + Page Up → Move back
➡️ Shift + F11 → Create a new sheet
➡️ Rename the sheet using the keyboard
➡️ Hide unnecessary sheets when preparing a report
🔥 Double Tap ❤️ For More
❤2👍1😁1😍1
📊 Excel Shortcuts — Part 7
This part focuses on Paste Special, Go To Special & Advanced Productivity shortcuts.
🟢 Paste Special
1️⃣ Ctrl + Alt + V → Open Paste Special
2️⃣ Ctrl + Alt + V → Then V → Paste Values
3️⃣ Ctrl + Alt + V → Then F → Paste Formulas
4️⃣ Ctrl + Alt + V → Then T → Paste Formats
5️⃣ Ctrl + Alt + V → Then W → Paste Column Widths
💡 Paste Special is extremely useful when you want to copy data without bringing unwanted formulas or formatting.
🔵 Go To & Special Selection
6️⃣ F5 → Open Go To
7️⃣ Ctrl + G → Open Go To
8️⃣ Ctrl + G → Special → Open Go To Special
Go To Special can help select:
→ Blank cells
→ Formulas
→ Constants
→ Errors
→ Visible cells only
🟣 Powerful Selection Shortcuts
9️⃣ Ctrl + Shift + _ → Select the current data region
🔟 Ctrl + Shift + Arrow Key → Select data to the edge of a data region
1️⃣1️⃣ Alt + ; → Select visible cells only
💡 Alt + ; is especially useful when copying filtered data without copying hidden rows.
🟠 Freeze & View
1️⃣2️⃣ Alt + W + F + F → Freeze Panes
1️⃣3️⃣ Alt + W + F + R → Freeze Top Row
1️⃣4️⃣ Alt + W + F + C → Freeze First Column
🔥 Real-World Example
Suppose you have a filtered dataset with thousands of rows.
You want to copy only the visible records:
➡️ Apply a filter
➡️ Select the required range
➡️ Press Alt + ;
➡️ Press Ctrl + C
➡️ Paste the selected visible cells
This prevents hidden/filtered rows from being copied.
🔥 Double Tap ❤️ For More
This part focuses on Paste Special, Go To Special & Advanced Productivity shortcuts.
🟢 Paste Special
1️⃣ Ctrl + Alt + V → Open Paste Special
2️⃣ Ctrl + Alt + V → Then V → Paste Values
3️⃣ Ctrl + Alt + V → Then F → Paste Formulas
4️⃣ Ctrl + Alt + V → Then T → Paste Formats
5️⃣ Ctrl + Alt + V → Then W → Paste Column Widths
💡 Paste Special is extremely useful when you want to copy data without bringing unwanted formulas or formatting.
🔵 Go To & Special Selection
6️⃣ F5 → Open Go To
7️⃣ Ctrl + G → Open Go To
8️⃣ Ctrl + G → Special → Open Go To Special
Go To Special can help select:
→ Blank cells
→ Formulas
→ Constants
→ Errors
→ Visible cells only
🟣 Powerful Selection Shortcuts
9️⃣ Ctrl + Shift + _ → Select the current data region
🔟 Ctrl + Shift + Arrow Key → Select data to the edge of a data region
1️⃣1️⃣ Alt + ; → Select visible cells only
💡 Alt + ; is especially useful when copying filtered data without copying hidden rows.
🟠 Freeze & View
1️⃣2️⃣ Alt + W + F + F → Freeze Panes
1️⃣3️⃣ Alt + W + F + R → Freeze Top Row
1️⃣4️⃣ Alt + W + F + C → Freeze First Column
🔥 Real-World Example
Suppose you have a filtered dataset with thousands of rows.
You want to copy only the visible records:
➡️ Apply a filter
➡️ Select the required range
➡️ Press Alt + ;
➡️ Press Ctrl + C
➡️ Paste the selected visible cells
This prevents hidden/filtered rows from being copied.
🔥 Double Tap ❤️ For More
❤1👍1😁1😍1🏆1
📊 Excel Interview Questions — Part 1
1️⃣ What is the difference between VLOOKUP and XLOOKUP?
Sample Answer:
VLOOKUP searches for a value in the first column of a table and returns a value from a column to its right.
XLOOKUP is more flexible because it can search both left and right, supports exact matching by default, and allows a custom result when the value isn't found.
Example:
"=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")"
💡 In modern Excel, I would generally prefer XLOOKUP over VLOOKUP.
2️⃣ What is the difference between COUNT, COUNTA and COUNTBLANK?
Sample Answer:
"COUNT" → Counts cells containing numbers.
"COUNTA" → Counts non-empty cells.
"COUNTBLANK" → Counts empty cells.
Example:
"=COUNT(A2:A100)"
"=COUNTA(A2:A100)"
"=COUNTBLANK(A2:A100)"
💡 This is commonly asked to test your understanding of different data types.
3️⃣ What is the difference between SUMIF and SUMIFS?
Sample Answer:
"SUMIF" is used when we have one condition.
"SUMIFS" is used when we have multiple conditions.
Example:
"=SUMIF(A2:A100,"East",C2:C100)"
This calculates sales for the East region.
For multiple conditions:
"=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Laptop")"
This calculates Laptop sales in the East region.
4️⃣ What is the difference between relative and absolute cell references?
Sample Answer:
A relative reference changes when a formula is copied.
Example:
"A2"
An absolute reference remains fixed.
Example:
"A2"
A mixed reference fixes either the row or column:
"$A2"
"A$2"
💡 The "F4" key can be used while editing a formula to cycle through reference types.
5️⃣ What is a PivotTable?
Sample Answer:
A PivotTable is an Excel feature used to quickly summarize and analyze large datasets.
For example, from a sales dataset, I can create a PivotTable to calculate:
→ Sales by region
→ Sales by product
→ Monthly revenue
→ Number of transactions
It allows me to analyze data without manually writing multiple formulas.
6️⃣ How would you remove duplicate records in Excel?
Sample Answer:
I can use Excel's Remove Duplicates feature.
First, select the dataset.
Then:
"Data → Remove Duplicates"
I select the columns that should be considered when identifying duplicates and click OK.
💡 I would usually keep a copy of the original dataset before removing duplicates.
7️⃣ What is IFERROR and why is it useful?
Sample Answer:
"IFERROR" allows me to replace an Excel error with a more meaningful result.
Example:
"=IFERROR(A2/B2,0)"
If the calculation produces an error, Excel returns "0" instead.
Another example:
"=IFERROR(XLOOKUP(A2,E:E,F:F),"Not Found")"
This prevents errors such as "#N/A" from appearing in a report.
8️⃣ How would you find duplicate values in Excel?
Sample Answer:
One common approach is Conditional Formatting.
I select the required range and use:
"Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values"
Another approach is using a formula such as:
"=COUNTIF(A2:A100,A2)>1"
If the result is TRUE, the value appears more than once.
9️⃣ How would you extract the month from a date?
Sample Answer:
I can use the "MONTH" function to return the month number.
"=MONTH(A2)"
For example:
"15-Sep-2026" → "9"
If I need the month name:
"=TEXT(A2,"mmmm")"
Result:
"September"
💡 The approach depends on whether I need the numeric month or the actual month name.
🔟 What is the difference between VLOOKUP and INDEX + MATCH?
Sample Answer:
VLOOKUP searches for a value in the first column and returns a value from a column to the right.
INDEX + MATCH is more flexible because the lookup column doesn't have to be the first column, and it can look in either direction.
Example:
"=INDEX(C2:C100,MATCH(A2,A2:A100,0))"
Here:
"MATCH" → Finds the position of the lookup value.
"INDEX" → Returns the value from that position.
Double Tap ❤️ For More
1️⃣ What is the difference between VLOOKUP and XLOOKUP?
Sample Answer:
VLOOKUP searches for a value in the first column of a table and returns a value from a column to its right.
XLOOKUP is more flexible because it can search both left and right, supports exact matching by default, and allows a custom result when the value isn't found.
Example:
"=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")"
💡 In modern Excel, I would generally prefer XLOOKUP over VLOOKUP.
2️⃣ What is the difference between COUNT, COUNTA and COUNTBLANK?
Sample Answer:
"COUNT" → Counts cells containing numbers.
"COUNTA" → Counts non-empty cells.
"COUNTBLANK" → Counts empty cells.
Example:
"=COUNT(A2:A100)"
"=COUNTA(A2:A100)"
"=COUNTBLANK(A2:A100)"
💡 This is commonly asked to test your understanding of different data types.
3️⃣ What is the difference between SUMIF and SUMIFS?
Sample Answer:
"SUMIF" is used when we have one condition.
"SUMIFS" is used when we have multiple conditions.
Example:
"=SUMIF(A2:A100,"East",C2:C100)"
This calculates sales for the East region.
For multiple conditions:
"=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Laptop")"
This calculates Laptop sales in the East region.
4️⃣ What is the difference between relative and absolute cell references?
Sample Answer:
A relative reference changes when a formula is copied.
Example:
"A2"
An absolute reference remains fixed.
Example:
"A2"
A mixed reference fixes either the row or column:
"$A2"
"A$2"
💡 The "F4" key can be used while editing a formula to cycle through reference types.
5️⃣ What is a PivotTable?
Sample Answer:
A PivotTable is an Excel feature used to quickly summarize and analyze large datasets.
For example, from a sales dataset, I can create a PivotTable to calculate:
→ Sales by region
→ Sales by product
→ Monthly revenue
→ Number of transactions
It allows me to analyze data without manually writing multiple formulas.
6️⃣ How would you remove duplicate records in Excel?
Sample Answer:
I can use Excel's Remove Duplicates feature.
First, select the dataset.
Then:
"Data → Remove Duplicates"
I select the columns that should be considered when identifying duplicates and click OK.
💡 I would usually keep a copy of the original dataset before removing duplicates.
7️⃣ What is IFERROR and why is it useful?
Sample Answer:
"IFERROR" allows me to replace an Excel error with a more meaningful result.
Example:
"=IFERROR(A2/B2,0)"
If the calculation produces an error, Excel returns "0" instead.
Another example:
"=IFERROR(XLOOKUP(A2,E:E,F:F),"Not Found")"
This prevents errors such as "#N/A" from appearing in a report.
8️⃣ How would you find duplicate values in Excel?
Sample Answer:
One common approach is Conditional Formatting.
I select the required range and use:
"Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values"
Another approach is using a formula such as:
"=COUNTIF(A2:A100,A2)>1"
If the result is TRUE, the value appears more than once.
9️⃣ How would you extract the month from a date?
Sample Answer:
I can use the "MONTH" function to return the month number.
"=MONTH(A2)"
For example:
"15-Sep-2026" → "9"
If I need the month name:
"=TEXT(A2,"mmmm")"
Result:
"September"
💡 The approach depends on whether I need the numeric month or the actual month name.
🔟 What is the difference between VLOOKUP and INDEX + MATCH?
Sample Answer:
VLOOKUP searches for a value in the first column and returns a value from a column to the right.
INDEX + MATCH is more flexible because the lookup column doesn't have to be the first column, and it can look in either direction.
Example:
"=INDEX(C2:C100,MATCH(A2,A2:A100,0))"
Here:
"MATCH" → Finds the position of the lookup value.
"INDEX" → Returns the value from that position.
Double Tap ❤️ For More
❤1⚡1👍1🤩1🏆1