Ravindra BagaleCourses & study guides Track your progress

Excel Interview Prep — HR / Admin

By · Last updated:

Friends, hR Excel: attendance COUNTIF, leave balance, payroll pieces, headcount Pivot, and privacy habits.

Fictional sample data for learning. Industry flavour: Services company HR ops (Hyderabad / Austin) ₹ / $. 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 28 real interview-style questions for HR / Admin. 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-hr-interview.xlsx (includes About this data, practice sheets, Answers).

Ravindra Bagale's Tip

Friends, never WhatsApp the salary register — share need-to-know only.

Ravindra Bagale's Tip

Emp ID first, name second — duplicates in names break VLOOKUP.

Questions (28)

Q1 · Formula

Present days with COUNTIF on attendance grid?

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

What (short answer): COUNTIF each row for "P" or tick mark across date columns.

How — tiny example (vertical steps):

  1. =COUNTIF(C2:AG2,"P").

Formula: =COUNTIF(C2:AG2,"P")
Result: 22
What it means: Present days.

Common wrong answer: Counting blank as present.

Q2 · Formula

Leave balance basic formula?

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

What (short answer): Opening + accrued - taken = balance. Keep types (CL/SL/EL) separate.

How — tiny example (vertical steps):

  1. =B2+C2-D2.

Formula: =B2+C2-D2
Result: balance
What it means: Leave ledger.

Common wrong answer: Mixing all leave types in one number without rules.

Q3 · Theory

Salary components you model in Excel?

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

What (short answer): Basic, HRA, allowances, PF employee, professional tax, net = gross - deductions.

How — tiny example (vertical steps):

  1. Gross then deductions then net.

Formula: =Gross-PF-PT
Result: Net
What it means: Payslip line.

Common wrong answer: Hard-coding net without showing parts.

Q4 · Formula

Overtime pay simple model?

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

What (short answer): OT hours * OT rate; OT rate may be 1.5*hourly.

How — tiny example (vertical steps):

  1. =OT_Hours*Hourly*1.5.

Formula: =E2*F2*1.5
Result: OT pay
What it means: Payroll input.

Common wrong answer: Forgetting approval flag before paying OT.

Q5 · Theory

Headcount Pivot — what fields?

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

What (short answer): Rows: Department or Location; Values: COUNT of Employee ID; Filters: Active status.

How — tiny example (vertical steps):

  1. Active = Yes filter.

Formula: (Pivot COUNT)
Result: HC
What it means: Workforce MIS.

Common wrong answer: Summing salary when asked for headcount.

Q6 · Formula

Simple attrition rate?

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

What (short answer): Exits in period / average headcount (or opening). Confirm definition with HRBP.

How — tiny example (vertical steps):

  1. =Exits/((Open+Close)/2).

Formula: =B2/((B3+B4)/2)
Result: attrition %
What it means: HR KPI.

Common wrong answer: Using closing HC only without agreeing definition.

Q7 · Formula

Roster with INDEX/MOD idea?

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

What (short answer): Cycle shifts across people using INDEX on a shift list with MOD of row number.

How — tiny example (vertical steps):

  1. =INDEX(Shifts, MOD(row-1,n)+1).

Formula: =INDEX($H$2:$H$4,MOD(ROW()-2,3)+1)
Result: Shift A
What it means: Roster pattern.

Common wrong answer: Copy-pasting shifts by hand for 200 staff.

Q8 · Theory

Offer CTC breakup sheet?

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

What (short answer): Annual and monthly for Basic, HRA, Special, PF employer, Gratuity estimate, CTC total.

How — tiny example (vertical steps):

  1. Monthly = Annual/12.

Formula: =Annual/12
Result: monthly
What it means: Offer clarity.

Common wrong answer: Showing only CTC without breakup.

Q9 · Practical

Clean employee master: TRIM and PROPER.

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

What (short answer): Helper columns =TRIM(PROPER(Name)) then replace values.

How — tiny example (vertical steps):

  1. Fix spaces and casing.

Formula: =TRIM(PROPER(A2))
Result: Priya Sharma
What it means: Clean master.

Common wrong answer: VLOOKUP failures on dirty names.

Q10 · Formula

Birthday reminder with DATEDIF/TEXT?

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

What (short answer): Next birthday date from DOB; flag if within 7 days.

How — tiny example (vertical steps):

  1. Use DATE(YEAR(TODAY()),MONTH(DOB),DAY(DOB)) and adjust if passed.

