MS Excel for Data Analysis

Excel Shortcut & Functions 🤍

🚀𝗠𝗔𝗦𝗧𝗘𝗥 𝗘𝗫𝗖𝗘𝗟 𝗙𝗢𝗥 𝗙𝗥𝗘𝗘 𝗜𝗡 𝟮𝟬𝟮𝟲! 📊 Discover 4 FREE Websites to learn Microsoft Excel from beginner to advanced level! 🎓 🔥 FREE Learning Resources 🔥 Beginner to Advanced Tutorials 🔥 Practical Examples & Exercises 🔥 Learn Anytime, Anywhere 🔗 𝗟𝗶𝗻𝗸👇:- https://pdlink.in/4jDfcCP ⚡ One powerful skill can open doors to multiple career opportunities!

📊 Excel Interview Questions – Part 7 1️⃣ What is the difference between SUM and SUBTOTAL? 👉 "SUM" calculates the total of a range, while "SUBTOTAL" can perform calculations while ignoring filtered-out rows. Example: "=SUBTOTAL(9,A2:A100)" 2️⃣ What is the use of IFERROR in Excel? 👉 "IFERROR" replaces an error with a value or message you specify. Example: "=IFERROR(A2/B2,0)" If the calculation produces an error, it returns "0". 3️⃣ How do you calculate the percentage change between two values? 👉 Use: "=(New Value-Old Value)/Old Value" Then format the result as a percentage. 4️⃣ How do you calculate an employee's age from their date of birth? 👉 You can use: "=DATEDIF(A2,TODAY(),"Y")" This returns the completed number of years. 5️⃣ How do you extract the year, month, or day from a date? 👉 Use: "=YEAR(A2)" "=MONTH(A2)" "=DAY(A2)" 6️⃣ How do you calculate working days between two dates? 👉 Use: "=NETWORKDAYS(A2,B2)" You can also provide a holiday range to exclude holidays. 7️⃣ How do you create a dropdown list in Excel? 👉 Select the cells → Data → Data Validation → List → enter the allowed values. 8️⃣ How do you highlight values greater than 10,000? 👉 Select the range → Conditional Formatting → Highlight Cells Rules → Greater Than → enter "10000". 9️⃣ How do you calculate a running total? 👉 If sales are in column B: "=SUM(B2:B2)" Copy the formula downward. 🔟 How do you calculate the percentage contribution of each product to total sales? 👉 Use: "=B2/SUM(B2:B100)" Then format the result as a percentage. 1️⃣1️⃣ How do you find the highest and lowest values in a dataset? 👉 Use: "=MAX(A2:A100)" and "=MIN(A2:A100)" 1️⃣2️⃣ How do you find the second-highest value? 👉 Use: "=LARGE(A2:A100,2)" 1️⃣3️⃣ How do you count employees whose salary is greater than ₹50,000? 👉 Use: "=COUNTIF(B2:B100,">50000")" 1️⃣4️⃣ How do you calculate the average salary for a particular department? 👉 Use: "=AVERAGEIF(A2:A100,"Finance",B2:B100)" 1️⃣5️⃣ What would you do if Excel shows ###### in a cell? 👉 It usually means the column is too narrow to display the value. 💡 Simply increase the column width or use AutoFit Column Width. 💯 Double Tap ❤️ For More

