Scenario-Based Advanced Excel Interview Questions

Number formatting only changes how a value looks; Excel still adds the full decimals stored in each cell. So the sum of hidden paise differs from the sum of the rounded figures shown. If the report must add up exactly as displayed, round the values with =ROUND(value,0) in the source formulas rather than using the Precision as displayed option, which permanently changes stored data for the whole workbook.

The numbers are most likely stored as text, often with leading apostrophes, trailing spaces or non-breaking spaces (CHAR(160)). Check with =ISTEXT(A2) or LEN. Quick fixes are Data > Text to Columns > Finish, the green error triangle > Convert to Number, or Paste Special > Multiply by 1. For stubborn cases use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),""))) or set the column type to Decimal in Power Query.

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.

The workbook is probably in Manual calculation mode, often inherited from another file opened first in the same session. Press F9 to recalculate now, then set Formulas > Calculation Options > Automatic. If only some cells stay stale, check whether they are formatted as Text or whether Show Formulas is on. For very heavy files, manual mode can be kept deliberately, but tell users to press F9 before reading results.

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.

Move the file to OneDrive or SharePoint so everyone co-authors one live copy, and stop emailing attachments. Restructure it into a single input Table with Data Validation, lock formula columns with sheet protection, and give each person a filtered view or their own sheet view. Use version history for recovery. If edits keep growing, move the data to a SharePoint list or database and keep Excel for reporting.

Work on a copy. Unmerge all cells, then fill the blanks with the value above using Go To Special > Blanks and =cell above, followed by paste as values. Filter out blank rows and subtotal lines and delete them, keep one header row and convert the range to a Table. If the same report arrives every month, repeat these steps once in Power Query so the cleanup becomes a single refresh.

Left alignment usually means the dates are stored as text. Select the column, use Data > Text to Columns, choose Date with the correct order such as DMY and finish; Excel converts them to real dates. For mixed or messy text, DATEVALUE or DATE with LEFT, MID and RIGHT works. If the file comes regularly, set the column type in Power Query using Using Locale so day and month are never swapped.

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.

Avoid hard-coded column numbers and fixed ranges. Load the export into a Table or through Power Query, so columns are referenced by name. Replace VLOOKUP with column index 7 by XLOOKUP or INDEX/MATCH, keep constants like rates in labelled assumption cells, and use LET for readability. Add a check cell that compares the report total to the raw total, so any break is caught immediately.

Check that the last argument is FALSE, then compare the two values directly with =A2=Sheet2!A5. Common causes are trailing or non-breaking spaces, one side being a number and the other text (like employee codes), and the lookup value sitting outside the first column of the range. Use LEN to spot hidden characters, and fix with TRIM, CLEAN, VALUE or by making both columns the same data type.

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.

Avoid repeating the same search: run one MATCH in a helper column and use several INDEX formulas on that position. Sort the lookup table and use XLOOKUP binary search (search_mode 2), which is much faster than a linear scan. Replace full-column references with Tables, set calculation to manual while editing, and for regular jobs move the join into Power Query or the Data Model with relationships.

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.

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

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.

Pivot date grouping uses calendar quarters, so add a helper column in the source data. For example, fiscal quarter ="Q"&ROUNDUP((MOD(MONTH(A2)-4,12)+1)/3,0) and a fiscal year column. Use them as row fields instead of grouped dates. In Power Query or a Data Model, put these columns in a proper Date table so every report uses the same April to March logic.

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.

Instead of merging with VLOOKUP, load each Table into the Data Model (Insert > PivotTable > Add this data to the Data Model, or Power Pivot). In Data > Relationships, link Sales[ProductID] to Products[ProductID] and Sales[RegionID] to Regions[RegionID]. The pivot field list then shows all three tables, so category and zone come from the lookup tables while amounts come from Sales.

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.

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.

Put all files in one folder and use Data > Get Data > From File > From Folder > Combine & Transform. Power Query appends them using the first file as a sample, adds the source file name as a column and applies the same cleaning steps. Load the result as a Table or to the Data Model and build the pivot on it. Next year, adding a file to the folder and clicking Refresh All updates everything.

Use sparklines: select the 12 monthly values for all rows, choose Insert > Sparklines > Line and set the location to a column next to the data. Turn on high and low point markers, and under Axis choose the same minimum and maximum for all sparklines if branches should be compared fairly. They fit inside a cell, print well and update when the data changes.

Clarify the audience, KPIs and refresh frequency first. Load and clean the data through Power Query into a Table or the Data Model. Build pivots for each view on a calculation sheet, create pivot charts, and connect slicers for region, product and month to all pivots. Add KPI cards using GETPIVOTDATA, format consistently, protect the layout and test that Refresh All updates everything when new data is added.

Make sure all pivots share one cache or the Data Model, so a slicer click filters one source. Reduce slicer cross-filtering by unticking Visually indicate items with no data, remove slicers that are rarely used, and replace pivots that show the same data with GETPIVOTDATA from one pivot. Remove volatile formulas, limit conditional formatting, and move large data to the Data Model with measures.

The most common cause is a column or bar chart whose vertical axis does not start at zero, which exaggerates small differences. Other issues are inconsistent scales between side-by-side charts, a secondary axis that is not labelled, 3D effects distorting sizes, missing months making a trend look smooth, or totals mixed with categories. Fix the axis, label units and state the time period in the title.

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.

Decide what they need: a view or a working file. For a view, export the dashboard sheet to PDF or share a view-only link from SharePoint or OneDrive. For a file, save a copy, break external links, paste pivots or formulas as values if needed, remove hidden sheets and personal data with Inspect Document, and protect the sheet. Automate the steps with a macro or Office Script so every week is identical.

Since Excel files cannot restrict data inside a workbook securely, create separate files. Add a Region parameter cell that drives Power Query filters or pivot filters, then loop through regions with a short VBA macro that sets the region, refreshes, removes other data and saves a copy or PDF per region. For regular use at scale, Power BI with row-level security is the better solution.

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.

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.

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.

First agree a fixed template with the branches, ideally a named Table. Save all files to one SharePoint or shared folder, then build a From Folder query that combines them, adds the branch name from the file name, standardises types and filters bad rows. Load to the Data Model, build the pivots and dashboard on it, and add a check sheet comparing row counts per branch. Monthly work becomes refresh and review.

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.

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.

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.

The field in the rows is not filtering the table the measure uses. Usually there is no relationship between the two tables, the relationship is inactive, or it points the wrong way, for example the row field comes from the many side of a different fact table. Excel often shows a warning that relationships may be needed. Check Manage Relationships and use fields from dimension tables linked to the fact table.

Create a hierarchy in the Date table in Power Pivot diagram view, such as Year > Quarter > Month, so users drag one field and drill down. Set Month Name to sort by Month Number so months do not appear alphabetically. Hide key columns and raw fields that should not be used directly, and give measures clear business names. This keeps the field list small and the pivots consistent.

VertiPaq size depends mostly on columns and how many unique values each has. Remove columns not used in reports, especially IDs and free text. Split date-time columns into date and time or drop the time, round decimals to required precision, and replace calculated columns with measures. Use integer keys instead of long text keys, and load only the years required. Filtering at Power Query level avoids loading unneeded rows.

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.

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.

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.

Power Query is best for importing, cleaning and combining data, because it is refreshable and needs no code security. VBA suits desktop automation such as formatting, emailing, file handling and interacting with other Office apps. Office Scripts, written in TypeScript, automate Excel on the web and work with Power Automate flows. Python in Excel suits statistics and advanced analysis inside a cell. Pick the simplest tool your users can run and maintain.

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.