41. What is What-If Analysis in Excel?
A tool that lets you experiment with data by changing input values to see different outcomes. It includes Scenario Manager (compare scenarios), Goal Seek (find input to reach desired output), and Data Tables (sensitivity analysis).
42. What is the difference between a function and a subroutine in VBA?
Functions return values and can be used in formulas, while subroutines perform actions but don’t return values. Functions are called like variables; subroutines can be called from anywhere in the code.
43. How do you check if a file exists in a specified location using VBA?
Use the
Dir() function to check for a file's existence. If it returns an empty string, the file does not exist; else, it exists.44. How do you debug VBA code?
Use F8 to step through code line-by-line, set breakpoints to pause execution at specific lines, and watch variables using the Immediate Window or Locals window.
45. Write a VBA function to calculate the area of a rectangle.
Function Area(Length As Double, Optional Width As Variant)
If IsMissing(Width) Then
Area = Length * Length
Else
Area = Length * Width
End If
End Function
46. Write a VBA function to check if a number is prime.
(Checks divisors, then shows message)
Sub Prime()
Dim divisors As Integer, number As Long, i As Long
divisors = 0
number = InputBox("Enter a number")
For i = 1 To number
If number Mod i = 0 Then divisors = divisors + 1
Next i
If divisors = 2 Then
MsgBox number & " is a prime number"
Else
MsgBox number & " is not a prime number"
End If
End Sub
47. Write a VBA code to create a bar chart with given data.
(You create chart by defining chart object, setting chart type to bar, and linking data; exact code depends on your data range.)
48. Explain how to consolidate data from multiple worksheets.
Create relationships between tables with common keys, then use Data > Relationships to manage them. PivotTables built on the data model can summarize across sheets.
49. How do you handle discrepancies or anomalies in Excel data?
Use conditional formatting to highlight outliers, data validation to prevent incorrect entries, and audit formulas to trace errors.
50. What are some latest Excel features in 2025?
Dynamic arrays (spill formulas), XLOOKUP function, new Lambda functions (custom reusable formulas), improved Power Query and data types integrations, and better collaboration tools.
React ♥️ if this helped you