✅ Most Asked Excel Interview Questions for Data Analysts 📊🔥

🔍 Q1. What is the difference between VLOOKUP and XLOOKUP?
Answer: VLOOKUP → works only left to right, needs column index
XLOOKUP → works in any direction, more flexible, handles errors better

🔍 Q2. Write a formula to find a value using INDEX and MATCH.
Answer: =INDEX(B2:B10, MATCH(E1, A2:A10, 0))
Returns value from column B where A matches E1

🔍 Q3. What is a Pivot Table and when do you use it?
Answer: Used to summarize large datasets (sum, count, average) and analyze patterns quickly without formulas.

🔍 Q4. How do you handle duplicates in Excel?
Answer: Data → Remove Duplicates, Conditional Formatting → Highlight duplicates, or use formula: =COUNTIF(A:A, A2) > 1

🔍 Q5. What is the difference between COUNT, COUNTA, and COUNTIFS?
Answer: COUNT → counts numeric values, COUNTA → counts non-empty cells, COUNTIFS → counts based on multiple conditions

🔍 Q6. How do you create a dynamic dashboard in Excel?
Answer: Use Pivot Tables + Pivot Charts, add slicers for filters, use named ranges or tables, keep layout clean and interactive

🔍 Q7. What is conditional formatting? Give a use case.
Answer: Used to highlight data based on conditions
Example: Highlight sales > target in green

🔍 Q8. How do you handle missing values in Excel?
Answer: Filter blanks, replace using mean/median, use IF or IFERROR, remove rows if needed

🔍 Q9. What is the difference between absolute and relative cell references?
Answer: Relative → changes when dragged (A1), Absolute → fixed ($A$1)

🔍 Q10. How do you find top 5 sales values?
Answer: =LARGE(A2:A100, 1) Change 1 → 5 for top 5, or use Pivot Table + Top N filter

🔍 Q11. What is IFERROR and why is it used?
Answer: Handles errors in formulas, Example: =IFERROR(A1/B1, "Error")

🔍 Q12. What is data validation?
Answer: Restricts user input (dropdowns, limits) to maintain data quality

Double Tap ❤️ For More

📊 Learn Microsoft Excel in 14 Days:

🟢 Day 1 – Excel Interface & Basics
Understand Excel layout, ribbons, cells, rows, columns, and basic navigation.

📝 Day 2 – Data Entry & Formatting
Learn to enter/edit data, use formatting tools (bold, color, borders, alignment).

➕ Day 3 – Basic Formulas
Use SUM(), AVERAGE(), MIN(), MAX(), and simple arithmetic formulas.

🔍 Day 4 – Cell Referencing
Understand relative, absolute, and mixed references using $.

📋 Day 5 – Sorting & Filtering
Sort data (A-Z, Z-A) and apply filters to analyze specific data quickly.

📐 Day 6 – Conditional Formatting
Highlight data using rules (greater than, color scales, data bars).

📊 Day 7 – Charts & Graphs
Create and customize pie, bar, line charts for data visualization.

🔁 Day 8 – Logical Functions
Learn IF(), AND(), OR(), IFERROR() for decision-making in formulas.

📈 Day 9 – Lookup Functions
Use VLOOKUP(), HLOOKUP(), and XLOOKUP() to fetch data from tables.

🧩 Day 10 – Text Functions
Learn CONCAT(), TEXT(), LEFT(), RIGHT(), LEN(), TRIM().

📆 Day 11 – Date & Time Functions
Work with TODAY(), NOW(), DATEDIF(), TEXT() for date/time formatting.

🔐 Day 12 – Data Validation
Restrict data input using drop-downs, number/text rules, and error messages.

📎 Day 13 – Pivot Tables
Summarize large datasets using Pivot Tables for quick insights.

🚀 Day 14 – Final Dashboard Project
Build a simple Excel dashboard using charts, pivot tables & conditional formatting.

❤️ React for more!

✅ 🔤 A–Z of SQL Commands 🗄️💻⚡

A – ALTER
Modify an existing table structure (add/modify/drop columns).

B – BEGIN
Start a transaction block.

C – CREATE
Create database objects like tables, views, indexes.

D – DELETE
Remove records from a table.

E – EXISTS
Check if a subquery returns any rows.

F – FETCH
Retrieve rows from a cursor.

G – GRANT
Give privileges to users.

H – HAVING
Filter aggregated results (used with GROUP BY).

I – INSERT
Add new records into a table.

J – JOIN
Combine rows from two or more tables.

K – KEY (PRIMARY KEY / FOREIGN KEY)
Define constraints for uniqueness and relationships.

L – LIMIT
Restrict number of rows returned (MySQL/PostgreSQL).

M – MERGE
Insert/update data conditionally (mainly in SQL Server/Oracle).

N – NULL
Represents missing or unknown data.

O – ORDER BY
Sort query results.

P – PROCEDURE
Stored program in the database.

Q – QUERY
Request for data (general SQL statement).

R – ROLLBACK
Undo changes in a transaction.

S – SELECT
Retrieve data from tables.

T – TRUNCATE
Remove all records from a table quickly.

U – UPDATE
Modify existing records.

V – VIEW
Virtual table based on a query.

W – WHERE
Filter records based on conditions.

X – XML PATH
Generate XML output (mainly SQL Server).

Y – YEAR()
Extract year from a date.

Z – ZONE (AT TIME ZONE)
Convert datetime to specific time zone.

❤️ Double Tap for More

🔹 DATA ANALYST – INTERVIEW REVISION SHEET

1️⃣ Role Clarity
> “A data analyst collects, cleans, analyzes data, and converts it into insights that help businesses make decisions.”

2️⃣ SQL (Most Important)
Must-know clauses:
• SELECT, WHERE, ORDER BY, LIMIT
• GROUP BY, HAVING
• JOINS (INNER, LEFT)
• Subqueries, CTEs
• Window functions (ROW_NUMBER, RANK)
Golden rules:
• WHERE → before aggregation
• HAVING → after aggregation
• LEFT JOIN → keeps all left table rows
• NULLs break calculations → use COALESCE
Classic questions:
• Top N per group
• Find duplicates
• Running totals

3️⃣ Excel Essentials
Formulas:
• IF, XLOOKUP
• COUNTIFS, SUMIFS
• TRIM, LEFT, RIGHT
Core features:
• Pivot tables
• Conditional formatting
• Data validation (dropdowns)
Avoid:
• Merged cells
• Hard-coded values

4️⃣ Power BI / Tableau
Concepts:
• Data model (star schema)
• Relationships (one-to-many)
• Measures > calculated columns
Must-know DAX:
• Total Sales = SUM(Sales[Amount])
• YTD Sales = TOTALYTD(SUM(Sales[Amount]), Sales[Date])
Design rules:
• KPIs on top
• One story per dashboard
• Minimal visuals

5️⃣ Statistics (Only What Matters)
• Mean vs Median
• Standard deviation
• Correlation ≠ causation
• Outliers distort averages
• Use median for Salaries, House prices

6️⃣ Data Cleaning (Interview Gold)
Steps you should say:
1. Remove duplicates
2. Handle missing values
3. Fix data types
4. Standardize text

7️⃣ Business Metrics
• Revenue
• Growth rate
• Conversion rate
• Churn
• Retention
• Average order value
Always connect metrics to business impact.

8️⃣ Case Question Framework (Very Important)
Always answer like this:
1. What happened
2. Why it happened
3. What should be done
Example:
> “Sales dropped due to lower traffic in one region, so I’d recommend increasing marketing spend there.”

9️⃣ Project Explanation Template
> “The goal was . I used to clean data, to analyze, and to visualize. The key insight was . The business impact was .”
Memorize this.

🔟 HR Power Answers
Why data analyst?
> “I enjoy finding patterns in data and turning them into actionable insights.”
Strength:
“I combine technical skills with business understanding.”
Weakness:
“I used to over-analyze, but now I focus on impact.”

🧠 Last-Day Interview Tips
• Think out loud
• Ask clarifying questions
• Don’t jump to tools immediately
• Focus on impact, not syntax

💬 Tap ❤️ for more!

✅ Data Analyst Resume Tips 🧾📊

Your resume should showcase skills + results + tools. Here’s what to focus on:

1️⃣ Clear Career Summary 
• 2–3 lines about who you are 
• Mention tools (Excel, SQL, Power BI, Python) 
• Example: “Data analyst with 2 years’ experience in Excel, SQL, and Power BI. Specializes in sales insights and automation.”

2️⃣ Skills Section 
• Technical: SQL, Excel, Power BI, Python, Tableau 
• Data: Cleaning, visualization, dashboards, insights 
• Soft: Problem-solving, communication, attention to detail

3️⃣ Projects or Experience 
• Real or personal projects 
• Use the STAR format: Situation → Task → Action → Result 
• Show impact: “Created dashboard that reduced reporting time by 40%.”

4️⃣ Tools and Certifications 
• Mention Udemy/Google/Coursera certificates  (optional)
• Highlight tools used in each project

5️⃣ Education 
• Degree (if relevant) 
• Online courses with completion date

🧠 Tips: 
• Keep it 1 page if you’re a fresher 
• Use action verbs: Analyzed, Automated, Built, Designed 
• Use numbers to show results: +%, time saved, etc.

📌 Practice Task: 
Write one resume bullet like: 
“Analyzed customer data using SQL and Power BI to find trends that increased sales by 12%.”

Double Tap ♥️ For More

🔳 Computer ✍🏻

🔰 आधारभूत शब्दावली

1⃣ कंप्यूटर (Computer) - वह इलेक्ट्रॉनिक उपकरण जो गणना, डेटा संग्रहण और प्रसंस्करण का कार्य करता है।

2⃣ हार्डवेयर (Hardware) - कंप्यूटर का भौतिक भाग जिसे आप छू सकते हैं, जैसे की सीपीयू, रैम, हार्ड डिस्क, मॉनिटर, माउस, कीबोर्ड आदि।

