Financial Analyst Interview Questions &amp; Answers

Show answer

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.

How do you show actual versus target in a compact bullet-style chart?

Show answer

Create a clustered column chart with Target and Actual series. Format the Target series with no fill and a thick outline or a light colour, set Series Overlap to 100 percent so both bars sit on top of each other, and make the Actual bar narrower by putting it on the secondary axis with a larger gap width. Match both axis scales. Readers immediately see whether Actual falls short of or crosses the target.

Your team wants lookups to show Not Found instead of #N/A for missing codes, without hiding genuine formula errors. How do you do it?

Show answer

Use a function that only handles missing values, not every error: =XLOOKUP(A2, Codes[Code], Codes[Price], "Not Found") or =IFNA(VLOOKUP(A2, Codes, 2, FALSE), "Not Found"). Avoid IFERROR here, because it also hides #REF! or #VALUE! caused by broken ranges. Add =COUNTIF(D:D, "Not Found") at the top so missing codes are counted and followed up.

VLOOKUP returns #N/A even though you can see the value in the table. What do you check?

Show answer

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.

Describe a time a formula mistake of yours reached a stakeholder. What did you do?

Show answer

Situation: a regional sales summary I sent showed one zone 18% higher than actual. Task: correct it before decisions were taken. Action: I traced it to a SUMIFS range that stopped one row short, sent a corrected file within the hour with a short note explaining the cause, and added a check cell comparing the summary total to the raw data total. Result: the manager appreciated the quick ownership and the check now catches such gaps automatically.

Ten regional heads each need the same dashboard but must see only their own region. How do you deliver it?

Show answer

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.

How is the Excel Data Model related to Power BI?

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.

Your manager asks you to replace all VLOOKUPs in a legacy workbook with XLOOKUP. How do you approach it?

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.

Tell me about a time a lookup gave wrong results that nobody noticed at first.

Show answer

Situation: a monthly incentive sheet used VLOOKUP with the last argument left blank, so it ran an approximate match. Task: I was asked to verify payouts before release. Action: I spotted employees getting a neighbour's target, changed every lookup to exact match with XLOOKUP, and added a count of missing IDs. Result: twelve wrong payouts were corrected before payment, and exact match became our team's standard rule.

Tell me about a time you got a last-minute analysis request. How did you handle it?

Pivot Tables and Data SummariesMedium

Show answer

Situation: at 5 PM, my manager needed customer-wise outstanding for a client call at 9 AM. Task: deliver an accurate summary quickly. Action: I confirmed the exact question and cut-off date, pulled the ledger, built a pivot by customer and ageing bucket, cross-checked the total with the trial balance and flagged three unusual balances. Result: the report was ready by 7 PM, and my notes helped the manager answer follow-up questions in the call.

What is the LET function and why is it useful?

Logical, Text and Date FunctionsHard

Show answer

LET assigns names to intermediate results inside one formula, so a value is calculated once and the formula reads like steps: =LET(sales,SUMIFS(Data[Amt],Data[Rep],A2), target,XLOOKUP(A2,Targets[Rep],Targets[Target]), IF(target=0,"",sales/target)) Without LET, the SUMIFS would be repeated in each branch. It improves speed on large sheets and makes complex formulas easier to review.

Calculate an employee's age or tenure in years and months from the joining date.

Show answer

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

Explain relationships, cardinality and filter direction in the Data Model.

Power Pivot, Data Model and DAXHard

Show answer

A relationship links a key column in one table to a matching column in another. Cardinality describes how many rows match: one-to-many is the standard case, where each product appears once in Products but many times in Sales. In Excel's Data Model, filters flow from the one side to the many side, so selecting a category in Products filters Sales, but not the reverse.

What is the difference between SUM and SUMX?

Power Pivot, Data Model and DAXHard

Show answer

