Excel VBA Developer Interview Questions &amp; Answers

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.

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

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.

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

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

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.

When does binary search in XLOOKUP or approximate VLOOKUP give wrong results?

Lookup and Dynamic Array FunctionsHard

Show answer

Binary search halves the range repeatedly, assuming the data is sorted. If the lookup column is unsorted, has mixed text and numbers or contains blanks, it can stop at the wrong row and return a plausible but incorrect value with no error. Use it only on data you sort in a controlled step, such as a Power Query output, and test a few values against an exact-match lookup.

What is the pivot cache and why does it matter?

Show answer

When a pivot is created, Excel stores a snapshot of the source data called the pivot cache, and the pivot reads from it, not directly from the sheet. That is why changes need a refresh. Pivots built from the same Table share one cache, which keeps file size down and lets slicers connect across them. Creating pivots from different copies of the data creates separate caches and makes the file heavier.

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

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.

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

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.

What are Applied Steps and the M language in Power Query?

Power Query and Data CleaningEasy

Show answer

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.

You need to find and remove duplicate customer records but keep the original data safe. How do you do it?

Show answer

First highlight duplicates with Conditional Formatting > Highlight Cells Rules > Duplicate Values to review them. Then work on a copy of the sheet and use Data > Remove Duplicates, selecting the key columns such as Mobile and Email. In Microsoft 365 a non-destructive option is =UNIQUE(A2:C500) on a new sheet. Always record how many rows were removed so the change can be explained later.

What is the difference between a Sub and a Function in VBA?

Macros and VBA AutomationEasy

Show answer

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.

Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?

Show answer

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.

Tell me about a time you had to explain a complex Excel report to a non-technical manager.

Logical, Text and Date FunctionsMedium

Show answer

Situation: my manager was unsure why the new incentive sheet showed lower payouts. Task: explain the logic without formulas. Action: I added a one-page summary with the slab table, walked through two real employees step by step, and used colour to show which inputs drove the result. Result: the manager understood it in one meeting, approved the sheet and asked me to add the same summary to future models.

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.

Tell me about a time you had to explain a DAX number that looked wrong to a manager.

Power Pivot, Data Model and DAXHard

Show answer

Situation: a manager said our margin percentage in the Data Model pivot did not match his calculator. Task: show whether the measure was right. Action: I took one region, showed that he was averaging row percentages while the measure divided total margin by total sales, and walked through both in a small table. Result: he agreed the measure was correct, and I added a note to the report explaining how the KPI is calculated.

A shared Excel file has grown to 40 MB and takes ages to recalculate. What do you check?

Excel Fundamentals and FormattingHard

Show answer

Start with Ctrl+End to see if the used range is far beyond the real data and delete the extra rows and formats. Then look for volatile functions like OFFSET, INDIRECT and TODAY, whole-column references inside SUMPRODUCT or array formulas, and duplicated conditional formatting rules. Replace repeated lookups with one helper column, move heavy transforms to Power Query, and save as .xlsb if size still matters.

How do you highlight an entire row when the Status column says Overdue?

Show answer

Select the full data range, for example A2:H500, then Home > Conditional Formatting > New Rule > Use a formula. Enter: =$F2="Overdue" Choose a fill colour and click OK. The dollar before F locks the column, while the relative row 2 lets the rule move down row by row, so every column in that row gets coloured when column F matches.

How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?

Show answer

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.

Write VBA to consolidate the first sheet of every Excel file in a folder into one sheet.

Macros and VBA AutomationHard

Show answer

Loop with Dir, open each file, copy its data below the last row and close it: Sub Consolidate() Dim f As String, wb As Workbook, dest As Worksheet, r As Long Const P As String = "C:\Reports\" Set dest = ThisWorkbook.Worksheets("All") f = Dir(P & "*.xlsx") Do While f <> "" Set wb = Workbooks.Open(P & f, ReadOnly:=True) r = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1 wb.Worksheets(1).UsedRange.Offset(1).Copy dest.Cells(r, 1) wb.Close False f = Dir Loop End Sub