📊 Excel Interview Questions – Part 6 1️⃣ What is the difference between a workbook and a worksheet? 👉 A workbook is the entire Excel file, while a worksheet is an individual sheet inside the workbook. 2️⃣ What is the difference between a row and a column? 👉 Rows run horizontally and are identified by numbers (1, 2, 3…), while columns run vertically and are identified by letters (A, B, C…). 3️⃣ What is the difference between sorting and filtering? 👉 Sorting rearranges the data based on a selected field. 👉 Filtering temporarily displays only the records that meet specific conditions. 4️⃣ How do you remove duplicate records in Excel? 👉 Select the dataset → Data → Remove Duplicates → select the columns to check → click OK. 5️⃣ How can you identify duplicate values without deleting them? 👉 Use Conditional Formatting → Highlight Cells Rules → Duplicate Values. 6️⃣ What is Conditional Formatting? 👉 It automatically changes the formatting of cells based on specified conditions. 💡 Example: Highlight sales below ₹10,000 in red. 7️⃣ What is Data Validation? 👉 Data Validation controls what users can enter into a cell. 💡 Example: Create a dropdown containing: "Male, Female, Other" 8️⃣ What is the difference between COUNT, COUNTA and COUNTBLANK? 👉 "COUNT" → counts cells containing numbers. 👉 "COUNTA" → counts non-empty cells. 👉 "COUNTBLANK" → counts empty cells. 9️⃣ What is the difference between formulas and functions? 👉 A formula is an expression created by the user. Example: "=A2+B2" 👉 A function is a predefined Excel operation. Example: "=SUM(A2:A10)" 🔟 What is the purpose of the $ symbol in Excel? 👉 "$" is used to create an absolute reference. Example: "=$A$1" When the formula is copied, the reference remains fixed. 1️⃣1️⃣ What is Freeze Panes used for? 👉 It keeps selected rows or columns visible while scrolling through a large dataset. 💡 Example: Freeze the header row so column names remain visible. 1️⃣2️⃣ What is the difference between CONCAT and TEXTJOIN? 👉 "CONCAT" combines text from multiple cells. 👉 "TEXTJOIN" combines text using a specified delimiter. Example: "=TEXTJOIN(", ",TRUE,A2:C2)" Result: "Delhi, Mumbai, Pune" 1️⃣3️⃣ How do you split data from one column into multiple columns? 👉 Use Text to Columns. 💡 Example: "John,25,India" can be split into separate columns using comma as the delimiter. 1️⃣4️⃣ How do you find blank cells in a dataset? 👉 Use Go To Special → Blanks or apply a filter for blank values. 1️⃣5️⃣ How would you clean a messy Excel dataset? 👉 A basic approach is: • Remove unnecessary duplicates • Handle blank values • Remove extra spaces • Standardize formats • Correct inconsistent values • Check data types • Identify errors • Validate the final dataset 💡 Double Tap ❤️ For More ----- 2.54 ₽ · /balance_help

𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀 📊 Build a strong foundation in Data Science .Explore Machine Learning and Deep Learning Eligibility :- Students • Freshers • Working Professionals 📅 Date:- 23 October 2026 ⏰ Time:- 7:00 PM–9:00 PM IST 💻 Mode:- Online 🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :- https://pdlink.in/4i8MKGn 📢 Share this opportunity with your friends and classmates.

𝗠𝗜𝗖𝗥𝗢𝗦𝗢𝗙𝗧 𝗙𝗥𝗘𝗘 𝗗𝗔𝗧𝗔 𝗦𝗖𝗜𝗘𝗡𝗖𝗘 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓 Microsoft offers valuable FREE learning resources to help you develop in-demand technical skills! 💻 ✅ 100% FREE Learning Resources ✅ Beginner-Friendly Tutorials ✅ Self-Paced Learning ✅ Practical Skills for Real-World Projects ✅ Useful for Data Analytics & Data Science Career Preparation 🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗔𝗟𝗟 𝟰 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 👇:- https://pdlink.in/4k6ylNq ⚡ Your Data Science journey can start today — completely FREE!

𝗠𝗜𝗖𝗥𝗢𝗦𝗢𝗙𝗧 𝗙𝗥𝗘𝗘 𝗗𝗔𝗧𝗔 𝗦𝗖𝗜𝗘𝗡𝗖𝗘 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓 Microsoft offers valuable FREE learning resources to help you develop in-demand technical skills! 💻 ✅ 100% FREE Learning Resources ✅ Beginner-Friendly Tutorials ✅ Self-Paced Learning ✅ Practical Skills for Real-World Projects ✅ Useful for Data Analytics & Data Science Career Preparation 🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗔𝗟𝗟 𝟰 𝗙𝗥𝗘𝗘 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀 👇:- https://pdlink.in/4k6ylNq ⚡ Your Data Science journey can start today — completely FREE!