Formula: =DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))
Result: next bday
What it means: Engagement list.

Common wrong answer: Ignoring leap-year DOB edge cases.

Q11 · Theory

Attendance vs timesheet difference?

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

What (short answer): Attendance is present/absent; timesheet may capture hours and projects. Different controls.

How — tiny example (vertical steps):

  1. Do not mix without mapping.

Formula: (definitions)
Result: clear data
What it means: HR ops clarity.

Common wrong answer: Paying OT from attendance ticks alone when policy needs hours.

Q12 · Formula

COUNTIFS for late marks by department.

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

What (short answer): COUNTIFS(Dept,G2,LateFlag,"Y").

How — tiny example (vertical steps):

  1. Department late KPI.

Formula: =COUNTIFS(D:D,G2,L:L,"Y")
Result: late count
What it means: Discipline MIS.

Common wrong answer: Counting all late without department split.

Q13 · Scenario

Payroll file has duplicate Emp IDs.

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

What (short answer): UNIQUE/COUNTIF to find duplicates; freeze pay until resolved; fix master.

How — tiny example (vertical steps):

  1. =COUNTIF(ID,A2)>1 helper.

Formula: =COUNTIF($A$2:$A$500,A2)
Result: dup flag
What it means: Payroll safety.

Common wrong answer: Paying both rows.

Q14 · Theory

Data privacy in HR Excel files?

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

What (short answer): Mask PAN/Aadhaar, restrict access, no open WhatsApp of salary sheets, password or OneDrive ACL.

How — tiny example (vertical steps):

  1. Share need-to-know only.

Formula: (privacy)
Result: safe HR
What it means: Compliance mindset.

Common wrong answer: Emailing full salary register to all managers.

Q15 · Practical

Pivot gender × department headcount.

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

What (short answer): COUNT Emp ID; filter Active.

How — tiny example (vertical steps):

  1. Check totals to master.

Formula: (Pivot)
Result: diversity MIS
What it means: HC view.

Common wrong answer: Using salary sum instead of count.

Q16 · Formula

Probation end date = join + 90 days / EDATE.

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

What (short answer): =EDATE(Join,3) or Join+90 per policy.

How — tiny example (vertical steps):

  1. Flag if TODAY()>probation end and status still Probation.

Formula: =EDATE(B2,3)
Result: probation end
What it means: HR ops reminder.

Common wrong answer: Hard-coding dates per employee.

Q17 · Theory

Full and final checklist in Excel?

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

What (short answer): Assets returned, leave encashment, notice recovery, last working day, approvals — tick columns.

How — tiny example (vertical steps):

  1. One row per exit.

Formula: (FnF)
Result: exit control
What it means: No missed steps.

Common wrong answer: Paying FnF before asset return tick.

Q18 · Formula

HRA exemption rough helper (conceptual)?

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

What (short answer): Interview: know Excel can model min of rules; final tax advice is not from a rough sheet alone.

How — tiny example (vertical steps):

  1. Show inputs and formula transparently.

Formula: (model)
Result: working paper
What it means: Not tax advice.

Common wrong answer: Promising tax outcomes from an unverified sheet.

Q19 · Scenario

Manager asks average experience by team.

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

What (short answer): DATEDIF or year fraction from DOJ to TODAY; AVERAGEIFS by team.

How — tiny example (vertical steps):

  1. =AVERAGEIFS(Exp,Team,G2).

Formula: =AVERAGEIFS(E:E,C:C,G2)
Result: avg years
What it means: Talent MIS.

Common wrong answer: Averaging join dates directly.

Q20 · Practical

Build attendance calendar month with P/A validation.

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

What (short answer): Data Validation list P/A/L/H; COUNTIF summaries.

How — tiny example (vertical steps):

  1. Protect formula totals.

Formula: (DV)
Result: clean attendance
What it means: Ops ready.

Common wrong answer: Allowing free text like "present ".

Q21 · Formula

TEXTJOIN of skills for a role search.

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

What (short answer): TEXTJOIN comma across skill columns; FILTER people with ISNUMBER(SEARCH()).

How — tiny example (vertical steps):

  1. Search "Python".

Formula: =TEXTJOIN(", ",TRUE,H2:L2)
Result: skills
What it means: Talent search.

Common wrong answer: One skill per row without a person key.

Q22 · Theory

Why Emp ID as primary key?

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

