200EXCEL

INTERVIEW
QUESTIONS
Wasim Patwari
Become Job-Ready in Data
Analytics with
Our Paid Mentorship &
Placement Programs
WhatsApp:91- 9607157409
 Beginner
Level
Questions

Basic Excel questions to help readers understand

foundational concepts like cell structure, formulas,
and basic functionalities.

1. What is Microsoft Excel?

Microsoft Excel is a spreadsheet application used for

organizing, analyzing, and visualizing data. It
provides tools for creating calculations, charts, and
data models, making it an essential tool for
professionals in various industries.

2. What is a cell in Excel?

A cell is the smallest unit in an Excel worksheet,

located at the intersection of a row and column. It’s
where you enter data, which can be text, numbers,
or formulas. Each cell is identified by its address,
such as A1.

Analytics Career Connect Wasim Patwari

3. How do you save a file in Excel?

You can save your file using the excel shortcut Ctrl +
S or by navigating to File > Save As. This allows you
to save your work in various formats, including .xlsx,
.xls, or .csv.

4. What is a workbook in Excel?

A workbook is an Excel file that contains one or

more worksheets. It allows you to organize related
data into multiple sheets for easier analysis and
management.

5. How do you insert a new worksheet?

To insert a worksheet, click the + button at the

bottom of the screen or use the shortcut Shift + F11.
This creates a blank worksheet within the same
workbook.

6. What is the Ribbon in Excel?

The Ribbon is the toolbar at the top of Excel that

contains various tabs and commands. It is divided
into sections like Home, Insert, Data, and Formulas,
each offering specific tools for your tasks.

Analytics Career Connect Wasim Patwari

7. How do you use Undo and Redo in Excel?

Undo allows you to reverse your last action (Ctrl + Z),

while Redo re-applies an undone action (Ctrl + Y).
These shortcuts are useful for correcting mistakes or
reapplying changes.

8. What is the shortcut for copying data?

Use Ctrl + C to copy and Ctrl + V to paste data. This is

a quick way to duplicate data or formulas between
cells.

9. What is the formula bar in Excel?

The formula bar is a field at the top of the Excel

window that displays the contents of the selected
cell. It’s especially useful for viewing and editing
formulas.

10. How do you merge cells in Excel?

To merge cells, select the cells you want to combine

and click Merge & Center in the Home tab. This is
often used for creating headings or labels.

Analytics Career Connect Wasim Patwari

11. What is the difference between a column and
a row?

Columns are vertical sections identified by letters (A,

B, C), while rows are horizontal sections identified by
numbers (1, 2, 3). Together, they form the grid of an
Excel worksheet.

12. How do you wrap text in a cell?

To wrap text, select a cell and click Wrap Text in the

Home tab. This adjusts the cell content to fit within
its boundaries without spilling over into adjacent
cells.

13. What are cell references in Excel?

Cell references specify a cell’s location in a formula.

Relative references (e.g., A1) adjust when copied,
absolute references (e.g., $A$1) remain fixed, and
mixed references (e.g., $A1) partially adjust.

14. How do you delete a worksheet?

Right-click the sheet tab and select Delete. Deleting

a sheet permanently removes its data, so it’s
recommended to back up your workbook first.

Analytics Career Connect Wasim Patwari

15. How do you adjust column width?

Drag the boundary of a column header to resize it

manually or double-click the boundary to auto-
adjust based on the longest cell content.

Intermediate
Level
Questions

Intermediate Excel questions to enhance skills in

data manipulation, analysis, and presentation using
functions, Pivot Tables, and formatting.

16. What is a formula in Excel?

A formula is an expression that calculates the value

of a cell. It begins with = and can include operators,
cell references, and functions (e.g., =A1+B1).

17. What is conditional formatting in Excel?

Conditional formatting highlights cells based on

rules you set. For example, you can apply red
shading to cells with values less than 50 using
Home > Conditional Formatting.

Analytics Career Connect Wasim Patwari

18. What is a Pivot Table?

A Pivot Table summarizes and analyzes large

datasets by grouping, filtering, and aggregating
data dynamically. It’s a powerful tool for creating
reports and visual summaries.

19. How do you apply filters in Excel?

To filter data, select your dataset and click Data >

Filter. Dropdown arrows appear, allowing you to
filter rows based on specific criteria.

20. What is the VLOOKUP function?

VLOOKUP searches for a value in the first column of

a table and returns a value from a specified column
in the same row. For example, =VLOOKUP("Apple",
A1:C10, 2, FALSE) looks for "Apple" in column A and
returns the corresponding value from column B.

21. What is the difference between COUNT and

COUNTA?

COUNT counts numeric values, while COUNTA

counts all non-empty cells, including text. For
example, =COUNT(A1:A10) returns the count of
numbers, and =COUNTA(A1:A10) includes text and
numbers.

Analytics Career Connect Wasim Patwari

22. How do you create a chart in Excel?

Select your data, go to Insert > Chart, and choose a

chart type. Charts visually represent data trends and
patterns, making analysis more accessible.

23. What is the CONCATENATE function?

CONCATENATE combines text from multiple cells