In modern Excel, XLOOKUP is generally preferred because it can handle both vertical and horizontal lookups. 8️⃣ What is a PivotTable and why is it useful? Sample Answer: A PivotTable summarizes large datasets quickly without requiring complex formulas. For example, I can analyze sales by: → Region → Product → Month → Salesperson It is useful for quickly analyzing and reporting large datasets. 9️⃣ What are Rows, Columns, Values, and Filters in a PivotTable? Sample Answer: Rows → Categories displayed vertically. Columns → Categories displayed horizontally. Values → Calculations such as Sum, Count, or Average. Filters → Filters the PivotTable based on selected criteria. 🔟 How would you calculate total sales for each region using a PivotTable? Sample Answer: I would place Region in the Rows area and Sales in the Values area. Excel would then summarize the sales for each region automatically. 1️⃣1️⃣ How would you show monthly sales in a PivotTable? Sample Answer: I would place the transaction date in the Rows area and Sales in Values. Then I would group the dates by Months or Months + Years, depending on the requirement. If multiple years are present, grouping by both month and year avoids combining January from different years. 1️⃣2️⃣ What is the difference between a PivotTable and SUMIFS? Sample Answer: SUMIFS is useful when I need a specific calculation based on predefined criteria. A PivotTable is better when I want to explore and summarize data interactively. For example: SUMIFS → Calculate sales for the East region. PivotTable → Analyze sales by region, product, month, and salesperson. 1️⃣3️⃣ What are Slicers? Sample Answer: Slicers are visual filters that allow users to interactively filter Tables or PivotTables. For example, a dashboard could have slicers for: Region | Product | Year | Salesperson 1️⃣4️⃣ How would you find the top 5 products by sales? Sample Answer: I could create a PivotTable with Product in Rows and Sales in Values, sort the sales from largest to smallest, and apply a Top 5 Value Filter. In modern Excel, I could also use functions such as SORT, TAKE, and FILTER. 1️⃣5️⃣ A lookup is returning the wrong result. What would you check? Sample Answer: I would check: → Exact vs approximate match → Extra spaces or hidden characters → Data types → Duplicate lookup values → Lookup and return ranges → Incorrect cell references → Whether the correct sheet or dataset is being used I would first identify the cause of the incorrect result instead of simply changing the formula. 🎯 Double Tap ❤️ For More

📊 Excel Interview Questions — Part 5 1️⃣ What is XLOOKUP and how is it different from VLOOKUP? Sample Answer: XLOOKUP searches for a value in one range and returns the corresponding value from another range.

=XLOOKUP(E2,A2:A100,C2:C100,"Not Found")

Unlike VLOOKUP, XLOOKUP: → Can look left or right → Uses exact match by default → Doesn't require a column index number → Allows a custom not-found result → Can return multiple columns 2️⃣ What is the difference between exact and approximate match? Sample Answer: An exact match searches for the precise value.

=VLOOKUP(E2,A2:C100,3,FALSE)

An approximate match searches for the closest appropriate value. TRUE is commonly used for ranges such as tax slabs, commission rates, or grading bands. The lookup column should generally be sorted for reliable approximate matching. 3️⃣ Can VLOOKUP look to the left? Sample Answer: No. Standard VLOOKUP searches in the first column of the selected table and returns a value from a column to its right. For a left lookup, I could use XLOOKUP:

=XLOOKUP(E2,C2:C100,A2:A100)

Or INDEX + MATCH:

=INDEX(A2:A100,MATCH(E2,C2:C100,0))

4️⃣ What happens when XLOOKUP doesn't find a value? Sample Answer: By default, XLOOKUP returns #N/A. I can provide a custom result:

=XLOOKUP(E2,A2:A100,C2:C100,"Not Found")

This makes reports more user-friendly. 5️⃣ How would you perform a lookup using two conditions? Sample Answer: I can combine two conditions inside XLOOKUP:

=XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),C2:C100,"Not Found")

The formula returns the value where both conditions are satisfied. 6️⃣ What is INDEX + MATCH? Sample Answer: MATCH finds the position of a value, while INDEX returns the value at that position.

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

It provides a flexible lookup method and can perform lookups that traditional VLOOKUP cannot. 7️⃣ What is HLOOKUP? Sample Answer: HLOOKUP searches for a value horizontally in the first row and returns a value from a specified row.

=HLOOKUP(B1,B1:F5,4,FALSE)

