How to Create an Inventory Dashboard in Google Sheets with AI
Manually tracking inventory in a spreadsheet can quickly become a time-consuming chore, but transforming that static list into an intelligent dashboard is easier than you think. By integrating AI directly into Google Sheets, you can automate insights, generate forecasts, and create a dynamic tool that helps you make smarter reordering decisions. This guide will walk you through the process step-by-step, from structuring your data to building a visual dashboard that works for you.
First Things First: Structure Your Inventory Data
Before you can build a dashboard or use AI, you need a clean, organized data source. A well-structured inventory list is the foundation of everything that follows. If your data is messy, your dashboard will be unreliable.
Create a dedicated Google Sheet with a tab named "Inventory Data." Set up columns for each key attribute of your products. At a minimum, you should include:
- SKU (Stock Keeping Unit): A unique identifier for each product variation.
- Product Name: A clear, descriptive name.
- Category: The product category (e.g., T-Shirts, Mugs, Accessories).
- Supplier: Who you order the product from.
- Unit Cost: The cost to acquire one unit of the product.
- Stock on Hand: The current quantity of the product you have in stock. This will be the number you update most frequently.
- Last Restock Date: The last time you received a shipment of this item.
For even more powerful analysis, consider adding these columns:
- Reorder Point: The minimum stock level that should trigger a reorder.
- Sales Velocity (Units/Day): The average number of units sold per day. You can calculate this based on historical sales data.
Consistency is your best friend here. Make sure your category names and supplier names are spelled the same way every time. "T-Shirts" and "Tshirts" will be treated as two different categories by formulas and charts, leading to inaccurate reports.
Calculating Core Inventory Metrics
With your data structured, you can start adding formulas to bring it to life. These calculated metrics will serve as the engine for your dashboard. Add these as new columns in your "Inventory Data" sheet.
1. Total Inventory Cost
This metric tells you the total value of the stock you hold for each product line. Create a new column called "Total Cost."
In the first cell of this column (e.g., J2), enter the following formula and drag it down for all your products:
=E2*F2
(This assumes your Unit Cost is in column E and your Stock on Hand is in column F).
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.
2. Stock Status
Automatically flagging items as "In Stock," "Low Stock," or "Out of Stock" is incredibly useful. This status helps you see at a glance what needs immediate attention.
Create a column called "Stock Status." Using a nested IF formula, you can set the logic based on your reorder points. If your Reorder Point is in column H:
=IF(F2=0, "Out of Stock", IF(F2<=H2, "Low Stock", "In Stock"))
This formula checks two conditions:
- If the stock (F2) is zero, it labels the item "Out of Stock."
- If not, it then checks if the stock is less than or equal to the reorder point (H2) and labels it "Low Stock."
- If neither is true, the item is labeled "In Stock."
3. Days of Supply
This tells you how many days your current inventory will last based on your average sales velocity. It’s one of the best indicators for prioritizing reorders.
Create a new column titled "Days of Supply." The formula is simple: divide your stock quantity by your daily sales velocity. If Sales Velocity is in column I, use:
=IF(I2>0, F2/I2, 0)
The extra IF statement prevents a #DIV/0! error for items with zero daily sales.
With these foundational metrics calculated, your data sheet is now primed for AI analysis and dashboard visualization.
Bringing AI into Your Spreadsheet: GPT for Sheets
This is where the magic happens. You can use widely available Google Sheets add-ons to run AI prompts directly on your inventory data. The most popular option is "GPT for Sheets and Docs," which connects to an OpenAI API key.
Installing the Add-on
- In Google Sheets, navigate to Extensions > Add-ons > Get add-ons.
- Search for "GPT for Sheets and Docs" and click install.
- Once installed, you'll need to link it to your OpenAI account by providing an API key. The add-on contains instructions on how to get one.
With the add-on enabled, you gain access to new functions, most notably the versatile =GPT() function, which lets you ask questions about your data.
Using AI for Automated Insights
Now, you can create new columns to generate AI-powered insights for each product.
Example 1: Generating Product Reorder Recommendations
Instead of just seeing a "Low Stock" flag, what if the AI could give you a human-readable recommendation? Create a column called "AI Recommendation."
=GPT("Given "&F2&" units in stock with daily sales of "&I2&", create a one-sentence reorder recommendation.")
The AI will use the context you provided to generate dynamic advice for each row, such as:
- "With only 15 units left and a high sales velocity, an immediate reorder is highly recommended to avoid a stockout."
- "Stock levels are healthy, continue monitoring sales trends before placing a new purchase order."
- "This item is selling slowly, consider holding off on reordering to manage cash flow effectively."
Example 2: Identifying Stock Health
You can use AI to classify products beyond a simple stock status. Create a column called "Stock Health Class."
=GPT("Based on a sales velocity of "&I2&" per day and "&L2&" days of supply remaining, classify this product as 'Fast-Moving', 'Healthy', 'Slow-Moving', or 'At Risk'.")
This allows the AI to consider multiple data points to give you a more nuanced understanding of which products are tying up capital ("Slow-Moving") versus which products are driving your business ("Fast-Moving").
From Data to Dashboard: Visualizing Your Inventory
With your data enriched by both formulas and AI, it's time to build the visual dashboard. Create a new tab in your spreadsheet and call it "Dashboard." This is where you'll summarize your information using charts and scorecards.
Key Performance Indicators (KPIs)
Start with a high-level overview of your business. Use simple formulas to create KPI boxes at the top of your dashboard. Dedicate a cell for the title (e.g., "Total Inventory Value") and the cell next to it for the formula.
- Total Inventory Value:
=SUM('Inventory Data'!J:J) - Total Number of SKUs:
=COUNTUNIQUE('Inventory Data'!A:A) - Low Stock Items:
=COUNTIF('Inventory Data'!K:K, "Low Stock") - Out of Stock Items:
=COUNTIF('Inventory Data'!K:K, "Out of Stock")
Visual Charts and Graphs
Charts put your data into context and help you spot trends instantly.
1. Inventory Value by Category
This chart shows you which product categories hold the most value in your inventory.
Select your "Category" and "Total Cost" columns in your data sheet. Go to Insert > Chart. Google Sheets will likely recommend a Pie Chart, which is perfect for this purpose.
2. Top 10 Products by Stock Value
Knowing which specific items have the most capital invested in them is critical. Use the QUERY function in a section of your dashboard tab to pull this data:
=QUERY('Inventory Data'!A:J, "SELECT B, J ORDER BY J DESC LIMIT 10")
This formula pulls the Top 10 products (Column B, Product Name) by their Total Cost (Column J). Once you have this small table, select it and insert a horizontal Bar Chart for a clean and simple visualization.
3. Actionable Reorder List
This is arguably the most valuable part of your dashboard. Create a live list of every item that is flagged as "Low Stock" or "Out of Stock." In your dashboard tab, use the FILTER function:
=FILTER('Inventory Data'!A2:L, 'Inventory Data'!K2:K="Low Stock")
This will create a dynamic table showing every product that needs attention, along with its current stock level, sales velocity, and even the AI recommendation. This becomes your daily to-do list for inventory management.
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.
Bonus: AI-Powered Demand Forecasting
If you have historical sales data (e.g., weekly sales over the last few months), you can take it a step further and use AI to forecast future demand.
Add your historical sales data (e.g., Sales_Wk1, Sales_Wk2, etc.) to your data sheet. Then, in a new cell on your dashboard, create a simple forecast for a specific product using a GPT prompt like:
=GPT("Given the last 8 weeks of sales for product [Product Name] were [15, 18, 22, 20, 25, 23, 28, 30], forecast the next 4 weeks of sales.")
While not a substitute for advanced statistical modeling, this gives you a quick, AI-driven estimate to help inform your purchasing decisions, moving you from reacting to past sales to proactively planning for future demand.
Final Thoughts
By connecting a structured data set with the power of built-in formulas and AI add-ons, you can elevate your Google Sheet from a simple list into a powerful inventory management dashboard. It automates calculations, delivers actionable insights in plain English, and puts the most important information right at your fingertips.
Creating dashboards inside a spreadsheet is great, but the true challenge for many teams is gathering and consolidating real-time data from platforms like Shopify, Google Analytics, or Salesforce into that single sheet. At Graphed, we eliminate that manual work by connecting directly to your key data sources. Using simple conversation, you can ask Graphed to build real-time dashboards instantly, freeing you to focus on strategy instead of spending your day exporting and wrangling CSVs.
Related Articles
How to Create Facebook Ads API for Claude Code
Create a Meta Marketing API access token that never expires, using a system user, so Claude Code can pull Facebook ad reports and manage campaigns.
ClickHouse vs Postgres: When to Switch to Columnar
Comparing ClickHouse vs PostgreSQL means matching storage architecture to workload. Row storage wins for transactions. Columnar wins for scan-heavy analytics.
Google Ads for Orthodontists: Get More Booked Consults
Most orthodontic Google Ads campaigns waste money on low-intent searches. The problem is structure, not the platform.