EXCEL NOTES

1) Data cleaning
 Data cleaning is an important step in the data analysis process because
it ensures that the data is accurate and reliable.
 Trim() func on eliminates extra spaces in the text, including those at
the start and end
 Condi onal forma ng helps to iden fy and highlights duplicate data in
a range of cell.
 “Text to column” feature helps to split cell containing text into mul ple
cells based on a specified delimeter, such as comma, semicolon, or
space.

2) Vlookup
 VLookup func on is used to look up a value by searching for a
corresponding value in the le most column of another table.
 Syntax of Vlookup()-
Vlookup(lookup_value,table_array,col_index_num,[range_lookup]
 Limita ons- It searches only the first column of a reference table for a
matching value.
-Adding a new column to the reference table can cause error by
shi ing column indexes and changing the referenced columns

3) Xlookup
 Syntax-
Xlookup(lookup_value,lookup_array,return_array,[if_not_found],[match_m
ode]).
 Unlike vlookup, which only searches in the le most column of a table,
Xlookup is capable of searching in any column.
4) Commonly Used Business Metrics
 Revenue - Budget = Profit/loss
 Budget is the baseline for calcula ng the Profit/loss percentage.
 Market Value: Total revenue of all the movies
 Market share: The revenue contribu on of a par cular industry to the
overall market value.
 Target: Es mated revenue for a par cular me period.
 Mean - Synonym for average.
  Median - Middle value of a dataset when it is ordered in ascending order.
 If the dataset has an even number of values, the median is the average of
the two middle values.
 Mode - Most frequently occurring data value.
 Variance is a measure of how spread out a distribu on is. It is calculated as
the average of the squared differences from the mean.
 The smaller the variance, the less spread out the data is. Conversely, the
larger the variance, the more spread out the data is.
 Standard devia on is a measure of the amount of varia on or dispersion of
a set of values. It is calculated as the square root of the variance.
 The smaller the standard devia on, the closer the data points are to the
mean. Conversely, the larger the standard devia on, the more spread out
the data points are.
 Correla on is a sta s cal measure that shows the degree to which two
variables are related.
 A correla on coefficient can range from -1 to 1 -1 (perfect nega ve
correla on) < 0 (no correla on) < 1 (perfect posi ve correla on)

5) What is Power Query and why it is important?

Power Query is like a data cleaning assistant in Excel or Power BI.
It helps you do things like:
 Remove unwanted columns or rows
 Fix spelling or forma ng in data
 Combine data from mul ple files or tables
 Filter only the data you need
 Power Query simplifies data cleaning and transforma on compared to using
Excel formulas.
 Power Query uses M-language internally for all the steps performed using the
UI controls.
 It is advisable to give meaningful names to the transforma on steps in Power
Query. (Your future self will thank you for this)

6) what is the difference between in dis nct and unique in power query.
  Dis nct- Removes duplicate rows and keeps only one of each value, even
if it appears more than once.

Think of it like: “Just show me each value once.

 Unique- Shows only the values that appear exactly once — no repeats at
all.

Think of it like: “Only show me the values that are not repeated.

7) Merging Data in Power Query

 In Power Query, you can perform various types of joins between tables
based on your specific requirements.
 To quickly check the quality status of columns, use the "view" op on.

8) What is the difference b/w condi onal column and custom column?

Condi onal Column

 Think of it like an “if this, then that” builder — but without wri ng
formulas.
 You choose condi ons using dropdowns (no coding).
 Example:
“If score is more than 50, then ‘Pass’, else ‘Fail’.”

Custom Column

 This is where you write your own formula using M language.

 More flexible and powerful.
 You can do calcula ons, string manipula ons, logic, and more.
 Example
[Price] * [Quan ty] or
if [Age] >= 18 then "Adult" else "Minor

9) Types of error in excel

 Error 1- ############ - when the column width is less but the value is
bigger. Or when the data is nega ve.
 Error 2- #name- when the formula is not wri en correct. =su(A1-A2)
 Error 3- #value- when the data is having the text(abcd) in a number
column. It will appear while applying some formulas in that column.( Ex-
=sum)
  Error 4- #div/0!- when the value is divided by 0.
 Error 5- #null! – when the reference is not selected properly.[=sum(A1’A2)]
 Error 6= #Ref!- when the cell or column is deleted a er applying the
formula.
 Error 7= #N/A- when the value is doesn’t found in the data set while
applying the formula. Ex- data set- ankit, anil, sunil, pooja, Gaurav. And we
are finding the value of sourab
 Error 8=#NUM!- when the value does not found in the data set. Ex- data
set- 1,2,3,4,5,6,7,8,9,10 and we are finding the value of 12,13,14 etc.

10) Pivot table

 Pivot table makes it easy to summarize the date.

 It allows you to slice and dice the data in any format you want.
 Pivot tables are very similar to the matrix visualiza on that we will use in BI
tools

11) Data modeling in power pivot

 Power Pivot is a data modeling tool for Microso Excel. It allows users to
create rela onships between tables and perform advanced calcula ons
with large amounts of data.
 To create measures, we can use the DAX (Data Analysis Expression)
language.
 Primary key: It is a column or combina on of columns that uniquely
iden fies each row in a table.
 Foreign key: It is a column or combina on of columns that refers to the
primary key of another table and establishes a link between the two tables.
 Use the SUMX([table],[expression]) func on to add up the values in a table
based on a specified expression.
 All the work of VBA(Visual basic for Applica ons) can be done in Power
Query.

12) What is ETL?

 Extract- transform- load

 Extract the data from folder- transform the data through power query-
then load the data
  In power query while transforming the data we can check data errors by
going to, view tab- column distribu on & column quality
 While data transforming- ensure there are no missing values,
 Ensure all dimensions tables contain a unique column
 Ensure there are no errors/ #NA in column
 Check spellings randomly

13) Fiscal year?

 A calendar year is a 12 month period star ng from January 1st and ending
on dec 31st
 A Fiscal year is a 12 month period used by organiza ons for accoun ng and
financial repor ng purposes, which can begin on any date

14) Calculate Func on?

 Calculate func on allows you to apply filters to your data and perform
calcula ons based on those filtered results.
 Syntax- =Calculate(Expression, filter 1, filter2,…)
15) Some business metrics
 Net sales- 25
 Cost of goods sold(COGS)- 20
Manufacturing cost, transporta on cost, other cost etc.
 Gross margin- 5
 Gross margin% of net sales – 20%
Gross margin %= Gross Margin/Net sales

16) Some func ons

 The FORMAT() func on is used to form a number or a date/ me value into
a specific format. The syntax for the FORMAT() func on is
FORMAT(value,format_text)
 The MONTH() func on is a date and me func on used to extract the
month number from a date.
 The ROUNDUP() func on is used to round a number up to a specified
number of digits. The syntax for the ROUNDUP() func on is:
ROUNDUP(number,num_digits)
 Interview Ques ons.

Q1. What is data analysis?

→ Data analysis is a process of analyzing, modeling, and interpre ng data to draw
insights or conclusions.

Q2. Responsibili es of a data analyst?

→ Collec ng and analyzing data using sta s cal techniques
→ Inves gate and analyze trends
→ Determine organiza onal goals
→ Mine data from primary and secondary sources
→ Provide data reports

Q3. Key skills for a data analyst

→ Technical skills: Excel, SQL, Python, Power BI
→ So skills: Communica on skills, cri cal thinking, data visualiza on

Q4. Data analysis process

→ Understanding the problem
→ Collec ng data
→ Cleaning data
→ Analyzing data
→ Interpre ng the results

Q5. What are the common problems that data analysts encounter during analysis?
→ Handling duplicate data
→ Collec ng meaningful and right data
→ Managing data leakage and decoding
→ Working with incomplete data issues

Q6. Technical tools that are used for analysis

→ MS SQL Server, MySQL

• For working with data
 → Excel, Power BI, Tableau
• For crea ng reports and dashboards
→ Python, R, SPSS
• For sta s cal analysis
→ PowerPoint
• For presenta on

Q7. What are the best methods for data cleaning?

→ Iden fy and remove duplicates

→ Focus on accuracy of data

Q8. Significance of BI (Business Intelligence)

→ Helps to understand data be er

Q9. Descrip ve, Predic ve, and Prescrip ve Analy cs

→ Descrip ve: What has happened

→ Predic ve: What could happen
→ Prescrip ve: What should you do

Q10. Strengths and weaknesses of data analysis

Strengths:
→ Strong analy cal skills
→ Proficiency in data manipula on and visualiza on
→ Ability to derive insights from complex data
Weaknesses:
→ Lack of experience with certain data analysis tools
→ Limited domain knowledge

Q11. Ethical considera ons of data analysis

→ Privacy
→ Data security
→ Transparency
→ Data ownership and rights
Q12. Data visualiza on tools you have used

→ Power BI
→ Excel
→ Tableau

Q14. What is LOG / logarithm?

→ =LOG(number, base)

Q15. What is KPI?

→ KPI stands for Key Performance Indicator

Q16. What is an outlier?

→ An outlier in a dataset is a value that is different from others.

→ Some mes it is an error; in that case, it can be discarded.
→ Other mes it is a real but unusual value; in that case, it should be kept.
Example:
A list of ages of high school graduates:
18, 17, 18, 18, 19, 11, 17, 18, 36, 17
→ 36 is an outlier (possible error)

Q17. What is the syntax for VLOOKUP and its limita ons?

Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Explana on:
→ Lookup value: What you want to look up
→ Table array: Range containing the data
→ Column index number: Column number to return value from
→ Range lookup: TRUE (approximate match) or FALSE (exact match)
Limita ons:
→ Cannot look right to le
→ Lookup value must be in the first column
→ For approximate match, data must be sorted in ascending order
Q18. When would you use IF or Nested IF in Excel?

→ Use IF when there is one condi on

→ Use Nested IF when there are more than two condi ons
Example:
IF(condi on, value_if_true, IF(condi on, value_if_true, value_if_false))

Q19. In Excel, what is the precedence order of operators in formulas?

→ () Brackets
→ ^ Power
→ / Division
→ * Mul plica on
→ + Addi on
→ - Subtrac on

Q20. Can you walk me through using the SUM and SUMIF func on in Excel?

→ =SUM() → Calculates the total sum

→ =SUMIF() → Calculates sum by applying criteria

Q21. How do you calculate the number of working days?

→ =NETWORKDAYS()

Q22. What is the func on to retrieve the present date in Excel?

→ =TODAY() → Returns today’s date

→ =NOW() → Returns today’s date and me

Q23. Can you explain how the AND func on operates in Excel?

→ AND func on is used in formulas where we have mul ple condi ons
Example:
=IF(AND(condi on1, condi on2), value_if_true, value_if_false)

Q24. Can you explain the difference between COUNT, COUNTA, COUNTBLANK,
COUNTIF, COUNTIFS?
 → COUNT → Counts numbers
→ COUNTA → Counts numbers and text (non-blank cells)
→ COUNTBLANK → Counts blank cells
→ COUNTIF → Counts numbers by giving one criterion
→ COUNTIFS → Counts numbers by giving mul ple criteria

Q25. How do you calculate the correla on coefficient between two variables?

→ =CORREL(array1, array2)

Q26. What is INDEX & MATCH in Excel and how is it different from VLOOKUP?

→ =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

→ =INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))

Q27. Explain the difference between IF, IFS, and SWITCH func ons in Excel

→ IF → (logical_test, value_if_true, value_if_false)

→ IFS → (logical_test1, value_if_true1, logical_test2, value_if_true2, …)
→ SWITCH → (expression, value1, result1, value2, result2, default_result)
Notes:
→ IF can test mul ple condi ons but needs nes ng, which reduces
readability
→ IFS is easier to read and can handle many condi ons
→ SWITCH is used to test a value mul ple mes
→ SWITCH works only with exact matches and does not support logical
operators like > or <

Q28. Explain the difference between REPLACE and SUBSTITUTE

→ REPLACE(old_text, start_num, num_chars, new_text)

→ SUBSTITUTE(text, old_text, new_text, [instance_num])
→ SUBSTITUTE is easier to use for replacing text

PIVOT TABLE

Q29. What is a Pivot Table and when do you use it?

 → It is an Excel tool that allows you to calculate, summarize, and analyze
data
→ Helps to see comparisons, pa erns, and trends
→ Gives be er understanding of data
→ Provides an interac ve way of presen ng data from different perspec ves

Q30. What are Pivot Charts?

→ Pivot Charts are used to graphically summarize data and explain

complicated data
→ They are more flexible than normal charts

Q31. Are Pivot Table and Power Pivot the same in Excel?

→ Power Pivot is great for modeling data

→ It is more powerful than Pivot Table
→ Power Pivot can use pivot tables to see comparisons, pa erns, and trends

DATA ANALYSIS

Q32. What is Data Analysis?

→ Data analysis is a process of analyzing, modeling, and interpre ng data to

draw insights or conclusions.

Q33. What is Data Valida on?

→ Data valida on means checking the quality of correct data before using,
impor ng, and processing data.
When is it performed?
→ In data warehousing, data valida on is performed during the ETL process
so that analysts can get insights into the scope and nature of data conflicts.

Q34. How do you use Excel to detect outliers?

→ Two methods used to detect outliers are:

1. Box Plot / IQR Method
→ If a value is higher or lower than the IQR (Interquar le Range),
 → If the value is above the upper quar le (Q3) or below the lower quar le
(Q1), the value is an outlier.
2. Standard Devia on Method
→ If a value is higher or lower than:
Mean ± (2 × SD)
→ It is considered an outlier.

Q35. How can you find dis nct values from a range?

→ =UNIQUE()

Q36. Convert rows into columns

→ Copy the data → Select cell → Right-click → Paste Op ons → Transpose

OR
→ =TRANSPOSE(array)

Q37. How to import PDF into Excel?

→ Get Data → From PDF → Load

OR
→ Get Data → From Web → Open PDF in browser → Copy URL

Q38. How can we protect a workbook in Excel?

→ Review → Protect Workbook

Q39. What is the difference between rela ve and absolute cell reference?

→ Rela ve cell reference: A5 * B6

→ Absolute cell reference: $A$5 * $B$6

Q40. Is there any way to automate repe ve tasks in Excel?

→ Condi onal Forma ng

Q41. How can you enable a drop-down op on for a cell?

→ Data tab → Data Valida on → List → Select source

Q42. How do you clear forma ng in Excel without removing cell content?

→ Home → Clear → Clear Formats

OR
→ Ctrl + H + E + F

Q43. Difference between Time Intelligence and Date func ons

→ Time Intelligence: Used in DAX language and enables manipula on of

dates using me periods
→ Date Func on: An Excel formula used to calculate dates

Q44. What is Microso Excel and where is it used in business?

→ Microso Excel is a spreadsheet applica on developed by Microso that

is used to store, organize, analyze, and visualize data.
In business, Excel is widely used for tasks such as data analysis, financial
repor ng, budge ng, forecas ng, sales tracking, inventory management,
and crea ng dashboards to support decision-making.

45. What are cells and ranges in Excel?

→ A cell is the smallest unit in Excel where data is entered. It is formed by

the intersec on of a row and a column, for example, cell A1.
A range is a group of two or more cells selected together, such as A1:A10 or
A1:D5. Ranges are commonly used in formulas, func ons, and data analysis.

46. What is a workbook and worksheet?

A workbook is an Excel file that contains one or more worksheets.

A worksheet is a single spreadsheet within a workbook where data is
entered and analyzed. Each worksheet consists of rows, columns, and cells.

47. What are the different data types available in Excel?

The main data types in Excel are:

Text: Alphabe c or alphanumeric data such as names and labels
 Number: Numeric values used for calcula ons
Date & Time: Used to store dates and me values
Boolean/Logical: TRUE or FALSE values
Formula: Expressions that calculate values based on other cells

48. What is AutoFill and how does it work?

AutoFill is a feature in Excel that automa cally fills data into adjacent cells
based on a pa ern.
It works by dragging the fill handle (small square at the bo om-right corner
of a cell) to copy values, formulas, or con nue sequences like numbers,
dates, or days.
Example:
If you enter 1 and 2 in two cells and drag AutoFill, Excel con nues the series
as 3, 4, 5, and so on.

49. What is the difference between Delete and Clear in Excel?

Delete removes the en re cell or selected rows/columns, causing

surrounding cells to shi .
Clear removes only the content, forma ng, or comments from a cell but
keeps the cell structure intact.

50. What is Freeze Panes?

Freeze Panes is a feature in Excel that allows you to lock specific rows or
columns so they remain visible while scrolling through large datasets.
It is commonly used to keep headers visible when working with long tables.

51. How do you wrap text in Excel?

Text wrapping allows long text in a cell to appear on mul ple lines within the
same cell.
Steps:
Select the cell → Go to Home tab → Click Wrap Text

52. What is Text to Columns?

 Text to Columns is a feature used to split data from one column into mul ple
columns based on a delimiter such as a comma, space, or tab.
It is commonly used when data is imported from external sources like CSV
files.

53. What is Flash Fill and when would you use it?

Flash Fill is a feature that automa cally fills values based on pa erns it
detects in your data.
You would use Flash Fill when you need to quickly format, extract, or
combine data without wri ng formulas.
Example:
Extrac ng first names from full names or forma ng phone numbers.

54. What is the difference between formulas and func ons?

A formula is a user-created expression used to calculate values, such as

=A1+B1.
A func on is a predefined formula in Excel designed to perform specific
calcula ons, such as =SUM(A1:A10).

55. Explain SUMPRODUCT and where it is used.

SUMPRODUCT mul plies corresponding values in arrays and then adds the
results.
Syntax:
=SUMPRODUCT(array1, array2)
Use case:
It is commonly used for weighted calcula ons, condi onal summa on
without helper columns, and complex data analysis.

56. What is the difference between COUNTIF and SUMIF?

 COUNTIF counts the number of cells that meet a condi on.

 SUMIF adds the values of cells that meet a condi on.
Example:
COUNTIF counts how many sales are above 10,000, while SUMIF calculates
the total sales above 10,000.
57. What is the difference between AVERAGE, AVERAGEIF, and AVERAGEIFS?

 AVERAGE calculates the average of a range without condi ons.

 AVERAGEIF calculates the average based on one condi on.
 AVERAGEIFS calculates the average based on mul ple condi ons.

58. What is IFERROR and why is it used?

IFERROR is used to handle errors in formulas by replacing error values with a

custom message or value.
Example:
=IFERROR(A1/B1, "Error")
It improves readability and prevents formulas from showing error messages
like #DIV/0!.

59. What is the difference between LEFT, RIGHT, and MID?

 LEFT extracts text from the beginning of a cell.

 RIGHT extracts text from the end of a cell.
 MID extracts text from the middle of a cell based on posi on.

60. Explain LEN and TRIM func ons.

 LEN returns the total number of characters in a text string, including spaces.
 TRIM removes extra spaces from text except single spaces between words.
These func ons are commonly used in data cleaning.

61. What is CONCAT vs TEXTJOIN?

 CONCAT joins text from mul ple cells without a delimiter.

 TEXTJOIN joins text using a specified delimiter and can ignore empty cells.
TEXTJOIN is more flexible and powerful than CONCAT.

62. How do you extract month and year from a date?

 Month: =MONTH(A1)
 Year: =YEAR(A1)
 For month name:
=TEXT(A1, "mmmm")

63. What is ROUND, ROUNDUP, and ROUNDDOWN?

 ROUND rounds a number to a specified number of digits.

 ROUNDUP always rounds the number up.
 ROUNDDOWN always rounds the number down.
These func ons are commonly used in financial calcula ons.

64. Difference between VLOOKUP, HLOOKUP, and XLOOKUP

 VLOOKUP: Searches for a value ver cally in the first column and returns a
value from another column.
 HLOOKUP: Searches for a value horizontally in the first row and returns a
value from another row.
 XLOOKUP: A modern and flexible lookup func on that can search both
ver cally and horizontally.
Key point:
XLOOKUP replaces both VLOOKUP and HLOOKUP.

65. Why is INDEX-MATCH be er than VLOOKUP?

INDEX-MATCH is be er because:
 It can look up values in any direc on (le or right).
 It works faster on large datasets.
 It does not break if columns are inserted or deleted.
 It is more flexible and reliable.

66. What is MATCH func on and its match types?

MATCH returns the posi on of a value in a range.

Syntax:
=MATCH(lookup_value, lookup_array, match_type)
Match types:
 0 → Exact match
 1 → Approximate match (less than or equal)
 -1 → Approximate match (greater than or equal)
67. What are dynamic arrays in Excel?

Dynamic arrays allow formulas to return mul ple values that automa cally
spill into neighboring cells.
Func ons like FILTER, SORT, UNIQUE, and SEQUENCE use dynamic arrays.
They reduce the need for complex formulas and helper columns.

68. What is XLOOKUP and its advantages?

XLOOKUP is an advanced lookup func on that replaces VLOOKUP and

HLOOKUP.
Advantages:
 Supports le -to-right and right-to-le lookup
 No need for column index numbers
 Supports exact and approximate matches
 Handles missing values gracefully

69. What is OFFSET func on and where is it used?

OFFSET returns a reference to a range that is a specified number of rows and

columns away from a star ng point.
Use cases:
 Crea ng dynamic ranges
 Building dynamic charts
 Advanced dashboard repor ng

70. What is CHOOSE func on?

CHOOSE returns a value from a list based on a given index number.

