Training By Himanshu

Tag: Excel Interview Preparation

  • Top 50 Excel Interview Questions Asked by MNCs and Smart Answers (Complete Excel Interview Preparation Guide)

    Excel remains one of the most tested technical skills in MNC interviews, especially for roles in finance, data analytics, MIS, operations, HR, and business intelligence. According to recruitment data shared by corporate hiring panels, over 85% of MNCs include Excel-based questions in at least one interview round. Mastery of Excel concepts directly influences shortlisting, salary negotiation, and final selection.

    This comprehensive guide on Top 50 Excel Interview Questions Asked by MNCs and Smart Answers is designed to help candidates crack Excel interviews with confidence. The questions included here are curated from real interview experiences, assessment tests, and hiring trends across multinational organizations.

    Why Excel Is So Important in MNC Interviews

    • Excel is used by 90% of global companies for reporting and analysis
    • Recruiters test Excel to evaluate logical thinking and accuracy
    • Excel proficiency reduces training cost by 30–40%
    • Advanced Excel users are often shortlisted for leadership-track roles

    Top 50 Excel Interview Questions Asked by MNCs and Smart Answers

    Basic Excel Interview Questions (Foundation Level)

    QuestionSmart Answer
    1. What is Microsoft Excel?Excel is a spreadsheet application used for data storage, calculation, analysis, visualization, and reporting.
    2. What is a cell in Excel?A cell is the intersection of a row and a column where data is entered.
    3. What is a workbook?A workbook is an Excel file containing one or more worksheets.
    4. What is a worksheet?A worksheet is a single spreadsheet within a workbook.
    5. What are rows and columns?Rows run horizontally and columns run vertically to organize data.
    6. What is a range?A range is a selection of two or more cells.
    7. What is the difference between COUNT and COUNTA?COUNT counts numeric values; COUNTA counts non-empty cells.
    8. What is the use of Freeze Panes?It locks rows or columns to remain visible while scrolling.
    9. What are Excel charts?Charts visually represent data trends and comparisons.
    10. What is conditional formatting?It formats cells automatically based on defined rules.

    Intermediate Excel Interview Questions (Most Asked by MNCs)

    QuestionSmart Answer
    11. What is VLOOKUP?It searches vertically for a value and returns related data from another column.
    12. Difference between VLOOKUP and HLOOKUP?VLOOKUP works vertically, HLOOKUP works horizontally.
    13. What is IF function?IF performs logical testing and returns different values based on conditions.
    14. What is IFERROR?It handles errors by returning a custom value instead of error messages.
    15. What is Pivot Table?A Pivot Table summarizes large data sets dynamically.
    16. What is data validation?It restricts user input to maintain data accuracy.
    17. What is TEXT function?It converts numbers into formatted text.
    18. What is CONCAT vs CONCATENATE?CONCAT is newer and more flexible; CONCATENATE is older.
    19. What are absolute references?Cell references that do not change when copied, using $.
    20. What is sorting in Excel?Sorting arranges data in ascending or descending order.

    Advanced Excel Interview Questions (High-Frequency MNC Questions)

    QuestionSmart Answer
    21. What is INDEX and MATCH?A powerful lookup combination that replaces VLOOKUP.
    22. Why is INDEX-MATCH better than VLOOKUP?It is faster, flexible, and works left-to-right.
    23. What is XLOOKUP?A modern lookup function that replaces older lookup functions.
    24. What are Pivot Charts?Visual representations linked to Pivot Tables.
    25. What is Power Query?A tool used to clean, transform, and load data.
    26. What is Power Pivot?Used for advanced data modeling and large datasets.
    27. What is DAX?A formula language used in Power Pivot and Power BI.
    28. What is slicer?A visual filter for Pivot Tables.
    29. What is a macro?A recorded set of actions to automate tasks.
    30. What is VBA?Visual Basic for Applications used for automation.

    Scenario-Based Excel Interview Questions (MNC Favorite)

    QuestionSmart Answer
    31. How do you remove duplicates?Using Remove Duplicates or formulas.
    32. How do you protect an Excel sheet?By locking cells and applying sheet protection.
    33. How do you highlight duplicates?Using conditional formatting rules.
    34. How do you convert text to columns?Using Text to Columns feature.
    35. How do you find errors in formulas?Using formula auditing tools.
    36. How do you handle large datasets?Pivot Tables, Power Query, and optimized formulas.
    37. How do you automate reports?Using macros, Power Query, and templates.
    38. How do you link multiple sheets?Using cell references and formulas.
    39. How do you create dashboards?By combining Pivot Tables, charts, and slicers.
    40. How do you improve Excel performance?By reducing volatile formulas and optimizing data.

    Expert-Level Excel Interview Questions (Shortlisting Round)

    QuestionSmart Answer
    41. What are volatile functions?Functions that recalculate automatically like NOW and TODAY.
    42. What is array formula?A formula that performs multiple calculations at once.
    43. Difference between workbook and worksheet protection?Workbook protects structure; worksheet protects content.
    44. What is Name Manager?It manages named ranges for formulas.
    45. What is scenario manager?Used for what-if analysis.
    46. What is Goal Seek?Finds required input for a desired result.
    47. What is Solver?Performs optimization and constraints analysis.
    48. What is circular reference?When a formula refers to itself.
    49. What is flash fill?Automatically fills patterns based on input.
    50. How do you audit Excel models?By checking formulas, links, and validations.

    Why These Excel Interview Questions Matter in MNC Hiring

    • Tests practical knowledge, not theory
    • Evaluates problem-solving ability
    • Differentiates average and advanced candidates
    • Used in technical, managerial, and leadership rounds

    Candidates answering 70%+ questions correctly have a 3x higher selection probability.

    Preparation Tips for Excel Interviews in MNCs

    • Practice formulas daily
    • Work on real datasets
    • Focus on shortcuts and automation
    • Learn explanation-based answers, not definitions

    FAQ: Excel Interview Questions Asked by MNCs

    Q1. How many Excel questions are asked in MNC interviews?

    Typically 10–25 questions depending on role and experience level.

    Q2. Is Excel mandatory for non-technical roles?

    Yes, especially for reporting, coordination, and operations roles.

    Q3. Which Excel topics are most important for interviews?

    Formulas, Pivot Tables, lookup functions, and data handling.

    Q4. Do MNCs test Excel practically?

    Yes, many interviews include Excel assignments or live tests.

    Q5. Is VBA required for Excel interviews?

    VBA is not mandatory but gives a strong advantage.

    Q6. How long does Excel preparation take?

    With focused practice, 3–4 weeks is sufficient for most candidates.

    Conclusion

    Mastering these Top 50 Excel Interview Questions Asked by MNCs and Smart Answers equips candidates with the confidence and clarity needed to succeed in competitive interviews. Excel is not just a tool—it is a career accelerator. Strong Excel knowledge consistently translates into better job roles, higher salaries, and faster promotions.

    Disclaimer

    This article is intended for educational purposes only. Interview questions and difficulty levels may vary depending on company, role, industry, and experience. Figures mentioned are based on industry hiring trends and may differ across regions.

  • Top 50 Excel Questions Asked in Interviews for Freshers and Experienced Professionals (With Clear Explanations)

    Preparing for Excel interviews without knowing the top 50 Excel questions asked in interviews is one of the biggest mistakes candidates make. Excel is no longer just a spreadsheet tool; it is a core business skill used in accounting, data analysis, MIS reporting, finance, HR, and operations. Recruiters often use Excel interviews to test not only knowledge but also logic, accuracy, and real-world problem-solving ability.

    This comprehensive guide covers the most frequently asked Excel interview questions, ranging from basics to advanced concepts. It is designed for freshers, accountants, data analysts, and working professionals who want to strengthen their interview readiness and confidence.

    Why Excel Skills Are So Important in Interviews

    Surveys across hiring firms show that over 75% of non-technical job roles require practical Excel knowledge. Roles such as accounts executive, data analyst, MIS executive, business analyst, and operations coordinator all depend heavily on Excel.

    Recruiters typically assess:

    • Accuracy of formulas
    • Understanding of data structure
    • Speed and efficiency
    • Ability to analyze and present data

    Mastering these Excel interview questions significantly improves your chances of selection.

    Top 50 Excel Questions Asked in Interviews

    Basic Excel Interview Questions (1–15)

    1. What is Microsoft Excel and where is it used?
    2. What are cells and ranges in Excel?
    3. What is the difference between a workbook and a worksheet?
    4. What are rows and columns?
    5. What is an Excel formula?
    6. What is the difference between a formula and a function?
    7. What are absolute, relative, and mixed cell references?
    8. How do you freeze rows or columns?
    9. What is the use of AutoFill?
    10. What are common Excel file extensions?
    11. How do you protect a worksheet?
    12. What is conditional formatting?
    13. What is the use of filters?
    14. What are charts in Excel?
    15. What is the use of Wrap Text?

    Intermediate Excel Interview Questions (16–35)

    Formulas and Functions

    1. What is the VLOOKUP function and its limitations?
    2. What is the difference between VLOOKUP and HLOOKUP?
    3. Why is INDEX and MATCH better than VLOOKUP?
    4. What is the IF function?
    5. How does IFERROR work?
    6. What is COUNT, COUNTA, and COUNTBLANK?
    7. Difference between SUM, SUMIF, and SUMIFS?
    8. What is AVERAGEIF?
    9. What is the TEXT function used for?
    10. What are logical functions in Excel?

    Data Handling and Tools

    1. What is Data Validation?
    2. What are Pivot Tables and why are they used?
    3. What is sorting in Excel?
    4. What is filtering vs advanced filtering?
    5. What is Remove Duplicates?
    6. What are Named Ranges?
    7. What is Flash Fill?
    8. How do you split data using Text to Columns?
    9. What is conditional formatting with formulas?
    10. What is the use of slicers?

    Advanced Excel Interview Questions (36–50)

    Advanced Analysis and Automation

    1. What is a Pivot Chart?
    2. What is Power Query?
    3. What is Power Pivot?
    4. What is the difference between Pivot Table and Power Pivot?
    5. What is a Macro in Excel?
    6. What is VBA used for?
    7. What is the difference between a macro and a formula?
    8. What are dynamic arrays?
    9. What is XLOOKUP and why is it better?
    10. What is the use of NETWORKDAYS function?
    11. What are Excel dashboards?
    12. How do you handle large datasets in Excel?
    13. What is conditional formatting priority?
    14. What is data consolidation?
    15. What are common Excel errors (#N/A, #VALUE!, #REF!)?

    Most Important Excel Topics Recruiters Focus On

    Topic AreaInterview Weight
    Excel FormulasVery High
    Pivot TablesHigh
    Lookup FunctionsVery High
    Data AnalysisHigh
    Automation (Macros)Medium

    Common Mistakes Candidates Make in Excel Interviews

    • Memorizing formulas without understanding logic
    • Not knowing real-life use cases
    • Ignoring data accuracy
    • Poor explanation of steps
    • Confusing similar functions

    Hiring managers often reject candidates who know formulas but cannot apply them to business scenarios.

    How Recruiters Evaluate Excel Skills

    Recruiters generally assess Excel through:

    • Practical tests
    • Scenario-based questions
    • Live problem solving
    • Data interpretation exercises

    Statistics from placement teams show candidates with hands-on Excel practice perform 40% better than those with only theoretical knowledge.

    Excel Skills That Increase Salary Potential

    Professionals with strong Excel skills can expect:

    • Faster promotions
    • Higher job stability
    • Better role flexibility

    Advanced Excel users often earn 20–30% higher salaries than basic Excel users in similar roles.

    Frequently Asked Questions (FAQ)

    1. What are the most common Excel questions asked in interviews?

    Questions on formulas, VLOOKUP, Pivot Tables, IF functions, and data handling are most common.

    2. Is Excel required for freshers in interviews?

    Yes. Most entry-level roles test basic to intermediate Excel knowledge.

    3. Are Pivot Tables important for Excel interviews?

    Yes. Pivot Tables are one of the most frequently tested Excel features.

    4. How many Excel questions are usually asked in interviews?

    Typically 10–20 questions, including practical tasks.

    5. Is advanced Excel mandatory for all jobs?

    No. Advanced Excel is required mainly for data-heavy roles.

    6. Can Excel interviews include practical tests?

    Yes. Many companies conduct live Excel assignments.

    7. How long does it take to prepare Excel for interviews?

    With focused practice, 2–4 weeks is sufficient for most roles.

    Conclusion

    Mastering the top 50 Excel questions asked in interviews gives you a strong competitive advantage. Excel is a practical skill, and interview success depends on how well you understand concepts and apply them to real-world problems. Whether you are a fresher or an experienced professional, consistent practice and conceptual clarity are the keys to clearing Excel interviews confidently.

    Disclaimer

    This article is intended for educational and informational purposes only. The interview questions, hiring trends, and figures mentioned are based on industry observations, recruiter feedback, and practical experience. Actual interview questions and evaluation criteria may vary depending on company, role, and industry.

  • How to Build an Excel Portfolio for Job Applications: A Complete Step-by-Step Guide to Stand Out in Competitive Hiring

    Building an Excel portfolio for job applications has become one of the most effective ways for candidates to showcase their practical spreadsheet skills. In a job market where employers value real, demonstrable abilities, an Excel portfolio provides concrete proof of your analytical thinking, data manipulation techniques, reporting skills, dashboard creation, and overall command of Microsoft Excel. This long-tail guide explains exactly how to create a strong Excel portfolio for job applications, what to include, how to structure it, and how to ensure your work reflects professional standards.

    In the first 100 words, it is important to emphasize that the primary keyword, how to build an Excel portfolio for job applications, is essential for candidates applying for roles in accounting, finance, MIS, business analysis, data entry, operations, marketing analytics, and HR. A well-designed Excel portfolio boosts your credibility with recruiters and significantly increases the chances of being shortlisted for interviews.

    Why an Excel Portfolio Matters for Job Applications

    Employers want proof of skills rather than claims. A resume highlights what you can do; a portfolio shows what you have already done. A strong Excel portfolio demonstrates your ability to solve real-world problems using formulas, pivot tables, dashboards, charts, lookup functions, data cleaning methods, financial modeling techniques, or automation tools.

    Below is a table summarizing the importance of an Excel portfolio during recruitment.

    BenefitExplanation
    Skill verificationShows actual Excel skill instead of just mentioning it on a resume
    Practical demonstrationAllows employers to evaluate your problem-solving approach
    Competitive advantageSets you apart from candidates without demonstrable work
    Confidence boosterHelps you speak clearly during interviews using real examples

    Employers typically scan portfolios to see logical thinking, consistency, formatting expertise, and ability to organize data effectively. This is why building a polished portfolio is crucial for job seekers.

    Types of Projects to Include in an Excel Portfolio

    When learning how to build an Excel portfolio for job applications, selecting the right projects is essential. The portfolio should include a mix of beginner-friendly, intermediate, and advanced projects to demonstrate a full range of Excel capabilities.

    1. Data Cleaning and Preparation Projects

    Recruiters pay close attention to your ability to organize, clean, and standardize messy datasets. Examples include:

    • Removing duplicates
    • Standardizing date formats
    • Splitting and combining text using formulas
    • Handling errors with IFERROR
    • Cleaning email or phone number formats

    2. Excel Dashboard Projects

    Dashboards showcase creativity and analytical capacity. A well-designed dashboard includes charts, slicers, pivot tables, KPIs, and formatted layouts. Dashboards are a must-have in a strong Excel portfolio.

    3. Financial Modeling Projects

    These projects are ideal for finance, accounting, and business roles. Examples include:

    • Profit and loss statement
    • Sales forecasting model
    • Budget vs actual report
    • Break-even analysis

    4. Lookup Function Projects

    A portfolio should demonstrate skills using:

    • VLOOKUP
    • HLOOKUP
    • XLOOKUP
    • INDEX + MATCH

    These functions reflect real-world data retrieval scenarios.

    5. Pivot Table and Pivot Chart Projects

    Pivot tables are used in nearly every industry. Include:

    • Sales summary reports
    • Region-wise performance analysis
    • Product-wise profitability charts

    6. Automation Projects Using Excel VBA

    Even simple VBA scripts demonstrate advanced capabilities. Examples include:

    • Automatically formatting reports
    • Deleting blank rows
    • Generating monthly summaries

    7. Data Visualization Projects

    Charts, trend lines, sparklines, conditional formatting, and heatmaps show mastery of presentation skills.

    How to Structure Your Excel Portfolio Professionally

    A professional Excel portfolio should be clean, organized, and easy to navigate. Think of it as your digital representation.

    Below is a simple recommended structure:

    SectionContent
    Folder 1: BasicsData cleaning, formulas, text functions
    Folder 2: IntermediatePivot tables, dashboards, charts
    Folder 3: AdvancedFinancial models, scenario analysis, macros
    Folder 4: Case StudiesReal-world business problems and solutions

    This structure helps employers quickly see your progression from beginner to expert level.

    Step-by-Step Guide: How to Build an Excel Portfolio for Job Applications

    Below is a detailed step-by-step framework for creating a complete, professional portfolio that hiring managers value.

    Step 1: Identify the Job Role You Are Targeting

    The type of portfolio you build depends on the job description. For instance:

    • A finance role requires forecasting models
    • A data analyst role requires dashboards and analytics
    • An MIS role requires reporting automation
    • A marketing role requires campaign analysis

    Knowing the role helps you choose relevant projects.

    Step 2: Collect Sample Datasets or Create Your Own

    Your portfolio must contain real data, not blank templates. You can:

    • Use public datasets
    • Create sample company data
    • Generate synthetic sales figures
    • Create HR datasets

    The goal is realism.

    Step 3: Build 5 to 10 High-Quality Projects

    A typical portfolio should have at least:

    • Two dashboards
    • One financial model
    • One pivot table analysis
    • One lookup function project
    • One automation or VBA project
    • One data cleaning project

    Keep the projects compact, clean, and well-presented.

    Step 4: Format Your Work Professionally

    Employers place strong emphasis on formatting. Your Excel sheets should have:

    • Consistent font style
    • Clear headings
    • Color-coded sections
    • Freeze panes for navigation
    • Properly aligned tables
    • Conditional formatting for highlights

    Professional formatting increases the quality perception of your portfolio.

    Step 5: Add Explanations and Documentation in Each File

    Every project should include:

    • Problem statement
    • Steps taken
    • Final results
    • Key Excel functions used
    • Lessons learned

    Documentation helps interviewers understand your approach.

    Step 6: Save Each Project with a Clear and Professional File Name

    Examples:

    • Sales_Dashboard_Project
    • HR_Data_Cleaning_Analysis
    • Monthly_Forecasting_Model
    • Pivot_Table_Summary_Report

    Clear file names show professionalism and organization.

    Step 7: Keep a Master Index of All Projects

    Your portfolio should include an index sheet listing all projects with a short summary. This acts as a table of contents and helps employers navigate all items quickly.

    Best Practices for Designing a Strong Excel Portfolio

    A few best practices ensure your portfolio stands out during job applications.

    1. Keep Visuals Clean and Minimal

    Avoid unnecessary design elements that distract from the data.

    2. Use Realistic Scenarios

    Employers prefer practical business cases instead of academic examples.

    3. Demonstrate a Wide Range of Skills

    Include both simple and complex projects to show versatility.

    4. Avoid Overcrowding Dashboards

    Use meaningful KPIs and leave enough white space for clarity.

    5. Review and Revise Frequently

    Your portfolio should evolve as your skills grow.

    Frequently Asked Questions (FAQ)

    1. How many projects should I include in an Excel portfolio?

    Five to ten well-crafted projects are sufficient. More is not always better; clarity and quality matter most.

    2. Should I include Excel VBA in my portfolio?

    If the role involves automation, VBA projects can significantly strengthen your profile and demonstrate advanced skills.

    3. What types of Excel projects impress employers the most?

    Dashboards, financial models, pivot table reports, and real-world case studies are highly valued because they reflect practical business applications.

    4. Do I need advanced Excel skills to build a portfolio?

    No. Beginners can start with basics like data cleaning and formulas. As you grow, you can add intermediate and advanced projects.

    5. How should I present my Excel portfolio during interviews?

    Keep a master index and open the file to walk the interviewer through the problem, your process, and the final outcome.

    6. Can students create Excel portfolios even without job experience?

    Yes. Students can create sample business datasets, project simulations, and academic case studies.

    7. Should I include instructions or documentation in every project?

    Yes. Documentation shows your analytical thinking and communication skills.

    8. Do employers actually look at Excel portfolios?

    Yes. Recruiters often scan portfolios to verify skills before shortlisting candidates.

    Disclaimer

    This article is intended for educational and informational purposes. Portfolio examples and project structures should be customized based on individual job requirements. Excel is a registered trademark of its respective owner.

  • 1-Day Excel Interview Prep Plan: How to Master Key Skills Overnight

    If you have just one day to prepare for an Excel-related interview, your goal isn’t to learn everything — it’s to refresh the essentials, cover high-frequency questions, and get hands-on practice so you can answer with confidence.

    Here’s a step-by-step crash plan (8–10 hours total):

    ⏰ Hour 1: Understand the Job Role

    • Check the job description → Which Excel skills do they want? (e.g., data analysis, reporting, dashboards, VBA, Power Query).
    • Identify focus areas → If it says MIS, focus more on reporting formulas. If Data Analyst, focus more on lookup, filters, and pivot tables.
    • Quickly note down:
      • Core functions mentioned
      • Tools (Pivot Table, Power Query, Macros, SQL, etc.)
      • Business context (sales reports, financial data, etc.)

    ⏰ Hours 2–4: Formula Mastery

    Focus on 10–12 key formulas you will almost certainly be tested on:

    Formula / FunctionWhy ImportantQuick Example
    VLOOKUP / XLOOKUPMerge datasets, fetch related data=XLOOKUP(101, A2:A100, B2:B100, "Not Found")
    INDEX + MATCHFlexible lookups=INDEX(Sales, MATCH("Apple", Product, 0))
    IF + IFSConditional logic=IF(B2>5000,"High","Low")
    SUMIF / SUMIFSConditional totals=SUMIFS(Sales, Region, "East", Product, "Apple")
    COUNTIF / COUNTIFSCount with conditions=COUNTIFS(Region,"West", Sales, ">5000")
    TEXT functions (LEFT, RIGHT, MID, TRIM, LEN)Clean & extract text=LEFT(A2,5)
    FILTERDynamic filtering=FILTER(A2:D100, Region="North")
    UNIQUERemove duplicates=UNIQUE(Product)
    Date functions (YEAR, MONTH, EOMONTH, TEXT)Date-based analysis=TEXT(A2,"MMM-YYYY")

    Action:

    • Open Excel and type small practice datasets (10–15 rows).
    • Try each formula 3–4 times until you can do it without looking up syntax.

    ⏰ Hours 5–6: Pivot Tables & Data Cleaning

    • Create 2–3 quick Pivot Tables:
      • Sales by Region and Month
      • Top 5 products by revenue
    • Practice:
      • Sorting, filtering
      • Grouping dates
      • Adding calculated fields
    • In Power Query:
      • Remove duplicates
      • Split columns
      • Change data types
      • Merge two tables

    ⏰ Hours 7–8: Practice Real Problems

    • Download any sample dataset (e.g., sales data, HR data from Kaggle or random CSV).
    • Do these exercises:
      • Find top performer by sales
      • Monthly sales trend
      • Count customers who purchased more than 3 times
      • Merge customer table with orders table
      • Create a simple dashboard (Pivot + Slicer)

    ⏰ Hour 9: Review Common Interview Questions

    Technical Qs:

    1. Difference between VLOOKUP and INDEX+MATCH?
    2. How to remove duplicates without affecting original data?
    3. How do you handle missing data in Excel?
    4. How to extract month name from a date?
    5. What is the difference between Absolute and Relative cell references?

    Scenario Qs:

    1. “You have sales data; find the top 3 regions by revenue.”
    2. “Find customers who purchased in Jan but not in Feb.”
    3. “Your report shows wrong totals—how do you troubleshoot?”

    ⏰ Hour 10: Mock Drill

    • Set a 30-min timer.
    • Ask a friend (or yourself) to give you 5 tasks on a dataset.
    • Solve them without Google — this simulates test conditions.
    • After the drill, check your answers and note mistakes.

    💡 Last-Minute Tips for the Interview

    • Think out loud → Even if you don’t know the answer, walk through your approach.
    • Show shortcut keys (Ctrl+T for tables, Alt+N+V for Pivot Tables) — looks impressive.
    • Focus on accuracy first, speed later — wrong answers ruin trust.

    Featured products

  • Top 10 Excel Functions Every Data Analyst Must Master

    When Rohan, a 26-year-old commerce graduate from Pune, started preparing for his first data analyst interview, he quickly realized one thing – Excel is not just a spreadsheet tool, it’s a career-making skill.

    He had always used Excel for basic sums and formatting, but during mock interviews, he froze when asked,

    “Can you combine INDEX and MATCH to find a sales figure for a product in a given month?”

    That day, Rohan decided – No more guesswork. I will master the top Excel functions recruiters expect.
    Here’s what he learned, with examples from his practice sessions.

    1. VLOOKUP / XLOOKUP – Rohan’s ‘Data Detective’ Tool

    One day, Rohan had two datasets – one with Product Names, another with Sales Values.
    Instead of scrolling endlessly, he used:

    =XLOOKUP("Mango Juice", A2:A100, B2:B100, "Not Found")

    Result: Sales value for Mango Juice in seconds.
    Lesson: Lookup functions save hours in data matching.

    2. INDEX + MATCH – Rohan’s Upgrade

    During an interview test, the product name was in column C, and sales were in column A.
    VLOOKUP couldn’t help (it needs the lookup column first).
    Rohan used:

    =INDEX(A2:A100, MATCH("Mango Juice", C2:C100, 0))

    Lesson: INDEX+MATCH works in any direction and is interview gold.

    3. TEXT Functions – Cleaning Rohan’s Messy Data

    His dataset had customer IDs like " AB1234 " with spaces.
    He cleaned it using:

    =TRIM(A2)

    And extracted first 2 letters for state code:

    =LEFT(A2, 2)

    Lesson: TEXT functions like LEFT, RIGHT, MID, TRIM, and LEN are must-haves for messy datasets.

    4. IF + IFS – Decision Maker

    When given sales targets, Rohan categorized them:

    =IF(B2>=100000, "Top Performer", "Needs Improvement")

    For multiple conditions:

    =IFS(B2>=100000, "Top Performer", B2>=50000, "Average", TRUE, "Low")

    Lesson: IF helps classify data instantly.

    5. SUMIF / SUMIFS – Finding Patterns

    To know the total sales for “Mango Juice” in the “East” region:

    =SUMIFS(Sales, Product, "Mango Juice", Region, "East")

    Lesson: SUMIFS is perfect for quick conditional aggregations.

    6. COUNTIF / COUNTIFS – Counting What Matters

    In one dataset, Rohan needed to know how many orders were above ₹5,000:

    =COUNTIF(Sales, ">5000")

    Lesson: COUNT functions are quick ways to spot trends in large datasets.

    7. FILTER – Rohan’s Shortcut to Relevant Data

    Instead of applying Excel’s manual filter, Rohan extracted all sales for the “North” region with:

    =FILTER(A2:D100, Region="North")

    Lesson: Dynamic, criteria-based extraction beats manual filtering.

    8. UNIQUE – Finding Distinct Customers

    When asked for the number of unique buyers, Rohan did:

    =UNIQUE(CustomerName)

    Lesson: UNIQUE quickly deduplicates lists for better analysis.

    9. Date Functions – Time Travel in Excel

    Rohan needed monthly trends. He used:

    =TEXT(OrderDate, "MMM-YYYY")

    For month-end date:

    =EOMONTH(OrderDate, 0)

    Lesson: Date functions help slice and dice time-based data.

    10. Power Query + Power Pivot – Rohan’s Secret Weapon

    By now, Rohan could clean data in Power Query, load millions of rows, and use DAX for calculated measures.
    In one interview, he impressed the panel by transforming raw CSV files into a dashboard-ready table in 10 minutes.

    Rohan’s Takeaway

    “Excel isn’t about knowing formulas by heart—it’s about knowing which function to use when, and how to combine them.”

    Master these 10 functions, and you’re not just prepared for a data analyst job—you’re prepared for real-world problem solving.

    Top rated products