Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About Cognizant hiring for Advanced Excel roles
Cognizant is a US-headquartered IT services and consulting company with most of its workforce based in India. It provides application development, testing, cloud, digital engineering and data analytics services, with strong practices in healthcare, insurance and banking. In India it hires for programmer analyst, quality engineer and data roles through campus and off-campus programmes such as GenC.
For Advanced Excel roles, this Cognizant practice set focuses most on Excel Fundamentals and Formatting (5), Lookup and Dynamic Array Functions (5), Power Pivot, Data Model and DAX (4), Pivot Tables and Data Summaries (4). It has 15 easy, 14 medium, 1 hard questions, so you can start easy and work up to the harder ones.
Company type
IT services
Headquarters
Teaneck, New Jersey, USA
Main India offices
Chennai, Bengaluru, Pune, Hyderabad, Kolkata
Cognizant Advanced Excel interview process
Online assessment (aptitude + reasoning)
Coding assessment
Technical interview
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for CognizantStart 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
You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?
Create a relationship from Sales[ProductID] to Products[ProductID], then add a calculated column in Sales:
Category = RELATED(Products[Category])
RELATED follows the many-to-one relationship and works only in a row context. Often you do not need the column at all, because the pivot can use Products[Category] directly through the relationship, which saves memory.
02
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.
03
What does converting a range into an Excel Table (Ctrl+T) give you?
A Table expands automatically when new rows or columns are added, so formulas, charts and pivot tables based on it pick up new data without editing ranges. It also gives banded rows, header filters, a Total Row, calculated columns that fill down by themselves, and structured references like Sales[Amount]. For any data that grows every month, a Table is the safest starting point.
04
What are slicers and timelines, and how do you connect one slicer to several pivots?
Slicers are clickable filter buttons for a field, and timelines are slider filters for dates by day, month, quarter or year. They make filtering obvious for dashboard users. To control several pivots, right-click the slicer and choose Report Connections, then tick each pivot. This works only for pivots built on the same source or data model, because they share the same pivot cache.
05
What is the difference between a Sub and a Function in VBA?
A Sub performs actions, such as formatting cells or saving files, and is run as a macro. A Function returns a value and can be used in worksheet formulas as a user-defined function or called from other code. Both are written in a standard module, opened through Alt+F11 > Insert > Module. Event code goes in the sheet or ThisWorkbook module instead.
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.
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.
08
What is the difference between a workbook, a worksheet and a range?
A workbook is the whole file (.xlsx, .xlsm or .xlsb). A worksheet is one tab inside it with 1,048,576 rows and 16,384 columns. A range is any group of cells such as A2:D100 that a formula, chart or format refers to. Good practice is to keep raw data, calculations and output on separate worksheets of the same workbook so each layer is easy to audit.
09
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.
10
Calculate working days between two dates excluding Sundays and a holiday list.
NETWORKDAYS excludes Saturday and Sunday by default. For a six-day week common in India, use NETWORKDAYS.INTL with weekend code 11 (Sunday only):
=NETWORKDAYS.INTL(A2,B2,11,Holidays[Date])
To find a due date 10 working days after A2:
=WORKDAY.INTL(A2,10,11,Holidays[Date])
Keep the holiday list in a Table so it can be updated each year.
11
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.
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.
13
What is a macro and what file format is needed to save it?
A macro is a recorded or written set of instructions in VBA (Visual Basic for Applications) that automates repeated tasks, such as formatting a report or exporting PDFs. Normal .xlsx files cannot store macros, so the workbook must be saved as .xlsm (macro-enabled) or .xlsb. Macros can be run from the Developer tab, a button, a keyboard shortcut or automatically through events.
14
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.
15
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.
A histogram groups numeric values into bins and shows how often each bin occurs, for example how many orders fall between 0 to 1,000 rupees or 1,000 to 5,000 rupees. A Pareto chart sorts categories by frequency and adds a cumulative percentage line, showing the vital few, such as the 20 percent of complaint types causing 80 percent of complaints. Both are available under Insert > Statistic Charts.
17
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.
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.
19
Calculate an employee's age or tenure in years and months from the joining date.
DATEDIF is an older hidden function that still works but does not appear in autocomplete:
=DATEDIF(B2,TODAY(),"Y")&" years "&DATEDIF(B2,TODAY(),"YM")&" months"
Units: Y full years, M full months, D days, YM months after full years, MD days after full months. For a decimal tenure use =YEARFRAC(B2,TODAY()). The start date must be earlier than the end date or DATEDIF returns #NUM!.
Use On Error GoTo to jump to a handler, report the problem and clean up:
Sub ImportData()
On Error GoTo Fail
Application.ScreenUpdating = False
' ... main code ...
Done:
Application.ScreenUpdating = True
Exit Sub
Fail:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Done
End Sub
Avoid blanket On Error Resume Next, which hides real failures.
21
Your manager asks you to replace all VLOOKUPs in a legacy workbook with XLOOKUP. How do you approach it?
First confirm everyone who opens the file has Microsoft 365 or Excel 2021, because older versions show #NAME? for XLOOKUP. Save a backup, list the VLOOKUPs using Find with Look in: Formulas, and replace them sheet by sheet, keeping an exact-match default. Compare key totals before and after, document the change in a notes sheet, and keep a version that still uses INDEX/MATCH if external clients need it.
22
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.
23
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.
24
You added new rows of data but the pivot does not show them. Why, and how do you prevent it?
The pivot's source is probably a fixed range like A1:H500 and the new rows are below it, or the pivot has not been refreshed. Fix it with PivotTable Analyze > Change Data Source, then Refresh. To prevent it, convert the source to an Excel Table so the range expands automatically, use Data > Refresh All, and enable Refresh data when opening the file in PivotTable Options.
25
Write DAX measures for year-to-date sales and sales for the same period last year.
With a proper Date table marked as a date table and related to Sales:
Sales YTD := TOTALYTD([Total Sales],'Date'[Date],"3/31")
Sales LY := CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date]))
YoY % := DIVIDE([Total Sales]-[Sales LY],[Sales LY])
The "3/31" year-end argument gives an Indian April to March financial year; without it, YTD resets on 1 January.
26
What is the difference between Reference and Duplicate in Power Query?
Duplicate copies all steps of a query into an independent new query, so changes to the original do not affect it. Reference creates a new query whose source is the output of the original, so it inherits any future changes. Use Reference to build several outputs from one cleaned staging query, such as a monthly summary and a customer list, so the cleaning logic lives in one place.
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.
28
Explain the common Excel errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME? and #SPILL!.
#N/A means a lookup found no match. #VALUE! means a wrong data type, such as adding text. #REF! means a referenced cell or column was deleted. #DIV/0! is division by zero or a blank. #NAME? means a misspelt function or undefined name. #SPILL! means a dynamic array formula cannot spill because cells in its output area are not empty. Knowing the cause helps you fix the source instead of hiding errors.
29
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.
30
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.
Tips to crack the Cognizant Advanced Excel interview
Understand the GenC, GenC Next and GenC Elevate tracks so you know which skills and coding level each role expects.
Revise SQL queries, OOP concepts and basic testing terminology since many freshers are placed in development or QA projects.
Read about Cognizant's healthcare and life sciences domain work, which is a large part of its client base.
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; Cognizant interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the Cognizant Advanced Excel interview?
Candidates usually go through 4 stages: Online assessment (aptitude + reasoning); Coding assessment; 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 Cognizant Advanced Excel interview?
This practice set leans on Excel Fundamentals and Formatting (5), Lookup and Dynamic Array Functions (5), Power Pivot, Data Model and DAX (4), Pivot Tables and Data Summaries (4). Revise those first, then practise explaining one or two of your own projects end to end, because most Cognizant interviewers ask you to walk through real work.
Are these the exact questions asked at Cognizant?
No. These are curated practice questions that match the skills and difficulty usually tested in it services interviews like Cognizant's. They are not a leaked or verified list of past questions.
Where does Cognizant hire Advanced Excel professionals in India?
Cognizant has large teams in Chennai, Bengaluru, Pune, Hyderabad, Kolkata. Openings and locations change often, so check the Cognizant careers page and filter by city and experience.