41. What is VLOOKUP?
Answer:
VLOOKUP (Vertical Lookup) is used to search for a value in the first column of a table and return a value from another column.
Syntax:
=VLOOKUP(A2,$F$2:$H$100,2,FALSE)
Example:
Find Employee Name using Employee ID.
42. Difference Between VLOOKUP and XLOOKUP?
Concept | VLOOKUP | XLOOKUP
Search direction | Searches left to right only | Searches in any direction
Column reference | Requires column number | Uses column reference
Function age | Older function | Newer and more flexible
Return columns | Can return only one column | Can return multiple columns
Example:
=XLOOKUP(A2,F:F,G:G)
43. What are Pivot Tables?
Answer:
Pivot Tables summarize large datasets quickly.
They can:
โ Sum data
โ Count records
โ Calculate averages
โ Create reports
Example:
Total Sales by Region.
44. What are Slicers in Excel?
Answer:
Slicers are visual filters used with Pivot Tables and Pivot Charts.
Benefits:
โ Easy filtering
โ Interactive dashboards
โ User-friendly reports
45. Explain Conditional Formatting.
Answer:
Conditional Formatting automatically changes cell formatting based on conditions.
Examples:
โ Highlight top sales
โ Show duplicate values
โ Color negative profits
46. Difference Between COUNT, COUNTA, and COUNTIF?
COUNT
Counts numeric cells only.
=COUNT(A1:A10)
COUNTA
Counts non-empty cells.
=COUNTA(A1:A10)
COUNTIF
Counts based on criteria.
=COUNTIF(A1:A10,">100")
47. What are Absolute and Relative References?
Relative Reference
Changes when copied.
=A1+B1
Absolute Reference
Remains fixed.
=$A$1+$B$1
48. What is Data Validation?
Answer:
Data Validation restricts what users can enter.
Examples:
โ Dropdown lists
โ Date restrictions
โ Number ranges
Benefits:
โ Reduces errors
โ Improves data quality
49. Explain IFERROR().
Answer:
IFERROR handles errors and returns a custom value.
Example:
=IFERROR(A1/B1,"Error")
If B1 = 0, Excel returns "Error" instead of #DIV/0!
50. What is Power Query?
Answer:
Power Query is Excel's ETL tool.
Used for:
โ Importing data
โ Cleaning data
โ Transforming data
โ Combining datasets
Common tasks:
Remove duplicates
Split columns
Merge tables
51. What are Dashboards in Excel?
Answer:
Dashboards provide visual summaries of KPIs and business metrics.
Common elements:
โ KPI Cards
โ Charts
โ Slicers
โ Pivot Tables
52. Difference Between SUMIF and SUMIFS?
SUMIF
One condition.
=SUMIF(A:A,"East",B:B)
SUMIFS
Multiple conditions.
=SUMIFS(B:B,A:A,"East",C:C,"Electronics")
53. Explain INDEX + MATCH.
Answer:
A flexible alternative to VLOOKUP.
Example:
=INDEX(B:B,MATCH(A2,A:A,0))
Benefits:
โ Faster
โ More flexible
โ Can lookup left or right
54. What are Macros?
Answer:
Macros automate repetitive tasks.
Examples:
โ Formatting reports
โ Refreshing dashboards
โ Cleaning data
Recorded using:
View โ Macros โ Record Macro
55. What is VBA?
Answer:
VBA (Visual Basic for Applications) is Excel's programming language.
Used to:
โ Automate tasks
โ Create custom functions
โ Build advanced reports
Example: