Excel Interview Questions & Answers for Data Analyst Freshers
1. What is Excel and how is it used in Data Analysis?
Excel is a spreadsheet application used for storing, organizing, analyzing, and visualizing data. For data
analysts, Excel is helpful for cleaning data, performing calculations, creating pivot tables, and building
dashboards.
2. What is the difference between a Workbook and a Worksheet?
A Workbook is the entire Excel file, while a Worksheet is a single tab or page within that workbook where
data is entered.
3. What are relative, absolute, and mixed references?
Relative (A1): Changes when copied.
Absolute ($A$1): Does not change when copied.
Mixed (A$1 or $A1): Partially fixed.
4. What are formulas and functions in Excel?
Formula: A custom calculation written using cell references (e.g., =A1+B1)
Function: A built-in formula like SUM(), AVERAGE(), IF(), etc.
5. How to remove duplicates in Excel?
1. Select your data.
2. Go to Data tab -> Click Remove Duplicates.
3. Choose the columns to compare.
4. Click OK.
If it doesn't work, check for hidden characters like CHAR(160), extra spaces, or different formatting.
6. How do you remove unwanted spaces in Excel?
Use TRIM(A1) to remove leading/trailing spaces.
To remove non-breaking spaces (CHAR(160)), use: TRIM(SUBSTITUTE(A1,CHAR(160),""))
Excel Interview Questions & Answers for Data Analyst Freshers
7. What is VLOOKUP and how is it used?
VLOOKUP searches for a value in the first column of a table and returns a value from another column.
Example: =VLOOKUP(101, A2:D10, 2, FALSE)
8. What is the difference between COUNT, COUNTA, COUNTIF, and COUNTIFS?
COUNT: Counts numbers only.
COUNTA: Counts non-empty cells.
COUNTIF: Counts cells that meet one condition.
COUNTIFS: Counts cells with multiple conditions.
9. How do you use IF and IFS in Excel?
IF: =IF(A1>50, "Pass", "Fail")
IFS: =IFS(A1>90,"A",A1>80,"B",A1>70,"C")
10. What is a Pivot Table and how do you create it?
A Pivot Table summarizes large datasets.
Steps:
1. Select data.
2. Go to Insert -> Pivot Table.
3. Drag fields into Rows, Columns, Values.
11. How do you calculate the difference between two dates in Excel?
Use DATEDIF function:
=DATEDIF(A1, B1, "d") for days
=DATEDIF(A1, B1, "m") for months
=DATEDIF(A1, B1, "y") for years
12. How do you find and replace text in Excel?
Press Ctrl + H, enter the text to find and the replacement, then click Replace All.
Excel Interview Questions & Answers for Data Analyst Freshers
13. What is the difference between CONCATENATE and TEXTJOIN?
CONCATENATE: =CONCATENATE(A1, " ", B1)
TEXTJOIN: =TEXTJOIN(" ", TRUE, A1, B1)
14. What is data validation?
Data Validation restricts the type of data entered in a cell (e.g., only numbers, dates, list selections). Use:
Data -> Data Validation
15. What is the difference between charts and pivot charts?
Charts visualize regular data. Pivot Charts are based on Pivot Tables and update when filters change.
16. What is the use of conditional formatting?
It highlights cells based on rules like color coding values above 100 or showing duplicates.
17. How do you handle #DIV/0! or #N/A errors?
Use IFERROR(): =IFERROR(A1/B1, "Error")
18. What is the use of the FILTER and UNIQUE functions?
UNIQUE(A2:A10): Returns distinct values.
FILTER(A2:A10, B2:B10="India"): Filters rows where country = India.
19. How do you find the top 5 values in a list?
Use LARGE(): =LARGE(A1:A100, 1) for highest, =LARGE(A1:A100, 5) for 5th highest.
20. What is the use of Goal Seek in Excel?
Goal Seek finds the input value needed to reach a target result.
Example: Want A1*B1 to equal 500. Use Data -> What If Analysis -> Goal Seek.
