1What is the difference between VLOOKUP and
INDEX-MATCH?
INDEX-MATCH is more flexible since it can look in any
direction and doesn't break when columns are inserted, while
VLOOKUP only searches left-to-right and references columns
by a fixed position number.
2What is a pivot table used for?
It summarises and reorganises large datasets into
cross-tabulated views, letting you group, filter, and
aggregate quickly without writing formulas.
3What's the difference between relative and absolute cell
references?
Relative references shift automatically when copied to other
cells; absolute references, marked with $, stay fixed on the
row or column you lock.
4What does the XLOOKUP function do?
It's the modern replacement for VLOOKUP/HLOOKUP, searching
in any direction, returning exact matches by default, and
not requiring the lookup column to be first.
5How do you remove duplicate values in Excel?
Select the range and use Data > Remove Duplicates, or
flag duplicates first with COUNTIF so you can review them
before deleting anything.
6What is conditional formatting?
A way to visually highlight cells that meet specific rules,
such as values above a threshold or duplicate entries,
without altering the underlying data.
7What's the difference between COUNTIF and COUNTIFS?
COUNTIF checks a single condition on one range; COUNTIFS
checks multiple conditions across multiple ranges at the
same time.
8What is Power Query used for?
It's Excel's built-in ETL tool for connecting to, cleaning,
and reshaping data from multiple sources before loading it
into a worksheet or data model.
9How do you create a dynamic named range?
Using OFFSET or INDEX combined with COUNTA, or more simply
by defining the range as an Excel Table, which expands
automatically as data is added.
10What's the difference between SUMIF and SUMPRODUCT?
SUMIF sums values matching a single condition; SUMPRODUCT
can handle multiple conditions and array-based calculations
in one formula.
11How would you handle circular references in a
formula?
Identify the cell causing the loop using Excel's error
tracing tools, then redesign the formula logic, only
enabling iterative calculation if the loop is genuinely
intentional.
12What are array formulas?
Formulas that perform multiple calculations across one or
more items in an array and return either a single result or
several results across a range.
13Explain data validation in Excel.
It restricts what can be entered into a cell, like a
dropdown list or a bounded number range, to reduce input
errors at the source.
14What's the difference between a chart and a pivot
chart?
A pivot chart is directly linked to a pivot table and
updates automatically when the table's filters or data
change, unlike a static chart.
15How do you consolidate data from multiple sheets?
Using Data > Consolidate, Power Query's append or merge
features, or 3D formulas that reference the same cell across
multiple sheet tabs.
16What is the IFERROR function used for?
It catches formula errors and replaces them with a custom
value or message instead of displaying #N/A or #DIV/0!.
17Explain the TEXT function.
It converts a number or date into a formatted string, such
as displaying a date as "March 2026" instead of its
underlying serial number.
18What's the difference between .xlsx and .csv?
.xlsx stores formatting, formulas, and multiple sheets in a
structured container; .csv is plain text with just
comma-separated values and no formatting at all.
19How do you protect specific cells while keeping others
editable?
Unlock the cells you want editable through Format Cells
first, then apply sheet protection, which by default locks
every cell unless explicitly unlocked.
20What is a macro and when would you use one?
A macro is a recorded or scripted sequence of actions,
usually via VBA, that automates repetitive tasks like
formatting reports or refreshing data pulls.
SQLQ21–Q40
21What's the difference between WHERE and HAVING?
WHERE filters rows before aggregation happens; HAVING
filters groups after a GROUP BY or aggregate function has
already been applied.
22What is a JOIN, and what are the main types?
A JOIN combines rows from two or more tables based on a
related column. The main types are INNER, LEFT, RIGHT, and
FULL OUTER JOIN.
23What's the difference between a primary key and a foreign
key?
A primary key uniquely identifies each row in its own table;
a foreign key references a primary key in another table to
enforce a relationship between them.
24Explain the difference between UNION and UNION ALL.
UNION removes duplicate rows from the combined result set;
UNION ALL keeps every row, including duplicates, and runs
faster since it skips the de-duplication step.
25What are window functions used for?
They perform calculations across a set of rows related to
the current row, like running totals or rankings, without
collapsing the result into groups.
26What's the difference between RANK, DENSE_RANK, and
ROW_NUMBER?
RANK skips numbers after ties, DENSE_RANK doesn't skip after
ties, and ROW_NUMBER assigns a unique sequential number
regardless of ties.
27What is a CTE (Common Table Expression)?
A temporary, named result set defined with WITH that makes
complex queries more readable and can be referenced multiple
times within the same query.
28Explain normalization and why it matters.
It's the process of structuring tables to reduce data
redundancy and improve integrity, typically organised
through normal forms like 1NF, 2NF, and 3NF.
29What is an index, and why use one?
An index is a data structure that speeds up row lookups on a
column, at the cost of slightly slower writes and extra
storage space.
30What's the difference between DELETE, TRUNCATE, and
DROP?
DELETE removes specific rows and can be rolled back;
TRUNCATE removes all rows quickly and resets identity
counters; DROP removes the entire table structure.
31What is a subquery?
A query nested inside another query, used to filter,
calculate, or supply values that the outer query depends on.
32Explain GROUP BY with an example use case.
GROUP BY aggregates rows that share the same value in
specified columns, for example calculating total sales for
each region separately.
33What's the difference between a clustered and
non-clustered index?
A clustered index physically sorts and stores table rows in
that order, and a table can have only one; a non-clustered
index is a separate structure that points back to the rows.
34How do you find duplicate records in a table?
Group by the columns that should be unique and use HAVING
COUNT(*) > 1 to isolate the rows that repeat.
35What is a self-join, and when would you use it?
A table joined to itself, useful for comparing rows within
the same table, like finding employees who report to the
same manager.
36Explain the difference between INNER JOIN and LEFT
JOIN.
INNER JOIN returns only matching rows from both tables; LEFT
JOIN returns every row from the left table plus matches from
the right, filling unmatched columns with NULL.
37What are ACID properties in a database?
Atomicity, Consistency, Isolation, and Durability, the
guarantees that ensure transactions process reliably even
during errors or system failures.
38How would you optimize a slow-running query?
Check the execution plan, add appropriate indexes, avoid
SELECT *, filter as early as possible, and avoid functions
wrapped around indexed columns.
39What's the difference between a view and a table?
A table stores actual data on disk; a view is a saved query
that presents data dynamically without storing a separate
copy of it.
40Explain COALESCE and its practical use.
COALESCE returns the first non-null value from a list of
expressions, commonly used to substitute default values in
place of NULLs in a report.
Power BI / Tableau / VisualizationQ41–Q60
41What is DAX?
Data Analysis Expressions, the formula language used in
Power BI to build calculated columns, measures, and custom
aggregations.
42Difference between a calculated column and a measure in
Power BI?
A calculated column is computed row-by-row and stored in the
model; a measure is calculated dynamically at query time
based on the current filter context.
43What's the difference between Power BI and Tableau?
Power BI integrates tightly with the Microsoft ecosystem and
is generally more cost-effective; Tableau is often
considered stronger for highly customized, exploratory
visual analytics.
44Explain Power BI's data model relationships.
Relationships link tables together, usually one-to-many, so
filters and slicers applied to one table can flow through to
visuals built from related tables.
45What is a slicer?
An on-report filter control that lets viewers interactively
narrow down what's shown in visuals, without editing the
underlying query.
46What is filter context in DAX?
The set of filters currently applied to a calculation, from
slicers, visuals, or row context, that determines what
values a measure evaluates against.
47Difference between a dashboard and a report in Power
BI?
A report is a multi-page collection of visuals built from a
dataset; a dashboard is a single-page, pinned summary that
can pull tiles from multiple reports.
48What are calculated tables used for?
Tables generated using DAX expressions rather than imported
data, commonly used for date tables or summarized reference
tables.
49What is a Tableau LOD (Level of Detail) expression?
It lets you control the granularity of a calculation
independent of the view's current level of aggregation, like
computing each customer's first purchase date regardless of
how the report is filtered.
50Difference between Import and DirectQuery mode in Power
BI?
Import loads a copy of the data into Power BI's in-memory
model for faster performance; DirectQuery queries the source
live each time, keeping data current but often slower.
51What is a star schema and why is it preferred for BI
models?
A design with a central fact table connected to surrounding
dimension tables; it simplifies relationships and improves
query performance compared to deeply normalized schemas.
52Explain the difference between a fact table and a
dimension table.
A fact table holds measurable, quantitative data like sales
amount; a dimension table holds descriptive attributes like
product name or region used to filter and group facts.
53What are Power BI bookmarks used for?
They capture the current state of a report page, including
filters and visibility, so you can build guided, interactive
storytelling experiences.
54How do you handle performance issues in a large Power BI
report?
Reduce the visual count per page, use aggregated tables,
apply proper star-schema modeling, and limit
high-cardinality columns used directly in visuals.
55What's the difference between a Tableau extract and a
live connection?
An extract is a compressed, static snapshot of the data
optimized for speed; a live connection queries the source in
real time for up-to-date results.
56Explain drill-down vs. drill-through in Power BI.
Drill-down expands a visual to a lower level of detail
within the same visual; drill-through navigates to a
separate page filtered to the selected data point.
57What chart type would you use to show a trend over
time?
A line chart, since it best represents continuous change
across a time axis.
58When would you use a heatmap instead of a bar
chart?
When you need to show the intensity or concentration of
values across two categorical dimensions at once, which a
simple bar chart can't represent well.
59What is row-level security (RLS) in Power BI?
A feature that restricts data access for specific users
based on roles, so different viewers see only the rows
relevant to them within the same report.
60Why is data storytelling important in analytics?
Numbers alone rarely drive decisions; framing insights with
a clear narrative and visual hierarchy helps stakeholders
understand implications and act on them.
Python for AnalyticsQ61–Q80
61What is Pandas used for?
A Python library for loading, cleaning, transforming, and
analyzing structured tabular data using DataFrame and Series
objects.
62Difference between a Series and a DataFrame in
Pandas?
A Series is a single labeled one-dimensional array; a
DataFrame is a two-dimensional table made up of multiple
Series sharing the same index.
63How do you handle missing values in a dataset?
Common approaches include dropping rows or columns with
dropna(), filling with fillna() using a statistic like mean
or median, or interpolating depending on the context.
64What's the difference between loc and iloc in
Pandas?
loc selects data by label, using row or column names; iloc
selects data purely by integer position, regardless of the
labels.
65Explain groupby() in Pandas.
It splits data into groups based on column values, applies a
function like sum or mean to each group, then combines the
results back together.
66What is a NumPy array, and how is it different from a
Python list?
A NumPy array is a fixed-type, contiguous memory structure
optimized for fast numerical operations, unlike Python
lists, which are flexible but slower for numeric
computation.
67How would you merge two DataFrames?
Using pd.merge() with a specified join type, inner, left,
right, or outer, and a key column, similar to a SQL JOIN.
68What is the purpose of the apply() function in
Pandas?
It applies a custom function across rows or columns of a
DataFrame, useful for transformations that built-in
vectorized functions don't already cover.
69Explain the difference between axis=0 and axis=1 in
Pandas.
axis=0 operates down the rows, i.e. column-wise operations;
axis=1 operates across the columns, i.e. row-wise
operations.
70What are Python list comprehensions used for?
A concise syntax for creating lists by applying an
expression to each item in an iterable, often replacing
multi-line for-loops.
71How do you detect and handle outliers in a dataset?
Common methods include the IQR rule, Z-score thresholds, or
visual inspection with box plots, followed by capping,
removing, or transforming the flagged values.
72What's the difference between deep copy and shallow copy
in Python?
A shallow copy duplicates the outer object but still
references the same nested objects; a deep copy recursively
duplicates everything, so changes don't propagate back.
73How would you read a large CSV file efficiently in
Pandas?
Using chunksize to read the file in batches, specifying
dtypes upfront, or loading only the needed columns with
usecols to reduce memory usage.
74What is Matplotlib used for?
A foundational Python plotting library for creating static
charts like line graphs, bar charts, and scatter plots.
75What's the difference between Matplotlib and
Seaborn?
Matplotlib gives low-level, fully customizable plotting
control; Seaborn is built on top of it and offers
higher-level, statistically-aware chart types with cleaner
defaults.
76What is a virtual environment, and why use one?
An isolated Python environment with its own packages and
versions, used to avoid dependency conflicts between
different projects.
77How would you pivot a table in Pandas?
Using pivot_table(), which reshapes long-format data into a
summarized wide format, similar to a pivot table in Excel.
78What is vectorization, and why does it matter in
Pandas/NumPy?
Performing operations on entire arrays at once instead of
looping element by element, which is significantly faster
thanks to optimized underlying implementations.
79Explain try/except and why it's useful in a data
pipeline.
It catches runtime errors, like a bad file read or a type
mismatch, so the pipeline can log the issue and continue or
fail gracefully instead of crashing outright.
80What's the difference between == and is in Python?
== compares values for equality; is checks whether two
variables point to the exact same object in memory.
Statistics & Analytical ThinkingQ81–Q100
81What's the difference between mean, median, and
mode?
Mean is the average, median is the middle value when sorted,
and mode is the most frequently occurring value; each
responds differently to outliers.
82Explain the difference between correlation and
causation.
Correlation means two variables move together statistically;
causation means one directly influences the other, which
correlation alone can never prove.
83What is a p-value?
The probability of observing results at least as extreme as
your data, assuming the null hypothesis is true; a small
p-value suggests the observed effect is unlikely due to
chance alone.
84What is the Central Limit Theorem?
It states that the sampling distribution of the mean
approaches a normal distribution as sample size increases,
regardless of the population's original distribution shape.
85What's the difference between Type I and Type II
errors?
A Type I error is rejecting a true null hypothesis, a false
positive; a Type II error is failing to reject a false null
hypothesis, a false negative.
86What is standard deviation, and what does it tell
you?
A measure of how spread out data points are from the mean; a
higher standard deviation means more variability within the
dataset.
87Explain the difference between a population and a
sample.
A population includes every member of the group being
studied; a sample is a subset drawn from that population,
used to estimate its characteristics.
88What is A/B testing?
A controlled experiment comparing two variants, A and B, to
determine which performs better on a defined metric, using
statistical significance to validate the result.
89What is a confidence interval?
A range of values, calculated from sample data, that is
likely to contain the true population parameter at a stated
confidence level, such as 95%.
90Explain the difference between descriptive and
inferential statistics.
Descriptive statistics summarize the data you have;
inferential statistics use sample data to draw conclusions
or make predictions about a larger population.
91What is multicollinearity, and why is it a problem?
It occurs when independent variables in a regression model
are highly correlated with each other, making it hard to
isolate each variable's individual effect on the outcome.
92How would you explain a complex analytical finding to a
non-technical stakeholder?
Lead with the business implication first, use a simple
visual instead of raw numbers, and avoid statistical jargon
unless it's necessary to build trust in the result.
93What's the difference between a leading and lagging
indicator?
A leading indicator predicts future performance, like
website traffic before sales; a lagging indicator confirms a
trend that has already occurred, like quarterly revenue.
94Explain what a null hypothesis is.
The default assumption that there is no effect or difference
between groups, which a statistical test attempts to either
reject or fail to reject.
95What is data skewness?
A measure of asymmetry in a distribution; positive skew
means a longer tail on the right, negative skew means a
longer tail on the left.
96How would you approach a dataset with a low sample
size?
Be cautious about generalizing findings, consider
non-parametric tests that don't assume a normal
distribution, and clearly flag the limitation when
presenting results.
97What's the difference between a KPI and a metric?
A metric is any quantifiable measure; a KPI is a metric
explicitly tied to a strategic business goal and tracked to
judge performance against that goal.
98Explain survivorship bias with an example.
It's the error of focusing only on entities that "survived"
a process while ignoring those that didn't, like analyzing
only successful companies' habits while ignoring failed
companies that shared the same habits.
99What steps would you take when handed a messy, unfamiliar
dataset?
Profile the data first, checking types, missing values,
duplicates, and ranges, understand the business context and
definitions, then clean and validate before analysis begins.
100How do you decide which visualization is right for a
given dataset?
Match the chart type to the question being asked,
comparison, trend, distribution, or relationship, rather
than picking a chart type first and forcing the data into
it.
Ready to Practice These Live?
Our Data Analytics program includes real mock interview rounds built
around exactly these 100 questions, with mentor feedback after each
one.