How to Create an Inventory Dashboard in Google Sheets

Tracking inventory in Google Sheets is one of the most practical ways to manage your stock, but staring at rows of data doesn't give you the full picture. A well-designed inventory dashboard transforms that raw data into clear, actionable insights at a glance. This guide will walk you through, step by step, how to build a dynamic inventory dashboard in Google Sheets, from structuring your data to visualizing your most important 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

First, Why Use Google Sheets for an Inventory Dashboard?

Before we build, it's worth knowing why Google Sheets is such a great tool for this job, especially for small to medium-sized businesses. It’s free, cloud-based, and fantastic for collaboration. You can access and update your inventory from any device, share it with your team, and customize it to fit your exact needs without paying for expensive, specialized software.

Step 1: Create a Master Inventory List

A great dashboard is built on a foundation of clean, organized data. The first and most crucial step is to create a dedicated tab for all your raw inventory information. This will be the "single source of truth" that powers your entire dashboard. Don't worry about making this sheet look pretty - its purpose is pure data storage.

Create a sheet in your Google Sheets file and name it something like "Inventory Data". Then, set up columns for all the essential details about your products. Keep your headers simple and your data consistent.

Essential Columns for Your Inventory Data Sheet:

• SKU / Product ID: A unique identifier for each product. This is non-negotiable for accurate tracking.
• Product Name: The common name of the product.
• Category: Group similar products together (e.g., "T-Shirts," "Mugs," "Accessories"). This will be very useful for filtering and reporting later.
• Supplier: Who you buy the product from.
• Cost Price: The cost per unit you pay.
• Sale Price: The price you sell the product for.
• Quantity in Stock: The current number of units you have on hand. This is the column you will update most often.
• Reorder Level: The minimum quantity you want in stock. When the current quantity hits this number, it's time to reorder.
• Stock Status: An automated label like "In Stock," "Low Stock," or "Out of Stock," calculated with a formula.
• Total Value (Cost): An automated calculation showing the total value of your stock for a given product (Quantity in Stock * Cost Price).

Your data sheet should look clean and structured, something like this:

Pro Tip: Do not merge cells in your data sheet! Merged cells can cause major headaches with formulas, sorting, and filtering.

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

Step 2: Automate Key Metrics with Formulas

To make your inventory system truly dynamic, you'll want to automate as much as you can. We'll add a couple of formulas to our "Inventory Data" sheet that automatically update the "Stock Status" and "Total Value" columns.

Calculating Stock Status

