Skip to content
Advanced Excel
All coursesPlacement Report
‹ Back to previous sectionCourse homeAll page URLs

Advanced Excel Online Assessment Questions & Answers

40 questions for the online assessment stage — 18 easy, 21 medium, 1 hard.

About the online assessment round

Most companies start with an online test: aptitude and reasoning, a few MCQs on core Advanced Excel concepts, and one or two short hands-on tasks. It filters a large number of applicants, so speed and accuracy both count.

Tips for the online assessment round

  • Practise under a timer: about one minute per MCQ and 15-20 minutes per hands-on task.
  • Read every option before answering; tests often include two nearly correct choices.
  • Use a stable internet connection, keep your camera on and avoid switching tabs in proctored tests.
  • Attempt the questions you are sure about first, then return to the harder ones.
Solved — online assessmentStart 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

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?

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
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.
02

What is XLOOKUP and what advantages does it have?

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
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.
03

What is conditional formatting? Give two business uses.

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
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.
04

Write VBA to save each worksheet as a separate PDF.

Macros and VBA AutomationMedium
Save to ▾
Show answer
Use ExportAsFixedFormat inside a loop: Sub SheetsToPDF() Dim ws As Worksheet, p As String p = ThisWorkbook.Path & "\" For Each ws In ThisWorkbook.Worksheets ws.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=p & ws.Name & ".pdf", _ Quality:=xlQualityStandard, IgnorePrintAreas:=False Next ws End Sub Set print areas and page setup first so each PDF fits the page properly.
05

Explain the four areas of a pivot table.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Rows lists categories down the left side, such as Region. Columns spreads categories across the top, such as Month. Values holds the numbers being summarised, such as Sum of Sales or Count of Orders. Filters applies a filter to the whole pivot, such as Financial Year. Moving a field from Rows to Columns changes the layout instantly, which is why pivots are quick for exploring data.
06

How does INDEX/MATCH work and why is it often preferred over VLOOKUP?

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
07

You need serial numbers in a list that stay continuous after rows are filtered or deleted. How do you do it?

Lookup and Dynamic Array FunctionsEasy
Save to ▾
Show answer
Plain typed numbers or =ROW()-1 break when rows are hidden by a filter. Use SUBTOTAL, which ignores filtered-out rows: =SUBTOTAL(103, $B$2:B2) Function 103 is COUNTA that skips hidden rows, and the expanding range counts non-blank cells up to the current row. Now the numbering restarts from 1 for whatever is visible, which is useful in printed or shared filtered views.
08

What is the difference between Protect Sheet, Protect Workbook and Encrypt with Password?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
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.
09

Your manager wants each product's share of sales within its region, not of the grand total. How do you show it?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Put Region and then Product in Rows, and add Sales to Values twice. For the second copy, open Value Field Settings > Show Values As > % of Parent Row Total. Each product then shows its share of its own region, and each region totals 100 percent. Using % of Grand Total instead would show share of the whole company, which answers a different question.
10

How do you choose the right chart type for business data?

Charts and DashboardsEasy
Save to ▾
Show answer
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.
11

Explain the FILTER function with an example.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
FILTER returns all rows that meet a condition and spills them into neighbouring cells: =FILTER(Sales,Sales[Region]="South","No data") =FILTER(Sales,(Sales[Region]=G1)*(Sales[Amount]>100000)) Multiplying conditions means AND; adding them with + means OR. Because the output updates live, FILTER is useful for searchable lists and drill-down sheets without needing pivot tables or macros. It requires Microsoft 365 or Excel 2021.
12

Write VBA to loop through all worksheets and apply the same formatting.

Macros and VBA AutomationMedium
Save to ▾
Show answer
Use For Each over the Worksheets collection and skip any sheet you do not want to touch: Sub FormatAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then ws.Rows(1).Font.Bold = True ws.UsedRange.Columns.AutoFit End If Next ws End Sub
13

Write basic DAX measures for total sales, order count and average order value.

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
In Power Pivot > Measures > New Measure, or in the pivot field list: Total Sales := SUM(Sales[Amount]) Orders := DISTINCTCOUNT(Sales[OrderID]) Avg Order Value := DIVIDE([Total Sales],[Orders]) Referencing measures inside other measures keeps logic in one place. Set the number format on the measure itself, so every pivot that uses it shows the same format.
14

Write VBA to delete rows where column A is blank.

Macros and VBA AutomationMedium
Save to ▾
Show answer
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.
15