𝗧𝗔𝗧𝗔 𝗙𝗥𝗘𝗘 𝗠𝗜𝗖𝗥𝗢-𝗜𝗡𝗧𝗘𝗥𝗡𝗦𝗛𝗜𝗣𝗦 𝟮𝟬𝟮𝟲! 🎓 ✅ 100% FREE Learning Experience ✅ Short Programs — Approximately 4–6 Hours ✅ Practical, Real-World Tasks ✅ Self-Paced & Online ✅ Certificate Opportunities for Your Resume & LinkedIn ✅ No Traditional Interview Process 🔗 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝗧𝗔𝗧𝗔 𝗙𝗥𝗘𝗘 𝗠𝗶𝗰𝗿𝗼-𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽𝘀👇:- https://pdlink.in/4yWgaPn ⚡ Don't wait for your first job to start gaining practical experience!

𝟰 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗧𝗼 𝗠𝗮𝘀𝘁𝗲𝗿 𝗖𝗼𝗱𝗶𝗻𝗴 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘😍 ✅ 100% FREE YouTube Learning ✅ Beginner-Friendly Programming Tutorials ✅ Learn Popular & In-Demand Tech Skills ✅ Study at Your Own Pace ✅ Build a Strong Foundation for Coding Interviews 🔗 𝗔𝗰𝗰𝗲𝘀𝘀 𝗔𝗟𝗟 𝟰 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 👇:- https://pdlink.in/3WcuMvi ⚡ Your coding journey can start today—completely FREE!

For large or recurring comparisons, Power Query can be a better approach. 🔟 A formula is returning the wrong result. How would you troubleshoot it? Sample Answer: I would troubleshoot systematically rather than changing the formula randomly. I would check: 1. Cell references 2. Absolute and relative references 3. Data types 4. Extra spaces 5. Exact vs approximate matching 6. Hidden characters 7. Error values 8. Formula logic 9. Range boundaries I can also use Excel's formula auditing tools and evaluate the formula step by step. 🎯 Double Tap ❤️ For More

📊 Excel Interview Questions — Part 4 This part focuses on advanced formulas, dynamic arrays, lookup problems, and tricky interview scenarios. 1️⃣ What happens if VLOOKUP finds multiple matches? Sample Answer: VLOOKUP returns only the first matching result it finds. If multiple records have the same lookup value, it will not return all matches. If I need multiple matching records in modern Excel, I can use:

=FILTER(B2:D100,A2:A100=G2)

This returns all matching rows. 2️⃣ How would you perform a lookup using multiple conditions? Sample Answer: With XLOOKUP, I can combine multiple conditions. Example:

=XLOOKUP(1,(A2:A100=G2)**(B2:B100=H2),C2:C100,"Not Found")

Here, both conditions must be TRUE. For example: Region = East Product = Laptop The formula returns the value matching both conditions. 3️⃣ What is a dynamic array in Excel? Sample Answer: A dynamic array formula can automatically return multiple results into neighboring cells. For example:

=FILTER(A2:C100,B2:B100="East")

The results automatically spill into the required cells. Functions such as: • FILTER • SORT • UNIQUE • SEQUENCE make dynamic-array analysis much easier. 4️⃣ What is the #SPILL! error? Sample Answer: SPILL! occurs when a dynamic-array formula wants to return multiple values but the cells where the results need to appear are blocked. For example:

=UNIQUE(A2:A100)

If another value exists in the cells where the results need to spill, Excel may return #SPILL!. I would check and clear the cells blocking the spill range. 5️⃣ How would you find the position of a value in a dataset? Sample Answer: I can use the MATCH function.

=MATCH("Laptop",A2:A100,0)

The 0 specifies an exact match. If I want both the position and corresponding value, I can combine MATCH with INDEX. 6️⃣ Why would you use INDEX + MATCH instead of VLOOKUP? Sample Answer: INDEX + MATCH provides more flexibility. For example, the lookup column doesn't need to be the first column of the lookup range, and I can return values from either direction. Example:

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

However, in modern Excel, I would generally consider XLOOKUP first because it is simpler and more flexible. 7️⃣ How would you calculate a running total? Sample Answer: If sales are in column B, I could use:

=SUM($B$2:B2)

Then copy the formula downward. The first reference remains fixed while the second reference expands as the formula moves down. This produces a cumulative total. 8️⃣ How would you calculate a percentage contribution to total sales? Sample Answer: If sales are in B2:B100:

=B2/SUM($B$2:$B$100)