3⃣ सॉफ्टवेयर (Software) - कंप्यूटर को निर्देश देने वाले प्रोग्रामों का समूह। ये प्रोग्राम कंप्यूटर को कार्य करने के लिए कहते हैं।

4⃣ डेटा (Data) - कंप्यूटर में संग्रहीत सूचना या निर्देश।

5⃣ इनपुट (Input) - वह जानकारी या निर्देश जो कंप्यूटर को दिए जाते हैं, अक्सर कीबोर्ड या माउस के माध्यम से।

6⃣ आउटपुट (Output) - वह जानकारी जो कंप्यूटर द्वारा संसाधित की जाती है और फिर हमें दिखाई या दी जाती है, उदाहरण के लिए मॉनिटर पर प्रदर्शित पाठ्य या प्रिंटर पर छपा दस्तावेज़।

7⃣ यूजर (User) - वह व्यक्ति जो कंप्यूटर का उपयोग करता है।

🔰 उपकरण (Peripherals)

1⃣ मॉनिटर (Monitor) - वह स्क्रीन जिस पर कंप्यूटर द्वारा संसाधित जानकारी प्रदर्शित होती है।

2⃣ की-बोर्ड (Keyboard) - अक्षरों, संख्याओं और विशेष वर्णों को इनपुट करने के लिए इस्तेमाल किया जाने वाला उपकरण।

3⃣ माउस (Mouse) - डेस्कटॉप पर वस्तुओं को चुनने और इधर-उधर ले जाने के लिए इस्तेमाल किया जाने वाला उपकरण।

4⃣ प्रिंटर (Printer) - कागज पर आउटपुट को प्रिंट करने के लिए उपयोग किया जाने वाला उपकरण।

5⃣ स्कैनर (Scanner) - भौतिक दस्तावेजों या छवियों को कंप्यूटर में इनपुट करने के लिए उपयोग किया जाने वाला उपकरण।

🔰 सॉफ्टवेयर के प्रकार

1⃣ ऑपरेटिंग सिस्टम (Operating System) - कंप्यूटर के हार्डवेयर और सॉफ्टवेयर के बीच समन्वय स्थापित करने वाला सॉफ्टवेयर।

उदाहरण: विंडोज़ (Windows), मैक ओएस (Mac OS), लिनक्स (Linux)

2⃣ एप्लीकेशन सॉफ्टवेयर (Application Software) - विशिष्ट कार्यों को करने के लिए डिज़ाइन किया गया सॉफ्टवेयर।

उदाहरण: वर्ड प्रोसेसिंग सॉफ्टवेयर (Microsoft Word), स्प्रेडशीट सॉफ्टवेयर (Microsoft Excel), वेब ब्राउज़र (Google Chrome)

🔰 नेटवर्किंग

1⃣ नेटवर्क (Network) - आपस में जुड़े हुए कंप्यूटरों का समूह जो डेटा और संसाधनों को साझा कर सकते हैं।

2⃣ इंटरनेट (Internet) - दुनिया भर में फैला हुआ कंप्यूटर नेटवर्कों का एक विशाल नेटवर्क।

🔰 अन्य शब्द

1⃣ वेबसाइट (Website) - इंटरनेट पर स्थित सूचनाओं का एक संग्रह जो वेब पृष्ठों से मिलकर बना होता है।

2⃣ ईमेल (Email) - इलेक्ट्रॉनिक मेल, डिजिटल संदेशों को भेजने और प्राप्त करने का तरीका।

3⃣ वाई-फाई (Wi-Fi) - वायरलेस तरीके से इंटरनेट से जुड़ने की तकनीक।

✅ SHARE & SUPPORT
━━━━━━━━━━━━━━━━━━

🔰 COMPUTER MCQS 🔰

11. What is the file extension for a Microsoft Word document?

- A) .docx
- B) .txt
- C) .pdf
- D) .xls

12. Which of the following is not a type of computer network?

- A) LAN
- B) WAN
- C) MAN
- D) CPU

13. What is the main purpose of an operating system?

- A) Manage hardware resources
- B) Create documents
- C) Browse the internet
- D) Send emails

14. Which programming language is often used for game development?

- A) Python
- B) Java
- C) C++
- D) HTML

15. What does the abbreviation "HTTP" stand for?

- A) HyperText Transfer Protocol
- B) HyperText Transfer Program
- C) High Transfer Protocol
- D) HyperText Transmission Process

16. Which of the following is not a programming paradigm?

- A) Object-Oriented Programming
- B) Functional Programming
- C) Procedural Programming
- D) Internet Protocol

17. What does the term "URL" stand for in web addresses?

- A) Uniform Resource Link
- B) Uniform Resource Locator
- C) Unique Resource Link
- D) Unique Resource Locator

18. Which of the following is a popular relational database management system?

- A) MongoDB
- B) PostgreSQL
- C) Redis
- D) Cassandra

19. What does the acronym "HTML" stand for?

- A) HyperText Markup Language
- B) HyperText Machine Language
- C) High Transfer Markup Language
- D) Hyperlink Transfer Methodology Language

20. What is the purpose of an SSL certificate on a website?

- A) Secure Socket Layer
- B) Secure Sign-in Link
- C) Secure Search Locator
- D) Secure Software License