Example:
=CHOOSE(2, "Sales", "Profit", "Cost")
→ Returns "Profit"
It is commonly used in scenarios where you need to select values
dynamically.

71. How do you remove duplicates in Excel?

 You can remove duplicates using:
Data tab → Remove Duplicates
Excel keeps the first occurrence and removes repeated values.
This is commonly used during data cleaning.

72. How do you handle missing values in Excel?

Missing values can be handled by:

 Removing rows with blanks if they are not important
 Replacing blanks with 0, average, or a meaningful value
 Using formulas like IF, IFERROR, or ISBLANK
The method depends on business requirements.

73. How do you standardize text data?

Text data can be standardized by:

 Conver ng text to proper case using UPPER, LOWER, or PROPER
 Removing extra spaces using TRIM
 Cleaning unwanted characters using CLEAN
This ensures consistency in data.

74. How do you convert text to numbers?

Text can be converted to numbers by:

 Using VALUE() func on
 Mul plying by 1 (=A1*1)
 Using Text to Columns
 Using Paste Special → Mul ply

75. How do you split and combine columns?

Split columns:
 Use Text to Columns
 Use formulas like LEFT, RIGHT, MID
Combine columns:
 Use CONCAT, TEXTJOIN, or & operator

76. What is Power Query and why is it used?

 Power Query is a data transforma on tool in Excel used to import, clean, and
transform data from mul ple sources.
It is used because:
 It automates repe ve data cleaning tasks
 Handles large datasets efficiently
 Keeps data refreshable and consistent

77. Difference between Remove Duplicates vs Advanced Filter?

 Remove Duplicates: Permanently deletes duplicate records from the dataset.

 Advanced Filter: Extracts unique records to another loca on without altering
the original data.

78. How do you change date formats in Excel?

Select the date cells → Right-click → Format Cells → Date → Choose required
format
OR
Use TEXT(date, "dd-mm-yyyy") for custom formats.

79. What is a Pivot Table?

A Pivot Table is an Excel tool used to summarize, analyze, and explore large
datasets quickly.
It helps in calcula ng totals, averages, counts, and percentages, and allows
users to view data from different perspec ves.

80. Difference between Pivot Table and a normal table?

A normal table stores raw data and is mainly used for data entry.
A Pivot Table summarizes and analyzes data dynamically without changing
the original dataset.
Pivot Tables are used for repor ng and analysis.

81. What are calculated fields in Pivot Table?

Calculated fields are custom formulas created inside a Pivot Table to perform
calcula ons using exis ng fields.
 Example:
Profit = Sales – Cost
They help perform calcula ons without modifying the source data.

82. What are slicers and melines?

 Slicers are visual filters used to filter Pivot Table data by clicking bu ons.
 Timelines are special filters used only for date fields to filter data by year,
quarter, month, or day.
They make Pivot Tables more interac ve.

83. How do you refresh a Pivot Table?

Right-click on the Pivot Table → Click Refresh

OR
Use Data tab → Refresh All
Refreshing updates the Pivot Table when source data changes.

84. How do you group dates in a Pivot Table?

Right-click on a date field in the Pivot Table → Click Group → Select Year,
Quarter, Month, or Day.
This is useful for me-based analysis.

85. How do you show % of Grand Total in Pivot Table?

Right-click on the value field → Value Field Se ngs → Show Values As → %

of Grand Total
This helps compare contribu ons of each category.

86. What are Pivot Charts?

Pivot Charts are graphical representa ons of Pivot Table data.

They automa cally update when the Pivot Table changes and are used for
visual analysis and dashboards.

87. What is Data Valida on?

 Data Valida on is an Excel feature used to control the type of data that can
be entered into a cell.
It helps maintain data accuracy by restric ng inputs such as numbers, dates,
or predefined lists.

88. How do you create dependent drop-down lists?

Dependent drop-down lists are created using Data Valida on + named

ranges + formulas like INDIRECT().
Basic steps:
1. Create the main drop-down list
2. Create named ranges for dependent values
3. Use INDIRECT() in Data Valida on to link the second drop-down to the first
This is commonly used in forms and dashboards.

89. Difference between condi onal forma ng and filters?

 Condi onal Forma ng visually highlights data based on rules (colors, icons,
bars).
 Filters hide or display data based on selected criteria.
Condi onal forma ng focuses on visual insights, while filters focus on data
selec on.

90. How do you highlight duplicates?

