Graphed LogoGraphed
Login

How to Create a Procurement Dashboard in Google Sheets

Cody Schneider•

Tracking company spending can feel like trying to nail jelly to a wall, but a good procurement dashboard brings everything into focus. It transforms scattered purchase orders and vendor invoices into a clear, visual command center. This guide will walk you through building a dynamic, shareable procurement dashboard right inside Google Sheets, step by step.

What Exactly is a Procurement Dashboard?

Think of a procurement dashboard as a one-page summary of your company's entire purchasing process. Instead of digging through endless rows of data, you get a high-level view of the most important metrics at a glance. It helps you answer critical questions instantly:

  • How much are we spending this month?

  • Which departments or categories account for the most spend?

  • Are our vendors delivering on time?

  • Where are the bottlenecks in our approval process?

Using Google Sheets for this is a great starting point. It's free, cloud-based for easy collaboration with your team, and powerful enough to handle sophisticated tracking and analysis without needing a team of data scientists.

Step 1: Identify Your Core Procurement Metrics (KPIs)

Before you build anything, you need to decide what to measure. Creating a dashboard with dozens of metrics is a recipe for a cluttered, confusing screen. Start by picking 3-5 Key Performance Indicators (KPIs) that directly impact your business goals. Everything else is just noise.

Here are some of the most common and valuable procurement KPIs to consider:

Purchase Order (PO) Cycle Time

What it measures: The average time it takes from creating a purchase request to the final delivery of goods or services.

Why it matters: A long cycle time can signal inefficiencies in your approval workflow or issues with supplier response time, leading to project delays.

Spend Under Management

What it measures: The percentage of your company’s total spending that is actively managed by the procurement department through approved channels.

Why it matters: Higher spend under management means better control, more opportunities for cost savings, and less "maverick spend" (uncontrolled purchases).

Supplier Performance Metrics

  • On-Time Delivery Rate: The percentage of orders that are delivered on or before the agreed-upon date.

  • Supplier Defect Rate: The percentage of products received that are defective or don't meet quality standards.

  • Purchase Price Variance (PPV): The difference between the standard price of an item and the actual price paid.

Why they matter: These metrics help you evaluate which suppliers are reliable partners and which are causing problems, allowing you to make smarter vendor-sourcing decisions.

Cost Savings and Avoidance

What it measures: The tangible financial value your procurement efforts bring, either through negotiating better prices (cost savings) or avoiding future costs (cost avoidance).

Why it matters: This is the ultimate proof of procurement's value to the company’s bottom line.

Step 2: Collect and Structure Your Data

Your dashboard is only as good as the data it's built on. The key is organizing your information in a simple, standardized format that a spreadsheet can understand. Create a "Raw Data" tab in your Google Sheet and set it up as a flat-file database. This means each row is a single record (like one line item on a PO), and each column is a specific attribute.

Here’s a sample structure for your "Raw Data" sheet:

  • A: PO Number

  • B: Requisition Date

  • C: Approval Date

  • D: Vendor

  • E: Category (e.g., Software, Office Supplies, Marketing)

  • F: Item Description

  • G: Unit Price

  • H: Quantity

  • I: Total Cost (=G2*H2)

  • J: Status (e.g., Pending, Approved, Shipped, Delivered)

  • K: Expected Delivery Date

  • L: Actual Delivery Date

Pro Tip: Use Google Sheets' Data Validation feature (under the Data menu) to create dropdown menus for columns like "Vendor," "Category," and "Status." This prevents typos and ensures your data stays clean and consistent, which is a lifesaver for accurate calculations later.

Step 3: Crunch the Numbers with Formulas

Now, let's turn that raw data into meaningful insights. It's best practice to create a separate tab, let’s call it "Calculations," for all your formulas. This keeps your dashboard clean and prevents anyone from accidentally breaking a calculation.

Here are some examples of formulas you can use in your "Calculations" tab, based on our sample data structure.

Summarizing Spend

To calculate total spend, you just need a simple SUM of your "Total Cost" column:

=SUM('Raw Data'!I:I)

To find the total spend by a specific vendor (e.g., "WebWizards Inc."), use SUMIF:

