Top 100 Excel Interview Questions for Data Analyst
BASIC LEVEL
1. What are the common data types in Excel?
Number, Text, Date, Boolean, Error.
2. What is a cell in Excel?
Intersection of a row and a column.
3. What is a range in Excel?
Selection of two or more cells.
4. Difference between workbook and worksheet?
Workbook = entire file; Worksheet = single spreadsheet/tab.
5. Shortcut to save an Excel workbook?
Ctrl + S.
6. Shortcut to select an entire column?
Ctrl + Spacebar.
7. Shortcut to insert a new worksheet?
Shift + F11.
8. How to wrap text in a cell?
Home tab → Wrap Text.
9. How to change cell format to percentage?
Select → Home tab → % icon or Ctrl+Shift+%.
10.What is the maximum number of rows in Excel?
1,048,576 rows.
11.What is the maximum number of columns?
16,384 columns (up to column XFD).
12.How do you freeze a row?
View tab → Freeze Panes → Freeze Top Row.
13.What is conditional formatting?
Formatting based on specific conditions.
14.How to use Find and Replace?
Ctrl + H.
15.What is the use of AutoSum?
Quickly sum a range of cells.
16.What are cell references?
Relative, Absolute ($), and Mixed.
17.What is a relative cell reference?
Changes when formula is copied.
18.What is an absolute reference?
Doesn’t change when formula is copied ($A$1).
19.Shortcut to insert today's date?
Ctrl + ;
20.Shortcut to insert current time?
Ctrl + Shift + ;
21.How to add a hyperlink?
Insert tab → Link → Insert Hyperlink.
22.How to use Merge & Center?
Home tab → Merge & Center.
23.What is the default file extension for Excel 365?
.xlsx
24.What is an Excel Table?
Structured range with filter/sort features.
25.How to quickly apply a table style?
Insert → Table.
26.What is a chart in Excel?
Graphical representation of data.
27.Shortcut to open Format Cells dialog box?
Ctrl + 1.
28.What does the Fill Handle do?
Quickly copy or fill data/formulas.
29.How to group rows or columns?
Data → Group.
30.How to unhide rows?
Select area → Right-click → Unhide.
FORMULAS & FUNCTIONS
31.What is the syntax of VLOOKUP?
=VLOOKUP(lookup_value, table_array, col_index,
[range_lookup])
32.What is INDEX function?
Returns value at a given position in a range.
33.What is MATCH function?
Returns the relative position of an item.
34.What is IF function syntax?
=IF(condition, value_if_true, value_if_false)
35.Nested IF example?
=IF(A1>90,"A",IF(A1>80,"B","C"))
36.How do you handle errors in formulas?
Use IFERROR or IFNA.
37.What is SUM function?
Adds numbers.
38.What is COUNT function?
Counts numeric cells.
39.What is COUNTA function?
Counts non-empty cells.
40.What is COUNTBLANK?
Counts blank cells.
41.What is SUMIF?
Sum based on one condition.
42.What is SUMIFS?
Sum based on multiple conditions.
43.How to round numbers?
ROUND(), ROUNDUP(), ROUNDDOWN().
44.Formula to find maximum value?
=MAX(range)
45.Formula to find minimum value?
=MIN(range)
46.How to use CONCATENATE?
=CONCATENATE(A1,B1)
47.Difference between CONCAT and TEXTJOIN?
TEXTJOIN allows delimiter; CONCAT does not.
48.What does TRIM() do?
Removes extra spaces.
49.What does LEN() do?
Counts number of characters.
50.LEFT(), RIGHT(), MID() usage?
Extract parts of text.
51.How to replace text?
Use SUBSTITUTE() or REPLACE().
52.How to extract date from timestamp?
=INT(A1) (if A1 has timestamp).
53.How to find weekday name?
=TEXT(A1,"dddd")
54.How to find month name?
=TEXT(A1,"mmmm")
55.DATEDIF() function?
Find difference between two dates.
56.How to calculate age from DOB?
=DATEDIF(DOB, TODAY(),"Y")
57.What is the use of RAND()?
Generates random number between 0 and 1.
58.What is RANDBETWEEN()?
Generates random whole number between two numbers.
59.How to use IF with AND/OR?
=IF(AND(A1>10,B1<5),"Yes","No")
60.How to sum only visible cells?
Use SUBTOTAL(9,range).
INTERMEDIATE LEVEL
61.What is a Pivot Table?
Dynamic summary of data.
62.Steps to create a Pivot Table?
Insert → Pivot Table → Select range → Layout.
63.What is a Pivot Chart?
Graph based on Pivot Table.
64.What is a slicer?
Interactive filter for PivotTables.
65.What is GETPIVOTDATA?
Extracts data from PivotTables.
66.How to refresh Pivot Table data?
Right-click → Refresh.
67.Difference between manual and automatic calculation?
Manual needs F9; automatic updates instantly.
68.What is What-If Analysis?
Tools like Goal Seek, Data Table, Scenario Manager.
69.How to use Goal Seek?
Data → What-If Analysis → Goal Seek.
70.What is Scenario Manager?
Save and switch between different sets of values.
71.What is Data Validation?
Restrict data input.
72.How to create drop-down list?
Data Validation → List.
73.What is the FILTER function?
Filters range based on conditions.
74.What is SORT function?
Sorts range based on columns.
75.What is UNIQUE function?
Returns unique values from a list.
76.What is XLOOKUP?
Modern replacement for VLOOKUP, HLOOKUP, and INDEX-MATCH.
77.Syntax of XLOOKUP?
=XLOOKUP(lookup_value, lookup_array, return_array,
[if_not_found])
78.How to dynamically create charts?
Use dynamic named ranges.
79.What is OFFSET()?
Returns a reference offset from a cell.
80.How to create dynamic ranges?
Using OFFSET with COUNTA.
81.What is INDIRECT()?
Returns reference specified by a string.
82.What is a dashboard?
Interactive visual summary of data.
83.What are KPIs?
Key Performance Indicators.
84.What is Power Query?
Tool for data extraction, transformation, and load.
85.What is Power Pivot?
Data modeling add-in.
86.What is a calculated field in Pivot Table?
Custom field created from existing data.
87.What is the Watch Window?
Monitor formula results easily.
88.What is Quick Analysis Tool?
Provides shortcuts for common tasks.
89.How do you transpose rows to columns?
Paste Special → Transpose.
90.How to create sparkline charts?
Insert tab → Sparklines.
ADVANCED LEVEL
91.How to connect Excel to SQL Database?
Data → Get Data → From Database → SQL Server.
92.What is a Macro?
Record/automate repetitive tasks.
93.What is VBA?
Visual Basic for Applications - coding in Excel.
94.How to record a Macro?
View tab → Macros → Record Macro.
95.What is a Named Range?
A name assigned to a cell/range.
96.How to audit formulas?
Formulas tab → Trace Precedents/Dependents.
97.What is the use of Solver?
Optimization tool for decision problems.
98.How to validate email format?
Use Data Validation + Custom Formula: =ISNUMBER(FIND("@",A1))
99.How to calculate running total?
=SUM($A$1:A1) dragged down.
100. Best practices for Excel modeling?
Naming conventions, documentation, consistent formatting, minimal hardcoding.