Advanced Data Analysis Q&A

Get advanced interview questions and answers on Data Analysis using Excel, Google Sheets, and WPS Office – ideal for analyst roles, HR ops, finance, reporting, and MIS positions

 

Courtesy: anywheregk.in
Get certified in Data Analysis, Excel, Google Sheets & MIS from India’s leading edutech & staffing platform

 

Advanced Data Analysis Q&A – Excel, Google Sheets & WPS Office

Number	Tool/Topic	Question	Answer
1	Excel	What is a Pivot Table and how is it used?	A Pivot Table summarizes large data sets dynamically, letting users analyze totals, averages, and trends by row/column categories.
2	Excel	Difference between VLOOKUP and XLOOKUP?	VLOOKUP works only vertically and left to right; XLOOKUP allows both directions, better error handling, and replaces VLOOKUP, HLOOKUP.
3	Excel	When would you use INDEX-MATCH over VLOOKUP?	INDEX-MATCH is faster on large datasets, allows left-side lookups, and works well in dynamic formulas.
4	Excel	How do you create a dynamic dashboard in Excel?	Use Pivot Tables, slicers, charts, named ranges, and formulas to build interactive and auto-updating dashboards.
5	Excel	What is conditional formatting?	It highlights cells based on rules (e.g., color-coding top values or overdue dates), making patterns and issues visible.
6	Excel	How do you remove duplicates in Excel?	Use the “Remove Duplicates” tool under Data tab or use advanced filters or formulas like UNIQUE().
7	Excel	What is the purpose of Data Validation?	To control input in a cell (e.g., dropdowns, text limits), ensuring data accuracy and consistency.
8	Excel	How do you handle missing data?	Use filtering, conditional formatting, IFERROR(), or formulas like IF(ISBLANK()) to flag, fill, or ignore missing values.
9	Excel	What is Power Query used for?	Power Query is used to import, clean, transform, and load data from various sources without complex coding.
10	Excel	What are array formulas?	Formulas that can return multiple results using Ctrl+Shift+Enter (legacy) or dynamic arrays (Excel 365).
11	Google Sheets	How is QUERY() different from FILTER()?	QUERY() uses SQL-like syntax for complex queries; FILTER() returns rows that match a condition. QUERY is more powerful.
12	Google Sheets	How can you share and collaborate on real-time reports?	Use “Share” and set permissions. Multiple users can view/edit simultaneously with live changes and comment threads.
13	Google Sheets	What is IMPORTRANGE()?	It allows data import from other Google Sheets using URL and range reference – useful for consolidating reports.
14	Google Sheets	Can you do Pivot Tables in Google Sheets?	Yes, Google Sheets supports Pivot Tables under Data → Pivot Table, offering similar flexibility as Excel.
15	Google Sheets	What is Apps Script?	Apps Script is Google’s scripting tool to automate tasks, build custom functions, and enhance Sheets functionality.
16	Google Sheets	How do you protect a sheet or cell?	Go to Data → Protect Sheets and Ranges. Assign permissions to prevent unwanted edits.
17	Google Sheets	How do you apply conditional formatting for text and dates?	Use custom formulas like =TEXT(A1,"MMMM")="July" or =A1>TODAY() under conditional formatting.
18	Google Sheets	Explain how to use SPLIT() and TEXTJOIN().	SPLIT breaks text into multiple cells based on a delimiter; TEXTJOIN combines values with a delimiter.
19	WPS Office	Is WPS Spreadsheet compatible with Excel formulas?	Yes, most Excel formulas (VLOOKUP, IF, SUMIFS, etc.) are supported in WPS Spreadsheet.
20	WPS Office	Can WPS Office handle large datasets like Excel?	Yes, WPS supports large datasets with similar features, though performance might vary with Excel 365.
21	Excel	How do you automate monthly reports?	Use dynamic ranges, formulas, Power Query, and optionally macros or VBA to auto-refresh data.
22	Excel	What is the use of SUMIFS()?	SUMIFS adds values that meet multiple criteria – e.g., total salary by department and month.
23	Excel	What is the difference between COUNTIF and COUNTIFS?	COUNTIF applies a single condition, COUNTIFS supports multiple criteria for counting data.
24	Excel	What is the use of the OFFSET function?	OFFSET returns a dynamic cell or range based on reference – used in dynamic charts and named ranges.
25	Excel	What is a slicer?	A visual filter added to Pivot Tables/Charts for user-friendly data exploration.
26	Google Sheets	How do you create a drop-down list?	Use Data → Data Validation → List of items or range-based list.
27	Excel/Sheets	How can you track changes in a shared sheet?	Excel: Use Track Changes feature; Sheets: use Version History to monitor edits and collaborators.
28	Google Sheets	How do you create a progress tracker with conditional formatting?	Use percentage bars or color-scale formatting based on task completion values.
29	Excel/Sheets	What is data cleaning in analytics?	Removing errors, duplicates, blanks, and inconsistencies to prepare data for analysis.
30	Excel/Sheets	How do you merge datasets from different files?	Use Power Query (Excel), IMPORTRANGE (Sheets), or copy-paste with structured lookups or joins.

