100 Excel Interview Questions and Answers: Crack Your Next MIS, Data Analyst, or Excel Job Interview

—

by

Himanshu
in

Microsoft Excel is a powerful tool used across industries for data analysis, reporting, financial modeling, and business intelligence. Whether you’re applying for roles in data analysis, finance, accounting, MIS (Management Information System), operations, or even marketing, a strong grip on Excel can set you apart.

👤 Who Should Use This?

This list is ideal for:

  • Job seekers in roles like MIS Executive, Data Analyst, Financial Analyst, Business Analyst, Operations Manager, or Accountant
  • Freshers preparing for entry-level roles requiring Excel
  • Professionals upskilling for promotions or transitions to analytical roles
  • Trainers or HR professionals preparing candidates for interviews

✅ Excel Interview Questions and Answers (100 Q&A)

🟩 Section 1: Basic Excel Skills

  1. Q: What is Microsoft Excel used for?
    A: Excel is used for data entry, data analysis, calculations, charting, pivot tables, and automation using formulas and macros.
  2. Q: What is a cell in Excel?
    A: A cell is the intersection of a row and a column where data is entered.
  3. Q: What is the difference between a worksheet and a workbook?
    A: A worksheet is a single sheet in Excel; a workbook is a file containing one or more worksheets.
  4. Q: How do you save a workbook in Excel?
    A: Use Ctrl + S or go to File > Save/Save As.
  5. Q: What are the different data types in Excel?
    A: Text, Numbers, Dates, Boolean (TRUE/FALSE), Currency, and Custom formats.
  6. Q: How do you insert a new row or column?
    A: Right-click on the row/column header > Insert, or use Ctrl + Shift + "+".
  7. Q: How do you freeze panes?
    A: Go to View > Freeze Panes to lock rows/columns for scrolling.
  8. Q: What is a range in Excel?
    A: A range is a selection of two or more cells, e.g., A1:A10.
  9. Q: How can you wrap text in a cell?
    A: Select the cell, go to Home > Wrap Text.
  10. Q: How do you merge cells?
    A: Select cells > Home > Merge & Center.

🟨 Section 2: Formulas and Functions

  1. Q: What is the difference between a formula and a function?
    A: A formula is user-created (e.g., =A1+A2), while a function is a predefined operation (e.g., =SUM(A1:A2)).
  2. Q: What does the SUM function do?
    A: It adds up numbers in a given range. Example: =SUM(A1:A5)
  3. Q: What is the use of IF function?
    A: It performs logical tests. Example: =IF(A1>50, “Pass”, “Fail”)
  4. Q: What does VLOOKUP do?
    A: It searches for a value in the first column and returns data from a specified column.
    Example: =VLOOKUP(101, A2:C10, 3, FALSE)
  5. Q: What is the difference between VLOOKUP and HLOOKUP?
    A: VLOOKUP searches vertically; HLOOKUP searches horizontally.
  6. Q: What does the INDEX function do?
    A: It returns the value of a cell at a specific row and column in a range.
  7. Q: How does MATCH work?
    A: MATCH returns the position of a value in a range.
    Example: =MATCH(50, A1:A10, 0)
  8. Q: What is the use of CONCATENATE or CONCAT function?
    A: Joins multiple text strings into one.
    Example: =CONCAT(A1, " ", B1)
  9. Q: What is the difference between COUNT, COUNTA, and COUNTBLANK?
    A:
    • COUNT: counts numbers only
    • COUNTA: counts non-empty cells
    • COUNTBLANK: counts empty cells
  10. Q: How do you round numbers in Excel?
    A: Use ROUND, ROUNDUP, or ROUNDDOWN functions.

🟧 Section 3: Intermediate Excel (Data Tools & Formatting)

  1. Q: What are conditional formatting rules?
    A: They format cells based on criteria (e.g., highlight values > 100).
  2. Q: How do you apply data validation?
    A: Data > Data Validation to restrict input (e.g., allow only numbers 1–100).
  3. Q: What is the use of “Remove Duplicates”?
    A: It deletes repeated data from a range.
  4. Q: How to use Text to Columns?
    A: Data > Text to Columns (used to split data based on delimiters).
  5. Q: What is a named range?
    A: A defined name for a cell or range (e.g., =SalesTotal)
  6. Q: What are sparklines?
    A: Mini charts within a cell to show trends.
  7. Q: How do you use Find and Replace?
    A: Ctrl + F (Find), Ctrl + H (Replace)
  8. Q: What is Flash Fill?
    A: Automatically fills patterns based on previous entries (Ctrl + E)
  9. Q: What is a drop-down list in Excel?
    A: Created using Data Validation to restrict input to a list.
  10. Q: What is the use of Goal Seek?
    A: To find the input value needed to achieve a desired result.