=SUMIF('Raw Data'!D:D, "WebWizards Inc.", 'Raw Data'!I:I)

For more advanced summaries, like Spend by Category, the QUERY function is your best friend. It’s like running a mini-SQL query inside your sheet:

=QUERY('Raw Data'!E:I, "SELECT E, SUM(I) GROUP BY E LABEL SUM(I) 'Total Spend'")

This single formula will generate a neat two-column table showing each category and the total spend for it.

Tracking Purchase Orders

To count the number of POs with a specific status, like "Delivered," use COUNTIF:

=COUNTIF('Raw Data'!J:J, "Delivered")

Calculating Cycle Time and On-Time Delivery

First, calculate the cycle time for each individual order. In your "Raw Data" tab, create a new column named "Cycle Time (Days)". Assuming your requisition date is in column B and your actual delivery date is in column L, use this formula:

=IF(L2="", "", NETWORKDAYS(B2,L2))

The IF statement ensures it only calculates if a delivery date exists. The NETWORKDAYS function cleverly calculates only the working days between two dates, ignoring weekends and holidays (which you can specify). Drag this formula down for all your rows.

Then, back in your "Calculations" tab, you can find the average cycle time for all delivered orders:

=AVERAGEIFS('Raw Data'!M:M, 'Raw Data'!J:J, "Delivered")

(Assuming Cycle Time is in column M now).

To get your On-Time Delivery Rate, you can count how many deliveries were on or before the expected date and divide. Create another column in your "Raw Data" tab called "On Time?", assuming Expected Date is in column K and Actual Delivery is in column L:

=IF(L2 > K2, "No", "Yes")

Then, use this formula in your "Calculations" tab to get the percentage:

=COUNTIF('Raw Data'!N:N, "Yes") / COUNTA('Raw Data'!N:N)

Don't forget to format this cell as a percentage!

Step 4: Visualize Your Data with Charts

This is the fun part! Create a new tab and name it "Dashboard." This is where you’ll assemble your charts and graphs. The goal is to make the information intuitive and easy to digest in seconds.

From the "Dashboard" tab, go to Insert > Chart. The chart editor will open, where you can select the chart type and the data range (pointing it to your "Calculations" tab).

Choosing the Right Chart for Each Metric

  • Scorecards: Use these for your big, headline numbers like Total Spend or Average PO Cycle Time. They are perfect for at-a-glance KPIs.

  • Pie or Donut Charts: Excellent for showing proportions, like Spend by Category or Spend by Vendor. They instantly show you the biggest slices of the pie.

  • Bar or Column Charts: Ideal for comparing values across different suppliers, such as On-Time Delivery Rate by Vendor or Average Spend by Department.

  • Timeline or Line Charts: Use these to track trends over time, like plotting Monthly Spend to spot seasonal changes or growth.

Once you've created your charts, arrange them logically on your dashboard tabsheet. A common layout places the main KPIs in scorecards at the top, followed by more detailed charts below.

Add Interactive Filters with Slicers

To make your dashboard interactive, add Slicers. Go to Data > Add a Slicer. You can add a slicer for "Vendor," "Category," or even a time period represented as a column.

Now, when you select a specific vendor from the slicer dropdown, all your dashboard charts will automatically update to show data only for that vendor. This empowers your team to dig into the data and answer their own follow-up questions without having to edit formulas.

Final Thoughts

Building a procurement dashboard in Google Sheets gives you a powerful, real-time view of your spending, supplier performance, and operational efficiency. By defining your KPIs, structuring your data logically, and using the right formulas and charts, you can transform a simple spreadsheet into an indispensable tool for making smarter, more cost-effective purchasing decisions.

Of course, as your company grows, manually updating data in a spreadsheet and managing all those formulas can become a significant time-sink. We built Graphed to solve exactly this challenge. It connects directly to your cloud applications (like QuickBooks, Shopify, Salesforce, and even Google Sheets) to stream data automatically. Instead of writing formulas, you can just ask questions in plain English - like "Show me a chart of our top 10 vendors by spend this quarter" - and the dashboard builds itself in seconds, giving you back valuable time to focus on strategy instead of just data wrangling.