Why use DIVIDE instead of the / operator in DAX?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
DIVIDE handles division by zero safely and returns blank, or a value you choose, instead of an error: Margin % := DIVIDE([Total Profit],[Total Sales]) Margin % := DIVIDE([Total Profit],[Total Sales],0) Blank results are hidden in pivots, which keeps reports clean when a product has no sales in a month. Use the / operator only when the denominator can never be zero, because it is slightly faster.
16

What is a waterfall chart and where is it used?

Charts and DashboardsMedium
Save to ▾
Show answer
A waterfall chart shows how a starting value moves to an ending value through a series of positive and negative steps. It is common in finance for profit bridges, such as last year's profit, plus volume growth, minus price discount, minus higher costs, giving this year's profit. Excel 2016 and later include it under Insert > Waterfall; set the first and last bars as totals so they start from zero.
17

How do you get a distinct count, such as unique customers, in a pivot table?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
A normal pivot only offers Count, which counts every row. When inserting the pivot, tick Add this data to the Data Model. Then in Value Field Settings choose Distinct Count, which counts unique customer IDs per region or month. Alternatively, create a DAX measure =DISTINCTCOUNT(Sales[CustomerID]) in Power Pivot, which can be reused across pivots.
18

What is the difference between Summarize Values By and Show Values As?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
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.
19

How does macro security work in Excel?

Macros and VBA AutomationEasy
Save to ▾
Show answer
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.
20

What is the difference between relative, absolute and mixed cell references?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
A relative reference like A1 shifts when a formula is copied, an absolute reference like $A$1 never shifts, and a mixed reference like $A1 or A$1 locks only the column or only the row. In a multiplication grid, =$A2*B$1 copied across and down keeps the header column and header row fixed. Press F4 while editing a reference to cycle through the four forms.
21

A macro works on your computer but fails on a colleague's. What could be the reasons?

Macros and VBA AutomationMedium
Save to ▾
Show answer
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.
22

Explain the match_mode and search_mode arguments of XLOOKUP.

Lookup and Dynamic Array FunctionsMedium
Save to ▾
Show answer
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.
23

How do you build a dependent dropdown, where the City list changes based on the selected State?

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
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#.
24

How do you remove duplicate customers but keep the most recent record in Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
Sort by Updated Date descending, then buffer the table so the sort order is respected before removing duplicates on the key: Sorted = Table.Sort(Source,{{"UpdatedOn", Order.Descending}}), Buffered = Table.Buffer(Sorted), Latest = Table.Distinct(Buffered,{"CustomerID"}) Without Table.Buffer, Power Query may not keep the sorted order, so the record kept could be an older one.
25

What are parameters in Power Query and when do you use them?

Power Query and Data CleaningMedium
Save to ▾
Show answer
A parameter is a named value, created in Manage Parameters, that queries can reference instead of a hard-coded value. A common use is a FolderPath parameter so the same workbook works when moved to another computer or shared drive. Parameters can also hold a cut-off date or a region filter. Changing one value updates every query that uses it, which makes files easier to hand over.
26

What is a pivot table and when should you use one?

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
A pivot table summarises large tabular data by dragging fields into rows, columns, values and filters, without writing formulas. Use it when you need totals, counts or averages broken down by categories such as region, product and month, and when the breakdown changes often. It works best on clean data with one header row, no blank rows and one record per row, ideally stored in an Excel Table.
27

What does converting a range into an Excel Table (Ctrl+T) give you?

Excel Fundamentals and FormattingEasy
Save to ▾
Show answer
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.
28

How do you group dates and numbers in a pivot, and why does grouping sometimes fail?

Pivot Tables and Data SummariesMedium
Save to ▾
Show answer
Right-click a date in Rows and choose Group to group by days, months, quarters or years; for numbers, group by a range such as 0 to 100000 in steps of 10000 to create sales bands. Grouping fails with the message Cannot group that selection when the column contains blanks, text values or dates stored as text. Clean the source column, refresh, and grouping works.
29

How do you automatically highlight the highest and lowest points in a line chart?

Charts and DashboardsMedium
Save to ▾
Show answer
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.
30

Return the Indian financial year (April to March) for a date, like FY 2026-27.

Logical, Text and Date FunctionsMedium
Save to ▾
Show answer
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).
31

How do you make a chart title change automatically with the selected region or month?

Charts and DashboardsMedium
Save to ▾
Show answer
Build the title text in a cell with a formula, for example in B1: ="Sales trend - "&G1&" - FY 2026-27" where G1 is the selected region. Click the chart title, click in the formula bar, type = and select B1, then press Enter: =Dashboard!$B$1 The title now updates whenever G1 changes. The same trick works for data labels and text boxes.
32

