1.
Introduction to Excel (Basic)
What are the key components of the Excel interface?
Excel interface includes Ribbon, Formula Bar, Worksheet Grid, Sheet Tabs, Status Bar, and Quick
Access Toolbar.
How do you navigate between worksheets in a workbook?
Click on sheet tabs at the bottom or use Ctrl + Page Up/Page Down.
What is the difference between "Save" and "Save As"?
"Save" updates the current file, "Save As" creates a new file with a different name/location.
How do you import data from a CSV file into Excel?
Go to File > Open > Browse and select the CSV file, or use Data > Get External Data.
Explain the use of "Cut, Copy, and Paste" in Excel.
"Cut" moves data, "Copy" duplicates it, and "Paste" places it elsewhere.
What is "Paste Special," and when would you use it?
Paste Special lets you paste specific elements like values, formats, or formulas.
How do you use "Undo" and "Redo" in Excel?
Use Ctrl + Z for Undo and Ctrl + Y for Redo to reverse or repeat actions.
What are "Cell Styles," and how do you apply them?
Cell Styles are pre-set formats for cells. Use the Home tab to apply them.
How do you format numbers as currency or percentages?
Select the cell, go to Home > Number group, and choose Currency or Percentage.
How do you add and manage comments in a cell?
Right-click the cell > New Comment to add, Edit Comment to update.
2. Formulas and Functions
11. What is the difference between a formula and a function?
A formula is user-defined (e.g., =A1+B1), while a function is built-in (e.g., =SUM(A1:A5)).
How do you write a basic SUM formula?
Use =SUM(range), e.g., =SUM(A1:A5).
Explain the use of the AutoSum feature.
AutoSum quickly adds numbers above or beside the selected cell.
What are some common Excel functions (e.g., AVERAGE, MAX, MIN)?
AVERAGE calculates mean, MAX finds the highest value, MIN finds the lowest.
How do you use the Formulas Tab in Excel?
Use it to insert functions, define names, and check formula errors.
How do you copy a formula without changing cell references?
Use Absolute References with $ (e.g., =$A$1).
What are Date Functions (e.g., TODAY, NOW, DATEDIF)?
They return current dates/times or calculate differences between dates.
How do you extract part of a text string using LEFT, RIGHT, MID?
LEFT and RIGHT get text from ends; MID gets from the middle of a string.
What is the CONCATENATE function (or & operator)?
It joins two or more text strings, e.g., =A1 & B1.
How do you use TRIM to clean text data?
TRIM removes extra spaces from text.
3. Formula Referencing
21. What is Relative Reference in Excel?
It adjusts automatically when copied (e.g., A1 becomes A2).
What is Absolute Reference, and when would you use it?
It remains constant (e.g., $A$1), useful for fixed cells in formulas.
Explain Mixed References with an example.
Mix of relative and absolute, like $A1 or A$1.
How do you reference data from another worksheet?
Use SheetName!CellAddress, e.g., =Sheet2!A1.
What is 3D Reference, and how does it work?
Refers to same cell range across multiple sheets, e.g., =SUM(Sheet1:Sheet3!A1).
How do you consolidate data from multiple sheets?
Use Data > Consolidate to summarize data from different sheets.
What is the purpose of Tracing Precedents/Dependents?
Shows which cells affect (precedents) or are affected by (dependents) a formula.
How does the Watch Window help in debugging formulas?
Monitors key cells and their values while scrolling through a sheet.
What is Formula Evaluation, and how is it useful?
Step-by-step tool to evaluate and debug formulas.
How do you fix a circular reference error?
Edit the formula to remove self-referencing logic.
4. Named Ranges
31. What is a Named Range in Excel?
A label given to a cell/range for easier reference in formulas.
How do you create a Named Range?
Select range > Formulas Tab > Define Name.
How do you use Named Ranges in formulas?
Replace cell references with name, e.g., =SUM(SalesData).
What are the benefits of using Named Ranges?
Easier to read, understand, and manage formulas.
How do you edit or delete a Named Range?
Use Name Manager in the Formulas tab.
5. Tables in Excel
36. How do you convert a range into an Excel Table?
Select range > Insert > Table.
What are the advantages of using Tables?
Easy sorting, filtering, auto expansion, and structured references.
How do you add a Total Row to a Table?
Select Table > Table Design > Check âTotal Rowâ.
How do you apply Table Styles?
Use Table Design tab > Table Styles gallery.
How do you reference Table columns in formulas?
Use structured references like =SUM(Table1[Sales]).
6. AutoFill & Custom Lists
41. How does AutoFill work in Excel?
Definition: AutoFill helps quickly fill cells with a series or pattern based on your initial data.
Answer: AutoFill copies or extends a pattern from selected cells by dragging the fill handle across
adjacent cells, automatically completing sequences like numbers, dates, or custom lists.
42. How do you create a Custom List for AutoFill?
Definition: Custom Lists let you define your own sequences to use with AutoFill.
Answer: Go to File > Options > Advanced > Edit Custom Lists, then type your list entries separated
by commas or enter them from a range, and save to use in AutoFill.
43. How do you use AutoFill for non-adjacent cells?
Definition: Usually AutoFill works on adjacent cells, but you can fill non-adjacent cells selectively.
Answer: Select the source cell, then while dragging the fill handle, hold Ctrl (Windows) or
Command (Mac) and click on the non-adjacent target cells to fill them.
44. How do you format a series using AutoFill?
Definition: AutoFill can also carry over formatting when filling a series.
Answer: After entering the start of a series with desired formatting, select those cells and drag the
fill handle. The formatting (like number format, font color) will apply along with the data.
45. How do you sort data using a Custom List?
Definition: Sorting with Custom Lists allows ordering data by a predefined sequence instead of
alphabetically or numerically.
Answer: Use Data > Sort > Order dropdown > Custom List, then pick your list to sort your data in
the custom order you want.
7. Conditional Formatting
46. What is Conditional Formatting?
Definition: Conditional Formatting automatically changes cell appearance based on rules or
conditions.
Answer: It highlights or formats cells (color fill, font color, icons) when data meets criteria you
define, making important data stand out.
47. How do you highlight cells based on a rule?
Definition: You apply rules to cells so Excel formats them if conditions are true.
Answer: Select the cells, then go to Home > Conditional Formatting > Highlight Cells Rules,
choose a rule type (like Greater Than), set criteria, and pick a format.
48. What are Data Bars and Icon Sets?
Definition: Visual aids in Conditional Formatting to represent values graphically.
Answer: Data Bars display horizontal bars proportional to cell values; Icon Sets add symbols like
arrows or flags to show value categories.
49. How do you create a custom Conditional Formatting rule?
Definition: Custom rules let you use formulas or specific conditions not in default options.
Answer: Select cells, go to Conditional Formatting > New Rule > Use a formula to determine which
cells to format, enter your formula, then set formatting.
50. How do you manage and clear Conditional Formatting rules?
Definition: You can edit or remove existing conditional formatting rules.
Answer: Go to Home > Conditional Formatting > Manage Rules to edit or delete rules; to clear, use
Clear Rules > Clear Rules from Selected Cells or Entire Sheet.
8. Logical Functions
51. What is the IF function, and how does it work?
Definition: IF tests a condition and returns one value if TRUE and another if FALSE.
Answer: Syntax: =IF(condition, value_if_true, value_if_false). Example: =IF(A1>10, "High",
"Low").
52. How do you write a Nested IF statement?
Definition: Nesting means putting one IF inside another to test multiple conditions.
Answer: Use multiple IFs inside each other: =IF(A1>90, "A", IF(A1>80, "B", "C")).
53. Explain the AND, OR, NOT functions.
Definition: Logical functions to combine or invert conditions.
Answer:
AND returns TRUE if all conditions are true.
OR returns TRUE if any condition is true.
NOT reverses TRUE/FALSE value.
54. What is IFERROR, and why is it useful?
Definition: IFERROR catches errors in formulas and lets you show a custom result instead.
Answer: =IFERROR(formula, value_if_error) prevents errors like #DIV/0! from showing,
displaying a message or alternative instead.
55. How do SUMIF and COUNTIF work?
Definition: Functions to sum or count cells meeting a single condition.
Answer:
SUMIF(range, criteria, [sum_range]) sums cells matching criteria.
COUNTIF(range, criteria) counts cells matching criteria.
56. What is the difference between SUMIF and SUMIFS?
Definition: SUMIF applies one condition, SUMIFS applies multiple conditions.
Answer:
SUMIF sums with one criteria.
SUMIFS sums with two or more criteria.
57. How do you use COUNTIFS with multiple criteria?
Definition: COUNTIFS counts cells meeting multiple conditions simultaneously.
Answer: Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)
58. Write a formula to find the average of values meeting a condition.
Definition: Use AVERAGEIF or AVERAGEIFS for conditional averaging.
Answer:
=AVERAGEIF(range, criteria, [average_range])
Example: =AVERAGEIF(A1:A10, ">50")
59. How do you use IF with VLOOKUP to handle errors?
Definition: Combine IFERROR with VLOOKUP to avoid errors if lookup fails.
Answer:
=IFERROR(VLOOKUP(lookup_value, table_array, col_index, FALSE), "Not Found")
60. How do you combine IF with AND/OR for complex conditions?
Definition: Use AND or OR inside IF to test multiple conditions.
Answer:
=IF(AND(A1>10, B1<5), "Yes", "No")
or
=IF(OR(A1=1, B1=1), "True", "False")
9. Reference Functions (VLOOKUP, INDEX-MATCH, etc.)
61. What is VLOOKUP, and how does it work?
Definition: VLOOKUP searches for a value in the first column of a table and returns a value in the
same row from another column.
Answer: Syntax: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
62. What are the limitations of VLOOKUP?
Definition: VLOOKUP can only look right, requires sorted data for approximate matches, and is
less flexible.
Answer: It cannot lookup left, breaks if columns move, and may return wrong results without exact
match.
63. How do you perform an exact match vs. approximate match in VLOOKUP?
Definition: The 4th argument controls match type.
Answer:
Exact match: range_lookup = FALSE
Approximate match: range_lookup = TRUE or omitted
64. What is HLOOKUP, and when would you use it?
Definition: HLOOKUP searches horizontally in the first row instead of vertically.
Answer: Use HLOOKUP when your lookup values are in the first row of a table.
65. Explain INDEX and MATCH as an alternative to VLOOKUP.
Definition: INDEX returns a value by position; MATCH finds the position of a value. Together,
they offer flexible lookups.
Answer:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
66. Why is INDEX-MATCH better than VLOOKUP?
Definition: More flexible, allows left lookup, not affected by column changes, and faster on large
data.
Answer: It can lookup any column and doesn’t depend on table structure like VLOOKUP.
67. What is INDIRECT, and how is it used?
Definition: INDIRECT returns a reference from a text string, useful for dynamic references.
Answer: =INDIRECT("A1") returns the value in cell A1.
68. How does the OFFSET function work?
Definition: OFFSET returns a reference shifted by a specified number of rows and columns from a
starting point.
Answer: =OFFSET(reference, rows, cols, [height], [width])
69. What are ROW and COLUMN functions used for?
Definition: They return the row or column number of a cell or reference.
Answer:
=ROW(A3) returns 3,
=COLUMN(B1) returns 2.
70. What are Array Formulas, and how do you use them?
Definition: Array formulas process multiple values in one formula, returning multiple results or a
single result from multiple calculations.
Answer: Enter with Ctrl+Shift+Enter (in older Excel) or just enter in dynamic arrays (new Excel).
10. Data Validation
71. What is Data Validation in Excel?
Definition: Data Validation restricts what can be entered in cells to maintain data accuracy.
Answer: You can limit input to lists, numbers, dates, or custom rules.
72. How do you create a drop-down list?
Definition: Drop-down lists allow users to select from predefined options.
Answer: Select cells > Data > Data Validation > List > enter items separated by commas or select a
range.
73. How do you restrict input to numbers only?
Definition: Use Data Validation to allow only numeric values.
Answer: Data Validation > Allow: Whole Number or Decimal, then set limits.
74. How do you use a Custom Formula in Data Validation?
Definition: Custom formulas let you set complex criteria.
Answer: Data Validation > Allow: Custom > enter formula that returns TRUE/FALSE.
75. How do you validate data across multiple sheets?
Definition: Use named ranges or refer to ranges on other sheets in Data Validation lists.
Answer: Create named range on another sheet and refer to it in Data Validation list.
11. PivotTables & PivotCharts
76. What is a PivotTable, and why is it useful?
Definition: PivotTable summarizes and analyzes large datasets interactively.
Answer: It helps quickly group, filter, and aggregate data without formulas.
77. How do you create a PivotTable?
Definition: Select data and insert a PivotTable.
Answer: Select range > Insert > PivotTable > choose location > drag fields to Rows, Columns,
Values.
78. How do you group data in a PivotTable?
Definition: Grouping organizes items into categories like date ranges or numeric bins.
Answer: Right-click a field > Group > select grouping options (dates, numbers).
79. What are Slicers and Timelines?
Definition: Visual filters for PivotTables.
Answer: Slicers filter categories; Timelines filter date fields interactively.
80. How do you create a PivotChart?
Definition: PivotCharts are graphical PivotTable representations.
Answer: Select PivotTable > Insert > PivotChart > choose chart type.
81. How do you refresh a PivotTable when data changes?
Definition: Refresh updates the PivotTable with new or changed data.
Answer: Right-click PivotTable > Refresh or use Data > Refresh All.
82. What is the difference between Rows and Values in a PivotTable?
Definition: Rows organize data categories; Values show summarized numeric data.
Answer: Rows group data, Values calculate sums, averages, counts, etc.
83. How do you use Calculated Fields in a PivotTable?
Definition: Calculated Fields add custom formulas using PivotTable data.
Answer: PivotTable Analyze > Fields, Items & Sets > Calculated Field > enter formula.
84. How do you filter data in a PivotTable?
Definition: Filters limit what data is shown.
Answer: Use Filters area or dropdown arrows on fields to select items.
85. How do you format a PivotTable for better readability?
Definition: Formatting improves appearance and clarity.
Answer: Use Design tab for styles, adjust column widths, and apply number formatting.
12. Data Visualization (Charts & Graphs)
86. What are the different types of charts in Excel?
Definition: Charts visually represent data in various ways.
Answer: Common types include Column, Bar, Line, Pie, Scatter, Area, and Combo charts.
87. How do you create a line chart?
Definition: Line charts show trends over time or categories.
Answer: Select data > Insert > Line Chart > choose style.
88. When would you use a bar chart vs. a column chart?
Definition: Both show categorical comparisons but differ in orientation.
Answer: Use bar charts for long category names (horizontal bars), column charts for vertical
comparison.
89. What are Sparklines, and how do you insert them?
Definition: Sparklines are tiny charts inside cells showing trends.
Answer: Select cells > Insert > Sparklines > choose Line, Column, or Win/Loss > select data range.
90. How do you add a Trendline to a chart?
Definition: Trendlines show data trends or projections.
Answer: Click chart series > Chart Tools > Add Chart Element > Trendline > choose type.
91. What is a Forecast Sheet, and how does it work?
Definition: Forecast Sheets predict future values based on historical data.
Answer: Select data > Data tab > Forecast Sheet > customize options > create.
92. How do you customize chart titles and axes?
Definition: Titles and axes labels explain chart data.
Answer: Click chart > Chart Tools > Add Chart Element > Axis Titles or Chart Title > edit text.
93. How do you change the chart style and colors?
Definition: Styles control overall chart look and color scheme.
Answer: Select chart > Design tab > choose Style or Change Colors.
94. How do you create a dual-axis chart?
Definition: Dual-axis charts plot two data series with different scales.
Answer: Create chart > select data series > Format Data Series > Plot on Secondary Axis.
95. How do you save a chart as a template?
Definition: Templates let you reuse chart formatting.
Answer: Right-click chart > Save as Template > give name > use from Insert Chart > Templates.
13. What-If Analysis
96. What is Goal Seek, and how does it work?
Definition: Goal Seek finds the input value needed to reach a desired output.
Answer: Data > What-If Analysis > Goal Seek > set cell (formula), to value, by changing cell >
OK.
97. How do you use Scenario Manager?
Definition: Scenario Manager lets you save and switch between different input value sets.
Answer: Data > What-If Analysis > Scenario Manager > Add scenarios with different inputs >
Show.
98. What is a Data Table in Excel?
Definition: Data Tables show how changing variables affects results.
Answer: Use one- or two-variable tables under What-If Analysis to see multiple outcomes.
99. How do you use the Solver Tool?
Definition: Solver finds optimal solutions to problems with constraints.
Answer: Data > Solver > set objective cell, variables, constraints > Solve.
100. Give an example of a business case where What-If Analysis is useful.
Definition: What-If helps forecast or decision-making.
Answer: Calculating required sales to meet profit targets if prices or costs change.
Bonus: Advanced & AI-Related Excel Questions
How do you record and run a Macro in Excel?
Definition: Macros automate repetitive tasks by recording actions.
Answer: View > Macros > Record Macro > perform actions > Stop Recording; run via Macros
menu.
What is VBA, and how is it used in Excel?
Definition: VBA is a programming language to write custom macros and automate Excel beyond
recording.
Answer: Use VBA Editor (Alt+F11) to write scripts for complex automation.
How can ChatGPT or AI plugins help in Excel automation?
Definition: AI tools can generate formulas, write VBA code, analyze data, and create reports.
Answer: ChatGPT can help create formulas, explain functions, generate VBA code snippets, and
automate tasks faster.
1. Lookups & Reference Functions
1. How does XLOOKUP improve upon VLOOKUP?
XLOOKUP is more flexible than VLOOKUP as it can search both vertically and horizontally,
return exact or approximate matches, and lookup values to the left (which VLOOKUP cannot). It
also handles missing values better.
2. When would you use INDEX-MATCH over XLOOKUP?
INDEX-MATCH is preferred when working in older Excel versions that donât support
XLOOKUP or when needing more control over complex lookups combining row and column
indexing.
3. How do you perform a two-way lookup (row + column criteria)?
By combining INDEX and MATCH functions: MATCH finds the row and column positions based
on criteria, and INDEX returns the intersecting value.
4. How would you return multiple matches (e.g., all products sold by a
seller)?
Using array formulas or the FILTER function (Excel 365+) to extract all matching records based on
criteria.
5. Write a formula to find the last non-empty cell in a column.
=LOOKUP(2,1/(A:A<>""),A:A) â looks for the last cell in column A that is not empty.
2. Logical & Conditional Functions
6. How do you use IFS instead of nested IF statements?
IFS simplifies multiple conditions by testing them sequentially without deep nesting, improving
readability.
7. Explain how SUMIFS works with multiple criteria.
SUMIFS adds values that meet all specified conditions across ranges, allowing complex
summations with multiple filters.
8. How would you count unique values in a range?
Using SUMPRODUCT(1/COUNTIF(range, range)) or the UNIQUE function in Excel 365 to count
distinct entries.
9. Write a formula to flag duplicates in a dataset.
=IF(COUNTIF(range, cell)>1, "Duplicate", "") â marks cells that appear more than once.
10. How do you use AGGREGATE to ignore hidden rows/errors?
AGGREGATE performs calculations (like SUM, AVERAGE) while optionally ignoring errors,
hidden rows, or filtered-out data.
3. Data Cleaning & Text Manipulation
11. How do you split text into columns without using "Text to Columns"?
Using functions like TEXTSPLIT (Excel 365) or LEFT, RIGHT, and MID combined with FIND or
SEARCH.
12. Write a formula to remove extra spaces and non-printable characters.
=TRIM(CLEAN(text)) â removes extra spaces and non-printable characters.
13. How do you combine data from multiple cells (e.g., full name from first + last)?
Using =A1 & " " & B1 or =CONCATENATE(A1, " ", B1) to join text with a space.
14. How would you extract numbers from a text string (e.g., "Price: $50")?
Using array formulas or VBA; a simple way is with TEXTJOIN and MID functions or Power
Query for complex cases.
15. How do you replace part of a string dynamically (e.g., masking emails)?
Using REPLACE or SUBSTITUTE with formulas that calculate the position and length of the text
to replace.
4. PivotTables & Data Analysis
16. How do you group dates in a PivotTable (e.g., by quarters)?
Right-click date field â Group â select grouping options like Months, Quarters, or Years.
17. Whatâs the difference between PivotTable "Filters" vs. "Slicers"?
Filters are dropdown selectors in the PivotTable, while Slicers are visual buttons for easier
interactive filtering.
18. How do you calculate running totals in a PivotTable?
Add the value field, then set Value Field Settings â Show Values As â Running Total In.
19. How would you compare two columns for differences (e.g., actual vs. forecast)?
Using a formula like =IF(A2=B2, "Match", "Difference") or creating a PivotTable to summarize.
20. How do you refresh all PivotTables at once when data changes?
Use Data tab â Refresh All, or press Ctrl + Alt + F5.
5. Advanced Formulas & Calculations
21. How do you calculate moving averages in Excel?
Using AVERAGE over a rolling range, e.g., =AVERAGE(B2:B4) and dragging down.
22. Write a formula to find the percentile of a dataset.
=PERCENTILE.EXC(range, k) â returns the kth percentile (e.g., 0.9 for 90th percentile).
23. How do you use SUMPRODUCT for conditional weighted averages?
=SUMPRODUCT(values, weights)/SUM(weights) â calculates weighted average, optionally
adding conditions inside SUMPRODUCT.
24. Explain how INDIRECT works for dynamic sheet references.
INDIRECT converts a text string into a cell or range reference, enabling dynamic references to
sheets or cells.
25. How would you rank data with ties (e.g., sales performance)?
Using =RANK.EQ(value, range, order) â assigns same rank to ties; use additional logic to break
ties if needed.
6. Data Validation & Error Handling
26. How do you create a dependent drop-down list (e.g., country â city)?
Create named ranges for dependent lists and use INDIRECT in Data Validation to link selections
dynamically.
27. How do you prevent invalid dates (e.g., Feb 30) using Data Validation?
Set Data Validation with a custom formula like =AND(ISNUMBER(A1), A1=DATE(YEAR(A1),
MONTH(A1), DAY(A1))).
28. How would you trap errors in a complex formula (e.g., IFERROR vs. IFNA)?
Use IFERROR to catch all errors or IFNA to catch only #N/A errors and provide alternative results.
29. How do you audit a formula for mistakes (e.g., Trace Precedents)?
Use Formula Auditing tools: Trace Precedents/Dependents, Evaluate Formula, and Error Checking.
30. How would you protect cells with formulas but allow data entry?
Lock formula cells, unlock input cells, then protect the worksheet to prevent changes to formulas
while allowing data input.
