Operations Analyst Excel Test: What to Expect

An operations analyst Excel test checks whether you can measure how a process performs: how long things take, which group is slowest, what share hit the target. You usually get a timed data set of shipments, orders or support tickets and a list of questions. This guide explains what such a test tends to cover, works through sample tasks with answers, and gives you a plan to prepare.

What is an operations analyst Excel test?#

It is a skills check that employers use to see how you handle real operations data in Excel. Formats vary, but most fall into one of these:

• A live spreadsheet task: you get a workbook and type formulas to answer each question.
• Multiple choice: you read a formula or a small table and pick the right result.
• A case: a larger data set and an open question, such as "which carrier should we keep?", where you build the analysis and explain it.

The data is nearly always about time and volume: ship and delivery dates, order counts, ticket open and close times, targets. These are the core tasks of the job itself; see Excel skills for operations managers for the wider role.

What skills does the test check?#

Most questions map to a small set of functions. This table shows the usual ones:

All the sample tasks below use this shipment table in A1:E7. Column E holds working days, calculated in the first task:

Sample task 1: lead time in working days#

Question: How many working days did each shipment take? In E2:

=NETWORKDAYS(C2,D2)

The result for S-01 is 5: Monday to Friday, counting both days. Fill down with Ctrl+D (Windows) or ⌘D (Mac) for the rest of the column. NETWORKDAYS skips Saturdays and Sundays. Add a third argument for holidays: if H2 holds Fri 7 Mar 2025, =NETWORKDAYS(C3,D3,$H$2) gives 6 for S-02 instead of 7.

Read the question carefully. Some tests define lead time as days after shipping, which is NETWORKDAYS minus 1. And if they ask for a due date rather than a count, use WORKDAY: =WORKDAY(C2,5) returns Mon 10 Mar 2025, five working days after 3 Mar. The NETWORKDAYS function guide and the NETWORKDAYS and WORKDAY lesson cover both.

Sample task 2: average and worst lead time per carrier#

Question: What is FastFreight's average lead time, and RoadOne's slowest shipment?

=AVERAGEIFS(E2:E7,B2:B7,"FastFreight")
=MAXIFS(E2:E7,B2:B7,"RoadOne")

The results are 5.33 working days ((5 + 4 + 7) / 3) and 7 (S-02). AVERAGEIFS puts the range to average first, then pairs of criteria range and criteria. Add a date condition to narrow it: =AVERAGEIFS(E2:E7,B2:B7,"FastFreight",C2:C7,">="&DATE(2025,3,5)) averages S-03 and S-05 only, giving 5.5. MAXIFS needs Excel 2019 or later, or Microsoft 365. The AVERAGEIFS function guide and the AVERAGEIF, MAXIFS and MINIFS lesson practise these.

Sample task 3: on-time rate#

Question: The target is 5 working days or fewer. What share of shipments met it?

=COUNTIF(E2:E7,"<=5")/COUNT(E2:E7)

Four of six shipments took 5 days or fewer, so the result is 0.667. Format it as a percentage to show 66.7%. For one carrier, swap COUNTIF for COUNTIFS: =COUNTIFS(B2:B7,"RoadOne",E2:E7,"<=5")/COUNTIF(B2:B7,"RoadOne") returns 66.7% too (2 of 3). To flag each late row, use =IF(E2>5,"Late","On time").

Sample task 4: a PivotTable summary#

Question: Show shipment count and average lead time by carrier. Select the table, choose Insert > PivotTable, then:

1. Drag Carrier to Rows.
2. Drag Shipment to Values. Text fields count automatically, giving 3 and 3.
3. Drag Workdays to Values, click it, choose Value Field Settings and pick Average: 5.33 and 5.

Tests often add a twist: a second row field, a filter, or showing values as a % of the grand total. When new rows are added, right-click the PivotTable and choose Refresh, or change the source range. The Pivot tables 2 lesson covers all of these.

How to prepare in one or two weeks#

A short, focused plan works better than reading about every function:

1. Find your gaps. Take the Excel level test to see which skills need work.
2. Drill the core functions on small operations tables: NETWORKDAYS, WORKDAY, COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS and XLOOKUP.
3. Build PivotTables from scratch until you can do it without thinking.
4. Practise under time. Take the operations case study and the Excel case study test with a timer.
5. Check the platform. If your invitation names a test provider, read the matching guide, such as the Kenexa Prove It Excel test guide or the TestGorilla Excel test guide.

Operations roles overlap with analyst roles, so the data analyst Excel test guide and the financial analyst Excel test guide are useful extra practice. The skill lists in Excel skills for business analysts are close to what operations teams use.

Common mistakes under time pressure#

These slips cost the most marks:

• Answering a slightly different question. "Working days" and "calendar days" give different answers. So do "average per shipment" and "average per carrier".
• Unlocked ranges. Filling down =AVERAGEIFS(E2:E7,B2:B7,G2) without $ shifts the ranges. Press F4 on Windows or ⌘T on a Mac to lock them.
• Dates stored as text. NETWORKDAYS returns #VALUE! if a date is really text. Check that dates sit on the right of the cell.
• Dividing by the wrong count. An on-time rate divides by all shipments, not by the late ones.
• Not checking one answer by hand. Count one row on a calendar. It takes ten seconds and catches most errors.

Practise it#

Try the NETWORKDAYS exercise on this page, then practise NETWORKDAYS and WORKDAY in a live spreadsheet, where you type each formula and it is checked instantly. Next, work through AVERAGEIF, MAXIFS and MINIFS and Pivot tables 2, then take the operations case study against the clock.