Ravindra BagaleCourses & study guides Track your progress

Excel Interview Prep — Fresher Basics

By · Last updated:

Friends, today we practise Excel interview questions for freshers: cells, SUM, IF, VLOOKUP, Pivot and dirty-data traps. Short answers, tiny examples, then you try on the practice file.

Fictional sample data for learning. Industry flavour: College marks + kirana orders (Pune / Nashik / London). Brands and people are made up for practice.

Why → What → How

Why: Excel interviews are not memory tests of every function name. They check if you can clean a small sheet, write a correct formula, and explain the result in plain English.

What: This page has 30 real interview-style questions for Fresher Basics. Each one has a short answer, vertical steps, a tiny example, a common wrong answer, and a follow-up question.

How — study plan:

  1. Skim all question titles once (10 minutes).
  2. Download the practice workbook and open sheet About this data.
  3. Answer aloud, then type the formula in the file.
  4. Check the yellow cells on sheet Answers only after you try.
  5. Revise with the three cards and take the quiz.

Download practice file: practice-fresher-interview.xlsx (includes About this data, practice sheets, Answers).

Ravindra Bagale's Tip

Friends, in every fresher interview they ask SUM, IF, VLOOKUP and Pivot. Practise these four until you can type them without looking.

Ravindra Bagale's Tip

Always say FALSE (exact) with VLOOKUP for IDs and names. Forgetting it is the most common fresher mistake.

Questions (30)

Q1 · Theory

What is a cell, a range and a worksheet in Excel?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): A cell is one box (like A1). A range is a group of cells (A1:A10). A worksheet is one tab (sheet) inside a workbook.

How — tiny example (vertical steps):

  1. Open Excel and look at the grid.
  2. Click A1 — that one box is a cell.
  3. Drag from A1 to A5 — that is a range.
  4. Look at the tab at the bottom — that is a worksheet.

Formula: A1
Result: one cell
What it means: The address of a single box on the sheet.

Common wrong answer: Saying a workbook and a worksheet are the same thing.

Q2 · Theory

What is the difference between a workbook and a worksheet?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): A workbook is the whole Excel file (.xlsx). A worksheet is one sheet (tab) inside that file. One workbook can have many worksheets.

How — tiny example (vertical steps):

  1. Save a new file as ShopSales.xlsx — that file is the workbook.
  2. Add sheets named Jan, Feb, Mar — each tab is a worksheet.
  3. Close and reopen the file — all sheets travel together in one workbook.

Formula: (file) ShopSales.xlsx
Result: 3 sheets
What it means: One file holds many related tabs.

Common wrong answer: Calling every tab a separate workbook.

Q3 · Formula

What does the SUM function do? Give an example.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): SUM adds numbers in a range. Blank cells are ignored. Text that looks like numbers may cause problems — keep amounts as real numbers.

How — tiny example (vertical steps):

  1. Type amounts in B2:B5: 120, 80, 45, 200.
  2. In B6 type =SUM(B2:B5).
  3. Press Enter — you get 445.

Formula: =SUM(B2:B5)
Result: 445
What it means: Total of four daily sales in ₹.

Common wrong answer: Typing =B2+B3+B4+B5 every time (breaks when you insert rows).

Q4 · Formula

Explain AVERAGE, MIN and MAX with one small example.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): AVERAGE finds the mean. MIN finds the smallest. MAX finds the largest. All ignore blank cells.

How — tiny example (vertical steps):

  1. Marks in C2:C5: 72, 88, 65, 91.
  2. Average: =AVERAGE(C2:C5) → 79.
  3. Min: =MIN(C2:C5) → 65. Max: =MAX(C2:C5) → 91.

Formula: =AVERAGE(C2:C5)
Result: 79
What it means: Class average marks for four students.

Common wrong answer: Including a blank row that has a zero typed by mistake.

Q5 · Formula

What is COUNT vs COUNTA vs COUNTBLANK?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): COUNT counts only numbers. COUNTA counts non-empty cells (text or numbers). COUNTBLANK counts empty cells.

How — tiny example (vertical steps):

  1. A2:A6 has: 10, apple, (blank), 20, banana.
  2. =COUNT(A2:A6) → 2. =COUNTA(A2:A6) → 4. =COUNTBLANK(A2:A6) → 1.

Formula: =COUNTA(A2:A6)
Result: 4
What it means: Four filled cells out of five.

Common wrong answer: Using COUNT on a name list (names are text — COUNT returns 0).

