11. What is data validation in Excel?
Data validation restricts the type of data that can be entered in a cell or range. For example, you can limit inputs to whole numbers, dates within a range, or create drop-down lists to ensure consistent and error-free data entry.
12. Explain VLOOKUP and HLOOKUP functions.
VLOOKUP searches for a value in the first column of a vertical table and returns a value in the same row from a specified column. HLOOKUP works similarly but searches horizontally across the first row.
13. What is the difference between Excel tables and ranges?
Excel tables have structured references, automatic filtering, and dynamic ranges that expand with data. Ranges are simple cell blocks without these advanced features.
14. How do you create charts and graphs?
Select your data, then go to Insert > Chart and choose the chart type (bar, line, pie, etc.). Charts visually represent data trends, comparisons, or distributions.
15. What are macros and how do you create them?
Macros automate repetitive tasks by recording a series of actions or writing VBA code. To create one, go to View > Macros > Record Macro, perform the task, then stop recording for playback.
16. How do you record and run a macro?
Use Record Macro to capture actions, assign a name/key shortcut, perform the steps, then stop recording. Run it again via View > Macros or assigned shortcut.
17. What is the purpose of the IF function?
IF performs conditional logic: it returns one value if a condition is TRUE, and another if FALSE. Example:
=IF(A1>10, "High", "Low").18. Explain nested functions with an example.
Nested functions involve using one function inside another. Example:
=IF(SUM(A1:A5)>100, "Pass", "Fail") uses SUM inside IF.19. How do you use INDEX and MATCH functions together?
MATCH finds the position of a value in a range; INDEX returns the value from a position. Combined, they provide more flexible lookups than VLOOKUP.
Example:
=INDEX(B2:B10, MATCH("Item1", A2:A10, 0))20. What are array formulas?
Array formulas perform multiple calculations on one or more ranges and return either single or multiple results. They’re entered with Ctrl+Shift+Enter in older Excel versions. Example:
{=SUM(A1:A5*B1:B5)} calculates sum of products.React ♥️ if this helped you