into one. For example, =CONCATENATE("Hello", "
World") results in "Hello World."

24. How do you remove duplicates in Excel?

Select your data, then go to Data > Remove

Duplicates. This helps clean datasets by eliminating
redundant entries based on selected columns.

25. What is data validation?

Data validation restricts data entry to specified

types or ranges. For instance, you can limit a cell to
accept only whole numbers between 1 and 100.

Analytics Career Connect Wasim Patwari

 Advanced
Level
Questions

Advanced Excel questions focused on automation,

advanced formulas, and tools like Power Query and
Power Pivot for comprehensive data management.

26. What is Power Query?

Power Query automates data import,

transformation, and cleaning tasks. It’s especially
useful for consolidating data from multiple sources
into one table.

27. What is the XLOOKUP function?

XLOOKUP is an improved lookup function that can

search in any direction and return a value or array.
For example, =XLOOKUP("Apple", A1:A10, B1:B10)
searches for "Apple" in column A and returns the
corresponding value from column B.

28. What is the purpose of macros in Excel?

Analytics Career Connect Wasim Patwari

Macros automate repetitive tasks by recording a
series of steps. They are written in VBA (Visual Basic
for Applications) and can save hours of manual
work.

29. What is the IF function?

The IF function performs logical tests and returns

different values based on the result. For instance,
=IF(A1>10, "Pass", "Fail") returns "Pass" if A1 is greater
than 10 and "Fail" otherwise.

30. How do INDEX and MATCH work together?

INDEX returns the value of a cell at a specified row

and column, while MATCH finds the position of a
value in a range. Combined, they perform advanced
lookups. For example, =INDEX(A1:C10,
MATCH("Apple", A1:A10, 0), 2) finds "Apple" in column
A and returns the corresponding value from column
B.

31. What are array formulas?

Array formulas perform multiple calculations on a

range of cells and return a single result or an array
of results. For example, =SUM(A1:A10*B1:B10)
calculates the sum of products for corresponding
rows in two ranges.
Analytics Career Connect Wasim Patwari
32. What is a slicer in Excel?

A slicer is a visual filtering tool used with Pivot

Tables or Pivot Charts. It allows users to filter data
dynamically by clicking on buttons corresponding
to filter criteria.

33. What is the TEXT function?

The TEXT function converts a number to text in a

specified format. For example, =TEXT(A1,
"MM/DD/YYYY") converts a date into a readable
format like 01/01/2025.

34. What is the ROUND function?

The ROUND function rounds a number to a

specified number of digits. For instance,
=ROUND(12.345, 2) returns 12.35, rounding to two
decimal places.

35. What is the TRIM function?

The TRIM function removes all extra spaces from

text except for single spaces between words. For
example, =TRIM(" Excel Tips ") returns "Excel Tips."

Analytics Career Connect Wasim Patwari

36. How do you use Goal Seek in Excel?

Goal Seek finds the input value needed to achieve a

desired result in a formula. Navigate to Data >
What-If Analysis > Goal Seek, set your target value,
and let Excel calculate the required input.

37. What is Solver in Excel?

Solver is an advanced optimization tool used to find

the best solution for a problem with multiple
constraints. It’s often used for resource allocation,
portfolio optimization, and scheduling.

38. What is the PMT function?

The PMT function calculates loan payments based

on constant interest rates and payment schedules.
For example, =PMT(5%/12, 60, -10000) calculates the
monthly payment for a loan.

39. How do you create a dependent drop-down

list?

Dependent drop-down lists change their options

based on the selection in another list. Use Data
Validation combined with the INDIRECT function to
reference the appropriate range dynamically.

Analytics Career Connect Wasim Patwari

40. What is a dynamic chart in Excel?

A dynamic chart updates automatically when its

source data changes. You can create one using
named ranges or Excel tables, ensuring the chart
adapts to data additions or removals.

41. How do you use Power Pivot in Excel?

Power Pivot enables advanced data modeling and

analysis. It allows users to create relationships
between tables, build complex calculations using
DAX formulas, and handle large datasets efficiently.

42. What is the NETWORKDAYS function?

The NETWORKDAYS function calculates the

number of working days between two dates,
excluding weekends and holidays. For example,
=NETWORKDAYS(A1, A2, Holidays) returns the count
of workdays between A1 and A2.

43. How do you split text into columns?

Use Data > Text to Columns to split text based on

delimiters (e.g., commas, spaces) or fixed widths.
This is useful for separating names, addresses, or
other concatenated data.

Analytics Career Connect Wasim Patwari

44. How do you compare two datasets in Excel?

You can use conditional formatting to highlight

matching or non-matching values between
datasets. Alternatively, use formulas like =IF(A1=B1,
"Match", "No Match").

45. How do you calculate CAGR (Compound

Annual Growth Rate) in Excel?

CAGR is calculated using the formula =(Ending

Value / Starting Value)^(1/Number of Years) - 1. For
example, =(20000 / 10000)^(1/5) - 1 calculates a
14.87% CAGR over five years.

46. What is the UNIQUE function?

The UNIQUE function, available in Excel 365, extracts

unique values from a range. For example,
=UNIQUE(A1:A10) generates a list of unique items
from the specified range.

47. How do you calculate a weighted average in

Excel?

Use the SUMPRODUCT function to calculate

weighted averages. For example,
=SUMPRODUCT(A1:A5, B1:B5)/SUM(B1:B5) computes
the average when weights are in column B.

Analytics Career Connect Wasim Patwari

48. How do you normalize data in Excel?

To normalize data, subtract the mean and divide by

the standard deviation: =(Value - MEAN) / STDEV.
This scales the data for comparison across datasets.

49. How do you highlight duplicates in Excel?

Use Conditional Formatting > Highlight Cells Rules >

Duplicate Values to identify duplicate entries in a
dataset.

50. How do you use the DATEDIF function?

DATEDIF calculates the difference between two

dates in years, months, or days. For example,
=DATEDIF(A1, B1, "Y") returns the number of years
between two dates.

Data Analyst-
Specific
Questions
Excel questions tailored for data analysts to handle
large datasets, analyze trends, and create insightful
reports.

Analytics Career Connect Wasim Patwari

51. What is the FILTER function?

The FILTER function dynamically returns rows or

columns based on criteria. For example,
=FILTER(A1:A10, B1:B10="Yes") filters rows where
column B equals "Yes."

52. What is the SORT function?

The SORT function sorts data dynamically based on

specified criteria. For example, =SORT(A1:A10, 1, -1)
sorts the range A1:A10 in descending order.

53. How do you use Excel for regression analysis?

Excel’s Data Analysis ToolPak offers regression tools

to calculate coefficients, R², and statistical
significance for predictive modeling.

54. What is XLOOKUP?

XLOOKUP replaces VLOOKUP and HLOOKUP,

allowing flexible searches in any direction. For
example, =XLOOKUP("Apple", A1:A10, B1:B10) returns
the value associated with "Apple" in column B.

55. How do you automate Excel reports?

You can automate reports using macros (VBA) or

Analytics Career Connect Wasim Patwari

Power Query for data refreshes. Use dynamic ranges
and Pivot Tables to handle recurring updates.

56. How do you connect Excel to external data

sources?

Use Data > Get Data to import data from databases,

web sources, or APIs. This feature allows integration
with tools like SQL Server and Power BI.

57. How do you use Excel with Power BI?

Excel integrates seamlessly with Power BI for

advanced data visualization. Publish Excel files
directly to Power BI or connect to datasets using
Analyze in Excel.

58. How do you use Excel 365 Copilot for

automation?

Excel 365 Copilot automates tasks, generates

insights, and creates formulas based on natural
language prompts. For instance, asking “Summarize
quarterly sales data” generates an immediate
summary.

59. What is the LET function?

The LET function allows you to assign names to

Analytics Career Connect Wasim Patwari

intermediate calculations within a formula,
improving readability and performance. For
example, =LET(x, A1+A2, x^2) calculates the square of
the sum of A1 and A2.

60. What is the TEXTAFTER function?

TEXTAFTER extracts text that appears after a

specified delimiter. For example, =TEXTAFTER("Excel
365", " ") returns "365."

61. What is the TEXTBEFORE function?

TEXTBEFORE extracts text that appears before a

specific delimiter in a string. For example,
=TEXTBEFORE("Excel 365", " ") returns "Excel."

62. How do you use the WRAPCOLS function?

The WRAPCOLS function organizes a single row or

column of data into multiple columns with a
specified number of entries per column. For
example, =WRAPCOLS(A1:A10, 3) groups the range
A1:A10 into columns of three values each.

63. What is the WRAPROWS function?

The WRAPROWS function reshapes a single column

or row of data into multiple rows with a fixed or row

Analytics Career Connect Wasim Patwari

of data into multiple rows with a fixed number of
entries per row. For example, =WRAPROWS(A1:A12,
4) organizes the range A1:A12 into three rows of four
values.

64. How do you create dependent drop-down lists

in Excel?

Use the INDIRECT function to reference named

ranges dynamically. For example, a drop-down list
for states changes based on the country selected in
another drop-down list.

65. How do you calculate ROI (Return on

Investment) in Excel?

ROI is calculated using the formula (Revenue - Cost)

/ Cost. For example, =(10000 - 5000) / 5000 results in
an ROI of 100%.

66. How do you split names into first and last

names?

Use Text to Columns or formulas like =LEFT(A1,

FIND(" ", A1)-1) for the first name and =RIGHT(A1,
LEN(A1)-FIND(" ", A1)) for the last name.

Analytics Career Connect Wasim Patwari

67. How do you calculate the rank of a value in
Excel?

Use the RANK function, such as =RANK(A1, A1:A10) to

find the position of a value within a range.

68. What is the MAKEARRAY function in Excel?

MAKEARRAY creates an array based on a custom

formula. For example, =MAKEARRAY(3, 3,
LAMBDA(row, col, row*col)) generates a 3x3 array
with the product of row and column numbers.

69. How do you handle circular references in

Excel?

Enable iterative calculations in File > Options >

Formulas. Alternatively, redesign formulas to avoid
self-references.

70. How do you calculate moving averages?

Use AVERAGE with dynamic ranges or the Analysis

ToolPak for a built-in Moving Average feature. For
example, =AVERAGE(A1:A3) calculates a 3-period
moving average.

Analytics Career Connect Wasim Patwari

 Excel 365
Copilot-Specific
Questions

Questions on Excel Copilot's AI-powered

functionalities for automation and analysis.

71. How does Copilot simplify data cleaning?

Copilot can suggest and apply data cleaning

methods like removing duplicates, filling missing
values, or reformatting text. For example, you can
ask, “Standardize product names in this dataset.”

72. How does Copilot enhance data analysis?

Copilot provides insights and visualizations based

on natural language queries like “Show the sales
trend for the last 6 months” or “Highlight regions
with the highest revenue.”

73. How does Copilot assist with complex

formulas?

Copilot generates complex formulas based on

Analytics Career Connect Wasim Patwari

descriptions. For example, you can type “Calculate
profit margin” and get the formula =(Revenue -
Cost) / Revenue.

74. How does Copilot improve report generation?

You can ask Copilot to “Create a monthly sales

report” or “Summarize this dataset,” and it
generates summaries, charts, and insights
automatically.

75. How does Copilot integrate with Power BI?

Copilot works with Power BI to connect Excel data,

generate interactive dashboards, and export
insights into Excel for further analysis.

Power BI
Integration
with Excel

How Excel and Power BI work together to

streamline data analytics and visualization.

76. How do you connect Excel to Power BI?

Analytics Career Connect Wasim Patwari

Save your Excel file to OneDrive or SharePoint, then
import it into Power BI Desktop or Power BI Service
using Get Data.

77. How do you use Analyze in Excel with Power

BI?

Analyze in Excel allows you to connect directly to

Power BI datasets and analyze data using Excel
Pivot Tables. Download the connection file from
Power BI Service and open it in Excel.

78. How do you export Power BI visuals to Excel?

Click on a visualization in Power BI, then select

Export Data to download the data as an Excel or
CSV file for further analysis.

79. How do you refresh Excel data connected to

Power BI?
Use Data > Refresh All to sync Excel files with live
Power BI datasets. Ensure the dataset is stored in
the cloud for automatic updates.

80. How do you combine multiple Excel files into

Power BI?

Use Power BI Desktop’s Get Data > Folder option to

load all Excel files in a folder. Power Query

Analytics Career Connect Wasim Patwari

consolidates data from these files based on a
common structure.

81. How do you use Power Query in Excel and

Power BI?

Power Query is used to clean and transform data

before analysis. In Excel, it’s found under Data > Get
& Transform; in Power BI, it’s part of the data
preparation process.

82. How do you schedule data refreshes for

Power BI-integrated Excel files?

Ensure Excel files are stored in OneDrive or

SharePoint. In Power BI, go to Dataset > Schedule
Refresh to set up periodic updates.

Scenario-Based
Excel
Questions

Real-world questions focusing on applying Excel to

solve practical problems across industries.

Analytics Career Connect Wasim Patwari

83. How do you use Excel to create a project
timeline?

Create a Gantt chart by organizing tasks, start dates,

and end dates. Use conditional formatting to
visualize progress and highlight overdue tasks.

84. How do you track expenses by category?

Use a Pivot Table to summarize expenses by

category. Add slicers for interactivity and visualize
spending trends using bar or pie charts.

85. How do you calculate employee turnover

rates in Excel?

Divide the number of employees who left during a

period by the average number of employees.
Formula: (Leavers / Average Employees) * 100.

86. How do you clean messy datasets in Excel?

Use TRIM to remove extra spaces, CLEAN for non-

printable characters, and Text to Columns for
splitting concatenated data.

87. How do you reconcile financial data?

Use VLOOKUP, XLOOKUP, or INDEX-MATCH to

Analytics Career Connect Wasim Patwari

compare two datasets for discrepancies. Highlight
mismatches using conditional formatting.

88. How do you perform scenario analysis in

Excel?

Use What-If Analysis > Scenario Manager to evaluate

multiple scenarios by varying input values, such as
sales growth or cost reductions.

89. How do you calculate the weighted average in

Excel?

Use the SUMPRODUCT function. For example,

=SUMPRODUCT(Scores, Weights) / SUM(Weights)
calculates a weighted test score.

90. How do you identify outliers in a dataset?

Use statistical formulas like =QUARTILE and

calculate the interquartile range (IQR). Highlight
values outside the range with conditional
formatting.

Important
Excel Interview
Questions
Analytics Career Connect Wasim Patwari
Important questions that touch on different and
essential Excel functionalities.

91. What is the purpose of dynamic named ranges

in Excel?

Dynamic named ranges automatically adjust their

size when new data is added. Use the OFFSET
function to define them, such as =OFFSET(A1, 0, 0,
COUNTA(A:A)).

92. How do you create a dependent drop-down

list?

Use Data Validation > List and the INDIRECT

function to create a drop-down list where options
depend on another cell's selection.

93. How do you visualize task dependencies in

Excel?

Use a Gantt chart to show relationships between

tasks. Add arrows to indicate dependencies and use
conditional formatting for critical paths.

94. What is the RANK function, and how is it

used?

The RANK function returns the rank of a number

Analytics Career Connect Wasim Patwari

within a dataset. For example, =RANK(A1, A1:A10)
ranks the value in A1 among the range A1:A10.

95. How do you compare year-over-year (YoY)

performance in Excel?

Calculate the percentage change using =((Current

Year - Previous Year) / Previous Year) * 100. Use a
Pivot Table to summarize YoY changes by category.

96. How do you normalize data in Excel?

Subtract the mean and divide by the standard

deviation using =(Value - MEAN) / STDEV. This
standardizes data for comparison across variables.

97. How do you protect sensitive data in Excel?

Encrypt the workbook using File > Info > Protect

Workbook. Lock specific cells with Format Cells >
Protection and enable sheet protection.

98. How do you create a performance dashboard

in Excel?

Combine Pivot Tables, slicers, and interactive charts

to track KPIs. Use Excel tables to enable automatic
updates as new data is added.

Analytics Career Connect Wasim Patwari

99. What is the difference between absolute and
relative cell references?

Absolute references ($A$1) remain constant when

copied, while relative references (A1) adjust relative
to their new location.

100. How do you use Excel to calculate

depreciation?

Use SLN for straight-line depreciation (=SLN(Cost,

Salvage, Life)) or DB for declining balance
depreciation.

101. How do you use Solver for optimization

problems?

Solver finds the optimal solution by adjusting input

values within defined constraints. Use it for resource
allocation or cost minimization problems.

102. How do you calculate the correlation

between two datasets?

Use the CORREL function to calculate the

correlation coefficient. For example,
=CORREL(A1:A10, B1:B10) measures the strength of
the relationship between two variables.

Analytics Career Connect Wasim Patwari

103. How do you export Excel charts into
PowerPoint?

Copy the chart in Excel (Ctrl + C) and paste it into

PowerPoint (Ctrl + V). Use the Paste Special option
to link the chart for real-time updates.

104. How do you create a Histogram in Excel?

Use the Data Analysis ToolPak or Insert > Chart >

Histogram to group data into intervals and display
the frequency distribution.

Excel 365
Advanced Excel
Interview Questions

Questions that focus on advanced Excel 365

features, including AI-driven Copilot functionalities.

105. How does Excel 365 Copilot help with

insights?

Excel Copilot uses AI to generate summaries,

identify trends, and create visualizations based on
natural language queries. For example, ask “What

Analytics Career Connect Wasim Patwari

are the sales trends for the last 6 months?” for
instant analysis.

106. How do you automate repetitive tasks with

Copilot?

Use Copilot to perform common tasks like cleaning

data, formatting tables, or creating formulas. For
example, prompt it to “Remove duplicates and
highlight outliers in this dataset.”

107. How do you use the IMAGE function in Excel

365?

The IMAGE function embeds images directly into

cells using a URL. For example,
=IMAGE("https://example.com/logo.jpg") displays
the image in the cell.

108. How do you analyze multiple datasets using

Copilot?

Copilot can consolidate data from multiple sheets or

files, identify patterns, and suggest charts or Pivot
Tables for better visualization.

109. How does Excel 365 Copilot simplify report

generation?

Analytics Career Connect Wasim Patwari

With Copilot, you can generate automated reports
by providing prompts like “Create a quarterly
performance report” or “Summarize sales by
region.”

110. How do you use the UNIQUE function in Excel

365?

The UNIQUE function extracts unique values from a

dataset dynamically. For example, =UNIQUE(A1:A10)
generates a list of distinct items from the range
A1:A10.

111. How do you use the SEQUENCE function in

Excel 365?

The SEQUENCE function generates a numeric array

in rows and columns. For example, =SEQUENCE(5, 2)
creates a 5x2 array with sequential numbers starting
from 1.

112. How do you use the SORTBY function?

SORTBY sorts a dataset based on one or more

criteria. For example, =SORTBY(A1:A10, B1:B10, 1) sorts
A1:A10 based on the ascending order of B1:B10.

113. How do you use the RANDARRAY function?

Analytics Career Connect Wasim Patwari

RANDARRAY generates an array of random
numbers within a specified range. For example,
=RANDARRAY(5, 1, 1, 100, TRUE) creates 5 random
integers between 1 and 100.

114. How do you use the TEXTJOIN function?

TEXTJOIN combines text from multiple ranges or

arrays with a specified delimiter. For example,
=TEXTJOIN(", ", TRUE, A1:A5) joins text in A1 through
A5, separated by commas.

115. How do you use the TOROW function?

The TOROW function converts a range of cells into a

single row. For example, =TOROW(A1:C3)
consolidates the range A1:C3 into a horizontal array.

116. How do you use the TOCOL function?

The TOCOL function converts a range of cells into a

single column. For example, =TOCOL(A1:C3)
consolidates the range A1:C3 into a vertical array.

117. How do you create interactive slicers for Pivot

Tables?

Interactive slicers are created by selecting the Pivot

Table and navigating to Insert > Slicer. Slicers allow

Analytics Career Connect Wasim Patwari

filtering data with clickable buttons.

118. How do you create a Gantt chart in Excel?

Create a Gantt chart by using bar charts to

represent task durations. Adjust bar start and end
dates using conditional formatting or chart
customization.

119. How do you create a cumulative sum in

Excel?

Use a running total formula like =SUM($A$1:A1) and

drag it down to calculate the cumulative total for
each row.

120. How do you use the SUBSTITUTE function?

SUBSTITUTE replaces occurrences of a specified text

within a string. For example, =SUBSTITUTE(A1, "old",
"new") replaces all instances of "old" with "new" in
cell A1.

121. How do you calculate working days between

two dates?

Use the NETWORKDAYS function to calculate the

number of working days between two dates. For
example, =NETWORKDAYS(A1, B1) excludes

Analytics Career Connect Wasim Patwari

weekends and optionally holidays.

122. How do you calculate a percentage

difference between two values?

Use the formula =((New Value - Old Value) / Old

Value) * 100 to calculate the percentage change.

123. How do you identify duplicates in Excel?

Use conditional formatting with the rule Highlight

Cells Rules > Duplicate Values to easily spot
duplicate entries.

124. How do you transpose data from rows to

columns?

Use the TRANSPOSE function or Paste Special >

Transpose to switch rows to columns or vice versa.

125. How do you use the LAMBDA function in

Excel 365?

LAMBDA creates custom reusable functions. For

example, =LAMBDA(x, x^2)(5) calculates the square
of 5 and returns 25.

126. How do you apply conditional formatting

based on another cell’s value?

Analytics Career Connect Wasim Patwari

Use a custom formula in conditional formatting. For
example, apply =A1>100 to format cells where the
value is greater than 100.

127. How do you group data in a Pivot Table?

Right-click a value in the Pivot Table and select

Group. Grouping can be used to organize dates into
months, quarters, or years.

128. How do you remove extra spaces from text in

Excel?

Use the TRIM function to remove unnecessary

spaces, leaving only single spaces between words.

129. How do you use the DAX language in Power

Pivot?

DAX (Data Analysis Expressions) is used in Power

Pivot to create calculated columns and measures.
For example, =SUM(Sales[Amount]) calculates the
total sales.

130. How do you use the AVERAGEIF function?

AVERAGEIF calculates the average of a range based

on a condition. For example, =AVERAGEIF(A1:A10,
">50") returns the average of numbers greater than
50.
Analytics Career Connect Wasim Patwari
131. How do you highlight top 10 values in Excel?

Use conditional formatting with the rule

Top/Bottom Rules > Top 10 Items to automatically
highlight the highest values in a dataset.

132. How do you automate repetitive tasks in

Excel?

Automate tasks using VBA macros or Excel’s built-in

Power Automate tools to streamline recurring
workflows.

133. How do you calculate the mode of a dataset

in Excel?

Use the MODE.SNGL function to find the most

frequently occurring number in a dataset.

134. How do you create an amortization schedule

in Excel?

Use the PMT, IPMT, and PPMT functions to calculate

loan payments, interest, and principal for each
period.

135. How do you use the CONCAT function?

The CONCAT function combines text from multiple

Analytics Career Connect Wasim Patwari

ranges into one string. For example,
=CONCAT(A1:A5) concatenates all values in the
range A1:A5.

136. How do you use Excel to manage inventory?

Create an inventory tracker using tables, formulas

like SUMIF for totals, and conditional formatting to
flag low stock levels.

137. How do you find the nth largest value in a

dataset?

Use the LARGE function, such as =LARGE(A1:A10, 3),

to find the 3rd largest value in a range.

138. How do you calculate Net Present Value

(NPV) in Excel?

Use the NPV function to calculate the present value

of cash flows based on a discount rate. For example,
=NPV(10%, A1:A5) calculates NPV with a 10% rate.

139. How do you create an interactive data

summary in Excel?

Combine Pivot Tables with slicers and charts to

create a dynamic dashboard for interactive data
exploration.

Analytics Career Connect Wasim Patwari

140. How do you use the OFFSET function for
dynamic ranges?

OFFSET defines a range based on a reference point.

For example, =OFFSET(A1, 0, 0, COUNTA(A:A))
adjusts dynamically as new data is added.

141. How do you calculate the median in Excel?

Use the MEDIAN function to find the middle value

of a dataset. For example, =MEDIAN(A1:A10) returns
the median value in A1:A10.

142. How do you analyze time-series data in

Excel?

Use line charts with trendlines or the

FORECAST.LINEAR function to identify patterns and
make predictions.

143. How do you create a formula to compare two

lists?

Use =IF(COUNTIF(List1, List2)>0, "Match", "No

Match") to compare items in two lists.

144. How do you apply a custom number format?

Go to Format Cells > Custom and enter a format

Analytics Career Connect Wasim Patwari

code like "Total: "$#,##0 to display numbers with
custom text or styles.

145. How do you use the HYPERLINK function?

The HYPERLINK function creates clickable links. For

example, =HYPERLINK("https://example.com", "Visit
Site") generates a link labeled "Visit Site."

146. How do you calculate the frequency of data

in Excel?

Use the FREQUENCY function to calculate how

often values occur within intervals. For example,
=FREQUENCY(A1:A10, B1:B5) counts occurrences in
specified bins.

147. How do you use the ROUNDDOWN function?

ROUNDDOWN always rounds a number down to

the specified number of digits. For example,
=ROUNDDOWN(12.345, 2) returns 12.34.

148. How do you use the ROUNDUP function?

ROUNDUP always rounds a number up. For

example, =ROUNDUP(12.345, 2) returns 12.35.

Analytics Career Connect Wasim Patwari

149. How do you highlight weekends in a date
range?

Use conditional formatting with a formula like

=OR(WEEKDAY(A1)=1, WEEKDAY(A1)=7) to identify
weekends.

150. How do you analyze customer churn in

Excel?

Calculate churn rates using =COUNTIF(Status,

"Lost")/COUNTA(Status) and visualize trends using
line or bar charts.

151. How do you calculate payback period in

Excel?

Calculate cumulative cash flows and identify the

period where the cumulative value becomes
positive.

152. How do you use data bars in conditional

formatting?

Apply Conditional Formatting > Data Bars to

visualize numeric values as horizontal bars.

153. How do you find blank cells in a range?

Analytics Career Connect Wasim Patwari

Use the ISBLANK function combined with
conditional formatting or a filter to identify empty
cells.

154. How do you calculate growth rates in Excel?

Use the formula =((Ending Value / Starting

Value)^(1/Years)) - 1 to calculate Compound Annual
Growth Rate (CAGR).

155. How do you use Power Automate with Excel?

Power Automate integrates Excel with other

applications to automate workflows like data
updates, notifications, or report generation.

156. How do you use a scatter plot in Excel?

Insert a scatter plot (Insert > Chart > Scatter) to

visualize relationships between two variables, such
as sales and advertising spend.

157. How do you calculate a breakeven point in

Excel?

Use the formula =Fixed Costs / (Selling Price -

Variable Costs) to determine the sales quantity
needed to cover costs.

Analytics Career Connect Wasim Patwari

158. How do you create a running balance in
Excel?

Use a formula like =Previous Balance + Inflows -

Outflows to calculate balances row by row.

159. How do you find cells with errors in Excel?

Use the ISERROR function combined with

conditional formatting to highlight cells with errors.

160. How do you automate email notifications

with Excel?

Use VBA to send emails from Excel based on cell

data, or integrate with Power Automate for more
advanced automation.

161. How do you extract data from a web page

into Excel?

Use Data > Get Data > From Web to scrape data
directly from a web page into an Excel sheet.

162. How do you consolidate data from multiple

workbooks?

Use Power Query’s Get Data > Combine Queries

feature to merge data from multiple Excel files.
Analytics Career Connect Wasim Patwari
163. How do you validate data entry in Excel?

Use Data > Data Validation to restrict entries to

specific formats, such as dates, numbers, or text
length.

164. How do you forecast data trends in Excel?

Use the FORECAST.ETS function for seasonal

forecasting or create a trendline in a chart to project
future values.

165. How do you summarize large datasets

quickly?

Use Pivot Tables to group and analyze data, and

Power Query to clean and reshape large datasets
before summarization.

Marketing-
Specific Excel
Questions

Excel questions tailored to meet the needs of

marketing professionals for campaign analysis,
budgeting, and customer insights.

Analytics Career Connect Wasim Patwari

166. How do you calculate ROI for a marketing
campaign in Excel?

ROI is calculated using the formula (Revenue - Cost)

/ Cost. For example, if a campaign cost $5,000 and
generated $15,000 in revenue, =(15000 - 5000) /
5000 gives an ROI of 200%.

167. How do you track marketing campaign

performance in Excel?

Create a table with columns for metrics like

impressions, clicks, and conversions. Use Pivot
Tables to summarize and analyze the data, and
charts to visualize trends.

168. How do you calculate customer acquisition

cost (CAC)?

Use the formula Total Marketing Expenses /

Number of New Customers. For example, if
marketing expenses are $50,000 and 1,000 new
customers are acquired, CAC is $50.

169. How do you analyze customer segmentation

in Excel?

Group customers by demographics, purchasing

behavior, or engagement levels using Pivot Tables.

Analytics Career Connect Wasim Patwari

Visualize the segments with pie or bar charts.

170. How do you calculate the conversion rate in

Excel?

Conversion rate is calculated as (Conversions / Total

Visitors) * 100. For instance, if there are 500 visitors
and 50 conversions, the conversion rate is (50 / 500)
* 100 = 10%.

Finance-
Specific Excel
Questions

Excel questions for financial analysts and

accountants to handle financial models, forecasting,
and budgeting.

171. How do you create a financial model in Excel?

Build separate sheets for inputs, calculations, and

outputs. Use formulas like NPV, IRR, and PMT to
analyze cash flows, valuations, and debt schedules.

172. How do you perform a sensitivity analysis in

Excel?

Analytics Career Connect Wasim Patwari

Use Data > What-If Analysis > Data Table to vary
input values and see their impact on key financial
metrics, such as profit or ROI.

173. How do you calculate loan payments in

Excel?

Use the PMT function. For example, =PMT(5%/12, 60,

-10000) calculates the monthly payment for a loan
of $10,000 with a 5% annual interest rate over 60
months.

174. How do you calculate EBITDA in Excel?

EBITDA is calculated as Net Income + Interest +

Taxes + Depreciation + Amortization. Create a
structured table to calculate and track these
components.

175. How do you track and reconcile financial

transactions in Excel?

Use VLOOKUP, XLOOKUP, or INDEX-MATCH to

match transaction records between two sheets and
highlight discrepancies with conditional formatting.

Analytics Career Connect Wasim Patwari

 HR-Specific
Excel Interview
Questions

Excel questions for HR professionals to handle

employee data, payroll, and performance tracking.

176. How do you calculate employee turnover

rates in Excel?

Turnover rate is calculated as (Number of Leavers /

Average Number of Employees) * 100. For example,
if 10 employees leave out of 200, the rate is (10 / 200)
* 100 = 5%.

177. How do you track employee attendance in

Excel?

Create a table with columns for dates and

employees. Use COUNTIF to count attendance
status like “Present” or “Absent” for each employee.

178. How do you automate payroll calculations in

Excel?

Use formulas like Gross Pay - Deductions = Net Pay

Analytics Career Connect Wasim Patwari

181. How do you create a project schedule in
Excel?

Use a Gantt chart with task names, start dates, and

durations. Use conditional formatting to highlight
task progress and overdue tasks.

182. How do you calculate project costs in Excel?

Create a table for tasks, estimated costs, and actual

costs. Use SUM to calculate totals and =(Actual -
Estimated) / Estimated * 100 to calculate cost
variance percentages.

183. How do you use Excel for resource

allocation?

Create a resource matrix with tasks as rows and

team members as columns. Use formulas to
calculate workloads and highlight over-allocated
resources.

184. How do you track project progress using

Excel?

Use Conditional Formatting to create progress bars

in cells. Calculate completion percentages with =
(Completed Tasks / Total Tasks) * 100.

Analytics Career Connect Wasim Patwari

and IF functions to calculate tax brackets. For
example, =IF(Salary>50000, Salary*0.3, Salary*0.2)
applies tiered tax rates.

179. How do you create a training tracker in

Excel?

Use conditional formatting to highlight completed

training sessions. Add a progress tracker column
with a formula like =(Completed Modules / Total
Modules) * 100.

180. How do you manage recruitment data in

Excel?

Use Excel tables to organize candidate details. Use

filters to sort candidates by status, and charts to
visualize metrics like time-to-hire and source
effectiveness.

Project Management
Specific Excel
Interview Questions

Excel questions for project managers to handle

schedules, budgets, and progress tracking.

Analytics Career Connect Wasim Patwari

185. How do you identify project risks in Excel?

Create a risk assessment matrix with probability and

impact scores. Use conditional formatting to color-
code high, medium, and low-risk items.

Chartered Accountant (CA)

and Company Secretary (CS)
Excel Interview Questions

Excel questions tailored for professionals in

accounting and corporate compliance roles.

186. How do you audit financial statements using

Excel?

Use Pivot Tables to summarize large datasets and

VLOOKUP or XLOOKUP to reconcile ledger entries.
Highlight anomalies using conditional formatting.

187. How do you prepare tax computation sheets

in Excel?

Use logical functions like IF to calculate applicable

tax rates. For example, =IF(Income<=500000,
Income*0.05, Income*0.1) applies tiered tax rates.

Analytics Career Connect Wasim Patwari

188. How do you handle depreciation schedules in
Excel?

Use the SLN and DB functions to calculate straight-

line and declining balance depreciation. Create
tables to track yearly depreciation values.

189. How do you calculate dividend payouts in

Excel?

Use the formula Net Profit * Dividend Payout Ratio.

For example, if net profit is $1,000,000 and the
payout ratio is 30%, the dividend is $300,000.

190. How do you track compliance deadlines in

Excel?

Create a table with deadlines and statuses. Use

conditional formatting to flag overdue tasks and
reminders.

Excel Tricks and

Shortcuts Interview
Questions

Time-saving tips and tricks to improve efficiency

and productivity in Excel.
Analytics Career Connect Wasim Patwari
191. How do you use Excel shortcuts to speed up
workflows?

Memorize common shortcuts like Ctrl + C (copy),

Ctrl + V (paste), and Alt + H + O + I (auto-fit column
width) for faster navigation and data entry.

192. How do you use Flash Fill in Excel?

Flash Fill automatically fills patterns based on the

first example. Use Ctrl + E to quickly split or combine
text without complex formulas.

193. How do you create drop-down lists in Excel?

Use Data Validation > List to create a dropdown.

Enter the items manually or reference a cell range
for dynamic lists.

194. How do you use Quick Analysis in Excel?

Select a range and click the Quick Analysis icon to

access formatting, charts, totals, and more. This
provides instant visualizations and summaries.

195. How do you split text into columns without

formulas?

Use Data > Text to Columns and select a delimiter (e

.g., space or comma) to split text into separate cells.
Analytics Career Connect Wasim Patwari
196. How do you navigate large datasets
efficiently?

Use Ctrl + Arrow Keys to jump to the edges of data

ranges and Ctrl + Shift + Arrow Keys to select large
ranges.

197. How do you use Format Painter in Excel?

Select a cell with the desired format, click Format

Painter, and apply it to other cells to replicate
formatting.

198. How do you create dynamic named ranges?

Use the OFFSET function to create ranges that

adjust automatically as data grows. For example,
=OFFSET(A1, 0, 0, COUNTA(A:A)).

199. How do you quickly sum data in Excel?

Use the Alt + = shortcut to automatically insert a

SUM formula for a selected range.

200. How do you lock cells in Excel?

Select cells, go to Format Cells > Protection, and

check Locked. Protect the sheet to enforce cell
locking.

Analytics Career Connect Wasim Patwari

 About Analytics Career Connect

In today’s fast-paced digital world, thousands of students from Tier 2, 3, and

4 cities struggle to access affordable and practical tech education. Most
platforms are either too expensive or focus only on theory, leaving learners
unprepared for real-world jobs.

Analytics Career Connect is solving this problem by providing a complete,

job-oriented learning ecosystem that includes:

Placement Program: Have 50% knowledge but not job-ready? In just 3

months, we help you gain the skills and confidence to land your
dream job.

Mentorship Program – Start your career in the data domain without

spending lakhs on expensive courses. Our guided mentorship (under
₹7,000) takes you from beginner to job-ready.

Job Assistance Program – Get support with resume building, LinkedIn

optimization, mock interviews, and recruiter connections to land your
dream job.

We are part of a broader ecosystem that includes:

Analytics Career Connect – Focused on affordable, job-ready
education.

Our mission is to make tech education accessible, practical, and outcome-

driven — especially for students in non-metro areas.

If you have any questions or need guidance, feel free to contact us at:
+91 9607157409