---

☑️Answers:

11. A) .docx
12. D) CPU
13. A) Manage hardware resources
14. C) C++
15. A) HyperText Transfer Protocol
16. D) Internet Protocol
17. B) Uniform Resource Locator
18. B) PostgreSQL
19. A) HyperText Markup Language
20. A) Secure Socket Layer

✅ SHARE & SUPPORT
━━━━━━━━━━━━━━━━━━━

🔰 COMPUTER 🔰

❇️ COMPUNYS & THEIR FOUNDER....✍🏻

1. Samsung के संस्थापक कौन है ?
Ans ➞ Lee Byungchul (1938)

2. Google के संस्थापक कौन है ?
Ans ➞ लैरी पेज, सर्फ ब्रिन (1998)

3. Instagram के संस्थापक कौन है ?
Ans ➞ केविन सिस्ट्रोम, माइक करिजर (2010)

4. Microsoft के संस्थापक कौन है ?
Ans ➞ बिल गेट्स, पॉल एलन (1975)

5. WhatsApp के संस्थापक कौन है ?
Ans ➞ ब्रायन ऐक्टन ,जैन कौम (2009)

6. Amazon के संस्थापक कौन है ?
Ans ➞ जैफ बेजोस (Jef bez0s) 1994

7. Flipkart के संस्थापक कौन है ?
Ans ➞ सचिन बंसल और बिन्नी बंसल (2007)

8. Yahoo के संस्थापक कौन है ?
Ans ➞ David filo, Jerry yang (1994)

9. Apple के संस्थापक कौन है ?
Ans ➞ स्टीव जॉब्स (1976)

10. Wikipedia के संस्थापक कौन है ?
Ans ➞ जिमी वेल्स (2001)

11. Motorola के संस्थापक कौन है ?
Ans ➞ Paul & Joseph Gablin (1928)

12. Facebook के संस्थापक कौन है ?
Ans ➞ मार्क जुकरबर्ग (2004)

13. Alibaba के संस्थापक कौन है ?
Ans ➞ जैक मा (1999)

14. Nokia के संस्थापक कौन है ?
Ans ➞ Fredrik Idestam, Leo Mechelin

15. Reliance के संस्थापक कौन है ?
Ans ➞ धीरूभाई अम्बानी (1997)

16. Ebay के संस्थापक कौन है ?
Ans ➞ Pierre Omidyar (1995)

17. Twitter के संस्थापक कौन है ?
Ans ➞ जैक डॉर्स (2006)

18. Walmart के संस्थापक कौन है ?
Ans ➞ सैम वॉल्टन (Sam Walton) 1962

19. YouTube के संस्थापक कौन है ?
Ans ➞ Jawed KarimStee Chen (2005)

20. Skype के संस्थापक कौन है ?
Ans ➞ Niklas Zennstrom Janus Friis (2003)

21. Tesla के संस्थापक कौन है ?
Ans ➞ एलोन मस्क (2003)

22. Intel के संस्थापक कौन है ?
Ans ➞ गॉर्डन मूरे (1968)

23. Xiaomi के संस्थापक कौन है ?
Ans ➞ Lie Jun (2010)

24. Paytm के संस्थापक कौन है ?
Ans ➞ विजय शेखर शर्मा (2010)

✅ SHARE & SUPPORT
━━━━━━━━━━━━━━━━━━━

✅ 🔤 A–Z of Full Stack Development

A – Authentication
Verifying user identity using methods like login, tokens, or biometrics.

B – Build Tools
Automate tasks like bundling, transpiling, and optimizing code (e.g., Webpack, Vite).

C – CRUD
Create, Read, Update, Delete – the core operations of most web apps.

D – Deployment
Publishing your app to a live server or cloud platform.

E – Environment Variables
Store sensitive data like API keys securely outside your codebase.

F – Frameworks
Tools that simplify development (e.g., React, Express, Django).

G – GraphQL
A query language for APIs that gives clients exactly the data they need.

H – HTTP (HyperText Transfer Protocol)
Foundation of data communication on the web.

I – Integration
Connecting different systems or services (e.g., payment gateways, APIs).

J – JWT (JSON Web Token)
Compact way to securely transmit information between parties for authentication.

K – Kubernetes
Tool for automating deployment and scaling of containerized applications.

L – Load Balancer
Distributes incoming traffic across multiple servers for better performance.

M – Middleware
Functions that run during request/response cycles in backend frameworks.

N – NPM (Node Package Manager)
Tool to manage JavaScript packages and dependencies.

O – ORM (Object-Relational Mapping)
Maps database tables to objects in code (e.g., Sequelize, Prisma).

P – PostgreSQL
Powerful open-source relational database system.

Q – Queue
Used for handling background tasks (e.g., RabbitMQ, Redis queues).

R – REST API
Architectural style for designing networked applications using HTTP.

S – Sessions
Store user data across multiple requests (e.g., login sessions).

T – Testing
Ensures your code works as expected (e.g., Jest, Mocha, Cypress).

U – UX (User Experience)
Designing intuitive and enjoyable user interactions.