Write VBA to save each worksheet as a separate PDF.

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.

What checks do you run when cleaning a new dataset?

Show answer

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.

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

Lookup and Dynamic Array FunctionsMedium

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.

Your pivot chart changes whenever someone filters the pivot, but your manager wants a fixed chart for a presentation. What do you do?

Show answer

A pivot chart always follows its pivot, so it cannot be frozen while the pivot is filtered. Options: copy the chart and paste it as a picture into the slide; or copy the pivot values to a normal range and build a regular chart from them; or create a second pivot from the same cache, set its filters once and protect the sheet. For a live report, keep the interactive version on a separate sheet.

The pivot grand total does not match SUM of the source column. How do you investigate?

Pivot Tables and Data SummariesHard

Show answer

Check for active filters on any field, slicers or timelines, and whether the source range excludes recent rows. Look for text values in the amount column, which pivots ignore, and calculated items that double-count. Confirm date grouping is not hiding rows outside the grouped range. Then double-click the grand total to see the rows the pivot used and compare the row count with the source.

Your macro accidentally deleted data, and Undo does not work. How do you prevent this in future?

Show answer

Macro actions clear Excel's undo history, so prevention matters. Add a confirmation prompt before destructive steps, test on a copy of the file, and have the macro save a backup copy with a timestamp before it changes data. Restrict deletion to clearly identified ranges such as a named Table, not ActiveSheet. For files on OneDrive or SharePoint, version history can usually restore the earlier copy.

How do you speed up a slow VBA macro?

Macros and VBA AutomationHard

Show answer

Switch off screen updates, automatic calculation and events while the macro runs, and always restore them: Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' ... main code ... Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.EnableEvents = True Also avoid Select, process data in arrays instead of cell by cell, and use an error handler so settings are restored on failure.

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

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#.

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

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.

What is conditional formatting? Give two business uses.

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.

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

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.

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.

Explain the join kinds available in Power Query Merge.

Show answer

Left Outer keeps all rows from the first table and matches from the second, the most common choice. Right Outer does the reverse. Full Outer keeps all rows from both. Inner keeps only matching rows. Left Anti keeps rows in the first table with no match, and Right Anti keeps rows in the second with no match. Anti joins are excellent for finding missing invoices or customers during reconciliation.

What are named ranges and why are they useful?

Show answer

A named range gives a meaningful name, such as GST_Rate or Target_FY26, to a cell, range or constant. Formulas like =Amount*GST_Rate are easier to read and audit than =B2*$Z$1. Names are managed in Formulas > Name Manager and can be scoped to the workbook or a single sheet. They also work as navigation targets in the Name Box and as Data Validation sources.

Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?

Power Query and Data CleaningHard

Show answer

Reduce work as early as possible: remove unneeded columns and filter rows in the sample file transform so every file is smaller. Delete redundant Changed Type steps and set types once. Avoid sorting and merges against large tables before the combine. Set helper queries to connection only. Copy files to a local drive if SharePoint is slow, and archive old years into a single file so only recent files are combined each time.

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.

How do you send personalised emails from Excel through Outlook using VBA?

Macros and VBA AutomationHard

Show answer

Use late binding so no library reference is needed, and loop through the rows: Sub SendMails() Dim ol As Object, m As Object, r As Long Set ol = CreateObject("Outlook.Application") For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row Set m = ol.CreateItem(0) m.To = Cells(r, 2).Value m.Subject = "Payment reminder" m.Body = "Dear " & Cells(r, 1).Value & ", your due amount is Rs " & Cells(r, 3).Value m.Display Next r End Sub Use .Display to review before switching to .Send.

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

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.

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

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.

Tell me about a time you had to clean up a messy spreadsheet someone else built.

Show answer

Situation: I inherited a sales tracker with merged cells, hard-coded totals and three different date formats. Task: my manager needed reliable weekly numbers. Action: I kept a backup copy, converted the data into an Excel Table, fixed dates with Text to Columns, replaced typed totals with SUMIFS and added data validation dropdowns. Result: weekly reporting time fell from two hours to twenty minutes and the totals stopped being questioned.

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