How to Create a Procurement Dashboard in Google Sheets with ChatGPT

Building a procurement dashboard from scratch probably sounds like a heavy lift, but it’s more accessible than you think. Using the familiar interface of Google Sheets and the power of ChatGPT, you can create a clear, insightful dashboard to track your purchasing without needing any deep technical skills. This article will guide you through setting up your data, generating formulas with simple prompts, and visualizing your most important procurement metrics.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Google Sheets is a Great Choice for Your Dashboard

Before jumping into the "how," let's cover the "why." You might think you need expensive business intelligence software for a proper dashboard, but Google Sheets offers a compelling starting point for many businesses, especially smaller teams.

• It's Free and Accessible: Nearly everyone with a Google account has access to Google Sheets. There's no software to install and no subscription fee to worry about.
• Highly Collaborative: Sharing your dashboard with stakeholders, team members, or finance is as simple as sending a link. Multiple users can view or edit the dashboard in real-time.
• Flexible and Customizable: Unlike rigid in-app reporting, you have complete control over the layout, metrics, and visuals. You can build exactly what you need to see.

First, Outline Your Key Procurement Metrics

A dashboard is only as good as the data it displays. A focused dashboard that tracks the right handful of metrics is far more useful than a cluttered one that tracks everything. Start by defining what's most important for your business to monitor. Here are some of the most common and valuable procurement KPIs:

• Total Spend: The most fundamental metric. What is the total amount of money spent over a specific period?
• Spend by Supplier: Which vendors are you spending the most with? This can help identify opportunities for volume discounts or consolidation.
• Spend by Category: Understanding where your money is going (e.g., "Software," "Office Supplies," "Marketing Services") helps with budgeting and cost control.
• Purchase Order (PO) Count: The total number of POs issued can give you a sense of procurement activity volume.
• Average PO Value: Total spend divided by the number of POs. This helps you understand if you're processing many small orders or fewer large ones.
• On-Time Delivery Rate: A crucial metric for supplier performance. What percentage of orders are arriving by the promised date?
• Procurement Cycle Time: The average time it takes from creating a purchase request to the order being delivered. A shorter cycle time means a more efficient process.

Setting Up Your Data Foundation in Google Sheets

The secret to a stress-free dashboard is a well-organized data source. This is the most important step. Your goal is to create one "master tab" for all your raw procurement data. This tab will act as the database for your dashboard.

Create a new Google Sheet and name one tab "Procurement Data." The best practice is to structure your data so that each row represents a single line item on a purchase order.

Here’s a great starting structure for your columns:

• PO Number: The unique identifier for each purchase order.
• Order Date: When the order was placed.
• Supplier: The name of the vendor.
• Category: The purchasing category (e.g., Software, Hardware, Professional Services).
• Item Description: What you purchased.
• Unit Cost: The cost per individual item.
• Quantity: The number of units purchased.
• Total Cost: (Should be a formula: Unit Cost * Quantity)
• Promised Delivery Date: The date the supplier said the order would arrive.
• Actual Delivery Date: When the order actually arrived.
• Status: The current state of the order (e.g., Ordered, Shipped, Delivered, Canceled).

Manually populate this sheet with 15-20 rows of sample data. This will give you something to work with as you start building formulas and charts.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Using ChatGPT to Generate Your Formulas

Now for the fun part. Instead of searching for complex spreadsheet functions, you can ask ChatGPT to write them for you. You'll create a new tab in your Google Sheet and call it "Dashboard." This is where your summary metrics and charts will live.

The key to getting great results from ChatGPT is providing clear context. Always start your prompt by explaining your data structure.

Good Prompting Structure: "I have a Google Sheet with a tab named 'Procurement Data'. In that tab, column A is 'PO Number', column C is 'Supplier', column H is 'Total Cost', and column B is 'Order Date'. I need a formula to..."

Example 1: Calculating Total Spend

Let's start simple. You want a single number showing your total spend.

Your Prompt to ChatGPT: "I have a Google Sheet tab named 'Procurement Data' where all my purchases are logged. Column H contains the 'Total Cost' for each line item. I need a Google Sheets formula for my 'Dashboard' tab to calculate the sum of all values in the 'Total Cost' column."

ChatGPT will give you this formula:

