Excel Basics for Data Analysts (Q1–20)
First set from the Top 100 Excel & Power Query Interview Questions [2025]: Excel basics, formulas, shortcuts, data organization, and reporting essentials. Perfect for Excel interview questions for freshers and analysts.
1. What is Excel and why is it important for analysts?
Answer: Excel is a spreadsheet application used to store, organize, analyze, and visualize data. Analysts use it for quick data cleaning, reporting, PivotTables, and dashboard creation.
2. What is the difference between a Formula and a Function in Excel?
Answer: A formula is a user-defined calculation (e.g., =A1+A2), while a function is a predefined formula (e.g., =SUM(A1:A10)).
3. What is a PivotTable in Excel?
Answer: A PivotTable is a tool to summarize, analyze, and explore large datasets by grouping, filtering, and aggregating data dynamically.
4. Explain VLOOKUP with an example.
Answer: VLOOKUP searches for a value in the first column of a range and returns a value from another column. Example: =VLOOKUP(101, A2:D10, 3, FALSE) finds ID 101 and returns the 3rd column value.
5. Difference between VLOOKUP and HLOOKUP?
Answer: VLOOKUP searches vertically (columns), while HLOOKUP searches horizontally (rows).
6. What is Conditional Formatting?
Answer: A feature to highlight cells based on rules (e.g., sales > 1000 → green fill).
7. What is the difference between Absolute and Relative cell references?
Answer: Absolute references ($A$1) remain fixed when copied; relative references (A1) adjust based on the new location.
8. What is a Named Range in Excel?
Answer: A feature to assign a name to a cell or range (e.g., “SalesData”) for easy reference in formulas.
9. What are Excel Tables?
Answer: Structured ranges with features like auto-filter, easy referencing, and dynamic expansion as new data is added.
10. What is the use of Data Validation?
Answer: A feature that controls what users can enter in a cell (e.g., restrict to numbers 1–100, dropdown lists).
11. Difference between COUNT, COUNTA, and COUNTBLANK?
Answer: COUNT → numeric cells; COUNTA → all non-empty cells; COUNTBLANK → empty cells.
12. What is Freeze Panes in Excel?
Answer: A feature to lock rows/columns while scrolling (useful for headers).
13. How do you remove duplicates in Excel?
Answer: Use Data → Remove Duplicates feature or Power Query for advanced cleaning.
14. What is the difference between Save As and Save?
Answer: Save updates the current file; Save As creates a copy with a new name/location/format.
15. What are Excel Macros?
Answer: Macros are recorded VBA scripts that automate repetitive tasks.
16. What is Goal Seek in Excel?
Answer: A tool to find the input value required to achieve a desired result in a formula.
17. What are Excel Shortcuts every analyst should know?
Answer: Ctrl+C (copy), Ctrl+Z (undo), Ctrl+Shift+L (toggle filters), Alt+= (AutoSum), Ctrl+Arrow (jump to data edge).
18. Difference between CSV and XLSX file formats?
Answer: CSV stores plain text data separated by commas (lightweight, no formatting); XLSX supports formatting, formulas, and advanced features.
19. What is Flash Fill in Excel?
Answer: A feature that auto-fills data based on detected patterns (e.g., splitting full names into first/last names).
20. What is Power Pivot in Excel?
Answer: An add-in that allows data modeling, DAX calculations, and handling large datasets beyond standard Excel limits.
Advanced Excel Functions & Data Analysis Tools (Q21–40)
Second set from the Top 100 Excel & Power Query Interview Questions [2025]: Advanced formulas, text functions, logical operators, data analysis tools, and error handling. Ideal for advanced Excel interview questions for analysts.
21. What is INDEX and MATCH? How is it better than VLOOKUP?
Answer: INDEX returns a value at a row/column, MATCH returns the position. Combined, they are more flexible than VLOOKUP since they allow left lookups and avoid column index issues.
22. What is the difference between IF and IFS function?
Answer: IF handles a single logical test; IFS handles multiple conditions in one formula without nesting.
23. What is the difference between CONCATENATE and TEXTJOIN?
Answer: CONCATENATE joins values without delimiters; TEXTJOIN allows delimiters and can ignore blanks.
24. What is the difference between TRIM and CLEAN function?
Answer: TRIM removes extra spaces; CLEAN removes non-printable characters from text.
25. What is the use of LEFT, RIGHT, and MID functions?
Answer: They extract specific characters from text strings (start, end, or middle).
26. How does the PROPER, UPPER, and LOWER functions work?
Answer: PROPER capitalizes first letters, UPPER converts to all caps, LOWER converts to all small letters.
27. What is the difference between FIND and SEARCH?
Answer: FIND is case-sensitive, SEARCH is not. Both locate substrings within text.
28. What is the use of LEN function?
Answer: LEN counts the number of characters in a cell, including spaces.
29. What is the difference between ROUND, ROUNDUP, and ROUNDDOWN?
Answer: ROUND → nearest value, ROUNDUP → always rounds up, ROUNDDOWN → always rounds down.
30. What is the difference between CEILING and FLOOR?
Answer: CEILING rounds up to nearest multiple; FLOOR rounds down.
31. What is the difference between NOW() and TODAY()?
Answer: NOW() returns date & time; TODAY() returns only the date.
32. What is the difference between YEARFRAC and DATEDIF?
Answer: YEARFRAC returns year fraction between dates; DATEDIF calculates full years, months, or days.
33. What are Array Formulas in Excel?
Answer: Formulas that perform multiple calculations on arrays of data (entered using Ctrl+Shift+Enter in older versions).
34. What is the difference between UNIQUE and FILTER functions?
Answer: UNIQUE extracts distinct values; FILTER extracts rows meeting criteria.
35. What is the difference between XLOOKUP and VLOOKUP?
Answer: XLOOKUP is more powerful: allows both vertical & horizontal lookups, handles missing values better, and doesn’t require column numbers.
36. What is the difference between SUBTOTAL and AGGREGATE functions?
Answer: SUBTOTAL applies functions with/without hidden rows; AGGREGATE is more advanced with error handling and multiple functions.
37. What is the difference between NPER and PMT?
Answer: NPER → number of periods for a loan; PMT → payment amount based on interest, periods, and principal.
38. What is the difference between IRR and NPV?
Answer: IRR → internal rate of return; NPV → net present value. Both are financial metrics for investment analysis.
39. How do you handle errors using IFERROR?
Answer: IFERROR returns a custom value (e.g., “Not Found”) instead of showing errors like #N/A or #DIV/0.
40. What is the difference between Data Table and Scenario Manager?
Answer: Both are What-If Analysis tools; Data Table tests multiple input values, while Scenario Manager saves & compares entire sets of assumptions.
Data Cleaning, Charts & Power Query Basics (Q41–60)
Third set from the Top 100 Excel & Power Query Interview Questions [2025]: data cleaning methods, PivotTables, charts, conditional formatting, and Power Query basics. Useful for Excel data cleaning interview questions and Power Query interview questions for analysts.
41. What are common data cleaning functions in Excel?
Answer: TRIM (remove extra spaces), CLEAN (remove non-printable characters), SUBSTITUTE/REPLACE (fix text), and TEXT functions for formatting.
42. How to remove duplicates in Excel?
Answer: Use Data → Remove Duplicates or the UNIQUE() function in modern Excel.
43. What is Text-to-Columns used for?
Answer: It splits one column into multiple columns using delimiters (comma, space, tab).
44. How to use Flash Fill?
Answer: Flash Fill automatically fills values based on patterns (Ctrl+E). Useful for cleaning names, emails, etc.
45. What are PivotTables in Excel?
Answer: PivotTables summarize, analyze, and aggregate large datasets dynamically with drag-and-drop fields.
46. How to create PivotCharts?
Answer: PivotCharts are visual representations of PivotTables, updating automatically when PivotTables refresh.
47. Difference between regular chart and PivotChart?
Answer: Regular charts use static data ranges; PivotCharts link to PivotTables, making them dynamic.
48. What are slicers in Excel?
Answer: Slicers provide clickable filters for PivotTables and PivotCharts, improving interactivity.
49. What is a Timeline in PivotTables?
Answer: A special slicer for date fields that allows filtering by months, quarters, or years.
50. How to group data in PivotTables?
Answer: Right-click → Group allows grouping by numbers (bins), text, or dates (months/quarters/years).
51. What is Conditional Formatting?
Answer: A feature to highlight cells with colors/icons based on conditions (e.g., highlight sales > 1000).
52. What is the difference between Custom Sorting and AutoSort?
Answer: AutoSort orders data ascending/descending; Custom Sort allows sorting by custom rules (e.g., High/Medium/Low).
53. How to use Data Validation?
Answer: It restricts inputs (e.g., only numbers, dates, or dropdown lists for consistency).
54. What is the difference between Freeze Panes and Split?
Answer: Freeze locks rows/columns for scrolling; Split divides the window into multiple scrollable areas.
55. What are Named Ranges in Excel?
Answer: Named Ranges assign names to cell ranges for easier formula writing and navigation.
56. What is Power Query in Excel?
Answer: Power Query is a data connection, transformation, and automation tool (ETL) inside Excel.
57. How do you load data into Power Query?
Answer: Use Data → Get Data (from CSV, Excel, SQL, Web, etc.) to open datasets in Power Query Editor.
58. What transformations can you perform in Power Query?
Answer: Remove duplicates, filter rows, change data types, split/merge columns, pivot/unpivot data.
59. What is the difference between Power Query and PivotTables?
Answer: Power Query transforms and cleans raw data; PivotTables summarize already cleaned data.
60. How do you refresh data in Power Query?
Answer: Click Refresh All in Excel; it re-runs all applied Power Query transformations on the source data.
Advanced Power Query, M Language & Data Modeling (Q61–80)
Fourth set from the Top 100 Excel & Power Query Interview Questions [2025]: joins, merges, appends, advanced transformations, M language basics, and data modeling integration. Useful for Power Query advanced interview questions and Excel data analyst interviews.
61. Difference between Append and Merge in Power Query?
Answer: Append stacks tables (like UNION in SQL), Merge joins tables based on matching keys.
62. What join types are available in Power Query?
Answer: Left, Right, Inner, Full Outer, Anti-Join (Left Anti & Right Anti).
63. How to unpivot data in Power Query?
Answer: Select columns → Right-click → Unpivot → Converts wide-format data into long-format.
64. What is the benefit of Power Query over manual cleaning?
Answer: Automated, repeatable transformations that can be refreshed with one click.
65. What is M language in Power Query?
Answer: A functional programming language used behind the scenes in Power Query to define transformation steps.
66. How to view or edit M code?
Answer: Open the Advanced Editor in Power Query to see or edit the applied steps in M language.
67. Example of simple M code?
Answer: = Table.RemoveColumns(Source,{"Column1"}) removes Column1 from a table.
68. How to change column data types in Power Query?
Answer: Use “Data Type” dropdown in Power Query or M function Table.TransformColumnTypes().
69. How to create a custom column?
Answer: Add Column → Custom Column → Write expressions using M language (e.g., if/else logic).
70. Difference between “Replace Values” and “Transform”?
Answer: Replace Values substitutes specific data; Transform applies changes to data type, case, or format.
71. What is query folding in Power Query?
Answer: When Power Query pushes transformations back to the data source (SQL Server, etc.) for efficiency.
72. How to check if query folding is happening?
Answer: Right-click a step → View Native Query (if enabled, folding is happening).
73. How to merge multiple files in Power Query?
Answer: Use “Folder” connector → Combine Files → Power Query auto-generates consolidation steps.
74. Difference between Power Query and Power Pivot?
Answer: Power Query transforms data; Power Pivot models & analyzes data using DAX.
75. How to create a date table in Power Query?
Answer: Use M function List.Dates() to generate dates, then convert to a table.
76. Can Power Query handle large datasets?
Answer: Yes, but performance depends on query folding and system resources.
77. How to parameterize queries in Power Query?
Answer: Create Parameters (e.g., file path, date) and reference them in queries for dynamic loading.
78. How to handle errors in Power Query?
Answer: Use “Remove Errors” or add conditional columns to replace/fix error values.
79. How to document steps in Power Query?
Answer: Rename steps meaningfully, add descriptions in Advanced Editor, or use comments in M code.
80. Can you reuse queries across workbooks?
Answer: Yes, export/import queries as .pqt files or copy/paste M code between workbooks.
Real-World Case Studies & Analyst Scenarios (Q81–100)
Final set from the Top 100 Excel & Power Query Interview Questions [2025]: business scenarios, ETL workflows, performance optimization, and real-time analyst case questions. Ideal for Power Query interview questions for data analysts and Excel data cleaning case studies.
81. Case: Clean sales data with missing customer IDs
Answer: Use Remove Duplicates, Fill Down, and Replace Errors in Power Query to fix customer IDs.
82. Case: Consolidate 12 monthly Excel files
Answer: Use “Folder” connector in Power Query, Combine & Transform → Append queries automatically.
83. Case: Transform data for pivot table reporting
Answer: Use Unpivot to convert wide format (Month1, Month2, …) into long format for pivot use.
84. Case: Combine sales and customer tables
Answer: Merge queries using CustomerID key, choose Left Join to keep all sales records.
85. Case: Remove extra spaces & clean text fields
Answer: Use Transform → Format → Trim/Clean in Power Query.
86. Case: Add “Profit” column (Revenue – Cost)
Answer: Create Custom Column in Power Query with formula [Revenue] - [Cost].
87. Case: Detect duplicate transactions
Answer: Use Remove Duplicates by Transaction ID or flag with Group By → Count Rows.
88. Case: Customer retention report
Answer: Load customer activity → Group By → Count distinct logins by month.
89. Case: Clean inconsistent date formats
Answer: Change data type to Date in Power Query → Power Query auto-detects & converts formats.
90. Case: ETL from CSV → Clean → Load to Excel
Answer: Import CSV → Transform in Power Query → Close & Load to Excel sheet.
91. How to handle performance issues in Power Query?
Answer: Enable query folding, filter early, reduce applied steps, and remove unnecessary columns.
92. Case: Create rolling 12-month sales summary
Answer: Use Date filters in Power Query or DAX measures in Power Pivot after load.
93. Case: Build KPI dashboard in Excel using Power Query
Answer: ETL data in Power Query → Load to Data Model → Build PivotCharts for KPIs.
94. Case: Dynamic file path for queries
Answer: Use Parameter for file path → Reference parameter in query → Allows easy updates.
95. Case: Handling nulls in financial data
Answer: Use Fill Down for balance sheets or fillna() equivalent in Power Query.
96. Case: Extract year & month from transaction date
Answer: Use Date.Year() and Date.Month() functions in Power Query.
97. Case: Merge survey results with demographics
Answer: Merge queries on RespondentID, expand fields, then transform categorical responses.
98. Case: Automate monthly report generation
Answer: Connect to data source → Apply transformation steps → Refresh each month automatically.
99. Case: Power Query vs SQL for ETL?
Answer: Power Query = no-code ETL for analysts, SQL = code-based for database admins. Often used together.
100. End-to-end case: Clean sales, merge customers, create profit dashboard
Answer: Import sales → Clean nulls & duplicates → Merge with customer data → Add Profit column → Load to Pivot → Build dashboard.
🎯 Excel & Power Query Quick Quiz (20 Questions)
Test your knowledge with these MCQs from our Top 100 Excel & Power Query Questions. Each question has a one-word answer. ✅
🚀 Boost Your Career with Vista Academy
Enroll in our industry-leading courses in Dehradun and build a strong career in Data Analytics, Business Analytics, and Data Science.
📞 +91 94117 78145 | 📧 thevistaacademy@gmail.com | 💬 WhatsApp