SUM adds a single column. SUMX iterates a table, evaluates an expression for each row and adds the results: Revenue := SUMX(Sales,Sales[Qty]*Sales[Price]) This avoids storing a Qty times Price calculated column. Other X functions such as AVERAGEX, MAXX and COUNTX work the same way. Iterating a very large table with complex expressions can be slower, so keep the expression simple.

Explain the match_mode and search_mode arguments of XLOOKUP.

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.

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

Power Query and Data CleaningMedium

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.

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

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.

Build a live leaderboard of the top 5 sales reps by total sales using dynamic array functions.

Show answer

Get unique reps, total their sales, sort descending and take the top five: =LET(r,UNIQUE(Sales[Rep]), t,SUMIFS(Sales[Amount],Sales[Rep],r), TAKE(SORTBY(HSTACK(r,t),t,-1),5)) HSTACK puts names and totals side by side, SORTBY orders by totals, and TAKE keeps five rows. Without TAKE, use INDEX(...,SEQUENCE(5),{1,2}).

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

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

What principles do you follow when designing an Excel dashboard?

Show answer

Start from the questions the audience asks. Put three to five headline KPIs at the top, trends and breakdowns below, and filters such as slicers in one consistent place. Use one colour palette with a highlight colour for what matters, avoid 3D and decorative effects, label units clearly like Rs lakh, and fit the view on one screen. Keep raw data and calculations on separate hidden sheets.

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.

You added new rows of data but the pivot does not show them. Why, and how do you prevent it?

Pivot Tables and Data SummariesEasy

Show answer

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.

Users of your Data Model pivot keep dragging Year, Quarter and Month separately and make mistakes. How do you make it easier?

Show answer

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.

Your manager says your chart is misleading. What could be wrong?

Show answer

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.

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.

When should a team move from an Excel dashboard to Power BI?

Charts and DashboardsHard

Show answer

Excel dashboards work well for small teams, one-off analysis and data up to a few lakh rows. Move to Power BI when data comes from several live sources, needs scheduled refresh, row-level security for different users, mobile access, or a large audience who should not receive files. The Data Model and DAX skills learned in Power Pivot transfer directly, so the move is usually smooth.

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.

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

Power Query and Data CleaningHard

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.

You need product category in the Sales table to create a calculated column. How do you do it in the Data Model?

Show answer

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.

What checks do you run when cleaning a new dataset?

Power Query and Data CleaningMedium

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.

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.

Why does a date show as a number like 46023 when joined with text, and how do you fix it?

Show answer

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.

What does INDIRECT do and what are its risks?

Show answer

INDIRECT converts text into a reference, so =SUM(INDIRECT(B1&"!C2:C100")) sums the sheet whose name is typed in B1. It is useful for summary sheets that switch between months and for dependent dropdowns. The risks are that it is volatile, it breaks silently when sheets are renamed, it does not work with closed external workbooks, and formula auditing tools cannot trace it. Use it sparingly.

What is the difference between a calculated column and a measure in DAX?

Show answer

A calculated column is computed row by row when data is refreshed and stored in the table, like Sales[Margin] = Sales[Amount] - Sales[Cost]. It uses memory and can be used in rows, columns and slicers. A measure is computed on the fly for each cell of a pivot based on its filters, like Total Margin = SUM(Sales[Margin]). Use measures for aggregations and calculated columns only for attributes you slice by.

What is the difference between nested IF and IFS? Show an example.

Logical, Text and Date FunctionsEasy

Show answer

Nested IF places one IF inside another, which becomes hard to read after three levels. IFS (Excel 2019 and later) tests conditions in order and returns the value for the first true one: =IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",TRUE,"Fail") The final TRUE acts as the else part. For many bands, a lookup table with XLOOKUP approximate match is easier to maintain than either.

You are given raw sales data and asked to build an interactive dashboard. Walk through your approach.

Show answer

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.

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 automatically highlight the highest and lowest points in a line chart?

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.

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.

Get the latest price of each product from a transaction log sorted by date.