=SUM('Procurement Data'!H:H)

On your "Dashboard" tab, create a label in one cell like "Total Spend" and paste this formula into the cell next to it. You now have your first KPI!

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Example 2: Analyzing Spend by Supplier

This is where things get more interesting. You want to create a small summary table showing your total spend for each supplier.

First, on your "Dashboard" tab, list your unique supplier names in a column (say, A2, A3, A4, etc.). Then, ask ChatGPT for the formula to calculate the spend for each one.

Your Prompt to ChatGPT: "In my 'Procurement Data' sheet, column C contains 'Supplier' names and column H contains 'Total Cost'. On my 'Dashboard' tab, I have a list of suppliers starting in cell A2. I need a formula for cell B2 that finds all the costs associated with the supplier name in A2 and sums them up. I want to be able to drag this formula down for my other suppliers."

ChatGPT will likely give you the SUMIF function:

=SUMIF('Procurement Data'!C:C, A2, 'Procurement Data'!H:H)

Paste this formula in cell B2, then drag the little blue fill-handle down. It will automatically calculate the total spend for each supplier in your list.

Example 3: Calculating Average Procurement Cycle Time

Efficiency metrics like cycle time are incredibly valuable. Let's get ChatGPT to write a formula for that.

Your Prompt to ChatGPT: "I need to calculate the average procurement cycle time. In my 'Procurement Data' sheet, 'Order Date' is in column B and 'Actual Delivery Date' is in column J. Give me a formula to calculate the average number of days between these two dates for all rows."

ChatGPT should provide a formula using ARRAYFORMULA:

=AVERAGE(ARRAYFORMULA('Procurement Data'!J2:J - 'Procurement Data'!B2:B))

This formula subtracts the order date from the delivery date for every single row and then calculates the average. Make sure your date columns are formatted as dates for this to work correctly!

Building Your Dashboard Visuals

Numbers are great, but visuals make data easy to understand at a glance. Google Sheets has simple but effective built-in charting tools that you can pair with your ChatGPT-generated summary tables.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Creating a "Spend by Supplier" Bar Chart

Use the summary table you created with the SUMIF formula. This is the perfect data for a bar chart.

1. Highlight your supplier names and their corresponding total spends (e.g., columns A and B).
2. Go to the menu and click Insert > Chart.
3. Google Sheets will usually default to a good chart type, but if not, use the Chart Editor on the right side of the screen to select a Bar Chart or Column Chart.
4. Use the "Customize" tab in the editor to change titles, colors, and fonts to make it look professional.

You can even ask ChatGPT for advice on which chart to use for your data.

Prompt for ChatGPT: "I have a table showing total spend broken down by supplier. What is the best chart type to visualize this to quickly see which suppliers get the most spend?"

It will correctly tell you that a bar chart is ideal for comparing values across categories.

Creating Other Essential Charts

You can repeat this process for your other KPIs:

• Spend by Category: Use a SUMIF formula (just like for suppliers) to create a summary table by category. Visualize this with a Pie Chart to show the proportion of spend each category represents.
• Spend Over Time: Create a summary table that sums up 'Total Cost' by month. (Hint: Use SUMIFS with a condition for the month). Visualize this with a Line Chart to see trends, seasonality, or sudden spikes in spending.

Organizing Your Dashboard

Now, arrange your KPIs and charts on the "Dashboard" tab. A common layout is to put your high-level KPIs (like Total Spend and Average Cycle Time) in scorecards at the top, with your visual charts down below. Drag and drop the charts and resize them until you have a clear, easy-to-read layout.

Final Thoughts

By connecting the simplicity of Google Sheets with the AI-driven power of ChatGPT, you can build a highly effective procurement dashboard without needing to learn complex software or functions. This approach breaks down the technical barriers, allowing you to focus on what the data is telling you about your purchasing habits, supplier relationships, and operational efficiency.

As you get more comfortable, you might find that the process of pulling data, asking AI to generate formulas, and building charts still takes up valuable time. We built Graphed to solve exactly this problem. Instead of working between a chat AI and a spreadsheet, you can connect your data sources directly to Graphed and simply ask, "create a dashboard showing total spend by supplier and spend by category for the last quarter." It builds the entire real-time, interactive dashboard for you in seconds, skipping all the manual steps completely.