Excel for Operations Managers: The Core Skills
By ExcelReady TeamUpdated 5 min read
The short answer
An operations manager uses Excel to track how work flows: NETWORKDAYS for lead times in working days, WORKDAY for promised dates, AVERAGEIFS, MAXIFS and MINIFS to compare sites or teams, pivot tables for weekly reports, and simple ratios such as on-time rate and capacity use. Clean data and clear check cells keep those reports trusted.
Key takeaways
- NETWORKDAYS counts working days between two dates, counting both ends and skipping weekends and listed holidays.
- WORKDAY gives the date a set number of working days after a start date, which is ideal for promised dates.
- AVERAGEIFS, MAXIFS and MINIFS compare sites, shifts or suppliers in one formula each.
- Pivot tables turn a long log into a weekly report in a few clicks.
On this page+
Excel for operations manager roles is about measuring how work flows: how long orders take, which site is slower, whether deadlines are met and how full capacity is. The core skills are working-day maths with NETWORKDAYS and WORKDAY, comparisons with AVERAGEIFS and MAXIFS, and pivot tables for regular reports. This guide shows each one on a small shipping log, so you can see exactly what the formulas return.
What operations managers use Excel for#
Operations teams often pull data from order, warehouse or scheduling systems into Excel to answer day-to-day questions. Typical files include:
- Order and shipping logs: order date, ship date, site and status.
- Capacity plans: hours or units available against hours or units needed.
- Supplier scorecards: lead times and on-time rates by supplier.
- Weekly reports: volumes, delays and exceptions by site or team.
The examples use this shipping log for March 2024:
| Order | Site | Ordered | Shipped | Workdays |
|---|---|---|---|---|
| O-101 | Dallas | Mon 4 Mar | Fri 8 Mar | 5 |
| O-102 | Reno | Tue 5 Mar | Tue 12 Mar | 6 |
| O-103 | Dallas | Thu 7 Mar | Mon 11 Mar | 3 |
| O-104 | Reno | Fri 8 Mar | Tue 19 Mar | 8 |
| O-105 | Dallas | Wed 13 Mar | Fri 15 Mar | 3 |
The Workdays column is what you'll calculate first.
Lead times in working days with NETWORKDAYS#
NETWORKDAYS counts the working days between two dates, Monday to Friday, and includes both the start and the end date. In E2:
=NETWORKDAYS(C2,D2)returns 5 for O-101 (Monday to Friday of the same week). Fill it down (Windows: Ctrl+D; Mac: ⌘D) to get 6, 3, 8 and 3. O-104 crosses two weekends, so a plain subtraction (=D5-C5) would give 11 calendar days, while NETWORKDAYS gives 8 working days.
Add a holiday list as the third argument to skip public holidays: =NETWORKDAYS(C2,D2,$H$2:$H$10). Lock the holiday range with $ so it doesn't move as you fill. For example, March 2024 has 21 working days: =NETWORKDAYS(DATE(2024,3,1),DATE(2024,3,29)) returns 21. If H2 holds Friday 29 March as a company holiday, =NETWORKDAYS(DATE(2024,3,1),DATE(2024,3,29),H2) returns 20. Keep the holiday list on its own sheet and update it once a year, so every report uses the same calendar. The NETWORKDAYS function guide explains the arguments. If your sites work a different weekend, such as Friday and Saturday, use NETWORKDAYS.INTL instead.
Promised dates with WORKDAY#
WORKDAY does the opposite job: it starts from a date and counts forward a number of working days.
=WORKDAY(C2,5)returns Monday 11 March 2024, five working days after Monday 4 March. The result is a date serial number, so format the cell as a date if it shows a number like 45362.
To flag late orders, compare the ship date with the promised date: =IF(D2>WORKDAY(C2,5),"Late","On time"). With a five-day promise, O-104 is Late (shipped 19 March, promised 15 March) and the rest are On time. Then =COUNTIF(F2:F6,"On time")/COUNTA(F2:F6) gives the on-time rate: 4 ÷ 5 = 80%.
Comparing sites with AVERAGEIFS, MAXIFS and MINIFS#
Conditional statistics compare groups without building a separate table for each. AVERAGEIFS puts the range to average first, then each condition:
=AVERAGEIFS(E2:E6,B2:B6,"Dallas")returns about 3.67 working days ((5 + 3 + 3) ÷ 3). For Reno it returns 7 ((6 + 8) ÷ 2). That's a clear difference worth investigating.
=MAXIFS(E2:E6,B2:B6,"Reno")returns 8, Reno's slowest order.=MINIFS(E2:E6,B2:B6,"Dallas")returns 3, Dallas's fastest.=COUNTIFS(B2:B6,"Reno",E2:E6,">5")returns 2, Reno orders that took more than five working days.
MAXIFS and MINIFS need Excel 2019, Excel 2021 or Microsoft 365. See the AVERAGEIFS function guide for more conditions.
Weekly reports with pivot tables#
A pivot table summarises a long log by site, week or product without formulas. Select the data, choose Insert > PivotTable, drag Site to Rows and Workdays to Values, then change the value setting from Sum to Average. You get Dallas 3.67 and Reno 7 in a few clicks.
Add the order date to Columns and group it by week or month (right-click a date > Group) to see whether lead times are improving. When new orders arrive, refresh the pivot (Data > Refresh All). Keeping the log as an Excel table (Ctrl+T; Mac: ⌘T) means new rows are picked up automatically.
Capacity and rate calculations#
Many operations numbers are simple ratios. The skill is setting them up so they're easy to check:
| Measure | Formula | Example |
|---|---|---|
| Capacity use | =Actual/Capacity | 1,840 ÷ 2,000 hours = 92% |
| On-time rate | =OnTime/Total | 4 ÷ 5 orders = 80% |
| Defect rate | =Defects/Units | 12 ÷ 1,500 units = 0.8% |
| Change vs last week | =(This-Last)/Last | (460 - 400) ÷ 400 = 15% |
Put targets in their own labelled cells and point formulas at them, so changing a target updates every comparison. Conditional formatting can then colour any site below target in red.
How these skills show up in interviews#
Operations interviews and Excel tests often use a log like the one above and ask: what's the average lead time by site, which orders were late, and what's the on-time rate? Practise answering each in a minute or two. Be ready to explain your choices too, such as why you measured lead time in working days rather than calendar days, and what you would check next if one site is much slower. These skills overlap with Excel skills for sales operations and Excel skills for project managers, which add pipeline and timeline work. If you're early in your career, Excel skills for fresh graduates covers the foundations, and Excel skills for finance jobs helps when budgets join the picture.
Practise it#
Working-day maths is easy to get wrong until you've done it a few times. Practise NETWORKDAYS and WORKDAY in a live spreadsheet: you type the formula, and it checks it instantly. Then compare groups with AVERAGEIF, MAXIFS and MINIFS and build a summary in Pivot tables 1. For a realistic timed task, try the operations case study.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
How many working days did order O-104 take to ship? Click E5 and use NETWORKDAYS from the order date in C5 to the ship date in D5.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Workdays | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 |
Tip: NETWORKDAYS takes the start date first, then the end date.
Practise this in a lesson
Frequently asked questions
What Excel skills does an operations manager need?
An operations manager needs date maths with NETWORKDAYS and WORKDAY, conditional statistics with AVERAGEIFS, MAXIFS and COUNTIFS, pivot tables, percentages for rates like on-time delivery, lookups, and conditional formatting to flag exceptions.
How do I calculate lead time in working days in Excel?
Use =NETWORKDAYS(start_date,end_date). It counts Monday to Friday between the two dates, including both ends. Add a third argument with a range of holiday dates to skip those too.
How do I calculate a due date that skips weekends?
Use =WORKDAY(start_date,days). For example, =WORKDAY(C2,5) returns the date five working days after the date in C2. Add a holiday range as the third argument if needed.
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and puts the average range last. AVERAGEIFS takes one or more conditions and puts the average range first, like SUMIFS.