20 Proven Data Analyst Interview Questions with Excel & SQL

✅ Introduction

The demand for skilled data analysts is higher than ever. Companies are hiring professionals who can interpret data using tools like Excel and SQL to make business decisions. If you’re preparing for a data analyst interview, mastering the most common data analyst interview questions is essential.

In this guide, you’ll find 20 proven questions, covering Excel interview questions for data analysts, SQL interview questions for data analysts, and real-life examples.

📊 Why Excel & SQL Are Essential for Data Analysts

💼 Used in 90%+ data analyst job roles
🧩 Excel handles quick data exploration and dashboards
🧠 SQL manages large datasets and relational queries
🔎 Knowing both = More job offers and higher pay

🔍 20 Proven Data Analyst Interview Questions (Excel + SQL)

🟩 Section 1: Excel Interview Questions for Data Analysts

1. What are pivot tables and how do you use them?

Pivot tables summarize large datasets by grouping, counting, summing, or averaging data. They’re useful for sales, marketing, and operational reports.

2. What is the difference between VLOOKUP, HLOOKUP, and XLOOKUP?

VLOOKUP: Searches vertically in a column
HLOOKUP: Searches horizontally in a row
XLOOKUP: Replaces both, offers more flexibility

3. How do you handle missing or duplicate data in Excel?

Use Filters and Conditional Formatting to find duplicates
Remove Duplicates tool or COUNTIF()
Use formulas like IFNA() or IF(ISBLANK()) to handle missing data

4. What is data validation in Excel?

It controls what data can be entered in a cell (e.g., dropdown lists, number limits).

5. Explain the difference between relative, absolute, and mixed cell references.

Relative: =A1+B1 (changes with drag)
Absolute: =$A$1+$B$1 (fixed reference)
Mixed: =$A1+B$1 (partial lock)

🟦 Section 2: SQL Interview Questions for Data Analysts

6. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN: Returns only matching rows from both tables
LEFT JOIN: Returns all rows from the left table, with matches (or NULL) from the right

				
					SELECT a.name, b.salary 
FROM employees a 
LEFT JOIN salaries b ON a.id = b.emp_id;

7. How do you find duplicate records in SQL?

				
					SELECT name, COUNT(*) 
FROM employees 
GROUP BY name 
HAVING COUNT(*) > 1;

8. What is a subquery?

A query within another query. Used to fetch intermediate results.

9. What are aggregate functions in SQL?

Used to perform calculations on multiple rows:

COUNT()
AVG()
MAX()
MIN()
SUM()

10. How can you rank or number rows in SQL?

Using RANK() or ROW_NUMBER() functions:

				
					SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank 
FROM employees;

🧪 10 Real-World Examples for Data Analyst Interviews

1. Find the top 5 highest-paid employees.

				
					SELECT name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 5;

2. Remove duplicates in Excel

Use Remove Duplicates under the Data tab or:

				
					=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "Unique")

3. Create a dynamic drop-down list using data validation.

Use Excel’s Data > Validation and refer to a named range.

4. Calculate moving average in Excel

				
					=AVERAGE(B2:B4)

Drag the formula to create a moving average over any range.

5. Get monthly sales totals using pivot tables.

Insert Pivot Table
Drag Date to Rows, Sales to Values
Group dates by month

6. Retrieve employees hired in the last 6 months.

				
					SELECT * FROM employees 
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

7. Show department-wise employee count.

				
					SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;

8. Highlight duplicate values in Excel.

Use Conditional Formatting > Highlight Cell Rules > Duplicate Values

9. Use IF with AND in Excel

				
					=IF(AND(A2>50, B2="Yes"), "Pass", "Fail")

10. Update salaries for high performers

				
					UPDATE employees 
SET salary = salary * 1.1 
WHERE performance = 'Excellent';

❓ Detailed FAQs on Data Analyst Interview Questions

Q1. What tools should a data analyst know in 2025?

Excel (Power Query, Pivot Tables, XLOOKUP)
SQL (PostgreSQL, MySQL, MS SQL Server)
Optional: Power BI, Tableau, Python, R

Q2. Do companies test Excel and SQL together?

Yes, many companies include both in a technical round. Expect hands-on tasks like cleaning data in Excel and writing SQL queries for analytics.

Q3. How should freshers prepare for data analyst interviews?

Learn Excel basics and formulas
Practice SQL joins and aggregations
Understand business KPIs and dashboards
Mock projects and practice datasets help

Q4. Can I use ChatGPT to learn SQL and Excel?

Absolutely! Use it to generate queries, understand errors, and get practice questions — but always validate answers with hands-on tools.

Q5. What are some common mistakes candidates make?

Not explaining their logic
Copy-pasting SQL without understanding
Missing edge cases in Excel filters
Focusing only on theory, not practice

Conclusion: Crack Your Data Analyst Interview with Confidence

Mastering these data analyst interview questions in Excel and SQL gives you a strong edge in today’s job market. With consistent practice and a clear understanding of the logic behind each query or formula, you’ll be well-prepared to solve real business problems during interviews.

💡 For more insights, examples, and technical prep tips,

👉 Explore more content on Capable Techies and stay ahead with Fresh Blog.