Q6 · Formula

Write an IF formula that shows Pass if marks are 35 or more, else Fail.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): IF checks a condition. If true, it returns one value; if false, another. Syntax: =IF(test, value_if_true, value_if_false).

How — tiny example (vertical steps):

  1. Marks in D2 = 42.
  2. In E2 type =IF(D2>=35,"Pass","Fail").
  3. Result is Pass. Change D2 to 30 — result becomes Fail.

Formula: =IF(D2>=35,"Pass","Fail")
Result: Pass
What it means: Student cleared the 35-mark cut-off.

Common wrong answer: Writing =IF(D2>35,"Pass") without the Fail part (returns FALSE).

Q7 · Formula

What is a relative reference vs an absolute reference ($)?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Relative (A1) changes when you copy the formula. Absolute ($A$1) stays fixed. Press F4 to cycle. Mixed ($A1 or A$1) locks one part.

How — tiny example (vertical steps):

  1. GST rate 18% is in H1. Amount in B2.
  2. Wrong: =B2*H1 copied down changes H1 to H2.
  3. Right: =B2*$H$1 — rate stays locked.

Formula: =B2*$H$1
Result: ₹1,180 if B2=1000
What it means: Amount plus GST using a fixed rate cell.

Common wrong answer: Forgetting $ and wondering why every row uses a different rate.

Q8 · Formula

How do you join first name and last name into one cell?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Use & or CONCAT or TEXTJOIN. & is the simplest for two pieces. TEXTJOIN can add a space and skip blanks.

How — tiny example (vertical steps):

  1. A2=Priya, B2=Sharma.
  2. In C2 type =A2&" "&B2.
  3. Result: Priya Sharma.

Formula: =A2&" "&B2
Result: Priya Sharma
What it means: Full name for a letterhead.

Common wrong answer: Using =A2+B2 (plus is for numbers — gives #VALUE!).

Q9 · Formula

What does TRIM do? Why do interviewers ask this?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): TRIM removes extra spaces before, after and between words (leaves one space between words). Dirty data often has hidden spaces that break VLOOKUP.

How — tiny example (vertical steps):

  1. A2 has ' Pune ' with spaces.
  2. =TRIM(A2) → Pune.
  3. Now VLOOKUP can match the clean city name.

Formula: =TRIM(A2)
Result: Pune
What it means: City name ready for a clean lookup.

Common wrong answer: Using SUBSTITUTE to remove all spaces including the needed one in 'New Delhi'.

Q10 · Formula

Explain LEFT, RIGHT and MID with one example each.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): LEFT takes characters from the start. RIGHT from the end. MID from the middle (start position + length).

How — tiny example (vertical steps):

  1. Code in A2 = BLK-1001.
  2. =LEFT(A2,3) → BLK. =RIGHT(A2,4) → 1001. =MID(A2,5,4) → 1001.

Formula: =LEFT(A2,3)
Result: BLK
What it means: Platform code from an order id.

Common wrong answer: Counting spaces wrong and getting MID off by one.

Q11 · Formula

What is TODAY() and how is it different from a typed date?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): TODAY() always shows today's date and updates every day. A typed date stays fixed. Use TODAY for age, overdue and 'days left' formulas.

How — tiny example (vertical steps):

  1. In A1 type =TODAY().
  2. Due date in B2 = 15-Nov-2026.
  3. Days left: =B2-A1.

Formula: =TODAY()
Result: current date
What it means: Live date for overdue checks.

Common wrong answer: Typing the date by hand in a formula that should stay current.

Q12 · Theory

What is Freeze Panes and why do we use it?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Freeze Panes keeps header rows or columns visible while you scroll. Useful for long lists so you always see column names.

How — tiny example (vertical steps):

  1. Select cell B2 (below header, right of first column).
  2. Go to View → Freeze Panes → Freeze Panes.
  3. Scroll down — row 1 stays. Scroll right — column A stays.

Formula: (View → Freeze Panes)
Result: headers locked
What it means: You can read 500 rows and still see titles.

Common wrong answer: Freezing with A1 selected (nothing freezes).

Q13 · Theory

What is Filter? How is it different from Sort?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Filter hides rows that do not match your choice (city = Pune). Sort reorders all rows (A to Z or high to low). Filter does not change order permanently; Sort does.

