How to Create a Procurement Dashboard in Excel with ChatGPT
Creating a functional procurement dashboard in Excel used to mean wrestling with complex VLOOKUPs, nested IF statements, and tangled Pivot Tables. Today, you can build a powerful, custom dashboard to monitor your purchasing activities by simply telling ChatGPT what you need. This guide will walk you through the process of using ChatGPT as your co-pilot to set up your data, generate formulas, and build insightful visualizations in Excel.
What Is a Procurement Dashboard?
A procurement dashboard is a visual report that consolidates and displays your most important purchasing metrics in one easy-to-understand location. Instead of digging through endless spreadsheets, a dashboard gives you a high-level overview of your procurement health, helping you spot trends, identify cost-saving opportunities, and pinpoint potential bottlenecks in your supply chain.
A good dashboard empowers you to make smarter, data-driven decisions about supplier management, contract negotiations, and budget allocation.
Key Procurement Metrics to Track
Before you build anything, you need to know what you want to measure. While the exact KPIs will vary based on your business goals, here are some of the most common and valuable metrics for procurement teams:
Cost Savings: The total amount of money saved through negotiations, discounts, or strategic sourcing compared to a baseline or budget.
Total Spend: The overall expenditure, often broken down by supplier, category, or department.
Purchase Order (PO) Cycle Time: The average time it takes from when a purchase order is created to when the goods or services are received. A long cycle time can indicate inefficiencies.
Supplier Defect Rate: The percentage of orders from a specific supplier that contain defects or fail to meet quality standards. This is critical for evaluating supplier performance.
Spend Under Management: The percentage of your company's total spend that is being actively managed by the procurement department. A higher percentage indicates better control over company spending.
Number of Suppliers: Tracking this metric helps you understand if you are consolidating spend with key partners or if your supplier base is too fragmented.
Step 1: Prepare and Structure Your Procurement Data in Excel
Your dashboard is only as good as the data powering it. Before you can build anything, you need a clean, organized dataset. The best practice is to have all your raw data in a single table on one worksheet, with each row representing a transaction and each column representing an attribute.
For ChatGPT to generate accurate formulas, your data needs to be structured logically. Let's create a hypothetical dataset on a sheet we'll name "Data".
Organize your columns like this:
PO Number: A unique identifier for each purchase order.
Supplier Name: The name of the vendor.
Category: The purchasing category (e.g., Office Supplies, IT Hardware, Marketing Services).
Item Description: A brief description of the purchased item.
Department: The internal department that made the request.
Order Date: The date the PO was created.
Received Date: The date the items were received.
Status: The status of the order (e.g., Completed, Pending, Defective).
Unit Cost: The cost per unit.
Quantity: The number of units purchased.
Total Cost: The total cost of the order (Unit Cost * Quantity).
Format Your Data as an Excel Table
Once you have your data organized, the single most important formatting step is to turn it into an official Excel Table. This makes your formulas and visualizations dynamic, so they automatically update when you add new data.
Click on any cell within your data range.
Go to the Insert tab on the Ribbon and click Table.
Excel will automatically detect your data range. Make sure the "My table has headers" box is checked.
Click OK.
Go to the Table Design tab that now appears, and in the "Table Name" box on the left, rename your table to something meaningful, like "ProcurementData". This makes your ChatGPT prompts much easier to write.
Your table will now be formatted and ready for analysis.
Step 2: Use ChatGPT to Generate Excel Formulas
This is where the magic happens. Instead of googling "how to sum if multiple criteria excel," you can just describe your goal to ChatGPT in plain English. For the best results, structure your prompts to include three key pieces of information:
Context: Tell it you're working in Excel and mention your Table Name ("ProcurementData").
Data: Specify the exact column names you want to use in the calculation.
Goal: Clearly state what you want to calculate (e.g., total cost, average time, count of items).
Example 1: Calculating Total Spend by Category
Let's say you want to calculate the total spend for the "IT Hardware" category. On a new worksheet named "Dashboard", you can write your label in one cell, and ask ChatGPT for the formula to go in the cell next to it.
Your Prompt to ChatGPT:
"I have an Excel Table named 'ProcurementData'. The table includes a 'Total Cost' column and a 'Category' column. Give me the Excel formula to calculate the sum of 'Total Cost' for all rows where the 'Category' is 'IT Hardware'."
ChatGPT's Formula Output:
=SUMIFS(ProcurementData[Total Cost], ProcurementData[Category], "IT Hardware")
Simply copy this formula from ChatGPT and paste it into the desired cell on your dashboard. It works instantly and is much easier to read than traditional range-based formulas like =SUMIFS(Data!$K:$K, Data!$C:$C, "IT Hardware") as it uses a structured Excel Table.
Example 2: Calculating Average PO Cycle Time
Average PO Cycle time is a great KPI for measuring efficiency. Let's calculate the average number of days between the order date and the received date.
Your Prompt to ChatGPT:
"In my Excel Table 'ProcurementData', I have an 'Order Date' column and a 'Received Date' column. Write the Excel formula to calculate the average difference in days between these two dates across all rows."
ChatGPT's Formula Output:
=AVERAGE(ProcurementData[Received Date] - ProcurementData[Order Date])
Paste this into your dashboard. If the result appears as a date, simply right-click the cell, select "Format Cells," and choose "Number" to display the average number of days.
Step 3: Creating Your Dashboard Layout and KPI Cards
With your formulas in hand, it's time to design your dashboard. The best dashboards are simple and scannable. A common layout is to place high-level KPIs in "cards" at the top, followed by more detailed charts and tables below.
A KPI card is just a cell containing your calculated metric, styled to stand out. Here's how to create them:
Set up Labels: On your "Dashboard" sheet, create a small grid. In a cell, type a label like "Total Spend" or "Avg. PO Cycle Time."
Paste the Formula: In the cell next to or below the label, paste the formula you generated with ChatGPT.
Style the Card:
Select the cell with the formula and increase the font size significantly.
Center the text and change the font color if you wish.
Give the cell a background fill color and add a border to make it look like a physical card.
Continue this process for all of your top-level KPIs, like Supplier Defect Rate, Total POs, and Spend Under Management, creating a row of clean, easy-to-read cards at the top of your dashboard.
Step 4: Building Charts and Visualizations With ChatGPT's Guidance
While ChatGPT can't click the buttons in Excel for you, it can give you the perfect instructions for creating the summary data needed for a chart which is an absolute breeze if you use pivot tables too. This is often the trickiest part, but with AI guidance, it's straightforward.
Tutorial: Creating a 'Spend by Supplier' Bar Chart
A bar chart is perfect for comparing spend across different suppliers to see who your top vendors are.
Step 1: Ask ChatGPT how to prepare the data.
Your Prompt to ChatGPT:
"I want to create a bar chart in Excel showing total spend by supplier. My data is in an Excel Table named 'ProcurementData' with columns 'Supplier Name' and 'Total Cost'. What's the easiest way to create a summary table for this chart?"
ChatGPT's Recommended Steps:
ChatGPT will likely suggest using a PivotTable, which is the ideal tool for this job. It will give you instructions similar to these:
Select any cell inside your
ProcurementDatatable.Go to the Insert tab and click PivotTable.
In the PivotTable dialog box, choose "New Worksheet" to keep things clean, and click OK.
The PivotTable Fields pane will appear on the right. Drag the Supplier Name field into the Rows area.
Drag the Total Cost field into the Values area. It should default to "Sum of Total Cost."
You now have a perfectly summarized table of total spend for each supplier.
Step 2: Create the chart.
Click anywhere inside your new PivotTable.
Go to the Insert tab and choose a chart type. A 2D Bar Chart works well here.
Excel will automatically create a chart based on your PivotTable data. Give it a title like "Total Spend by Supplier" and customize the colors as needed.
Step 5: Making Your Dashboard Interactive with Slicers
Slicers are user-friendly buttons that let you filter your dashboard with just a click. They are incredibly easy to add to any chart based on a PivotTable.
Click on the 'Spend by Supplier' chart you just created.
Go to the PivotChart Analyze tab on the Ribbon.
Click Insert Slicer.
A dialog box will pop up with all of your data columns. Check the boxes for the fields you want to filter by, such as Category and Department. Click OK.
You now have interactive buttons for 'Category' and 'Department' on your dashboard. Clicking "IT Hardware" on the category slicer will instantly update your chart to show spend by supplier only for that category. This turns a static report into a dynamic analysis tool.
Final Thoughts
Combining the organizational power of an Excel Table with the intelligence of ChatGPT allows you to move past the tedious mechanics of building reports and focus on what the data is telling you. By providing clear prompts, you can offload formula creation and data structuring, building a powerful, interactive procurement dashboard faster than ever before.
Even with AI speeding things up, setting up and maintaining reports in Excel can still require a fair amount of manual work - especially when you’re pulling data from multiple sources. We designed Graphed to remove this friction entirely. Instead of preparing tables and prompting for formulas, you connect your data sources and simply ask for a dashboard, like "Create a report showing our top 10 suppliers by spend this quarter compared to last," and the dashboard is built for you in seconds with live, always-up-to-date information.