Stop Drowning in POs: Build a Vendor Dashboard in Excel
The 4:59 PM Panic
You know the feeling. It’s Friday, one minute before you log off, and the Production Manager runs into your office—or pings you on Teams—asking, “Where is the steel shipment? We have a line down Monday morning if it’s not here.”
If you are digging through six different email threads, three sticky notes, and a mental map that only exists in your head to find the answer, you are already losing. This is the reality for countless procurement pros operating without a six-figure ERP system. But here is the good news: you don’t need Oracle or SAP to stop the bleeding. You just need to know How to Build a Simple Vendor Tracking Dashboard in Excel.
I’m going to show you how to turn that chaos into a command center. Let’s stop pretending complex software is the only answer. Sometimes, a well-structured spreadsheet is the hero we actually need.
Step 1: Kill the Merged Cells
Before we build the dashboard, we have to fix your data habits. This is where I get opinionated. If you are merging cells for “aesthetic” purposes in your data source, stop it immediately.
Your dashboard needs a clean, flat database. Create a tab called Data_Source. Your headers must be single-row: PO Number, Vendor Name, Item, Quantity, Order Date, Expected Delivery, and—crucially—Status.
The Golden Rule of Input
- One row per line item. Do not group POs visually.
- Use Data Validation. For the
Statuscolumn, restrict inputs to a drop-down list: Open, Dispatched, Received, Late. This prevents typos like “Dispached” from breaking your reporting later. - Format as Table. Highlight your data and hit
Ctrl + T. This makes your ranges dynamic, so when you add a new PO, your dashboard updates automatically.
Step 2: The “Brains” (Pivot Tables)
You want a summary dashboard that tracks open POs and dispatch details. Do not use complex macros. Macros break, and then you become the IT guy. Use Pivot Tables.
Create a new tab called Dashboard_Calc. Insert a Pivot Table from your Data_Source.
- Rows: Vendor Name
- Columns: Status
- Values: Count of PO Number (or Sum of Quantity)
Now you have a grid showing exactly how many orders are sitting in “Open” vs “Late” for every single vendor. It’s ugly right now, but it’s accurate.
Step 3: Visualizing the Dispatch
Copy that Pivot Table and create a second one focusing on Expected Delivery. Group the dates by Month or Week. This gives you a timeline view.
To make this a true “Dashboard,” cut these Pivot Charts and paste them onto a clean tab called EXECUTIVE_VIEW. Turn off the gridlines (View > Gridlines: Uncheck). This simple white space makes your sheet look like an app, not a grid.
The Secret Weapon: Slicers.
Select your Pivot Chart, go to Analyze > Insert Slicer. Choose Vendor Name and Status. Now, with a single click, you can filter your entire dashboard to show only “Late” orders for “Supplier X.”
The Friday Afternoon Save: A True Story
I remember back in 2015, I was consulting for a mid-sized manufacturing plant in Ohio. They were transitioning between ERPs—which is corporate speak for “we have no idea where anything is.” The floor manager, Dave, was constantly screaming about missing widgets.
Dave didn’t trust computers; he trusted his clipboard. One afternoon, he stormed in claiming a critical vendor, let’s call them “Acme Parts,” had missed three deliveries in a row. He wanted to fire them.
I pulled up my humble Excel dashboard. Because I had disciplined myself to log the Dispatch Date vs Arrival Date, I filtered for Acme Parts in two seconds. The screen turned green. They hadn’t missed a shipment. The receiving dock had been misplacing the boxes because the labels were small.
We didn’t fire the vendor. We bought the receiving guy new glasses and a better scanner. That dashboard—built in 30 minutes—saved a ten-year supplier relationship. That is the power of owning your data.
Conclusion: Start Imperfectly
The biggest mistake procurement teams make is waiting for the “perfect” system. There is no perfect system. There is only the system you use.
Start with these three columns: PO, Vendor, Status. Build the Pivot Table. Add the Slicer. Suddenly, you aren’t reacting to the 4:59 PM panic; you’re emailing the production manager at 2:00 PM to tell him what’s arriving. That is control. That is peace of mind.
FAQs
1. Can I automate the email reminders from Excel?
Yes, but proceed with caution. You can use a simple VBA script to email vendors based on “Late” status, but I prefer using Conditional Formatting to highlight late rows and emailing personally. Automated emails from Excel often end up in spam folders.
2. How do I handle partial shipments?
Treat partials as splits. If PO #101 orders 100 units and 50 arrive, change the original line to 50 (Received) and add a new row for PO #101-B for the remaining 50 (Open). It keeps the math clean.
3. What if I have thousands of POs?
Excel starts to chug around 100,000 rows if you have too many formulas. If you are hitting that volume, look into Power Query (built into Excel) to manage the data import. It’s a game-changer for speed.
4. How do I protect the dashboard so others don’t break it?
Move the Data_Source and Dashboard_Calc tabs to “Very Hidden” in the VBA properties, or simply Protect the Sheet/Workbook structure with a password, leaving only the Slicers clickable.
5. Is Google Sheets better for this?
For collaboration, yes. If multiple people need to edit the status simultaneously, Sheets is superior. However, Excel’s Pivot Tables and Slicers are still more robust for the actual reporting and visualization aspect.
6. Do I really need a ‘Status’ column?
Absolutely. Without a status column that you manually update (or update via a formula based on dates), you are just looking at a list of history. The Status column is what turns a list into a management tool.
