Excel Practical Test for Accounting Jobs: Questions, Formulas and Answers

🔥 IMPORTANT TAKEAWAY FOR JOB SEEKERS

Passing an Excel test isn't just about knowing formulas; it's about accuracy, efficiency, clean data formatting, and presenting error-free MIS reports under timed conditions.

1. Core Excel Functions Every Accountant Must Master

Category

Key Excel Functions

Accounting Application

Lookup & Reference

VLOOKUP, XLOOKUP, INDEX & MATCH

Fetching vendor details, price lists, & ledger entries

Logical Analysis

IF, AND, OR, IFERROR, IFS

Aging reports, tax slab eligibility, status tagging

Math & Summaries

SUMIFS, COUNTIFS, SUBTOTAL

Departmental cost summaries, filtered ledger totals

Text Processing

CONCATENATE, TEXTJOIN, TRIM, CLEAN

Standardizing bank statements & cleaning party names

Date & Time

EOMONTH, EDATE, DATEDIF, YEAR

Calculating invoice maturity, interest & depreciation

Data Visualization

Pivot Tables, Slicers, Conditional Format

Executive dashboards, variance analysis & BRS checks

2. Basic to Intermediate Practical Questions & Answers

QUESTION 1: Vendor Name Lookup (VLOOKUP / XLOOKUP)

Scenario: You have a list of unpaid invoices with Vendor ID in Column A. Vendor Name is stored on a master sheet (Sheet2!A2:B100).

Task: Write a formula in Column B to fetch the Vendor Name corresponding to Vendor ID 'VND-104'.

💡 Solution Formula:

   • VLOOKUP Method: =VLOOKUP("VND-104", Sheet2!$A$2:$B$100, 2, FALSE)

   • Modern XLOOKUP Method: =XLOOKUP("VND-104", Sheet2!$A$2:$A$100, Sheet2!$B$2:$B$100, "Not Found")

Key Note: At IICPA Institute, we always train students to lock table arrays using absolute cell references ($) to avoid formula shifts when dragging down.

QUESTION 3: Calculating Gross Invoice Amount Including GST

Scenario: Column A contains Base Value (₹15,000) and Column B contains GST Rate (18%).

Task: Calculate total invoice amount including GST in Column C.

💡 Solution Formula:

   • Formula: =A2 * (1 + B2)

Result: ₹15,000 * 1.18 = ₹17,700

3. Advanced Accounting Test Scenarios & Formulas

QUESTION 4: Multi-Criteria Expense Summarization (SUMIFS)

Scenario: The finance team maintains a master expense ledger with columns: Date (Col A), Department (Col B), Category (Col C), and Amount (Col D).

Task: Calculate total expenditure incurred by 'Marketing' department for 'Digital Ads'.

💡 Solution Formula:

   • Formula: =SUMIFS(D2:D500, B2:B500, "Marketing", C2:C500, "Digital Ads")

Pro Tip: Remember that SUMIFS places the Sum Range FIRST, followed by Criteria Ranges and Criteria pairs.

QUESTION 5: Suppressing Division Errors in Financial Dashboards (IFERROR)

Scenario: Profit Margin % calculation (=Net Profit / Net Sales) returns #DIV/0! for months with zero sales.

Task: Clean the report so zero-sales months show 0.0% or 'N/A' instead of harsh error codes.

💡 Solution Formula:

   • Formula: =IFERROR(A2/B2, 0)

IICPA Institute Insight: In corporate MIS reporting, clean presentation is critical. Unhandled formula errors lead to negative marks in technical test rounds.

QUESTION 6: Two-Way Reverse Lookup (INDEX & MATCH)

Scenario: You need to look up Item Description in Column A using Item Code in Column B. VLOOKUP fails because lookup value is on the right.

Task: Perform a left-side reverse lookup.

💡 Solution Formula:

   • Formula: =INDEX(A2:A100, MATCH("ITEM-501", B2:B100, 0))

4. Real-World Case Study: Bank Reconciliation Statement (BRS) Matching

⚡ STEP-BY-STEP BRS RECONCILIATION WORKFLOW (IICPA METHODOLOGY)

Step 1: Clean Raw Data — Use =TRIM() and =CLEAN() to eliminate extra spaces or non-printable characters in transaction references.

Step 2: Identify Unmatched Items — Apply =IF(COUNTIF(Bank_Stmt!A2:A500, Cash_Book!A2)=0, "Unmatched in Bank", "Matched") in Cash Book.

Step 3: Pivot Table Summary — Create a dynamic Pivot Table to group transactions by GL Head, displaying Debit, Credit, and Net Balance.

Step 4: Interactive Dashboard — Insert Slicers for Month/Quarter filtering and Conditional Formatting (Soft Red Fill) for outstanding uncleared cheques.

5. Common Mistakes to Avoid During Excel Hiring Tests

⚠️ CRITICAL ERRORS THAT CAUSE CANDIDATES TO FAIL

1. Hardcoding Calculated Values: Never type calculated numbers directly into cells. Interviewers check formula bars for active logic.

2. Forgetting Cell Anchoring ($): Failing to lock cell references when dragging formulas causes shifted arrays and incorrect calculations.

3. Ignoring Number Formatting: Always apply clean formatting — Currency symbols (₹), commas (1,00,000), percentage signs, and standard date formats (DD-MMM-YYYY).

4. Working on Dirty Data: Never run VLOOKUP or Pivot Tables without checking for duplicate records or trailing spaces first.

6. Why Choose IICPA Institute for Practical Accounting Training?

🎓 KEY HIGHLIGHTS OF IICPA INSTITUTE TRAINING PROGRAMS

• 100% Practical Training on Live Corporate Files & GST/TDS Portals

• Comprehensive Modules: Advanced Excel, MIS Dashboards, Power Query, Tally Prime, GST Returns, Income Tax, & MCA Compliance

• Rigorous Test Preparation: Regular mock Excel practical tests, corporate case studies, and resume building

• 100% Job Placement Assistance with top accounting firms and corporate organizations

Start Your Journey to Corporate Success Today!