🟦 Section 4: Charts and Visualizations

  1. Q: How do you insert a chart?
    A: Select data > Insert > Choose a chart type (e.g., column, line, pie)
  2. Q: What is a combo chart?
    A: A chart combining two chart types (e.g., column + line)
  3. Q: What is a pivot chart?
    A: A chart based on PivotTable data.
  4. Q: Can charts be dynamic?
    A: Yes, by using named ranges or tables with formulas.
  5. Q: What is a slicer in charts or pivots?
    A: A filter control used to filter PivotTables visually.

🟫 Section 5: Pivot Tables & Data Analysis

  1. Q: What is a PivotTable?
    A: A tool to summarize large data sets with drag-and-drop fields.
  2. Q: How do you insert a PivotTable?
    A: Insert > PivotTable > Choose data and location
  3. Q: Can you group data in PivotTable?
    A: Yes, right-click on values > Group (useful for dates or ranges)
  4. Q: What is the difference between Value Field Settings – SUM vs COUNT?
    A: SUM totals numeric values, COUNT counts entries.
  5. Q: How do you refresh a PivotTable?
    A: Right-click > Refresh or use the Refresh button in the Ribbon.

🟥 Section 6: Advanced Excel

  1. Q: What is Power Query?
    A: A data transformation tool to import, clean, and combine data.
  2. Q: What is Power Pivot?
    A: A data modeling tool to create relationships and use DAX formulas.
  3. Q: What are array formulas?
    A: Formulas that perform multiple calculations on one or more items.
  4. Q: What is the use of XLOOKUP?
    A: A more powerful and flexible replacement for VLOOKUP.
  5. Q: How do you use dynamic arrays like FILTER and SORT?
    A:
    • =FILTER(range, condition) to filter data
    • =SORT(range, column, order) to sort data
  6. Q: What is a dashboard in Excel?
    A: A visual interface using charts, KPIs, and PivotTables to monitor key metrics.
  7. Q: What is DAX in Power Pivot?
    A: Data Analysis Expressions – a formula language for creating custom calculations.
  8. Q: What is a data model in Excel?
    A: A relational database built using Power Pivot or linked tables.
  9. Q: What is Solver?
    A: An add-in used for optimization problems (e.g., maximize profit).
  10. Q: Can Excel connect to external data sources?
    A: Yes, from Access, SQL Server, web, CSV, etc.

🔵 Section 7: Macros and VBA

  1. Q: What is a macro in Excel?
    A: A recorded sequence of steps that can be replayed.
  2. Q: How do you record a macro?
    A: View > Macros > Record Macro
  3. Q: What is VBA?
    A: Visual Basic for Applications – programming language for automating tasks.
  4. Q: What is a module in VBA?
    A: A container for procedures or code.
  5. Q: How do you open the VBA editor?
    A: Press Alt + F11.

🟣 Section 8: Macros and VBA (Continued)

  1. Q: What is the difference between a Sub and a Function in VBA?
    A: A Sub performs actions but doesn’t return a value. A Function performs actions and returns a value.
  2. Q: How do you write a simple macro in VBA to display a message box?
    A:
Sub ShowMessage()
    MsgBox "Hello, this is a message!"
End Sub
  1. Q: How can you run a macro using a button?
    A: Insert a Form Control button from the Developer tab, assign the macro.
  2. Q: What is a UserForm in VBA?
    A: A custom form/dialog box you can design for data entry or interaction.
  3. Q: What are some common uses of VBA in Excel?
    A: Automating reports, generating emails, cleaning data, creating dashboards, etc.

🔶 Section 9: Error Handling and Troubleshooting

  1. Q: What does #DIV/0! error mean?
    A: Division by zero error – occurs when dividing by 0 or a blank cell.
  2. Q: What is #N/A error?
    A: “Not Available” – typically occurs with lookup functions when value not found.
  3. Q: What is #REF! error?
    A: Invalid cell reference – often happens when a cell referred in a formula is deleted.
  4. Q: What is #VALUE! error?
    A: Incorrect data type used in a formula.
  5. Q: How do you use IFERROR function?
    A: Wrap formulas to catch and replace errors.
    Example: =IFERROR(A1/B1, "Error in calculation")
  6. Q: What is circular reference in Excel?
    A: A formula that refers to its own cell, creating an endless loop.
  7. Q: How do you audit formulas in Excel?
    A: Use Formula Auditing tools (Formulas > Trace Precedents/Dependents)
  8. Q: How to evaluate formulas step by step?
    A: Use “Evaluate Formula” tool under Formulas tab.
  9. Q: What is the purpose of Watch Window?
    A: To monitor the values of key cells during calculations.
  10. Q: How can you protect a worksheet or cell?
    A: Review > Protect Sheet. Use Format Cells > Protection to lock/unlock cells first.