How — tiny example (vertical steps):

  1. Select the table. Data → Filter (or Ctrl+Shift+L).
  2. Open City drop-down → tick only Pune.
  3. To sort Amount high to low: Data → Sort.

Formula: Ctrl+Shift+L
Result: filter arrows on headers
What it means: Quick city-wise view without deleting rows.

Common wrong answer: Deleting filtered rows thinking the other cities are gone forever.

Q14 · Theory

What is an Excel Table (Ctrl+T) and why is it better than a plain range?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Ctrl+T turns a range into a Table. Benefits: auto-expand, structured names like Sales[Amount], Total Row, slicers, and formulas that copy down alone.

How — tiny example (vertical steps):

  1. Select A1:D20 with headers.
  2. Press Ctrl+T → OK.
  3. Add a new row under the table — it joins automatically.

Formula: =SUM(Sales[Amount])
Result: total of Amount column
What it means: Formula keeps working when rows are added.

Common wrong answer: Leaving gaps in the header row so Ctrl+T fails.

Q15 · Formula

Write a SUMIF that totals sales for Mumbai only.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): SUMIF adds values that meet one condition. Syntax: =SUMIF(range, criteria, sum_range).

How — tiny example (vertical steps):

  1. Cities in A2:A10. Amounts in B2:B10.
  2. In D2 type =SUMIF(A2:A10,"Mumbai",B2:B10).
  3. Only Mumbai rows are added.

Formula: =SUMIF(A2:A10,"Mumbai",B2:B10)
Result: ₹48,500
What it means: Mumbai branch sales for the week.

Common wrong answer: Putting sum_range first like SUMIFS (wrong argument order).

Q16 · Formula

What does VLOOKUP do? What does the FALSE / TRUE mean?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): VLOOKUP looks for a value in the first column of a range and returns a value from a column to its right. FALSE (or 0) = exact match. TRUE = approximate match (needs sorted data).

How — tiny example (vertical steps):

  1. Ids in A2:A5, Names in B2:B5.
  2. =VLOOKUP(102,A2:B5,2,FALSE) returns the name for id 102.
  3. Always use FALSE for IDs and names.

Formula: =VLOOKUP(102,A2:B5,2,FALSE)
Result: Ananya
What it means: Employee name for code 102.

Common wrong answer: Omitting FALSE so approximate match quietly returns the wrong person.

Q17 · Scenario

Your VLOOKUP returns #N/A. What do you check first?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Check: (1) exact spelling and spaces (TRIM), (2) number stored as text vs real number, (3) lookup value really missing, (4) column index too big, (5) table range cut off.

How — tiny example (vertical steps):

  1. Compare lookup cell and table cell with =A2=D2.
  2. Wrap both sides with TRIM and VALUE if needed.
  3. Use IFERROR to show a friendly message after you fix data.

Formula: =IFERROR(VLOOKUP(A2,T,2,FALSE),"Not found")
Result: Not found
What it means: Clean message instead of #N/A on screen.

Common wrong answer: Hiding #N/A with IFERROR before fixing the real data problem.

Q18 · Theory

What is Conditional Formatting? Give one interview example.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Conditional Formatting changes a cell's colour or icon based on a rule — for example, mark sales below target in red.

How — tiny example (vertical steps):

  1. Select Amount column.
  2. Home → Conditional Formatting → Highlight Cells → Less Than → 5000.
  3. Low sales turn red automatically.

Formula: rule: cell < 5000
Result: red fill
What it means: Manager sees weak days at a glance.

Common wrong answer: Manually painting cells red (will not update when data changes).

Q19 · Theory

What is Data Validation? Why use a drop-down list?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Data Validation limits what users can type. A drop-down list forces City to be only from an allowed list — fewer spelling mistakes.

How — tiny example (vertical steps):

  1. Select City column.
  2. Data → Data Validation → List → Source: Pune,Mumbai,Nashik.
  3. Users pick from the list; wrong spellings are blocked.

Formula: List: Pune,Mumbai,Nashik
Result: drop-down in cell
What it means: Clean city names for SUMIF and Pivot.

Common wrong answer: Allowing free text and then fighting 'Pune ' vs 'pune' forever.

Q20 · Practical

On the practice sheet Orders, find total Amount for Status = Delivered.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Use SUMIF on Status and Amount. Or filter Delivered and read the Total Row of a Table.

How — tiny example (vertical steps):

  1. Open sheet Orders.
  2. In a blank cell type =SUMIF(StatusRange,"Delivered",AmountRange).
  3. Match your answer with the check cell on sheet Answers (yellow).

