The Scenario
A logistics analyst has 500 completed order rows in Sheet1 of an Excel workbook — scheduled times in column D, actual completion times in column E, driver name in column F. The weekly performance review goes to the ops director at 4 PM Friday. The expectation is a summary dashboard: on-time rate, average delay, and daily order counts broken down by driver.
The workbook was handed off from someone who left. No formula documentation. The dashboard worksheet has headers but no data. The driver name field has inconsistent formatting — some rows have last name first, some first name first — which breaks any grouping you try to build with a pivot.
The bad version:
- You build a COUNTIFS formula for on-time orders per driver. It takes 20 minutes because the timestamp fields are text, not date values, requiring a helper column to parse them.
- You start on average delay and realize you need to define what "late" means. You message the director. She responds three hours later.
- The driver name inconsistency creates 14 pivot groups instead of 8. You standardize by hand, row by row.
- It is 4:40 PM. The review started without the dashboard.
The analysis was not complicated. The setup was.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Excel workbook. It reads the raw completion data in Sheet1 and builds the summary dashboard in Sheet2 directly — no formula configuration, no pivot setup.
Open the SheetXAI sidebar and ask:
Using the order data in Sheet1 — scheduled time in column D, actual time in column E, driver name in column F — calculate on-time percentage, average delay in minutes, and total orders per day for last week, then write a summary table to Sheet2.
What You Get
- Sheet2 gets a structured summary table with one row per driver.
- Columns: driver name, total deliveries, on-time count, on-time percentage, average minutes late.
- A second section shows daily totals across the fleet for the week.
- Driver names are normalized before grouping — formatting variants of the same name are consolidated.
- Early completions are treated as on-time by default; the prompt can specify a different rule.
What If the Data Is Not Quite Ready
The timestamp fields are stored as text strings, not date values
The times in columns D and E are stored as text in "HH:MM AM/PM" format — parse them before calculating delay, then build the summary in Sheet2 showing on-time rate and average delay by driver.
The summary needs to break down by both driver and day
Analyze the completions in Sheet1 and build a 'Performance' worksheet with two sections: per-driver totals for the week, and daily totals for the fleet — include on-time percentage and average delay in both sections.
Some rows have no actual completion time because the order was not fulfilled
Build the Sheet2 summary using the completions in Sheet1 — exclude rows where column E is blank, count them separately as unfulfilled, and include an unfulfilled count column in the driver summary.
The director needs the full pipeline: pull from OptimoRoute, calculate performance, and flag underperformers
Pull all OptimoRoute completion records from last week into Sheet1 with scheduled time in column D and actual time in column E, calculate on-time percentage and average delay by driver, write the summary to Sheet2, and mark the three drivers with the highest average delay as needing review in column F of Sheet2.
From raw API data to a flagged summary in one prompt.
Try It
Get the 7-day free trial of SheetXAI and open the Excel workbook where your weekly completion data lives, then ask it to build the driver performance dashboard you would normally spend an afternoon constructing. Also see how to export raw completion data from OptimoRoute, and how to search by order status to investigate specific failures.