Supplier Fill Rate %: How to Track Vendor
Performance in Excel

 How to Build a Supplier Fill Rate Tracker by Month

1. Set up the required columns in your dataset

• Confirm your dataset includes:
  • PO Date
  • Ordered Quantity
  • Received Quantity
  • Supplier

2. Create a Month field from the PO Date

• Add a new column called Month.
• Use the EOMONTH function to convert each PO Date into a month-ending date.
• Format the Month column as a date so it displays cleanly.

3. Generate a sequential month list for the heat map rows

• Create a Month list area on your summary sheet.
• Use the UNIQUE function to pull the list of months from your Month column.
• Convert the dynamic array to static values using copy and paste values (so the spill borders disappear).

4. Generate the supplier list across the columns

• Use the UNIQUE function to pull suppliers from the Supplier column.
• Wrap that with SORT to make the list alphabetical.
• Use TRANSPOSE to place suppliers across the top row (instead of down a column).
• Copy and paste values to lock the headers in place.

5. Build the monthly-by-supplier fill rate grid

• In the first grid cell (intersection of first Month row and first Supplier column), calculate fill rate using:
  • SUMIFS to sum Received Quantity for that Month and Supplier
  • SUMIFS to sum Ordered Quantity for that same Month and Supplier
  • Divide the two results to get fill rate
• Apply percentage formatting to the grid.

6. Make the grid copy correctly

• Adjust relative and absolute references so:
  • Moving left-to-right changes Supplier
  • Moving top-to-bottom changes Month
• Populate the grid:
  • Drag down to fill months
  • Copy and paste across suppliers (instead of dragging) to avoid table column reference issues

7. Add overall fill rate by supplier

• Create a supplier summary row (overall by supplier).
• Reuse your grid logic but remove the Month criteria from the SUMIFS setup.
• Copy and paste across suppliers.

8. Add overall fill rate by month

• Create a month summary column (overall by month).
• Reuse your grid logic but remove the Supplier criteria from the SUMIFS setup.
• Fill down the months.

9. Add a dynamic target cell for conditional formatting

• Create a target input cell (example: 95%).
• Select the heat map range (and any summary cells you want included).
• Apply Conditional Formatting:
  • Highlight Cells Rules
  • Less Than
  • Reference the target cell so the rule updates when the target changes

10. Format the target label without breaking the logic

• If you want the target cell to display text like “Target: 95%”:
  • Open Format Cells with Ctrl + 1
  • Use a Custom number format so Excel still treats the value as a number
• Confirm conditional formatting still updates properly when you change the target value.