Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About TCS hiring for Advanced Excel roles
Tata Consultancy Services is a Tata Group company and one of the largest IT services and consulting firms in the world. It delivers application development, testing, cloud, data and analytics services to clients in banking, retail, manufacturing and other industries. In India it hires freshers and experienced staff for software testing, data engineering, BI reporting and analytics roles across its delivery centres.
For Advanced Excel roles, this TCS practice set focuses most on Logical, Text and Date Functions (7), Excel Fundamentals and Formatting (5), Macros and VBA Automation (4), Power Query and Data Cleaning (4). It has 15 easy, 15 medium questions, so you can start easy and work up to the harder ones.
Company type
IT services
Headquarters
Mumbai, India
Main India offices
Mumbai, Chennai, Pune, Bengaluru, Hyderabad
TCS Advanced Excel interview process
TCS NQT online assessment (aptitude + coding)
Technical interview
Managerial round
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for TCSStart 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 form controls like dropdowns, check boxes and scroll bars make a dashboard interactive?
Form controls are added from the Developer tab > Insert. Each control writes a value into a linked cell: a combo box writes the position of the selected item, a check box writes TRUE or FALSE, and a scroll bar writes a number within a set range. Formulas such as INDEX, CHOOSE or FILTER read that cell to change the chart data, so the dashboard reacts without any macros.
02
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.
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.
Loop from the bottom up so deleting a row does not skip the next one:
Sub DeleteBlankRows()
Dim r As Long, ws As Worksheet
Set ws = ActiveSheet
For r = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Trim(ws.Cells(r, "A").Value) = "" Then ws.Rows(r).Delete
Next r
End Sub
For big data, filtering blanks and deleting visible rows in one action is faster.
05
Return the Indian financial year (April to March) for a date, like FY 2026-27.
If the month is April or later, the FY starts in the same year; otherwise it started the previous year:
=LET(y,IF(MONTH(A2)>=4,YEAR(A2),YEAR(A2)-1),
"FY "&y&"-"&RIGHT(y+1,2))
For 15-May-2026 this gives FY 2026-27, and for 10-Feb-2026 it gives FY 2025-26. The fiscal quarter is =ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0).
EDATE moves a date by whole months, keeping the day where possible; EOMONTH returns the last day of a month that many months away.
=EDATE(A2,3) date three months later, useful for EMI or renewal dates
=EOMONTH(A2,0) last day of the same month
=EOMONTH(A2,-1)+1 first day of the same month
These are standard in billing cycles, month-end closing and ageing reports.
07
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.
08
Extract the first and last name from a full name like Rahul Kumar Sharma.
Classic formulas:
First: =LEFT(A2,FIND(" ",A2)-1)
Last: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
In Microsoft 365 it is simpler:
First: =TEXTBEFORE(A2," ")
Last: =TEXTAFTER(A2," ",-1)
The -1 tells TEXTAFTER to use the last space, which handles middle names. Run TRIM on the source first to remove double spaces.
09
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.
10
What does SUMPRODUCT do and when would you use it?
SUMPRODUCT multiplies matching items in arrays and adds the results. It is ideal for weighted averages and for conditional sums in older Excel versions:
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) weighted average price
=SUMPRODUCT((A2:A100="North")*(MONTH(D2:D100)=4)*C2:C100)
The second example handles a condition on MONTH, which SUMIFS cannot do directly. Avoid full-column references because SUMPRODUCT evaluates every row.
11
A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?
Use Get Data > From PDF or import the converted file, then promote the first row to headers. Filter out rows where the first column equals the header text, such as Name, and rows containing footer text like Page or Printed on, using Text Filters > Does Not Contain. Remove blank rows, Fill Down any group labels, and set types. The steps then run automatically on next month's PDF.
12
What is the difference between Flash Fill and Text to Columns?
Text to Columns splits a column by a fixed delimiter or fixed width in one go, which suits clean data like comma-separated codes. Flash Fill (Ctrl+E) learns a pattern from one or two examples you type, such as extracting first names or formatting phone numbers. Both produce static values, so if the source changes you must redo them, unlike formulas or Power Query steps.
Structured references use Table and column names instead of cell addresses, which makes formulas readable and self-adjusting. For a Table named Sales:
=SUM(Sales[Amount]) totals the whole column
=[@Qty]*[@Price] multiplies values in the same row
=COUNTIFS(Sales[Region],"North")
If rows are added or a column moves, the formula still points to the right data.
IFERROR replaces any error, such as #N/A, #DIV/0!, #VALUE! or #REF!, with a value you choose. IFNA replaces only #N/A, which is the normal result when a lookup finds no match. IFNA is safer around lookups because a genuine problem like a deleted column (#REF!) still shows up instead of being hidden as a blank or zero. Use IFERROR only where every error truly means the same thing.
15
Explain the match_mode and search_mode arguments of XLOOKUP.
match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. search_mode: 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending.
=XLOOKUP("*Infra*",Cust[Name],Cust[ID],,2) wildcard match
=XLOOKUP(A2,Log[Item],Log[Price],,0,-1) last occurrence
Binary modes are fast but give wrong answers if the data is not sorted.
16
HR wants a column that marks employees Eligible for bonus if their rating is 4 or more and tenure is over 1 year, or if they appear in a Top Performers list. How do you write it?
Combine AND inside OR, and use COUNTIF to check the list:
=IF(OR(AND(C2>=4, D2>1), COUNTIF(TopPerformers, A2)>0), "Eligible", "Not Eligible")
Here C is rating, D is tenure in years, A is employee ID and TopPerformers is a named range. Matching on ID instead of name avoids errors from spelling differences.
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.
18
What are Applied Steps and the M language in Power Query?
Each transformation you click, like removing columns or changing types, is saved as a step in the Applied Steps pane, and the steps run in order on every refresh. Behind them is M, a functional formula language visible in the Advanced Editor. You can rename steps, delete or reorder them, and edit M directly for logic the interface does not provide, such as custom functions or dynamic column lists.
19
How does INDEX/MATCH work and why is it often preferred over VLOOKUP?
MATCH finds the position of a value in a single row or column, and INDEX returns the item at that position from another range:
=INDEX(Emp!$C:$C,MATCH(A2,Emp!$F:$F,0))
The return column can be to the left of the lookup column, inserting columns does not break it, and it only scans the two columns used. It works in every Excel version, which matters when files go to clients on older Excel.
20
What is the difference between Protect Sheet, Protect Workbook and Encrypt with Password?
Protect Sheet stops edits to locked cells; you first unlock input cells through Format Cells > Protection so users can type only there. Protect Workbook locks the structure, so sheets cannot be added, deleted or renamed. Encrypt with Password (File > Info) is the only option that blocks opening the file. Sheet and workbook protection are about preventing accidents, not real security, because they are easy to bypass.
21
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.
22
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.
When you click a pivot cell while writing a formula, Excel inserts GETPIVOTDATA, which pulls a value by field names instead of cell position:
=GETPIVOTDATA("Amount",$A$3,"Region","North","Month","Apr")
It keeps working when the pivot layout changes, which suits KPI cards in dashboards. If you want plain references, turn it off in PivotTable Analyze > Options > Generate GetPivotData.
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.
25
A few very large values make every other bar in your chart look tiny. How do you present the data clearly?
Options depend on the message. Sort bars and show the top items with the rest grouped as Others; split into two charts, one for the giants and one for the rest; add data labels so small values are still readable; or use a logarithmic axis for technical audiences, clearly labelled. Avoid cutting the axis on bar charts, because bar length then stops representing value and misleads readers.
26
A macro produces wrong totals because a misspelt variable name was silently treated as a new empty variable. How do you prevent this?
Add Option Explicit at the top of every module, and enable Tools > Options > Require Variable Declaration so new modules get it automatically. VBA then refuses to run code that uses an undeclared variable and highlights the typo at compile time. Also declare proper types such as Long or Double instead of Variant, which catches type mistakes and makes the code faster and easier to read.
Trust Center settings control whether macros run: disabled with notification is typical. Files in a trusted location, or signed with a trusted digital certificate, run without prompts. Since 2022, Office blocks macros by default in files downloaded from the internet or email attachments that carry the Mark of the Web; users must unblock the file in its Properties. Organisations usually manage these settings through policy.
28
How do you automatically highlight the highest and lowest points in a line chart?
Add two helper columns next to the data that show a value only at the max or min and #N/A elsewhere, because charts skip #N/A:
Max: =IF(B2=MAX($B$2:$B$13),B2,NA())
Min: =IF(B2=MIN($B$2:$B$13),B2,NA())
Add both as series, set them to markers only with distinct colours and data labels. When the data changes, the highlighted points move automatically.
29
What is the difference between a calculated field and a calculated item?
A calculated field creates a new value field from other fields, such as Margin = Profit / Sales, and it works on the summed totals. A calculated item creates a new member inside a field, such as North + East as a combined region. Calculated items cannot be used with the Data Model, can double-count in totals and slow pivots, so many analysts prefer grouping or a source column instead.
30
What is conditional formatting? Give two business uses.
Conditional formatting changes a cell's colour, font, icon or data bar based on a rule, so patterns stand out without reading every number. Common uses are highlighting invoices overdue by more than 30 days in red and adding data bars to a sales column so top performers are visible at a glance. Keep rules few and consistent, because many overlapping rules slow down large files.
Tips to crack the TCS Advanced Excel interview
Practise TCS NQT style numerical, verbal and reasoning sections along with two basic coding problems under strict time limits.
Be ready to explain your final-year project, SQL basics and core concepts like OOP, DBMS and software testing life cycle.
Read about TCS service lines such as BFSI and its iON and Ignio platforms to answer why you want to join.
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; TCS interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the TCS Advanced Excel interview?
Candidates usually go through 4 stages: TCS NQT online assessment (aptitude + coding); Technical interview; Managerial round; 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 TCS Advanced Excel interview?
This practice set leans on Logical, Text and Date Functions (7), Excel Fundamentals and Formatting (5), Macros and VBA Automation (4), Power Query and Data Cleaning (4). Revise those first, then practise explaining one or two of your own projects end to end, because most TCS interviewers ask you to walk through real work.
Are these the exact questions asked at TCS?
No. These are curated practice questions that match the skills and difficulty usually tested in it services interviews like TCS's. They are not a leaked or verified list of past questions.
Where does TCS hire Advanced Excel professionals in India?
TCS has large teams in Mumbai, Chennai, Pune, Bengaluru, Hyderabad. Openings and locations change often, so check the TCS careers page and filter by city and experience.