V – Version Control
Track and manage code changes (e.g., Git, GitHub).

W – WebSockets
Enable real-time communication between client and server.

X – XSS (Cross-Site Scripting)
Security vulnerability where attackers inject malicious scripts into web pages.

Y – YAML
Human-readable data format often used for configuration files.

Z – Zero Downtime Deployment
Deploy updates without interrupting the running application.

💬 Double Tap ❤️ for more!

Keyboard Shortcut Keys

Ctrl+A - Select All
Ctrl+B - Bold
Ctrl+C - Copy
Ctrl+D - Fill Down
Ctrl+F - Find
Ctrl+G - Goto
Ctrl+H - Replace
Ctrl+I - Italic
Ctrl+K - Insert Hyperlink
Ctrl+N - New Workbook
Ctrl+O - Open
Ctrl+P - Print
Ctrl+R - Fill Right
Ctrl+S - Save
Ctrl+U - Underline
Ctrl+V - Paste
Ctrl W - Close
Ctrl+X - Cut
Ctrl+Y - Repeat
Ctrl+Z - Undo
F1 - Help
F2 - Edit
F3 - Paste Name
F4 - Repeat last action
F4 - While typing a formula, switch between absolute/relative refs
F5 - Goto
F6 - Next Pane
F7 - Spell check
F8 - Extend mode
F9 - Recalculate all workbooks
F10 - Activate Menu bar
F11 - New Chart
F12 - Save As
Ctrl+: - Insert Current Time
Ctrl+; - Insert Current Date
Ctrl+" - Copy Value from Cell Above
Ctrl+’ - Copy Formula from Cell Above
Shift - Hold down shift for additional functions in Excel’s menu
Shift+F1 - What’s This?
Shift+F2 - Edit cell comment
Shift+F3 - Paste function into formula
Shift+F4 - Find Next
Shift+F5 - Find
Shift+F6 - Previous Pane
Shift+F8 - Add to selection
Shift+F9 - Calculate active worksheet
Shift+F10 - Display shortcut menu
Shift+F11 - New worksheet
Ctrl+F3 - Define name
Ctrl+F4 - Close
Ctrl+F5 - XL, Restore window size
Ctrl+F6 - Next workbook window
Shift+Ctrl+F6 - Previous workbook window
Ctrl+F7 - Move window
Ctrl+F8 - Resize window
Ctrl+F9 - Minimize workbook
Ctrl+F10 - Maximize or restore window
Ctrl+F11 - Inset 4.0 Macro sheet
Ctrl+F1 - File Open
Alt+F1 - Insert Chart
Alt+F2 - Save As
Alt+F4 - Exit
Alt+Down arrow - Display AutoComplete list
Alt+’ - Format Style dialog box
Ctrl+Shift+~ - General format
Ctrl+Shift+! - Comma format
Ctrl+Shift+@ - Time format
Ctrl+Shift+# - Date format
Ctrl+Shift+$ - Currency format
Ctrl+Shift+% - Percent format
Ctrl+Shift+^ - Exponential format
Ctrl+Shift+& - Place outline border around selected cells
Ctrl+Shift+_ - Remove outline border
Ctrl+Shift+* - Select current region
Ctrl++ - Insert
Ctrl+- - Delete
Ctrl+1 - Format cells dialog box
Ctrl+2 - Bold
Ctrl+3 - Italic
Ctrl+4 - Underline
Ctrl+5 - Strikethrough
Ctrl+6 - Show/Hide objects
Ctrl+7 - Show/Hide Standard toolbar
Ctrl+8 - Toggle Outline symbols
Ctrl+9 - Hide rows
Ctrl+0 - Hide columns
Ctrl+Shift+( - Unhide rows
Ctrl+Shift+) - Unhide columns
Alt or F10 - Activate the menu
Ctrl+Tab - In toolbar: next toolbar
Shift+Ctrl+Tab - In toolbar: previous toolbar
Ctrl+Tab - In a workbook: activate next workbook
Shift+Ctrl+Tab - In a workbook: activate previous workbook
Tab - Next tool
Shift+Tab - Previous tool
Enter - Do the command
Shift+Ctrl+F - Font Drop down List
Shift+Ctrl+F+F - Font tab of Format Cell Dialog box
Shift+Ctrl+P - Point size Drop down List
Ctrl + E - Align center
Ctrl + J - justify
Ctrl + L - align 
Ctrl + R - align right
Alt + Tab - switch applications
Windows + P - Project screen
Windows + E - open file explorer
Windows + D - go to desktop
Windows + M - minimize all windows
Windows + S - search

Important Excel, Tableau, Statistics, SQL related Questions with answers

1. What are the common problems that data analysts encounter during analysis?

The common problems steps involved in any analytics project are:

Handling duplicate data
Collecting the meaningful right data at the right time
Handling data purging and storage problems
Making data secure and dealing with compliance issues

2. Explain the Type I and Type II errors in Statistics?

In Hypothesis testing, a Type I error occurs when the null hypothesis is rejected even if it is true. It is also known as a false positive.

