Advanced Excel Interview Questions in Noida

Show answer

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.

The pivot shows Count of Sales instead of Sum of Sales when you drag the field. Why?

Pivot Tables and Data SummariesEasy

Show answer

Excel defaults to Count when a value column contains blanks or text, such as NA or a number stored as text. Change it through Value Field Settings > Sum, but also fix the source: replace text placeholders with zero or blank, convert text numbers to real numbers, and refresh. Otherwise the text entries are silently left out of the sum and totals will be understated.

Which Excel keyboard shortcuts do you use daily?

Excel Fundamentals and FormattingEasy

Show answer

Ctrl+Arrow jumps to the edge of data and Ctrl+Shift+Arrow selects to it. Ctrl+T creates a Table, Ctrl+Shift+L toggles filters, Ctrl+1 opens Format Cells, Alt+= inserts AutoSum, F4 toggles absolute references or repeats the last action, F2 edits a cell, Ctrl+; inserts today's date, Ctrl+Alt+V opens Paste Special and Ctrl+E runs Flash Fill. Mentioning a few you genuinely use is better than reciting a long list.

An IF formula says 100 is not equal to 100. Why might that happen?

Show answer

Usually one value is text and the other is a number, so =A2=B2 returns FALSE even though both show 100. Other causes are hidden spaces, or floating-point results like 99.9999999 that display as 100 because of formatting. Check with ISTEXT, LEN and by widening decimals. Fix with VALUE or TRIM on the text side, or compare with ROUND(A2,2)=ROUND(B2,2) for calculated numbers.

Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?

Show answer

A worksheet holds at most 1,048,576 rows, so the result will not fit. Choose Close & Load To and select Only Create Connection with Add this data to the Data Model. The model compresses data and can handle many millions of rows, and you analyse it with a pivot table or DAX measures. If users only need summaries, group the data inside Power Query before loading.

Tell me about a time you automated a repetitive task.

Show answer

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.

Why do you want a role that uses Excel heavily, such as MIS or reporting?

Macros and VBA AutomationEasy

Show answer

I enjoy turning raw numbers into reports that people actually use to decide things. During my studies and projects I built pivot dashboards and lookup-based trackers, and I liked seeing a three-hour manual task become a ten-minute refresh. An MIS role lets me build those skills daily, understand how the business runs, and grow towards analytics tools like Power BI and SQL, which build directly on Excel.

A sales file has employee names but the master list has employee IDs, and some names repeat. How do you join them safely?

Show answer

Do not look up by name alone, because a duplicate name returns the first match and silently assigns sales to the wrong person. First flag duplicates in the master with COUNTIF(names,name)>1. Then build a composite key, such as name plus branch or name plus email, in both tables and look up on that. Rows still ambiguous should go back to the data owner for an ID rather than be guessed.

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

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.

You need to reconcile a bank statement with the ledger and list entries missing from either side. How?

Show answer

Create a common key, for example date plus amount plus cheque or UTR number. On each sheet add =IF(COUNTIFS(Other[Key],[@Key])=0,"Missing","Matched") and filter the Missing rows. Duplicated amounts need COUNTIFS on both sides so counts are compared, not just presence. For repeated monthly work, load both into Power Query and use Left Anti and Right Anti merges, which list unmatched rows directly.

Budgets are set by category and month, but sales are recorded by product and day. How do you compare them in one pivot?

Power Pivot, Data Model and DAXHard

Show answer

Do not relate the two fact tables directly. Create shared dimension tables at a grain both can link to: a Category table and a Date table with a Month column. Relate Budget[Category] and Sales via Products to Category, and both date columns to the Date table, using the first day of the month for budgets. Then measures [Total Sales] and [Total Budget] work together when the pivot uses Category and Month.

How is the Excel Data Model related to Power BI?

Power Pivot, Data Model and DAXMedium

Show answer

Both use the same VertiPaq engine and the DAX language, and both use Power Query for data preparation. A Power Pivot model can be imported into Power BI Desktop through File > Import, carrying queries, relationships and measures. This makes Advanced Excel with Power Pivot a natural stepping stone, since most of the model and DAX skills transfer directly to Power BI.

How do you stop a pivot from losing formatting and column widths on refresh?

Pivot Tables and Data SummariesMedium

Show answer

Open PivotTable Options > Layout & Format, untick Autofit column widths on update and tick Preserve cell formatting on update. Set number formats through Value Field Settings > Number Format rather than formatting cells directly, because field-level formats survive refresh and layout changes. For a consistent look across reports, create a custom PivotTable Style and apply it to every pivot.

Dates from a vendor file in dd/mm/yyyy format show as errors or swap day and month. How do you fix it?

Show answer

The system locale is probably English (United States), which reads dates as mm/dd/yyyy, so 13/04/2026 becomes an error and 05/04/2026 becomes 4 May. In Power Query, right-click the column > Change Type > Using Locale, choose Date and English (India). This tells Power Query how to read the text regardless of the computer's settings, so the file refreshes correctly on any machine.

A few very large values make every other bar in your chart look tiny. How do you present the data clearly?

Show answer

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.

Extract the first and last name from a full name like Rahul Kumar Sharma.

Logical, Text and Date FunctionsEasy

Show answer

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.

Write VBA to save each worksheet as a separate PDF.

Macros and VBA AutomationMedium

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.

How do you combine all files in a folder using Power Query?

Show answer

Place files with the same structure in one folder. Go to Data > Get Data > From File > From Folder, select the folder and click Combine & Transform. Choose the sheet or table from the sample file; Power Query creates a helper function and appends all files, adding a Source.Name column with the file name. Apply cleaning steps once, Close & Load, and later just drop new files in and Refresh.