Formula: =SUMIF(E:E,"Delivered",D:D)
Result: see Answers!B2
What it means: Only delivered sales counted.

Common wrong answer: Summing the whole Amount column including Cancelled.

Q21 · Practical

Create a Pass/Fail column for marks using IF.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Add a helper column. If Marks >= 35 show Pass, else Fail. Copy the formula down or use a Table so it auto-fills.

How — tiny example (vertical steps):

  1. On sheet Marks, column C has marks.
  2. In D2: =IF(C2>=35,"Pass","Fail").
  3. Fill down. Count passes with COUNTIF.

Formula: =IF(C2>=35,"Pass","Fail")
Result: Pass or Fail
What it means: Ready for a count of pass students.

Common wrong answer: Comparing text '35' instead of number 35.

Q22 · Formula

What does ROUND do? ROUND vs INT?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): ROUND(number, digits) rounds to that many decimals. INT strips decimals toward zero for positive numbers (floors). ROUND(2.5,0)=3; INT(2.9)=2.

How — tiny example (vertical steps):

  1. Price 199.567 in A2.
  2. =ROUND(A2,2) → 199.57 for money.
  3. =INT(A2) → 199.

Formula: =ROUND(A2,2)
Result: 199.57
What it means: Money shown with two paise digits.

Common wrong answer: Using INT for money and losing the paise permanently.

Q23 · Scenario

Boss asks: 'How many unique cities are in this list?' What do you do as a fresher?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Easy ways: select the city column → Data → Remove Duplicates on a copy; or use UNIQUE function (=UNIQUE(A2:A100)) in Microsoft 365; or PivotTable with City in Rows.

How — tiny example (vertical steps):

  1. Copy City column to a new sheet.
  2. Data → Remove Duplicates.
  3. COUNTA the remaining list. Or =COUNTA(UNIQUE(A2:A100)).

Formula: =COUNTA(UNIQUE(A2:A100))
Result: 7
What it means: Seven different cities in the file.

Common wrong answer: Saying COUNT on the city column (counts numbers, not names).

Q24 · Theory

Name five useful Excel shortcuts and what they do.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Ctrl+C copy, Ctrl+V paste, Ctrl+Z undo, Ctrl+S save, Ctrl+Arrow jump to edge, Ctrl+Shift+L filter, F4 absolute ref, Alt+= AutoSum, Ctrl+T table, Ctrl+; today's date.

How — tiny example (vertical steps):

  1. Practice Alt+= on a number column.
  2. Practice Ctrl+Shift+L on a table.
  3. Practice F4 inside a formula.

Formula: Alt+=
Result: =SUM(...)
What it means: AutoSum writes SUM for you.

Common wrong answer: Memorising fifty shortcuts but not Alt+= and Ctrl+T.

Q25 · Theory

What file types do you know: xlsx, csv, xls?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): xlsx is the modern Excel workbook. csv is plain text with commas (no formulas or formatting). xls is the old Excel 97–2003 format. Prefer xlsx for work; use csv to share raw data.

How — tiny example (vertical steps):

  1. Save As → Excel Workbook (*.xlsx).
  2. Save As → CSV UTF-8 for data exchange.
  3. Never email an important dashboard only as csv.

Formula: Save As *.xlsx
Result: formulas kept
What it means: Workbook stays fully editable.

Common wrong answer: Editing a csv in Excel and expecting formatting to survive a re-open in Notepad.

Q26 · Formula

Explain COUNTIF with an example.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): COUNTIF counts cells that match one condition. =COUNTIF(range, criteria).

How — tiny example (vertical steps):

  1. Status in E2:E20.
  2. =COUNTIF(E2:E20,"Cancelled") counts cancelled orders.
  3. Use "Pune" on a city column for city count.

Formula: =COUNTIF(E2:E20,"Cancelled")
Result: 3
What it means: Three cancelled orders this week.

Common wrong answer: Using COUNTIFS syntax with only one range by mistake.

Q27 · Scenario

A colleague pasted numbers that will not sum. What could be wrong?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Numbers stored as text (green corner), leading spaces, currency symbols typed as text, or hidden characters from PDF/web paste. Fix with VALUE, Paste Special → Multiply by 1, Text to Columns, or NUMBERVALUE.

How — tiny example (vertical steps):

  1. =ISTEXT(A2) to test.
  2. Type 1 in a cell, copy, Paste Special → Multiply on the column.
  3. Or =VALUE(TRIM(A2)).

