Excel Skills for Supply Chain and Logistics Roles

The Excel skills for supply chain and logistics roles are practical: look up item and supplier data, total quantities by SKU or warehouse, flag what needs reordering, work out how many days of stock are left, and measure on-time delivery. This guide is for anyone applying for inventory, purchasing, warehouse, logistics coordinator or supply chain analyst roles, with worked examples you can copy into a sheet.

How supply chain teams use Excel#

Supply chain work produces long lists of SKUs (stock keeping units, the code for each item), orders, shipments and deliveries. Even where a company has a warehouse or ERP system, people often export the data to Excel to answer questions quickly:

• Which items are below their reorder point?
• How much did each warehouse ship last month?
• Which supplier delivers late most often?
• How many days of stock do we have left?
• When will this order arrive?

Each question maps to one or two formulas, shown below.

Look up item details with XLOOKUP or VLOOKUP#

Keep one master list of items, with the supplier, unit cost and lead time for each SKU, and pull those details into other sheets with a lookup. That way a cost changes in one place only.

With a SKU in F2, either formula returns its lead time:

=XLOOKUP(F2,A2:A4,D2:D4)
=VLOOKUP(F2,A2:D4,4,FALSE)

For SKU-102, both return 10. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365; VLOOKUP works in every version but needs FALSE for an exact match and can't look to the left. Lock the ranges with $ before filling down. The XLOOKUP function guide covers the optional "not found" argument, which is handy when a new SKU isn't in the master list yet.

Total quantities by SKU or warehouse with SUMIF#

SUMIF adds up the numbers in rows that match one condition. With warehouses in column B and quantities in column C:

=SUMIF(B2:B8,"North",C2:C8)

With the shipment list in the exercise on this page, North shipped 455 units. To add a second condition, such as warehouse and SKU, use SUMIFS, which puts the sum range first: =SUMIFS(C2:C8,B2:B8,"North",A2:A8,"SKU-101"). The SUMIF function guide and how to use SUMIF in Excel go further.

Flag items to reorder#

A reorder flag compares current stock with the reorder point and labels each item. With stock in column C and the reorder point in column D:

=IF(C2<=D2,"Reorder","OK")

Use <= rather than <, so an item exactly at its reorder point is flagged too. To count how many items need ordering, use =COUNTIF(F2:F5,"Reorder"), which returns 2 here.

Days of cover and arrival dates#

Days of cover tells you how long current stock will last at the normal rate of demand:

=C2/E2

Pallet wrap has 140 units and sells about 12 a day, so it has 11.7 days of cover. Wrap it in ROUNDDOWN(C2/E2,0) if you only want whole days. Compare it with the supplier's lead time: if days of cover is shorter than the lead time, stock will run out before a new order arrives.

Expected arrival is the order date plus the lead time. If lead times are in working days, use WORKDAY so weekends are skipped:

=WORKDAY(B2,C2)

With an order date in B2 and a lead time of 5 working days in C2, an order placed on Monday 3 March 2025 is due on Monday 10 March 2025. If lead times are in calendar days, simply add them: =B2+C2.

Measure on-time delivery#

On-time delivery rate is the share of deliveries that arrived by the promised date. First flag each delivery, with the promised date in D2 and the actual date in E2:

=IF(E2<=D2,"On time","Late")

Then divide the on-time count by all deliveries:

=COUNTIF(F2:F200,"On time")/COUNTA(F2:F200)

Format the result as a percentage. To compare suppliers, use COUNTIFS with the supplier as a second condition, and divide by COUNTIF of that supplier.

Highlight low stock with conditional formatting#

Conditional formatting colours cells automatically when a rule is true, so problems stand out without anyone reading every row. To colour the whole row of any item at or below its reorder point:

1. Select the data rows, for example A2:G5.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =$C2<=$D2, choose a fill colour and click OK.

The $ before C and D keeps every cell in the row checking the same two columns. Use a second rule with a different colour for late deliveries. Our step-by-step inventory tracker build combines all of this into one sheet.

Excel tests for supply chain roles#

Interview tests for supply chain roles usually give you an order or stock list and ask for totals, flags and a short summary. Read every question first, answer the quick ones, and point your formulas at input cells rather than typing values. Our guides to passing an Excel test for a job interview, managing your time in a timed test and handling an Excel case study cover the approach. Operations roles use many of the same skills; see the Excel skills for sales and operations.

Practise it#

Practise the three skills behind most supply chain sheets: pulling item data with VLOOKUP exact match, totalling by warehouse or SKU with SUMIF, and highlighting rows that need attention with Conditional formatting. The exercise on this page totals units shipped for one warehouse.