Walk through creating a pivot showing monthly sales by region.

Pivot Tables and Data SummariesEasy
Save to ▾
Show answer
Click inside the data, press Ctrl+T to make it a Table, then Insert > PivotTable > New Worksheet. Drag Region to Rows, Order Date to Columns and Amount to Values. Right-click a date and choose Group > Months (and Years if data covers more than one year). Set Value Field Settings > Number Format to #,##0 and add a slicer for Product if needed. Refresh after new data arrives.
33

What are Power Pivot and the Excel Data Model?

Power Pivot, Data Model and DAXEasy
Save to ▾
Show answer
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.
34

Explain TRIM, CLEAN, PROPER, UPPER, LOWER and SUBSTITUTE.

Logical, Text and Date FunctionsEasy
Save to ▾
Show answer
TRIM removes leading, trailing and repeated spaces between words. CLEAN removes non-printable characters such as line breaks from system exports. PROPER, UPPER and LOWER change case, for example PROPER turns RAHUL SHARMA into Rahul Sharma. SUBSTITUTE replaces specific text, like =SUBSTITUTE(A2,"-","") to strip hyphens from a phone number. They are often nested together when cleaning customer master data.
35

What is the difference between Reference and Duplicate in Power Query?

Power Query and Data CleaningMedium
Save to ▾
Show answer
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.
36

What are the limitations of the macro recorder, and what is relative recording?

Macros and VBA AutomationEasy
Save to ▾
Show answer
The recorder writes code for every click, including unnecessary Select statements and fixed cell addresses, so the macro breaks when data size changes. It cannot record loops, conditions or input boxes. By default it records absolute references; turning on Use Relative References records moves relative to the active cell. The recorder is great for learning syntax, but recorded code should be cleaned up by hand.
37

Write a custom number format that shows positives in green, negatives in red brackets and zero as a dash.

Excel Fundamentals and FormattingMedium
Save to ▾
Show answer
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.
38

Write a Power Query custom column that classifies orders into High, Medium and Low by amount.

Power Query and Data CleaningHard
Save to ▾
Show answer
Add Column > Custom Column and enter an M if expression; M is case-sensitive and uses lowercase if, then, else: if [Amount] >= 100000 then "High" else if [Amount] >= 25000 then "Medium" else "Low" Then set the new column type to Text. The Conditional Column dialog can build the same logic without typing, but writing M is faster for nested or combined conditions using and/or.
39

What are UserForms in VBA and when would you use one?

Macros and VBA AutomationMedium
Save to ▾
Show answer
A UserForm is a custom dialog box with text boxes, dropdowns, option buttons and command buttons, created in the VBA editor through Insert > UserForm. It is useful for controlled data entry, such as an employee or expense entry form that validates inputs before writing a row to a Table, or for letting users choose options before a report macro runs. It reduces errors from typing directly into sheets.
40

How do you find the last used row in a column with VBA?

Macros and VBA AutomationEasy
Save to ▾
Show answer
The most reliable common method starts from the bottom of the sheet and moves up: Dim lastRow As Long With ThisWorkbook.Worksheets("Data") lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With Using Long, not Integer, avoids overflow beyond 32,767 rows. If the data is an Excel Table, ListObjects("Sales").ListRows.Count is even cleaner.

Frequently asked questions

What happens in the Advanced Excel online assessment round?

Most companies start with an online test: aptitude and reasoning, a few MCQs on core Advanced Excel concepts, and one or two short hands-on tasks. It filters a large number of applicants, so speed and accuracy both count.

How do I clear the online assessment round?

Practise under a timer: about one minute per MCQ and 15-20 minutes per hands-on task. Read every option before answering; tests often include two nearly correct choices. Use a stable internet connection, keep your camera on and avoid switching tabs in proctored tests.

Which round comes next?

A typical order is online assessment, telephonic screening, one or two technical rounds, a managerial round and HR. Use the other round pages to practise each stage.

Practise every interview round

Telephonic Interview questionsTechnical Round questionsManagerial Round questionsRapid-Fire questionsHR & behavioural questionsFull mock interview

More ways to practise

Company-wise Advanced Excel questionsExperience-wise Advanced Excel questionsRole-wise Advanced Excel questionsCity-wise Advanced Excel questionsMost asked Advanced Excel questionsScenario-based questionsPractical / hands-on questionsHR & behavioural questionsAdvanced Excel mock interviewOnline assessment questionsTechnical round questionsManagerial round questionsAdvanced Excel topic sheets