Formula: =VALUE(TRIM(A2))
Result: 1250
What it means: Text '1,250' becomes a real number.

Common wrong answer: Re-typing hundreds of rows by hand.

Q28 · Theory

What is a PivotTable in one sentence? When would a fresher use it?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): A PivotTable summarises many rows into a compact table (sum/count by city, month, product) without writing formulas. Freshers use it for sales by city or headcount by department.

How — tiny example (vertical steps):

  1. Select the data → Insert → PivotTable.
  2. Drag City to Rows, Amount to Values.
  3. Change Values to Sum or Count as needed.

Formula: Insert → PivotTable
Result: City-wise sales
What it means: One-minute summary for the manager.

Common wrong answer: Saying you must know VBA to make a PivotTable.

Q29 · Practical

On sheet Staff, find how many employees are in HR using COUNTIF.

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Point COUNTIF at the Department column with criteria "HR".

How — tiny example (vertical steps):

  1. Open sheet Staff.
  2. Department is column C.
  3. =COUNTIF(C:C,"HR") or a tight range C2:C50.
  4. Check Answers!B3.

Formula: =COUNTIF(C2:C50,"HR")
Result: see Answers!B3
What it means: Headcount in HR for the interview task.

Common wrong answer: Counting the whole sheet including the header if criteria somehow matches.

Q30 · Theory

How should you answer 'Tell me about yourself' in an Excel fresher interview?

Why interviewers ask: To see if you understand the idea, not only the button clicks.

What (short answer): Keep it short: your course or degree, that you practised Excel formulas and PivotTables on sample data (say Blinkit-style or college marks), one concrete example you built, and that you want to learn more on the job. Do not list every function.

How — tiny example (vertical steps):

  1. Prepare a 45-second version.
  2. Name one file you built (attendance, marks, sales).
  3. Offer to open Excel and show if asked.

Formula: (spoken answer)
Result: clear 45-sec intro
What it means: Confidence + one proof of skill.

Common wrong answer: Reading a long life story or saying 'I know all of Excel'.

Practice tasks (12)

  1. 1. Total of all order amounts (Easy)
    Hint: Use one function that adds a whole column of numbers. Ignore the header.
  2. 2. Count how many orders exist (Easy)
    Hint: Count non-blank Order IDs — not the amount column.
  3. 3. Total sales for Pune only (Easy)
    Hint: One condition: city name. Add the matching amounts.
  4. 4. How many Delivered orders? (Easy)
    Hint: Count rows where Status matches one word exactly.
  5. 5. Pass or Fail from marks (Medium)
    Hint: Write a logical test against 35. Return two text labels.
  6. 6. Full name from first + last (Medium)
    Hint: Join two cells with a space in the middle. Avoid the plus sign.
  7. 7. Clean city names with spaces (Medium)
    Hint: Remove leading and trailing spaces without deleting the city word.
  8. 8. GST amount at 18% locked rate (Medium)
    Hint: Amount times rate. Lock the rate cell so copy-down stays correct.
  9. 9. Pune + Delivered total (Hard)
    Hint: Two conditions at once. Think of the function that allows many criteria.
  10. 10. Unique city count (Hard)
    Hint: Either UNIQUE+COUNTA, or a Pivot, or Remove Duplicates on a copy.
  11. 11. Highlight amounts under ₹5,000 (Medium)
    Hint: Use a rule that paints cells — do not colour by hand.
  12. 12. Drop-down for Status (Medium)
    Hint: Restrict the cell to a short list of allowed words.

Quiz (8 questions)

1. COUNT counts which cells?

2. To lock cell H1 in a formula you write:

3. Best exact-match VLOOKUP flag:

4. Ctrl+T creates a:

5. TRIM mainly removes:

6. TODAY() returns:

7. SUMIF needs how many required ideas?

8. Numbers that will not SUM are often:

Interview, comparison and revision cards

Standalone cards (no tip or photo strip). Each has a QR to this lesson. Download the PNG and revise offline.

Interview card: SUM vs SUMIF for freshers
Interview card 1/3 — SUM vs SUMIF
Comparison card: relative vs absolute references
Comparison card 2/3 — Relative vs Absolute
Revision card: four must-know fresher Excel skills
Revision card 3/3 — Four must-know skills

Next: back to the Interview Prep hub · related lesson Formulas and Functions.