Then format the result as a percentage. The $ ensures that the total-sales range remains fixed when the formula is copied down. 9️⃣ How would you compare two Excel datasets and find missing records? Sample Answer: I could use XLOOKUP to check whether a value from one dataset exists in another. Example:

=IF(ISNA(XLOOKUP(A2,Sheet2!A:A,Sheet2!A:A)),"Missing","Found")

Alternatively, I could use COUNTIF:

=IF(COUNTIF(Sheet2!A:A,A2)=0,"Missing","Found")

For large or recurring comparisons, Power Query can be a better approach. 🔟 A formula is returning the wrong result. How would you troubleshoot it? Sample Answer: I would troubleshoot systematically rather than changing the formula randomly. I would check: 1. Cell references 2. Absolute and relative references 3. Data types 4. Extra spaces 5. Exact vs approximate matching 6. Hidden characters 7. Error values 8. Formula logic 9. Range boundaries I can also use Excel's formula auditing tools and evaluate the formula step by step. 🎯 Double Tap ❤️ For More ----- 1.42 ₽ · /balance_help

📊 Excel Interview Questions — Part 4 This part focuses on advanced formulas, dynamic arrays, lookup problems, and tricky interview scenarios. 1️⃣ What happens if VLOOKUP finds multiple matches? Sample Answer: VLOOKUP returns only the first matching result it finds. If multiple records have the same lookup value, it will not return all matches. If I need multiple matching records in modern Excel, I can use:

=FILTER(B2:D100,A2:A100=G2)

This returns all matching rows. 2️⃣ How would you perform a lookup using multiple conditions? Sample Answer: With XLOOKUP, I can combine multiple conditions. Example:

=XLOOKUP(1,(A2:A100=G2)**(B2:B100=H2),C2:C100,"Not Found")

Here, both conditions must be TRUE. For example: Region = East Product = Laptop The formula returns the value matching both conditions. 3️⃣ What is a dynamic array in Excel? Sample Answer: A dynamic array formula can automatically return multiple results into neighboring cells. For example:

=FILTER(A2:C100,B2:B100="East")

The results automatically spill into the required cells. Functions such as: • FILTER • SORT • UNIQUE • SEQUENCE make dynamic-array analysis much easier. 4️⃣ What is the #SPILL! error? Sample Answer: SPILL! occurs when a dynamic-array formula wants to return multiple values but the cells where the results need to appear are blocked. For example:

=UNIQUE(A2:A100)

If another value exists in the cells where the results need to spill, Excel may return #SPILL!. I would check and clear the cells blocking the spill range. 5️⃣ How would you find the position of a value in a dataset? Sample Answer: I can use the MATCH function.

=MATCH("Laptop",A2:A100,0)

The 0 specifies an exact match. If I want both the position and corresponding value, I can combine MATCH with INDEX. 6️⃣ Why would you use INDEX + MATCH instead of VLOOKUP? Sample Answer: INDEX + MATCH provides more flexibility. For example, the lookup column doesn't need to be the first column of the lookup range, and I can return values from either direction. Example:

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

However, in modern Excel, I would generally consider XLOOKUP first because it is simpler and more flexible. 7️⃣ How would you calculate a running total? Sample Answer: If sales are in column B, I could use:

=SUM($B$2:B2)

Then copy the formula downward. The first reference remains fixed while the second reference expands as the formula moves down. This produces a cumulative total. 8️⃣ How would you calculate a percentage contribution to total sales? Sample Answer: If sales are in B2:B100:

=B2/SUM($B$2:$B$100)

Then format the result as a percentage. The $ ensures that the total-sales range remains fixed when the formula is copied down. 9️⃣ How would you compare two Excel datasets and find missing records? Sample Answer: I could use XLOOKUP to check whether a value from one dataset exists in another. Example:

=IF(ISNA(XLOOKUP(A2,Sheet2!A:A,Sheet2!A:A)),"Missing","Found")

Alternatively, I could use COUNTIF:

=IF(COUNTIF(Sheet2!A:A,A2)=0,"Missing","Found")