What (short answer): Names duplicate; IDs stay unique for payroll, attendance and VLOOKUP maps.

How — tiny example (vertical steps):

  1. Never reuse IDs casually.

Formula: (Emp ID)
Result: unique key
What it means: Clean joins.

Common wrong answer: Looking up only by name.

Q23 · Formula

Working days in month with NETWORKDAYS.

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

What (short answer): =NETWORKDAYS(month_start,month_end,holidays) for expected attendance base.

How — tiny example (vertical steps):

  1. Compare present to working days.

Formula: =NETWORKDAYS(A1,B1,Holidays)
Result: wdays
What it means: Attendance % base.

Common wrong answer: Using 30 for every month.

Q24 · Scenario

HRIS export vs Excel tracker out of sync.

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

What (short answer): Treat HRIS as master; Excel is working paper. Reconcile weekly with Emp ID MATCH.

How — tiny example (vertical steps):

  1. List Unmatched.

Formula: MATCH Emp ID
Result: gaps
What it means: Data sync.

Common wrong answer: Editing HRIS fields only in Excel forever.

Q25 · Practical

Salary register net pay column and bank upload sheet.

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

What (short answer): Net formula; separate sheet EmpID, Name, Account, IFSC, Amount for bank.

How — tiny example (vertical steps):

  1. Validate account length.

Formula: =Gross-Deductions
Result: Net
What it means: Bank file.

Common wrong answer: Including exited staff without LWD filter.

Q26 · Theory

Interview: confidentiality example you follow?

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

What (short answer): Need-to-know salary access, locked sheets, no screenshots on personal phone.

How — tiny example (vertical steps):

  1. State a concrete habit.

Formula: (habit)
Result: trust
What it means: HR fit.

Common wrong answer: Vague "I am careful" with no example.

Q27 · Formula

Age from DOB with DATEDIF.

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

What (short answer): =DATEDIF(DOB,TODAY(),"Y").

How — tiny example (vertical steps):

  1. Check odd DOB values.

Formula: =DATEDIF(B2,TODAY(),"Y")
Result: age
What it means: Demographics.

Common wrong answer: Using TODAY()-DOB formatted wrong.

Q28 · Theory

What belongs in HR Excel vs HRMS?

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

What (short answer): HRMS for official records; Excel for analysis, what-ifs, and temporary trackers with clear expiry.

How — tiny example (vertical steps):

  1. Do not parallel unofficial salary masters.

Formula: (boundary)
Result: right system
What it means: Mature answer.

Common wrong answer: Keeping the only attendance master in email.

Practice tasks (12)

  1. 1. COUNTIF present days row (easy)
    Hint: Count "P" across the month columns.
  2. 2. Leave balance CL/SL columns (easy)
    Hint: Opening+accrued−taken.
  3. 3. Net salary from components (medium)
    Hint: Gross minus each deduction.
  4. 4. Headcount Pivot by dept (medium)
    Hint: COUNT of Emp ID, filter Active.
  5. 5. Attrition % with agreed formula (medium)
    Hint: Document definition on About.
  6. 6. TRIM PROPER name clean (easy)
    Hint: Helper column then paste values.
  7. 7. Duplicate Emp ID flag (medium)
    Hint: COUNTIF > 1.
  8. 8. EDATE probation end (easy)
    Hint: EDATE(join,3).
  9. 9. NETWORKDAYS month base (medium)
    Hint: Pass holiday list.
  10. 10. Bank upload sheet from register (medium)
    Hint: Filter Active and Amount>0.
  11. 11. Birthday within 7 days flag (hard)
    Hint: Compute next birthday then subtract TODAY.
  12. 12. Gender × Dept Pivot (easy)
    Hint: COUNT not SUM.

Quiz (8 questions)

1. Emp ID is a better lookup key than name.

2. Attrition definitions should be agreed before reporting.

3. Salary sheets can be freely WhatsApp-forwarded.

4. COUNTIF can count Present marks across a row.

5. HRIS should be the system of record over shadow Excel masters.

6. DATEDIF can compute age in years.

7. Headcount Pivots should COUNT employee IDs.

8. Duplicate Emp IDs are safe to pay twice.

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.

Attendance COUNTIF
Interview card 1/3 — Attendance COUNTIF
Name vs Emp ID
Comparison card 2/3 — Name vs Emp ID
HR privacy
Revision card 3/3 — HR habits

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