18.9 Capgemini, Infosys, Cognizant and Wipro

M61. In an online test you are asked to filter data, build a pivot and apply VLOOKUP. How do you approach it?

Reported for: Capgemini [S11]

Convert the data to a Table first, read the task carefully, apply filters with exact criteria, build the pivot from the Table on a new sheet, and write VLOOKUP with FALSE for exact match and absolute references. Check a couple of results manually and watch for #N/A caused by spaces or text numbers.

M62. You are given a raw dataset: clean it and build charts.

Reported for: Capgemini [S11]

Keep a raw copy; remove duplicates, trim and standardise text, fix number and date types, handle blanks; convert to a Table; summarise with a pivot; build a suitable chart (line for trend, bar for comparison) with a clear title and labels; state one insight in words.

M63. How would you use PivotTables and conditional formatting on sales data?

Reported for: Infosys [S12]

Pivot sales by city and month, then apply conditional formatting to the pivot values (Color Scale or Icon Sets; choose "All cells showing Sum of Amount values" so the rule survives refresh). This instantly shows which city-month cells are weak or strong.

M64. Create visual summaries using PivotTables and charts.

Reported for: Cognizant [S13]

Build one pivot per question (sales by city, trend by date, share by platform), insert PivotCharts, hide field buttons, add a slicer connected to all pivots and arrange them on one sheet – a mini dashboard (Module 13).

M65. What is data cleansing?

Reported for: Wipro [S14]

The process of detecting and correcting errors and inconsistencies – duplicates, missing values, wrong types, inconsistent spellings, extra spaces, invalid entries and outliers – so the analysis is accurate and trustworthy. It includes documenting the steps and reconciling counts and totals before and after.

M66. Can VLOOKUP or HLOOKUP be used for data cleansing?

Reported for: Wipro [S14]

They are lookup functions, not cleaning functions, but they support cleansing: a lookup against a mapping table standardises values ("Aurangabad" → "Sambhaji Nagar"), and a lookup against a master list exposes invalid or missing codes through #N/A. Actual text cleaning is done with TRIM, CLEAN, PROPER, SUBSTITUTE or Power Query.