The Stock Status column gives you an immediate visual cue about which items need attention. Let’s assume Quantity is in column G and Reorder Level is in column H. In the first cell of your Stock Status column (let's say I2), you can use an IF formula:

=IF(G2=0, "Out of Stock", IF(G2<=H2, "Low Stock", "In Stock"))

Here’s how this formula works:

• IF(G2=0, "Out of Stock", ...) checks if the quantity is exactly zero. If true, it labels the item "Out of Stock."
• If it's not zero, it moves to the next part: IF(G2<=H2, "Low Stock", "In Stock"). This checks if the quantity is less than or equal to the reorder level. If true, it’s labeled "Low Stock."
• If neither of the first two conditions is met, the item must be sufficiently stocked, so it's labeled "In Stock."

Once you've entered the formula, click the small blue square in the corner of the cell and drag it down to apply the formula to the entire column. Now your stock status updates automatically whenever you change the quantity!

Calculating Total Stock Value

Knowing the value of your inventory is critical for financial planning. To calculate this, you simply multiply the cost price by the quantity on hand. Assuming Cost Price is in column E and Quantity is in column G, the formula in your "Total Value" column (let's say J2) would be:

=E2*G2

Just like before, drag this formula down to apply it to all your products. Now you have a clean, automated dataset ready to power your dashboard.

Step 3: Build Your Google Sheets Dashboard Tab

This is where the magic happens. Create a new sheet and name it "Dashboard". This sheet will be your command center, pulling summary data from your "Inventory Data" tab and presenting it in an easy-to-read format.

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

Key Performance Indicators (KPIs) at a Glance

The top of your dashboard should feature the most important, high-level numbers. These are the KPIs you want to see instantly without having to scan a table. Let’s create a small section for these.

• Total Unique Products (SKUs): Counts how many different products you stock.

=COUNTA('Inventory Data'!A2:A)

• Total Units in Stock: Sums up the quantity of all your products.

=SUM('Inventory Data'!G2:G)

• Total Inventory Value (Cost): Sums up the total cost value of all your inventory.

=SUM('Inventory Data'!J2:J)

• Number of Items to Reorder: Counts how many products have a "Low Stock" or "Out of Stock" status.

=COUNTIF('Inventory Data'!I2:I, "Low Stock")

Arrange these on your dashboard with clear labels. Format the numbers to be large and bold. Seeing "5 Items to Reorder" pop out in red is much more effective than finding those 5 items in a list of 500.

Step 4: Create Charts and Visualizations

Numbers are great, but visuals tell a story. Let's add some charts to your dashboard to help you spot trends and understand your inventory composition.

Inventory Value by Category (Pie Chart)

A pie chart is a great way to see how your inventory value is distributed across different product categories. Is most of your cash tied up in T-Shirts or Accessories? To do this, we'll first need a pivot table.

1. Go to your "Inventory Data" sheet and select all your data.
2. Go to Insert > Pivot table. Choose to place it in a new sheet or an existing one (you can create a separate "ChartsData" sheet to keep things tidy).
3. In the Pivot table editor:
4. You now have a clean table summarizing the total stock value for each category. Select this summary data and go to Insert > Chart. Google Sheets will likely recommend a Pie Chart, which is perfect for this.
5. Customize the colors and title, then copy and paste this chart onto your "Dashboard" sheet.

Top 10 Most Stocked Products (Bar Chart)

What are your highest-volume products? You can create a dynamic list and a corresponding bar chart using the QUERY function. The QUERY function is one of the most powerful in Google Sheets, letting you use SQL-like commands to manipulate data.

Find an empty spot on your dashboard and enter this formula (adjust column letters as needed):

=QUERY('Inventory Data'!A:G, "SELECT B, G ORDER BY G DESC LIMIT 10", 1)

Here’s what this does:

• 'Inventory Data'!A:G refers to the dataset you want to look at.
• "SELECT B, G ... " tells it to grab the Product Name (column B) and the Quantity (column G).
• "... ORDER BY G DESC" sorts the results by Quantity in descending order (highest to lowest).
• "... LIMIT 10" restricts the output to only the top 10 results.
• 1 at the end indicates that our data has one header row.

This will generate a two-column list of your top 10 most stocked products. You can now select this list and insert a Bar Chart to create a simple, effective visualization.

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

Actionable List: Items to Reorder

A dashboard should drive action. The best way to do that is to create a dynamic list that shows exactly which products need to be reordered right now. You can do this with the FILTER function.

=FILTER('Inventory Data'!A:H, 'Inventory Data'!I:I="Low Stock")

This formula pulls all columns from A to H for any row where the Stock Status (column I) is "Low Stock". This creates an automatically updating to-do list for your purchasing manager right on the dashboard.

Step 5: Add Interactivity and Polish

A static dashboard is good, but an interactive one is even better. You can add "Slicers" to your dashboard to let users filter all the charts and data tables by certain criteria, like Category or Supplier.

1. Click on one of your pivot tables or charts.
2. Go to Data > Add a slicer.
3. A slicer filter box will appear. In the sidebar, choose the column you want to filter by, for example, "Category."
4. Move the slicer box to a convenient spot on your dashboard.

Now, when you select "T-Shirts" from the slicer dropdown, your charts and pivot-table-driven data will automatically update to show information for only T-Shirts. This is incredibly powerful for drilling down into specific segments of your inventory.

Finally, spend a few minutes on formatting. Use a consistent color scheme, freeze the top row so your headers are always visible, and align your elements on a grid to give your dashboard a clean, professional look.

Final Thoughts

Structuring your data properly and using a combination of formulas and charts allows you to build a powerful and dynamic Inventory Dashboard in Google Sheets. This single screen gives you a real-time, bird's-eye view of your business, turning raw numbers into an indispensable decision-making tool.

While Google Sheets is fantastic for building custom dashboards, it often requires manual setup and can be time-consuming to connect with live data from sales platforms like Shopify or CRM data from Salesforce. This is exactly why we built Graphed. We connect directly to all your data sources, allowing you to create live, automated dashboards using simple, natural language. Instead of wrangling pivot tables, you can just ask, "Show me my top 10 products by sales from Shopify and compare their current stock levels," and get an instant, real-time dashboard in seconds.