Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About Capgemini hiring for Advanced Excel roles
Capgemini is a French multinational IT services and consulting group providing technology, engineering, cloud and data services. India is its largest employee base, supporting projects in testing, application development, SAP, data engineering and analytics for global clients. It hires freshers as analysts and software engineers, and experienced professionals in QA automation, data and cloud roles.
For Advanced Excel roles, this Capgemini practice set focuses most on Power Query and Data Cleaning (7), Charts and Dashboards (5), Logical, Text and Date Functions (4), Excel Fundamentals and Formatting (4). It has 14 easy, 15 medium, 1 hard questions, so you can start easy and work up to the harder ones.
Company type
IT services
Headquarters
Paris, France
Main India offices
Mumbai, Bengaluru, Pune, Chennai, Hyderabad
Capgemini Advanced Excel interview process
Online assessment (pseudocode + English)
Game-based aptitude test
Technical interview
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for CapgeminiStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
How do you show only the top 10 customers by revenue in a pivot?
Put Customer in Rows and Revenue in Values. Click the Customer filter arrow > Value Filters > Top 10, choose Top, 10, Items, by Sum of Revenue. Then sort the Revenue column largest to smallest. The grand total by default still reflects only the visible rows; if you need overall totals, add a separate pivot or enable Include filtered items in totals when using the Data Model.
The Data Model is an in-memory database inside the workbook, powered by the VertiPaq engine, which compresses data by column and can handle millions of rows, far beyond the sheet limit. Power Pivot is the window used to manage it: import tables, create relationships, and write DAX measures and calculated columns. Pivot tables built on the Data Model can combine several related tables without VLOOKUP.
03
Why does a date show as a number like 46023 when joined with text, and how do you fix it?
Excel stores dates as serial numbers, so joining with & uses the raw number instead of the formatted view. Wrap the date in TEXT with a format code:
="Report date: "&TEXT(A2,"dd-mmm-yyyy")
="Sales: Rs "&TEXT(B2,"#,##0")
TEXT returns text, so use it only for labels and titles, never for values that need further calculation.
The & operator joins items one by one, CONCAT joins a list or range, and TEXTJOIN adds a delimiter and can skip blanks:
=A2&" "&B2
=CONCAT(A2:C2)
=TEXTJOIN(", ",TRUE,A2:E2)
TEXTJOIN is the best choice for building address lines or a comma-separated list of selected items, because the TRUE argument avoids double commas where cells are empty.
05
SUM returns 0 on a column of numbers exported from software. How do you fix it?
The numbers are most likely stored as text, often with leading apostrophes, trailing spaces or non-breaking spaces (CHAR(160)). Check with =ISTEXT(A2) or LEN. Quick fixes are Data > Text to Columns > Finish, the green error triangle > Convert to Number, or Paste Special > Multiply by 1. For stubborn cases use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) or set the column type to Decimal in Power Query.
06
Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.
Custom formats have up to four sections separated by semicolons: positive; negative; zero; text. Press Ctrl+1 > Number > Custom and enter:
[Green]#,##0;[Red](#,##0);"-";@
For Indian lakh-crore grouping you can use:
[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0
The value stays unchanged; only the display changes.
07
How do you choose the right chart type for business data?
Match the chart to the question. Use column or bar charts to compare categories, line charts for trends over time, pie or doughnut only for a share of a whole with few slices, scatter charts for the relationship between two numbers, histograms for distribution, and combo charts when two measures with different scales belong together, such as sales and margin percentage. The simplest chart that answers the question is usually the best.
VLOOKUP can only look to the right of the key column, so it cannot return a value from a column on the left. The column index is a hard-coded number that breaks when columns are inserted. It returns only the first match, defaults to approximate match if the last argument is forgotten, and can be slow on big ranges. XLOOKUP or INDEX/MATCH solve all of these problems.
09
Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?
Load the data into Power Query, select the identifying columns such as Branch and Product, then right-click and choose Unpivot Other Columns. The month headers become an Attribute column and the numbers become a Value column; rename them to Month and Sales. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are included automatically on refresh.
10
How do you create a chart that automatically shows the last 12 months as new data is added?
Create named ranges with INDEX so they move with the data, assuming dates in column A from row 2:
LastRow: =COUNTA(Data!$A:$A)
ChartDates: =INDEX(Data!$A:$A,LastRow-11):INDEX(Data!$A:$A,LastRow)
ChartSales: =INDEX(Data!$B:$B,LastRow-11):INDEX(Data!$B:$B,LastRow)
Then edit the chart series to =Book1.xlsx!ChartSales and the axis to ChartDates. A Table-based chart grows automatically but shows all months, not a rolling window.
11
What is the difference between nested IF and IFS? Show an example.
Nested IF places one IF inside another, which becomes hard to read after three levels. IFS (Excel 2019 and later) tests conditions in order and returns the value for the first true one:
=IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail")
The final TRUE acts as the else part. For many bands, a lookup table with XLOOKUP approximate match is easier to maintain than either.
12
Tell me about a time a lookup gave wrong results that nobody noticed at first.
Situation: a monthly incentive sheet used VLOOKUP with the last argument left blank, so it ran an approximate match. Task: I was asked to verify payouts before release. Action: I spotted employees getting a neighbour's target, changed every lookup to exact match with XLOOKUP, and added a count of missing IDs. Result: twelve wrong payouts were corrected before payment, and exact match became our team's standard rule.
13
Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?
The source file's header changed, for example Amount became Amount (Rs), and a step that refers to the old name, often Changed Type or Renamed Columns, breaks. Open the query, click through the steps to find the first error, and update the name or rename the column back early in the query. To prevent repeats, agree a fixed template with the data owner and avoid steps that list every column unnecessarily.
An asterisk matches any number of characters, a question mark matches exactly one, and a tilde escapes a real * or ?.
=COUNTIF(A:A,"*Pvt Ltd*") names containing Pvt Ltd
=SUMIFS(C:C,B:B,"INV-2026-????") invoice codes of a fixed length
=COUNTIF(A:A,"*~?*") cells that contain a question mark
Wildcards work only on text criteria, not on numbers.
15
Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?
Data types decide how values are sorted, summed and loaded, so a number stored as text will not total and a date stored as text will not group by month. Power Query adds an automatic Changed Type step that hard-codes every column name, so a renamed or removed column later causes refresh errors. Many analysts delete it and set types once, deliberately, after the structure is final.
16
What principles do you follow when designing an Excel dashboard?
Start from the questions the audience asks. Put three to five headline KPIs at the top, trends and breakdowns below, and filters such as slicers in one consistent place. Use one colour palette with a highlight colour for what matters, avoid 3D and decorative effects, label units clearly like Rs lakh, and fit the view on one screen. Keep raw data and calculations on separate hidden sheets.
17
Explain VLOOKUP syntax and the difference between exact and approximate match.
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) searches the first column of the table and returns a value from the column number given:
=VLOOKUP(A2,Products!$A:$D,3,FALSE)
FALSE (or 0) means exact match and is what you need for IDs and codes. TRUE, the default, means approximate match and needs the first column sorted ascending; it is used for slabs like tax or discount bands.
XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode) is available in Microsoft 365 and Excel 2021 or later:
=XLOOKUP(A2,Emp[ID],Emp[Name],"Not found")
It defaults to exact match, looks left or right, has a built-in not-found value, can return several columns at once, and can search from the bottom up. It replaces VLOOKUP, HLOOKUP and most INDEX/MATCH uses.
19
Ten regional heads each need the same dashboard but must see only their own region. How do you deliver it?
Since Excel files cannot restrict data inside a workbook securely, create separate files. Add a Region parameter cell that drives Power Query filters or pivot filters, then loop through regions with a short VBA macro that sets the region, refreshes, removes other data and saves a copy or PDF per region. For regular use at scale, Power BI with row-level security is the better solution.
20
How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?
Select the column, choose Split Column > By Delimiter, enter space-hyphen-space as a custom delimiter and split at each occurrence. This gives ID, Name and City columns. Then use Transform > Format > Trim and Clean to remove extra spaces and hidden characters, Capitalize Each Word for names, and set correct data types. Split by Number of Characters handles fixed-width codes like state and branch prefixes.
21
How do you build a dependent dropdown, where the City list changes based on the selected State?
Create a list of cities under each state name and give each list a named range matching the state, for example Maharashtra or Karnataka (no spaces). In B2 make a State list. In C2 use Data Validation > List with source:
=INDIRECT(B2)
In Microsoft 365 you can avoid named ranges with a spill formula in a helper cell:
=FILTER(Cities[City],Cities[State]=B2)
and point the validation at that spill range using =$H$2#.
22
What is the difference between Summarize Values By and Show Values As?
Summarize Values By chooses the calculation, such as Sum, Count, Average, Max or Min. Show Values As changes how that result is presented, for example % of Grand Total, % of Column Total, % of Parent Row Total, Running Total In, Difference From or Rank Largest to Smallest. Adding the same field twice, once as Sum and once as % of Grand Total, shows the amount and the share side by side.
Pie charts become unreadable with more than five or six slices, because people are poor at comparing angles. They cannot show change over time, and 3D or exploded pies distort sizes. Slices should add up to a meaningful whole; percentages from different bases should never be in one pie. A sorted bar chart almost always communicates the same information more clearly.
24
An imported report has merged cells, blank spacer rows and subtotal lines, so sorting and filtering keep failing. How do you make it usable?
Work on a copy. Unmerge all cells, then fill the blanks with the value above using Go To Special > Blanks and =cell above, followed by paste as values. Filter out blank rows and subtotal lines and delete them, keep one header row and convert the range to a Table. If the same report arrives every month, repeat these steps once in Power Query so the cleanup becomes a single refresh.
25
What are VBA events? Give an example of Worksheet_Change.
Events run code automatically when something happens, such as opening a workbook (Workbook_Open) or editing a cell (Worksheet_Change). In the sheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now
Application.EnableEvents = True
End If
End Sub
This timestamps column G when status in F changes.
26
What is Power Query and why use it instead of manual cleaning?
Power Query (Data > Get & Transform) is Excel's tool to import, clean and reshape data from files, folders, databases and the web. Every action is recorded as a step, so next month you just click Refresh and the same cleaning runs again on new data. This removes repeated copy-paste work, reduces errors and keeps the original source untouched, which manual cleaning cannot offer.
27
Tell me about a time you automated a repetitive task.
Situation: every Monday I merged five regional order files and cleaned them by hand, which took about three hours. Task: reduce the effort and errors. Action: I built a Power Query folder import, standardised columns, fixed date locale issues and loaded the result to a pivot dashboard, then wrote a one-page guide for the team. Result: the job took ten minutes, copy-paste errors disappeared and two other teams adopted the same setup.
28
What is the difference between RELATED and RELATEDTABLE?
RELATED is used on the many side to fetch a single value from the one side, such as getting a product's category in each sales row. RELATEDTABLE is used on the one side to return all matching rows from the many side, so in Products you could write Order Count = COUNTROWS(RELATEDTABLE(Sales)). Both need an existing relationship and a row context.
29
What checks do you run when cleaning a new dataset?
Check row counts against the source, then look for duplicate keys, blanks in mandatory fields, inconsistent text such as Delhi and New Delhi, extra spaces and mixed case, dates stored as text or in the wrong locale, negative or extreme numbers that look like typos, and codes losing leading zeros. Power Query's Column Quality and Column Profile views show errors, empties and distinct counts for each column quickly.
30
A macro works on your computer but fails on a colleague's. What could be the reasons?
Common causes are hard-coded file paths or user names, missing library references shown as MISSING in Tools > References, macros blocked by Trust Center settings or because the file came from the internet, and 32-bit versus 64-bit Office, which needs PtrSafe in API declarations. Different regional settings for dates and decimals, or a renamed sheet, also break code. Use ThisWorkbook.Path and late binding to make macros portable.
Tips to crack the Capgemini Advanced Excel interview
Practise game-based cognitive tests and pseudocode questions, which are commonly part of Capgemini's online assessment.
Prepare a short explanation of your projects, focusing on your role, tools used and what you would improve.
Learn about Capgemini's engineering arm and its work in sectors like automotive and financial services to show interest.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; Capgemini interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the Capgemini Advanced Excel interview?
Candidates usually go through 4 stages: Online assessment (pseudocode + English); Game-based aptitude test; Technical interview; HR round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the Capgemini Advanced Excel interview?
This practice set leans on Power Query and Data Cleaning (7), Charts and Dashboards (5), Logical, Text and Date Functions (4), Excel Fundamentals and Formatting (4). Revise those first, then practise explaining one or two of your own projects end to end, because most Capgemini interviewers ask you to walk through real work.
Are these the exact questions asked at Capgemini?
No. These are curated practice questions that match the skills and difficulty usually tested in it services interviews like Capgemini's. They are not a leaked or verified list of past questions.
Where does Capgemini hire Advanced Excel professionals in India?
Capgemini has large teams in Mumbai, Bengaluru, Pune, Chennai, Hyderabad. Openings and locations change often, so check the Capgemini careers page and filter by city and experience.