A Type II error occurs when the null hypothesis is not rejected, even if it is false. It is also known as a false negative.

3. How do you make a dropdown list in MS Excel?

First, click on the Data tab that is present in the ribbon.
Under the Data Tools group, select Data Validation.
Then navigate to Settings > Allow > List.
Select the source you want to provide as a list array.

4. How do you subset or filter data in SQL?

To subset or filter data in SQL, we use WHERE and HAVING clauses which give us an option of including only the data matching certain conditions.

5. What is a Gantt Chart in Tableau?

A Gantt chart in Tableau depicts the progress of value over the period, i.e., it shows the duration of events. It consists of bars along with the time axis. The Gantt chart is mostly used as a project management tool where each bar is a measure of a task in the project

50 essential Excel formulas

SUM: =SUM(A1:A5)
AVERAGE: =AVERAGE(A1:A10)
VLOOKUP: =VLOOKUP(B1, A2:D10, 3, FALSE)
IF: =IF(A1 > 10, "Yes", "No")
CONCATENATE (or CONCAT): =CONCATENATE(A1, " ", B1)
COUNT: =COUNT(A1:A10)
MAX: =MAX(A1:A10)
MIN: =MIN(A1:A10)
ROUND: =ROUND(A1, 2)
TRIM: =TRIM(A1)
LOWER: =LOWER(A1)
UPPER: =UPPER(A1)
LEFT: =LEFT(A1, 5)
RIGHT: =RIGHT(A1, 5)
MID: =MID(A1, 2, 3)
LEN: =LEN(A1)
FIND: =FIND("search_text", A1)
REPLACE: =REPLACE(A1, 3, 2, "new_text")
SUBSTITUTE: =SUBSTITUTE(A1, "old_text", "new_text")
INDEX: =INDEX(A1:A10, 3)
MATCH: =MATCH(B1, A1:A10, 0)
OFFSET: =OFFSET(A1, 1, 2)
SUMIF: =SUMIF(A1:A10, ">5")
COUNTIF: =COUNTIF(A1:A10, "apple")
AVERAGEIF: =AVERAGEIF(A1:A10, "<>0")
SUMIFS: =SUMIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
COUNTIFS: =COUNTIFS(A1:A10, ">5", B1:B10, "apple")
AVERAGEIFS: =AVERAGEIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
IFERROR: =IFERROR(A1/B1, "Error")
AND: =AND(A1>5, A1<10)
OR: =OR(A1="apple", A1="banana")
NOT: =NOT(A1="apple")
DATE: =DATE(2022, 12, 31)
TODAY: =TODAY()
NOW: =NOW()
DATEDIF: =DATEDIF(A1, A2, "D")
YEAR: =YEAR(A1)
MONTH: =MONTH(A1)
DAY: =DAY(A1)
EOMONTH: =EOMONTH(A1, 3)
NETWORKDAYS: =NETWORKDAYS(A1, A2)
WEEKDAY: =WEEKDAY(A1)
HLOOKUP: =HLOOKUP(B1, A1:D10, 3, FALSE)
MATCH: =MATCH(B1, A1:A10, 0)
INDEX-MATCH: =INDEX(A1:A10, MATCH(B1, C1:C10, 0))
TRANSPOSE: =TRANSPOSE(A1:D10)
PIVOT TABLE: =PIVOT_TABLE(A1:D10, "Sales", "Region", "Sum")
RANK: =RANK(A1, A1:A10, 1)
RAND: =RAND()
CHOOSE: =CHOOSE(B1, "Option 1", "Option 2", "Option 3")

Must important topics to look before any excel interview for Data/Business Analyst role :-

Data Handling: Cell formatting, rows/columns, basic functions (SUM, AVERAGE, COUNT etc).

Data Management Mastery: Sorting, filtering, data validation, diverse cell references. Function Proficiency: Explore SUMIF, (V & X)LOOKUP, INDEX, MATCH, IF, and advanced function nesting.

Advanced Analytics: Master PivotTables for dynamic data analysis and various chart creation.

Advanced Analysis Techniques: Conditional formatting, goal-seeking, in-depth what-if analysis.

Advanced Functions: COUNTIF/IFS, SUMIFS, AVERAGEIF/IFS, CONCATENATE, date/time functions.

These are the most important one's which I tried to summarise in the best possible way, please let me know in the comments if I have missed something important.

Excel Scenario-Based Questions Interview Questions and Answers :


Scenario 1) Imagine you have a dataset with missing values. How would you approach this problem in Excel?

Answer:

To handle missing values in Excel:

1. Identify Missing Data:

Use filters to quickly find blank cells.

Apply conditional formatting:
Home → Conditional Formatting → New Rule → Format only cells that are blank.


2. Handle Missing Data:

Delete rows with missing critical data (if appropriate).

Fill missing values:

Use =IF(A2="", "N/A", A2) to replace blanks with “N/A”.

Use Fill Down (Ctrl + D) if the previous value applies.

Use functions like =AVERAGEIF(range, "<>", range) to fill with average.


3. Use Power Query (for large datasets):

Load data into Power Query and use “Replace Values” or “Remove Empty” options.