What are structured references? Give an example.

Excel Fundamentals and FormattingEasy

Show answer

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.

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

Excel Fundamentals and FormattingMedium

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.

How do you build a KPI card showing achievement against target with an up or down indicator?

Show answer

Calculate achievement in a cell, for example =Actual/Target-1, and apply a custom number format that adds arrows and colours: [Color10]▲ 0.0%;[Red]▼ 0.0%;0.0% Place the actual value in a large font above it, link a text box to the cells for flexible placement, and keep labels short like Revenue vs Target. Conditional formatting icon sets are an alternative but are less precise.

What is a waterfall chart and where is it used?

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.

Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?

Show answer

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.

Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?

Show answer

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.

What is query folding in Power Query?

Power Query and Data CleaningHard

Show answer

Query folding means Power Query translates your steps into the source's own query language, such as SQL, so filtering and grouping happen inside the database and only the result is sent to Excel. This is much faster for large sources. Steps like adding index columns or some custom M functions can break folding. Right-click a step and check View Native Query to see whether it still folds.

Count orders from the North region with amount above 50,000 placed in January 2026.

Show answer

Use COUNTIFS with one pair of range and criteria per condition, with dates built using DATE so the formula does not depend on regional settings: =COUNTIFS(Orders[Region],"North", Orders[Amount],">50000", Orders[Date],">="&DATE(2026,1,1), Orders[Date],"<"&DATE(2026,2,1)) Using less than 1 February is safer than less than or equal to 31 January when dates contain time values.

Why are arrays faster than looping through cells in VBA?

Macros and VBA AutomationHard

Show answer

Each read or write to a cell crosses between VBA and Excel, which is slow when repeated thousands of times. Reading a whole range into a Variant array, processing it in memory and writing it back once is many times faster: Dim v As Variant, i As Long v = Range("A2:C50001").Value For i = 1 To UBound(v, 1) v(i, 3) = v(i, 1) * v(i, 2) Next i Range("A2:C50001").Value = v

A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?

Show answer

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.

Tell me about a time data from two teams did not match. How did you resolve it?

Show answer

Situation: finance and sales reported different revenue for the same quarter. Task: find the gap before the review meeting. Action: I pulled both datasets, built a key on invoice number, and used lookups to list invoices present in only one file. Most of the gap came from credit notes that sales did not deduct and invoices booked on the last day. Result: both teams agreed a single definition and I added a reconciliation tab to the monthly pack.

List the steps to build a sales and margin percentage combo chart.

Show answer

Select the Month, Sales and Margin % columns and go to Insert > Recommended Charts > All Charts > Combo. Set Sales to Clustered Column and Margin % to Line with Markers, ticking Secondary Axis for Margin %. Add axis titles, format the secondary axis as 0%, add data labels only to the line if needed, and remove gridline clutter. Give it a title that states the insight, such as Margin fell while sales grew.

What is the difference between SUMIF and SUMIFS?

Logical, Text and Date FunctionsEasy

Show answer

SUMIF handles one condition and puts the sum range last; SUMIFS handles many conditions and puts the sum range first: =SUMIF(A:A,"North",C:C) =SUMIFS(C:C,A:A,"North",B:B,"Laptop",D:D,">="&DATE(2026,4,1)) Because of the different order, many people use SUMIFS everywhere, even for a single condition, to stay consistent. COUNTIFS and AVERAGEIFS follow the same pattern.

Tell me about a time you presented data to senior leadership.

Show answer

Situation: I had to present quarterly branch performance to regional directors. Task: make twenty pages of tables understandable in ten minutes. Action: I built one dashboard sheet with four KPIs against target, a trend chart and a ranked bar chart of branches, and kept detail tables as backup. I opened with the key message and the two branches needing action. Result: the meeting ended with clear decisions, and the format became the standard quarterly review pack.

Your manager wants a fixed-format P&L with rows in a custom order, but the data is in the Data Model and the pivot keeps reshaping itself. What do you do?

Power Pivot, Data Model and DAXHard

Show answer

Use CUBE functions instead of a pivot. Select the pivot and use PivotTable Analyze > OLAP Tools > Convert to Formulas, or write them directly, for example CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Sales]", $A5, B$3). Each cell becomes an independent formula, so you can reorder rows, insert subtotal lines and apply fixed formatting, while numbers still refresh from the model.

How do you use approximate match to apply discount or tax slabs?

Show answer

Create a slab table with the lower limit of each band in ascending order and the rate next to it. Approximate match returns the row for the largest limit not greater than the value: =VLOOKUP(B2,Slabs!$A$2:$B$6,2,TRUE) =XLOOKUP(B2,Slabs[From],Slabs[Rate],,-1) If B2 is 7.5 lakh and the bands start at 0, 3, 7 and 10 lakh, the 7 lakh row is returned. Unsorted data gives wrong results silently.

Commission is 2% up to 1 lakh, 5% up to 5 lakh and 8% above 5 lakh, applied as a flat rate on total sales. Write the formula.

Show answer

With IFS: =B2*IFS(B2>500000,8%,B2>100000,5%,TRUE,2%) A cleaner and more maintainable option is a slab table (Lower: 0, 100001, 500001; Rate: 2%, 5%, 8%) with approximate match: =B2*XLOOKUP(B2,Slab[Lower],Slab[Rate],,-1) The -1 match mode returns the next smaller value, so changing rates only means editing the table.

Calculate working days between two dates excluding Sundays and a holiday list.