Select the data range → Home tab → Condi onal Forma ng → Highlight
Cells Rules → Duplicate Values
Excel automa cally highlights duplicate entries.

5. How do you highlight top 10 values?

Select the data range → Condi onal Forma ng → Top/Bo om Rules → Top
10 Items
You can also customize it to highlight top 5 or top 20 values.

91. How do you apply condi onal forma ng based on a formula?

Select the range → Condi onal Forma ng → New Rule → Use a formula to
determine which cells to format
 Example:
=A1>10000
This highlights cells with values greater than 10,000.

92. Difference between TODAY() and NOW()?

 TODAY() returns the current date only.

 NOW() returns the current date and current me.
Both update automa cally when the worksheet recalculates.

93. What is EOMONTH?

EOMONTH returns the last date of a month before or a er a given date.

Syntax:
=EOMONTH(start_date, months)
Example:
=EOMONTH(A1, 0) → Returns last day of the same month.

94. How do you calculate age in Excel?

Age can be calculated using:

=DATEDIF(Date_of_Birth, TODAY(), "Y")
This returns the number of completed years.

95. What is NETWORKDAYS vs WORKDAY?

 NETWORKDAYS calculates the number of working days between two dates

(excluding weekends and holidays).
 WORKDAY returns a future or past date a er adding or subtrac ng working
days.

96. How do you calculate me difference?

Time difference is calculated by subtrac ng the start me from the end me.
Example:
=End_Time - Start_Time
For hours:
=(End_Time - Start_Time) * 24
 ERROR HANDLING

97. What are common Excel errors (#N/A, #VALUE!, #DIV/0!)?

 #N/A → Value not available, usually in lookup func ons when no match is
found
 #VALUE! → Wrong type of argument or invalid data in a formula
 #DIV/0! → Division by zero or by an empty cell

98. How do you handle #N/A errors?

You can handle #N/A errors by:

 Using IFERROR() to replace the error with a custom value
 Using IFNA() specifically for #N/A errors
Example:
=IFERROR(VLOOKUP(A1, A:B, 2, FALSE), "Not Found")

99. What is IFERROR vs ISERROR?

 IFERROR returns a custom value when any error occurs

 ISERROR checks whether a formula returns an error and returns TRUE or
FALSE
Key point:
IFERROR is commonly used in repor ng, while ISERROR is used in logical
checks.

100. What causes #REF! error?

#REF! error occurs when a formula refers to an invalid cell reference, usually
because:
 A referenced cell or column was deleted
 A formula is copied incorrectly

ADVANCED / ANALYST-LEVEL QUESTIONS

101. What is Power Pivot?

Power Pivot is an advanced Excel feature used to create data models,

manage large datasets, and perform complex calcula ons using DAX.
It allows you to analyze millions of rows of data efficiently.
102. Difference between Power Pivot and Pivot Table?

 Pivot Table is used for basic summariza on and analysis of a single dataset.
 Power Pivot is used for advanced data modeling, handling mul ple tables,
and crea ng rela onships.
Power Pivot supports complex calcula ons and large data volumes.

103. What are rela onships in Excel data model?

Rela onships connect tables using common columns, such as Customer ID or

Product ID.
They allow analysis across mul ple tables without merging them physically.

104. What is DAX?

DAX stands for Data Analysis Expressions.

It is a formula language used in Power Pivot and Power BI to create
advanced calcula ons like measures and calculated columns.

105. What are measures vs calculated columns?

 Calculated Columns are computed row by row and stored in the table.
 Measures are calculated dynamically based on filters and aggrega ons.
Key point:
Measures are preferred for performance and repor ng.

106. What is CALCULATE func on?

CALCULATE is a powerful DAX func on used to modify filter context and

perform calcula ons.
It is the most important func on in DAX.

107. What is Time Intelligence in DAX?

Time Intelligence allows calcula ons over me, such as:

 Year-to-Date (YTD)
 Month-to-Date (MTD)
  Year-over-Year (YoY)
It helps analyze trends and performance over me.

108. What is ROW CONTEXT vs FILTER CONTEXT?

 Row Context: Calcula on happens row by row

 Filter Context: Calcula on is affected by filters, slicers, and visuals
Understanding this is cri cal for wri ng correct DAX formulas.

109. What is STAR SCHEMA in Excel model?

Star Schema is a data modeling structure with:

 One Fact table (transac on data)
 Mul ple Dimension tables (descrip ve data)
It improves performance and simplifies analysis.