This is a practical example of combining IF, AND, and date functions. 🔟 You receive a messy Excel file. What steps would you take before analyzing it? Sample Answer: I would first understand the structure and business meaning of the data. Then I would check: 1. Duplicate records 2. Missing values 3. Incorrect data types 4. Inconsistent text 5. Invalid dates 6. Extra spaces 7. Outliers or unusual values 8. Incorrect or inconsistent categories For example, "India", "india" and " INDIA " may need to be standardized. For repeatable or large-scale cleaning, I would prefer Power Query rather than manually editing thousands of rows. 🎯 Double Tap ❤️ For More ----- 1.31 ₽ · /balance_help

📊 Excel Interview Questions — Part 3 This part focuses on Power Query, PivotTables, charts, data cleaning, and practical interview scenarios. 1️⃣ What is Power Query in Excel? Sample Answer: Power Query is a data transformation and preparation tool in Excel. I can use it to: → Import data from different sources → Clean and transform data → Remove duplicates → Handle missing values → Split and merge columns → Change data types → Combine multiple files → Load the final dataset into Excel The major advantage is that the transformation steps can be saved and refreshed when new data arrives. 2️⃣ What is the difference between Power Query and formulas? Sample Answer: Formulas are useful for calculations and analysis directly within the worksheet. Power Query is more suitable for repeatable data cleaning and transformation. For example, if I receive the same monthly sales file every month, I can create a Power Query process once and refresh it instead of manually cleaning every file. 3️⃣ What is the difference between a PivotTable and a normal table? Sample Answer: A normal table stores and organizes the underlying data. A PivotTable summarizes and analyzes that data. For example, from a sales table containing thousands of transactions, I can create a PivotTable showing: → Total sales by region → Sales by product → Monthly sales → Number of transactions 4️⃣ What are Slicers in Excel? Sample Answer: Slicers are visual filters that allow users to filter Tables or PivotTables interactively. For example, a sales dashboard could have slicers for: → Region → Product → Year → Salesperson Instead of opening filter dropdowns, users can simply click the required option. 5️⃣ What is a PivotChart? Sample Answer: A PivotChart is a chart connected to a PivotTable. It automatically responds when the PivotTable is filtered or changed. For example, I can create a PivotChart showing monthly revenue and then use a Region slicer to see the trend for a specific region. 6️⃣ How would you compare actual sales with target sales? Sample Answer: First, I would calculate the variance. For example:

=Actual Sales - Target Sales

Then I could calculate the achievement percentage:

=Actual Sales / Target Sales

I could use Conditional Formatting to highlight: 🟢 Above target 🔴 Below target I could then summarize the results using a PivotTable or dashboard. 7️⃣ How would you find the top-performing salesperson? Sample Answer: If I only need the highest sales value, I can use:

=MAX(B2:B100)

If I need the salesperson associated with that value, I could use XLOOKUP:

=XLOOKUP(MAX(B2:B100),B2:B100,A2:A100)

In a larger analysis, I could also use a PivotTable and sort sales in descending order. 8️⃣ How would you calculate employee tenure? Sample Answer: If the joining date is in A2, I can use:

=DATEDIF(A2,TODAY(),"Y")

This returns completed years of service. If I need years and months:

=DATEDIF(A2,TODAY(),"Y")&" Years "&DATEDIF(A2,TODAY(),"YM")&" Months"

9️⃣ How would you identify overdue transactions? Sample Answer: I would compare the due date with today's date. For example:

=IF(B2<TODAY(),"Overdue","Not Overdue")

If I also need to consider whether the payment has already been completed:

=IF(AND(B2<TODAY(),C2<>"Paid"),"Overdue","Not Overdue")

𝗜𝗕𝗠 𝗙𝗥𝗘𝗘 𝗧𝗘𝗖𝗛 𝗖𝗢𝗨𝗥𝗦𝗘𝗦 𝟮𝟬𝟮𝟲! 🎓 Explore IBM Cognitive Class and build in-demand tech skills without spending money on course fees! 📚 𝗧𝗼𝗽 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝘁𝗼 𝗘𝘅𝗽𝗹𝗼𝗿𝗲:- 💫 Artificial Intelligence & Machine Learning 🔥Python for Data Science 📊 Data Science Fundamentals ☁️ Cloud Computing & Big Data 🔗 𝗦𝘁𝗮𝗿𝘁 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗛𝗲𝗿𝗲 👇 https://pdlink.in/4zGswuX 📲 Save & Share with friends preparing for tech careers! 🔥