🔷 Section 10: Excel Productivity Tips

  1. Q: How do you quickly select a range of data?
    A: Use Ctrl + Shift + Arrow keys.
  2. Q: How do you select non-contiguous cells?
    A: Hold Ctrl and click on individual cells.
  3. Q: How do you convert rows to columns (or vice versa)?
    A: Use Paste Special > Transpose.
  4. Q: How do you remove blank rows quickly?
    A: Use filters to find blanks and delete rows.
  5. Q: What does Ctrl + ; do?
    A: Enters the current date.
  6. Q: What does Ctrl + Shift + L do?
    A: Applies or removes filters.
  7. Q: How can you repeat the last action?
    A: Press F4.
  8. Q: How to lock row 1 while scrolling?
    A: View > Freeze Panes > Freeze Top Row.
  9. Q: What does Alt + = do?
    A: Inserts the SUM function automatically.
  10. Q: How do you insert the current time?
    A: Press Ctrl + Shift + ;

⚫ Section 11: Scenario-Based & Practical Questions

  1. Q: You have employee data. How do you find duplicate names?
    A: Use Conditional Formatting > Highlight Duplicates or use =COUNTIF(range, cell)>1
  2. Q: How would you create an attendance tracker in Excel?
    A: Use dates in columns, names in rows, and mark “P”/”A”; use COUNTIF for totals.
  3. Q: How to find top 3 sales from a list?
    A: Use =LARGE(range, 1), =LARGE(range, 2), etc.
  4. Q: How to split full names into first and last names?
    A: Use =LEFT() and =RIGHT() with FIND() or use Text to Columns.
  5. Q: How would you highlight weekends in a calendar?
    A: Use Conditional Formatting with formula: =WEEKDAY(A1,2)>5
  6. Q: How do you prepare a monthly sales dashboard?
    A: Use PivotTables, Pivot Charts, Slicers, Conditional Formatting, KPI indicators.
  7. Q: A client sends data in PDF – how do you get it into Excel?
    A: Use Power Query > Get Data from PDF or copy-paste and clean.
  8. Q: How do you track changes in Excel?
    A: Use File > Info > Version History (for OneDrive) or use manual versioning.
  9. Q: How would you remove all hyperlinks in a sheet?
    A: Select all cells > Right-click > Remove Hyperlinks.
  10. Q: How do you compare two columns for matching entries?
    A: Use =IF(A2=B2, "Match", "No Match") or use =COUNTIF(range, value)

🟤 Section 12: Bonus & Conceptual Questions

  1. Q: What is the default file extension for Excel?
    A: .xlsx (macro-enabled workbook: .xlsm)
  2. Q: Can you open CSV files in Excel?
    A: Yes, Excel can open and edit CSV files.
  3. Q: What are Excel Tables and their benefits?
    A: Structured data ranges with automatic formatting, filters, and dynamic references.
  4. Q: What is a 3D reference in Excel?
    A: A formula referring to the same cell across multiple sheets. Example: =SUM(Sheet1:Sheet3!A1)
  5. Q: What are dynamic named ranges?
    A: Named ranges that adjust automatically as data changes using formulas like OFFSET or INDEX.
  6. Q: How does Excel handle leap years in date calculations?
    A: Excel treats dates as serial numbers and accurately accounts for leap years.
  7. Q: What is the use of INDIRECT function?
    A: Returns a cell reference from a text string. Example: =INDIRECT("A"&1)
  8. Q: What is the TODAY function used for?
    A: Returns the current date. Example: =TODAY()
  9. Q: Can Excel perform web scraping?
    A: Yes, using Power Query or legacy Web connectors (with limitations).
  10. Q: What are some common interview tasks given in Excel interviews?
    A:
  • Creating dashboards
  • Cleaning raw data
  • Performing VLOOKUP/INDEX-MATCH
  • Creating PivotTables
  • Writing formulas for KPIs
  • Automating tasks using macros

🎓 Final Tips for Excel Interview Preparation

  • Practice real-world Excel projects (MIS reports, dashboards, sales trackers).
  • Be comfortable with both mouse navigation and keyboard shortcuts.
  • Focus on accuracy, speed, and logic—especially when solving lookup or data-cleaning tasks.
  • If the job requires automation, learn VBA basics and Power Query.

🚀 Master MIS & Data Automation – One Course, Endless Opportunities!


Boost your career with our Complete MIS Training Program – designed for professionals who want to excel in Data Management, Reporting, and Automation using Excel, Access, Macros, and SQL.

✅ 16.5 hours of expert-led video
📂 26 downloadable resources
🏅 Certificate of Completion
💼 Real-world projects & job-ready skills

👉 Perfect for MIS aspirants, analysts, and working professionals.

Start now and become the go-to expert for smart data solutions!
🔗 Enroll today

Featured products