Excel Interview Questions for Freshers (Data and Analyst Roles)

Last updated: September 2026

Excel comes up in almost every interview for data, finance, operations and analyst roles, even ones that do not name it in the job title. Some companies give a short written or practical test; others simply ask you to explain formulas out loud. Either way, the questions usually test the same core skills, not obscure tricks.

This guide covers the most common Excel interview questions for freshers, explains the difference between VLOOKUP and INDEX-MATCH with an example, lists eight formulas worth knowing well, and gives you a short practice plan.

What interviewers are actually testing

Interviewers are rarely checking whether you have memorised every function. They want to know whether you can look at messy data and get a correct, useful answer quickly. That means understanding when to use a formula, being able to explain your steps, and knowing when a pivot table is faster than a chain of formulas.

8 Excel formulas and features freshers should know

Learn these well before anything more advanced.

Practise each of these until you can write and explain them without searching online. Most fresher-level questions come from this list.

VLOOKUP vs INDEX-MATCH

This comparison is one of the most common Excel interview questions, because it checks whether you understand lookups beyond copying a formula.

Both formulas return the same result here, but they work differently.

VLOOKUP searches for a value in the first column of a range and returns a value from a column to its right. It is simple to write, but it cannot look to the left of the search column, and inserting a new column in the middle of the range can break the column number in the formula.

INDEX-MATCH uses MATCH to find the position of a value and INDEX to return a value from that position in any column, in either direction. It takes longer to learn but is more flexible and more stable when columns are added or removed.

A good interview answer explains both, when to use each, and mentions that INDEX-MATCH is generally considered more robust for larger or changing datasets, while VLOOKUP is often enough for a simple, unchanging table.

20 common Excel interview questions with short answers

Formula and function questions

1. What is the difference between SUMIF and SUMIFS?
SUMIF adds values based on one condition. SUMIFS adds values based on two or more conditions.

2. What does the FALSE argument do in VLOOKUP?
It tells Excel to look for an exact match. Without it, or with TRUE, Excel may return an approximate match, which is often wrong for lookups like IDs or names.

3. What is the difference between COUNT, COUNTA and COUNTIF?
COUNT counts cells with numbers. COUNTA counts cells that are not empty, including text. COUNTIF counts cells that meet a specific condition.

4. What is the difference between relative and absolute cell references?
A relative reference, such as A1, changes when you copy the formula to another cell. An absolute reference, such as $A$1, stays fixed. Press F4 to switch between them quickly.

5. How do you handle errors like #N/A in a formula?
Wrap the formula in IFERROR, for example =IFERROR(VLOOKUP(...), "Not found"), so the sheet shows a readable message instead of an error code.

6. What does the IF function do, and can you nest multiple IFs?
IF tests a condition and returns one value if true and another if false. You can nest several IFs for multiple conditions, but if there are more than two or three, IFS or a lookup table is usually clearer.

7. What is the difference between CONCAT (or CONCATENATE) and the & operator?
Both join text from different cells. CONCAT or CONCATENATE is a function; the & operator does the same thing directly in a formula, such as =A2&" "&B2.

8. How would you remove duplicate rows in a dataset?
Select the data, go to the Data tab, and use Remove Duplicates. To only find them without deleting, use conditional formatting to highlight duplicate values first.

Pivot tables and data organisation

9. What is a pivot table and when would you use one?
A pivot table summarises large datasets by grouping and aggregating them, such as total sales by region and month, without writing formulas. Use it when you need a quick summary or to explore data from different angles.

10. How do you refresh a pivot table after the source data changes?
Right-click inside the pivot table and choose Refresh, or use Refresh All on the Data tab if there are several pivot tables.

11. What is the difference between sorting and filtering?
Sorting reorders rows based on a column's values. Filtering hides rows that do not meet a condition, without changing their order.

12. What is conditional formatting used for?
It automatically changes a cell's appearance, such as colour, based on a rule, for example highlighting all sales figures below target in red.

13. What is data validation and why is it useful?
It restricts what can be entered in a cell, such as only allowing a dropdown list or a number in a certain range. It helps prevent typing errors in shared sheets.

14. What is freeze panes and when do you use it?
Freeze panes keeps chosen rows or columns, such as headers, visible while you scroll through a large sheet.

Practical and scenario questions

15. How would you find the second-highest value in a column?
Use =LARGE(range, 2), which returns the second-largest number directly.

16. How do you compare two lists and find items that appear in only one?
Use COUNTIF to check whether each item in one list appears in the other, then filter for a count of zero, or use conditional formatting to highlight non-matching values.

17. How would you calculate the percentage growth between two numbers?
=(New Value - Old Value) / Old Value, then format the result as a percentage.

18. What would you do if a formula shows #REF! after deleting a column?
It means the formula referred to the deleted column. I would check which reference broke, ideally using INDEX-MATCH, which is less likely to break than VLOOKUP, and repair the formula to point to the correct column.

19. How would you clean a column of names with inconsistent spacing or capitalisation?
Use TRIM to remove extra spaces and PROPER to standardise capitalisation, for example =PROPER(TRIM(A2)).

20. Walk me through how you would build a monthly expense summary from a year of transaction data.
I would organise the raw data with clear headers, use SUMIFS or a pivot table to total expenses by month and category, add a chart for a quick visual summary, and check the totals against the raw data to confirm accuracy.

How to prepare

A short, practical preparation routine.

• Practise on real data. Free sample datasets are widely available online. Rebuild a small report with SUMIFS, a lookup and a pivot table.
• Learn a few keyboard shortcuts. Ctrl+Arrow to jump to the edge of data, Ctrl+Shift+L to toggle filters, F4 to repeat the last action or switch reference types, and Alt+= for a quick sum are useful and easy to demonstrate.
• Practise explaining your steps aloud. Many interviews ask you to talk through your approach as you build a formula, not just produce the final answer.
• Time yourself on small tasks. Try to solve a simple lookup or summary task in about ten minutes to build speed and confidence.

Mistakes to avoid

• Claiming “Expert” level when you have not used pivot tables or lookups. Interviewers test what you list on your resume.
• Guessing a formula name confidently but incorrectly. It is better to say you are not sure and explain your general approach.
• Skipping the explanation. Writing the correct formula silently is less convincing than explaining why it works.
• Ignoring data quality. Extra spaces, mixed formats or duplicate rows can quietly break formulas, and mentioning how you would check for them is a good habit to show.

Frequently asked questions

Do I need to know VBA or macros as a fresher?

Usually not for entry-level roles. Solid formula skills, pivot tables and clean data habits matter more at this stage. Learn VBA later if your role needs automation.

Should I mention Google Sheets if I have not used Excel much?

Yes, if your skills transfer. Most formulas and concepts are the same. Mention it honestly and note that you are comfortable applying the same logic in Excel.

What if I am asked to solve a problem I have never seen?

Say what you would try first, explain your reasoning, and work through it step by step. Interviewers often care more about your approach than a perfect instant answer.

How advanced should my Excel skills be for a fresher role?

Confident use of SUMIFS, COUNTIFS, a lookup formula, IF, and basic pivot tables is usually enough. Add more advanced skills like Power Query or XLOOKUP once you have mastered these.

Final thoughts

Excel interviews for freshers usually test a well-defined set of core skills: lookups, conditional summaries, pivot tables and clean data habits. Practise the eight formulas above on real data, be ready to explain your thinking, and be honest about your current level. For more preparation, browse our Interview Prep section.

The data shown in the examples is illustrative. Excel's features can change between versions, so check your version's documentation if a menu step differs from what is described here.