𝗗𝗮𝘁𝗮 𝘀𝗰𝗶𝗲𝗻𝗰𝗲 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗪𝗶𝘁𝗵 𝗔𝗜😍 Future Ready Data Science and AI Skills You Build ->Build Python, Machine Learning, and AI Skills ->Get Mentored by Industry Experts ->Practice with Mock Interviews ->Ace Your Placements & Land Dream Tech Job 🤝 500+ Partner Companies |🎓 2000+ Students Placed 🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗡𝗼𝘄 👇 :- https://pdlink.in/47pezFz 📲 Save & Share with Friends Preparing for Data Science & AI Jobs!

📊 Excel Interview Questions — Part 1 1️⃣ What is the difference between VLOOKUP and XLOOKUP? Sample Answer: VLOOKUP searches for a value in the first column of a table and returns a value from a column to its right. XLOOKUP is more flexible because it can search both left and right, supports exact matching by default, and allows a custom result when the value isn't found. Example: "=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")" 💡 In modern Excel, I would generally prefer XLOOKUP over VLOOKUP. 2️⃣ What is the difference between COUNT, COUNTA and COUNTBLANK? Sample Answer: "COUNT" → Counts cells containing numbers. "COUNTA" → Counts non-empty cells. "COUNTBLANK" → Counts empty cells. Example: "=COUNT(A2:A100)" "=COUNTA(A2:A100)" "=COUNTBLANK(A2:A100)" 💡 This is commonly asked to test your understanding of different data types. 3️⃣ What is the difference between SUMIF and SUMIFS? Sample Answer: "SUMIF" is used when we have one condition. "SUMIFS" is used when we have multiple conditions. Example: "=SUMIF(A2:A100,"East",C2:C100)" This calculates sales for the East region. For multiple conditions: "=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Laptop")" This calculates Laptop sales in the East region. 4️⃣ What is the difference between relative and absolute cell references? Sample Answer: A relative reference changes when a formula is copied. Example: "A2" An absolute reference remains fixed. Example: "A2" A mixed reference fixes either the row or column: "$A2" "A$2" 💡 The "F4" key can be used while editing a formula to cycle through reference types. 5️⃣ What is a PivotTable? Sample Answer: A PivotTable is an Excel feature used to quickly summarize and analyze large datasets. For example, from a sales dataset, I can create a PivotTable to calculate: → Sales by region → Sales by product → Monthly revenue → Number of transactions It allows me to analyze data without manually writing multiple formulas. 6️⃣ How would you remove duplicate records in Excel? Sample Answer: I can use Excel's Remove Duplicates feature. First, select the dataset. Then: "Data → Remove Duplicates" I select the columns that should be considered when identifying duplicates and click OK. 💡 I would usually keep a copy of the original dataset before removing duplicates. 7️⃣ What is IFERROR and why is it useful? Sample Answer: "IFERROR" allows me to replace an Excel error with a more meaningful result. Example: "=IFERROR(A2/B2,0)" If the calculation produces an error, Excel returns "0" instead. Another example: "=IFERROR(XLOOKUP(A2,E:E,F:F),"Not Found")" This prevents errors such as "#N/A" from appearing in a report. 8️⃣ How would you find duplicate values in Excel? Sample Answer: One common approach is Conditional Formatting. I select the required range and use: "Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values" Another approach is using a formula such as: "=COUNTIF(A2:A100,A2)>1" If the result is TRUE, the value appears more than once. 9️⃣ How would you extract the month from a date? Sample Answer: I can use the "MONTH" function to return the month number. "=MONTH(A2)" For example: "15-Sep-2026" → "9" If I need the month name: "=TEXT(A2,"mmmm")" Result: "September" 💡 The approach depends on whether I need the numeric month or the actual month name. 🔟 What is the difference between VLOOKUP and INDEX + MATCH? Sample Answer: VLOOKUP searches for a value in the first column and returns a value from a column to the right. INDEX + MATCH is more flexible because the lookup column doesn't have to be the first column, and it can look in either direction. Example: "=INDEX(C2:C100,MATCH(A2,A2:A100,0))" Here: "MATCH" → Finds the position of the lookup value. "INDEX" → Returns the value from that position.