Scenario 2) You are given a dataset with multiple sheets. How would you consolidate the data for analysis?

Answer:

Approach 1: Manual Consolidation

1. Use Copy-Paste from each sheet into a master sheet.
2. Add a new column to identify the source sheet (optional but useful).
3. Convert the master data into a table for analysis.



Approach 2: Use Power Query (Recommended for large datasets)

1. Go to Data → Get & Transform → Get Data → From Workbook.
2. Load each sheet into Power Query.
3. Use the Append Queries option to merge all sheets.


4. Clean and transform as needed, then load it back to Excel.

Approach 3: Use VBA (Advanced Users)

Write a macro to loop through all sheets and append data to a master sheet.

Hope it helps :)

Keyboard Shortcut Keys

Ctrl+A - Select All
Ctrl+B - Bold
Ctrl+C - Copy
Ctrl+D - Fill Down
Ctrl+F - Find
Ctrl+G - Goto
Ctrl+H - Replace
Ctrl+I - Italic
Ctrl+K - Insert Hyperlink
Ctrl+N - New Workbook
Ctrl+O - Open
Ctrl+P - Print
Ctrl+R - Fill Right
Ctrl+S - Save
Ctrl+U - Underline
Ctrl+V - Paste
Ctrl W - Close
Ctrl+X - Cut
Ctrl+Y - Repeat
Ctrl+Z - Undo
F1 - Help
F2 - Edit
F3 - Paste Name
F4 - Repeat last action
F4 - While typing a formula, switch between absolute/relative refs
F5 - Goto
F6 - Next Pane
F7 - Spell check
F8 - Extend mode
F9 - Recalculate all workbooks
F10 - Activate Menu bar
F11 - New Chart
F12 - Save As
Ctrl+: - Insert Current Time
Ctrl+; - Insert Current Date
Ctrl+" - Copy Value from Cell Above
Ctrl+’ - Copy Formula from Cell Above
Shift - Hold down shift for additional functions in Excel’s menu
Shift+F1 - What’s This?
Shift+F2 - Edit cell comment
Shift+F3 - Paste function into formula
Shift+F4 - Find Next
Shift+F5 - Find
Shift+F6 - Previous Pane
Shift+F8 - Add to selection
Shift+F9 - Calculate active worksheet
Shift+F10 - Display shortcut menu
Shift+F11 - New worksheet
Ctrl+F3 - Define name
Ctrl+F4 - Close
Ctrl+F5 - XL, Restore window size
Ctrl+F6 - Next workbook window
Shift+Ctrl+F6 - Previous workbook window
Ctrl+F7 - Move window
Ctrl+F8 - Resize window
Ctrl+F9 - Minimize workbook
Ctrl+F10 - Maximize or restore window
Ctrl+F11 - Inset 4.0 Macro sheet
Ctrl+F1 - File Open
Alt+F1 - Insert Chart
Alt+F2 - Save As
Alt+F4 - Exit
Alt+Down arrow - Display AutoComplete list
Alt+’ - Format Style dialog box
Ctrl+Shift+~ - General format
Ctrl+Shift+! - Comma format
Ctrl+Shift+@ - Time format
Ctrl+Shift+# - Date format
Ctrl+Shift+$ - Currency format
Ctrl+Shift+% - Percent format
Ctrl+Shift+^ - Exponential format
Ctrl+Shift+& - Place outline border around selected cells
Ctrl+Shift+_ - Remove outline border
Ctrl+Shift+* - Select current region
Ctrl++ - Insert
Ctrl+- - Delete
Ctrl+1 - Format cells dialog box
Ctrl+2 - Bold
Ctrl+3 - Italic
Ctrl+4 - Underline
Ctrl+5 - Strikethrough
Ctrl+6 - Show/Hide objects
Ctrl+7 - Show/Hide Standard toolbar
Ctrl+8 - Toggle Outline symbols
Ctrl+9 - Hide rows
Ctrl+0 - Hide columns
Ctrl+Shift+( - Unhide rows
Ctrl+Shift+) - Unhide columns
Alt or F10 - Activate the menu
Ctrl+Tab - In toolbar: next toolbar
Shift+Ctrl+Tab - In toolbar: previous toolbar
Ctrl+Tab - In a workbook: activate next workbook
Shift+Ctrl+Tab - In a workbook: activate previous workbook
Tab - Next tool
Shift+Tab - Previous tool
Enter - Do the command
Shift+Ctrl+F - Font Drop down List
Shift+Ctrl+F+F - Font tab of Format Cell Dialog box
Shift+Ctrl+P - Point size Drop down List
Ctrl + E - Align center
Ctrl + J - justify
Ctrl + L - align 
Ctrl + R - align right
Alt + Tab - switch applications
Windows + P - Project screen
Windows + E - open file explorer
Windows + D - go to desktop
Windows + M - minimize all windows
Windows + S - search

✅ Data Analyst Mistakes Beginners Should Avoid ⚠️📊

1️⃣ Ignoring Data Cleaning
• Jumping to charts too soon
• Overlooking missing or incorrect data
✅ Clean before you analyze — always