---

Here are additional advanced interview questions and answers focused specifically on VLOOKUP, XLOOKUP, Data Cleaning, and Pivot Tables — perfect for analytics, finance, HR ops, MIS, and admin interviews.

Focused Q&A: VLOOKUP, XLOOKUP, Data Cleaning, Pivot Tables

Number	Topic	Question	Answer
1	VLOOKUP	What is the syntax of VLOOKUP in Excel?	=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) – It searches a value vertically in the first column and returns corresponding data.
2	VLOOKUP	What are common errors in VLOOKUP and how to fix them?	#N/A (not found), #REF (wrong column index), incorrect range – ensure exact match (FALSE), correct data range, and no leading/trailing spaces.
3	VLOOKUP	How to do VLOOKUP across multiple sheets or workbooks?	Use the sheet or workbook reference in the table_array, like =VLOOKUP(A2,Sheet2!A:D,2,FALSE) or use named ranges for clarity.
4	VLOOKUP	Can VLOOKUP return multiple values?	No, VLOOKUP returns the first match. Use FILTER(), TEXTJOIN(), or Power Query for multiple matches.
5	XLOOKUP	What is the difference between XLOOKUP and VLOOKUP?	XLOOKUP can search both vertically & horizontally, supports approximate/exact matches, error handling, and replaces VLOOKUP/HLOOKUP/INDEX-MATCH.
6	XLOOKUP	What is the syntax of XLOOKUP?	=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) – powerful and flexible lookup.
7	XLOOKUP	What does [if_not_found] do in XLOOKUP?	It provides a custom result if the lookup value isn’t found, instead of displaying an error like #N/A.
8	XLOOKUP	Can XLOOKUP search from last to first?	Yes, by setting the [search_mode] to -1 (reverse search). Useful for finding the last match.
9	Data Cleaning	What are common data cleaning techniques in Excel or Sheets?	Removing duplicates, trimming spaces, fixing text case, handling blanks, using Find/Replace, IFERROR(), and converting text to numbers/dates.
10	Data Cleaning	What is the purpose of the TRIM(), CLEAN(), and SUBSTITUTE() functions?	TRIM removes extra spaces, CLEAN removes non-printable characters, SUBSTITUTE replaces specific characters or strings in text.
11	Data Cleaning	How to handle inconsistent date or number formats?	Use TEXT(), DATEVALUE(), or formatting options. Ensure consistent locale and format settings across sheets.
12	Data Cleaning	How do you identify and remove duplicates?	Use “Remove Duplicates” under Data tab, or formulas like =COUNTIF() to find and flag repeated rows.
13	Pivot Table	What is a Pivot Table used for in analysis?	To quickly summarize and analyze data – totals, averages, counts – by dragging fields into rows, columns, and values.
14	Pivot Table	How do you filter data in a Pivot Table?	Use report filters, slicers, or row/column label filters to drill down or segment data easily.
15	Pivot Table	Can you group data in Pivot Tables?	Yes. You can group dates (months, years), numbers (ranges), or text values for summarized reporting.
16	Pivot Table	What are calculated fields in a Pivot Table?	Custom formulas added to a Pivot Table that perform calculations using fields in the data set.
17	Pivot Table	What are slicers and how do they enhance Pivot Tables?	Slicers are visual filters that allow users to filter Pivot Tables easily with a click.
18	Pivot Table	How do you refresh a Pivot Table automatically?	Right-click the table and select “Refresh.” To auto-refresh on file open, use VBA or Power Query settings.