Excel Skills Employers Test for Office Jobs (and How to Pass the Test)

Most office jobs in India, whether accounts, MIS, operations, HR, sales coordination or back-office support, expect you to be comfortable in Excel. And more and more employers check this with a short practical test instead of trusting the line "Proficient in MS Office" on your resume. If you can clean a messy sheet, use lookups, build a pivot table, and write a few conditional formulas without searching online, you'll pass most of these tests.

This guide lists what usually comes up, how to practise it, and the small mistakes that cost people the test even when they know the formulas.

What an Excel test usually looks like

There's no standard format, but most tests are one of these:

• A practical file task: you get a workbook with raw data (sales, attendance, invoices) and 5 to 10 questions to answer in 20 to 45 minutes. This is the most common format for MIS, accounts and operations roles.
• An online multiple-choice test: questions on functions and features. Common in large-volume hiring by BPOs and staffing agencies.
• A screen-share task during the interview: the interviewer asks you to do something live. Nerve-racking, but usually simpler tasks.

Ask the recruiter beforehand what format to expect and which Excel version they use. It's a normal question.

The skills that come up most often

Lookups. VLOOKUP is still everywhere, and XLOOKUP is now common in newer versions. Know INDEX with MATCH as well, because it handles lookups to the left and some interviewers specifically ask for it. Understand what happens when there's no match and how to handle it with IFERROR or the "if not found" argument in XLOOKUP.

Conditional formulas. IF, nested IF or IFS, AND/OR, SUMIFS, COUNTIFS, AVERAGEIFS. A typical question: "Total sales for the North region in March where the amount is above ₹50,000."

Pivot tables. Create one, change the summary from sum to count or average, group dates by month, add a filter, and show values as a percentage of total. Many candidates can create a pivot but get stuck when asked to group or change the calculation.

Cleaning data. TRIM for extra spaces, removing duplicates, Text to Columns, changing text-formatted numbers into real numbers, fixing dates stored as text. Real-world data is messy, and test files are deliberately messy too.

Text and date functions. LEFT, RIGHT, MID, LEN, CONCAT or the & operator, TEXT for formatting, and date functions like EOMONTH, DATEDIF or NETWORKDAYS for things like ageing reports or working days.

Useful extras. Conditional formatting, data validation (dropdown lists), freezing panes, absolute vs relative references ($A$1 vs A1), and a clean, readable chart. Some roles also ask about basic macros or Power Query; if the job description mentions them, prepare for them.

Mistakes that cost marks

• Typing values instead of using formulas. Examiners check formulas, not just answers. A correct number typed in by hand often gets zero.
• Forgetting absolute references. The formula works in the first row and breaks when you drag it down.
• VLOOKUP without FALSE or 0. An approximate match returns wrong results silently. It's one of the most common errors in tests.
• Not reading the question. "Count" and "sum" get mixed up under time pressure. Re-read each question once before you start it.
• Leaving the file messy. Label your answers clearly, one sheet or one clearly marked area per question. It helps the evaluator, and it shows you work cleanly.

How to practise in a week

Make your own messy dataset or download a public one. Data from data.gov.in works well because it's real and often needs cleaning. Then set yourself questions the way a manager would ask them: "Which district had the highest growth?", "Show me monthly totals by category", "List customers with more than three orders." Time yourself.

Do one practice set a day. On the last two days, do a full timed test without looking anything up. You'll find your weak spots quickly. For most people it's pivot grouping, text-to-number problems, or INDEX-MATCH.

If you have access only to Google Sheets, that's fine for learning most of this, since the core functions are very similar. But try to practise on desktop Excel at least a few times before the test, because menus and some features differ.

Putting it on your resume honestly

"Advanced Excel" means different things to different people, and interviewers know it. Be specific instead: "Excel: pivot tables, XLOOKUP and INDEX-MATCH, SUMIFS/COUNTIFS, data cleaning, dashboards." That gives the interviewer something concrete to test, and if you can back it up, it builds trust fast. If you've used Excel in an actual job, say what you did with it: "Built a weekly sales MIS for 40 stores used by the regional manager."

Excel is also a natural first step towards analyst roles. If that interests you, our guide on becoming a data analyst without a CS degree covers what comes next. And if you're looking for office roles now, browse administration and finance jobs on CareerCola, where many listings mention the Excel skills they expect.