2️⃣ Not Practicing SQL Enough
• Stuck on simple joins or filters
• Can’t handle large datasets
✅ Practice SQL daily — it's your #1 tool

3️⃣ Overusing Excel Only
• Limited automation
• Hard to scale with large data
✅ Learn Python or SQL for bigger tasks

4️⃣ No Real-World Projects
• Watching tutorials only
• Resume has no proof of skills
✅ Analyze real datasets and publish your work

5️⃣ Ignoring Business Context
• Insights without meaning
• Metrics without impact
✅ Understand the why behind the data

6️⃣ Weak Data Visualization Skills
• Crowded charts
• Wrong chart types
✅ Use clean, simple, and clear visuals (Power BI, Tableau, etc.)

7️⃣ Not Tracking Metrics Over Time
• Only point-in-time analysis
• No trends or comparisons
✅ Use time-based metrics for better insight

8️⃣ Avoiding Git & Version Control
• No backup
• Difficult collaboration
✅ Learn Git to track and share your work

9️⃣ No Communication Focus
• Great analysis, poorly explained
✅ Practice writing insights clearly & presenting dashboards

🔟 Ignoring Data Privacy
• Sharing raw data carelessly
✅ Always anonymize and protect sensitive info

💡 Master tools + think like a problem solver — that's how analysts grow fast.

💬 Tap ❤️ for more!

🚀 Top 100 Excel Interview Questions

🧠 Excel Basics & UI

1. What is Microsoft Excel and what is it mainly used for?
2. What is the difference between a workbook and a worksheet?
3. What is a cell, row, and column?
4. How do you rename, insert, or delete a worksheet?
5. How do you select a range, entire row, or entire column?
6. How do you copy, paste, and cut data?
7. How do you use Zoom, Freeze Panes, and Split Window?
8. How do you hide/unhide rows and columns?
9. How do you insert/delete rows and columns without breaking formulas?
10. How do you save, open, and share an Excel file (including via OneDrive/SharePoint)?

📊 Formatting & Data Entry

11. How do you format numbers as currency, percentage, or date?
12. How do you wrap text and merge cells?
13. How do you resize columns and rows to fit content?
14. How do you apply bold, italic, borders, and background colors?
15. How do you AutoFit column width and row height?
16. How do you print and set page layout (margins, gridlines, headers/footers)?
17. How do you protect a sheet or workbook with a password?
18. How do you use “Format as Table” and why is it useful?
19. How do you use conditional formatting (highlight cells, data bars, color scales, icons)?
20. How do you apply custom number formats?

🧮 Formulas & Functions (Beginner)

21. How do you write a basic arithmetic formula starting with =?
22. What are relative, absolute, and mixed references (A1, $A$1, A$1)?
23. How do you use SUM, AVERAGE, MIN, MAX, COUNT, COUNTA?
24. How do you calculate percentage and percentage change?
25. How do you use ROUND, ROUNDUP, and ROUNDDOWN?
26. How do you calculate current date and time (TODAY, NOW)?
27. How do you calculate the difference between two dates?
28. How do you use IF for simple logical conditions?
29. How do you handle errors using IFERROR or IFNA?
30. How do you nest multiple IF statements?

🔍 Lookups, Logic & Math

31. How do you use VLOOKUP and what are its limitations?
32. How do you use HLOOKUP?
33. How do you use INDEX + MATCH for flexible lookups?
34. How do you use XLOOKUP (if available)?
35. How do you use IF with AND / OR conditions?
36. How do you use SUMIF, SUMIFS for conditional sums?
37. How do you use COUNTIF, COUNTIFS for conditional counts?
38. How do you use AVERAGEIF, AVERAGEIFS?
39. How do you use COUNTBLANK, COUNTUNIQUE‑style techniques?
40. How do you use SUMPRODUCT for weighted calculations?

🗓️ Date, Time & Text Functions

41. How do you extract YEAR, MONTH, DAY from a date?
42. How do you calculate days between two dates?
43. How do you calculate workdays between two dates (NETWORKDAYS)?
44. How do you use DATEDIF for years/months/days?
45. How do you use TRIM to remove extra spaces?
46. How do you join text from multiple cells (CONCAT, &, CONCATENATE)?
47. How do you split text with TEXTSPLIT or LEFT/RIGHT/MID?
48. How do you find text inside a string (FIND, SEARCH)?
49. How do you replace or remove part of a string (SUBSTITUTE, REPLACE)?
50. How do you change case (UPPER, LOWER, PROPER)?

📊 Data Analysis & Conditional Formatting

51. How do you sort data by one or multiple columns?
52. How do you use AutoFilter and custom filters?
53. How do you filter by blanks, non‑blanks, or specific values?
54. How do you remove duplicates from a list?
55. How do you use conditional formatting with formulas?
56. How do you highlight top/bottom N values?
57. How do you standardize and clean messy data (e.g., “Yes / yes / Y” → “Yes”)?
58. How do you use Text‑to‑Columns and Flash Fill for quick cleaning?
59. How do you create subtotals and grouped views?
60. How do you build a simple KPI or summary table?