How to Create an Inventory Dashboard in Excel

Tired of guessing what's in stock or scrambling to fulfill orders because you thought you had more items on the shelf? It's a common headache, but you don't need complex, expensive software to solve it. This guide will walk you through creating a simple but powerful inventory dashboard directly in Excel, step-by-step. We'll cover everything from structuring your data correctly to building interactive charts that give you an instant, up-to-date overview of your stock levels and value.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why an Excel Dashboard is Your Inventory's Best Friend

Before we jump into the "how," let's quickly cover the "why." A static list of products and quantities is just data. A dashboard turns that data into actionable information. Think of it as a cockpit for your business, letting you see the most important information at a single glance. With a well-built inventory dashboard, you can:

• Prevent Stockouts: Instantly see which items are running low and need to be reordered, preventing lost sales and unhappy customers.
• Reduce Overstock: Identify slow-moving products that are tying up your cash and storage space.
• Understand Your Business DNA: Quickly see your best-selling items, most valuable product categories, and overall inventory value.
• Save Time: Stop manually digging through spreadsheets every time you need an answer. One look at the dashboard gives you the clear, current picture.

Step 1: Get Your Raw Inventory Data in Order

Your dashboard will only be as good as the data that powers it. The most common mistake people make is not structuring their raw data properly. A clean, organized data source is the foundation for everything that follows.

The Golden Rule: Keep it Clean and Tabular

Your data needs to live in a simple "tabular" format. This means each row is a unique item, and each column is a specific attribute of that item. Resist the urge to merge cells, add decorative colors, or put empty rows between products in your data sheet. Keep it clean and machine-readable.

At a minimum, your inventory data table should include these columns:

• SKU or Product ID: A unique identifier for each product. This is essential.
• Product Name: A clear, descriptive name.
• Category: Helps you group products (e.g., "T-Shirts," "Mugs," "Accessories").
• Cost Per Item: What it costs you to purchase one unit.
• Price Per Item: The price you sell one unit for.
• Quantity on Hand: The current number of units you have in stock.
• Reorder Level: The stock quantity at which you need to place a new order.
• Supplier: Who you purchase the item from.

You can also add other descriptive columns like Location, Date of Last Order, etc. The more useful data you have, the richer your analysis can be.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Transforming Your Data into an Excel Super-Table

Once you have your columns and data set up, the most important step in this entire process is to format it as an official Excel Table. This makes your data dynamic, meaning your dashboard will update automatically as you add or remove inventory.

1. Click on any cell within your data range.
2. Go to the Insert tab in the Excel ribbon and click Table.
3. A small box will pop up confirming the range of your data. Make sure it's correct and that the "My table has headers" box is checked.
4. Click OK.

Your data will now be formatted with alternating colors. More importantly, it is now a dynamic object. When you add a new product in the row directly below your table, it will automatically be included in the table's range - and therefore, in your dashboard's calculations!

Step 2: Define and Calculate Your Key Metrics

Now, let's start building the dashboard itself. Create a new sheet in your workbook and name it "Dashboard." This keeps your presentation layer separate from your raw data, which is a best practice.

Before creating charts, it's useful to calculate a few high-level Key Performance Indicators (KPIs) that give you a quick summary. We can create these KPI "cards" at the top of our dashboard.

Here are a few essential inventory metrics and the formulas to calculate them:

• Total Units in Stock: This tells you the total number of items you have on your shelves. In a cell on your Dashboard sheet, enter the formula:

=SUM(InventoryData[Quantity on Hand])

(Assuming your Excel Table is named "InventoryData." Excel often names it "Table1" by default, so adjust as needed.)

• Total Inventory Value: This shows you how much cash is tied up in stock. First, go back to your data sheet and add a new column to your table called "Stock Value." Use this formula:

=[@[Cost Per Item]]*[@[Quantity on Hand]]

Excel will automatically fill this formula down the entire column. Now, on your dashboard sheet, you can calculate the total value:

=SUM(InventoryData[Stock Value])

• Items Needing Reorder: This one is crucial. It counts how many products have fallen below their reorder threshold. The formula uses COUNTIFS to count rows that meet our criteria:

=COUNTIFS(InventoryData[Quantity on Hand], "<="&InventoryData[Reorder Level])

Place these formulas in separate cells on your dashboard sheet and give them clear labels. These numbers will form the basis of our KPI cards.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Bringing Your Inventory to Life with Visuals

Time for the fun part: visualizing the data. Charts make it easy to spot trends and identify outliers in seconds. We'll use PivotCharts, as they are incredibly powerful for summarizing data directly from our source table.

Chart 1: Inventory Value by Category (Donut Chart)

This chart helps you see where most of your money is invested. Is it tied up in T-Shirts or Hoodies?

1. Go back to your data sheet and click anywhere inside your inventory Table.
2. Go to the Insert tab and click PivotChart.
3. Excel will ask where to place the PivotChart. Choose Existing Worksheet and select a cell on your "Dashboard" sheet.
4. The PivotChart Fields pane will appear. Drag and drop the fields as follows:
5. Excel will default to a bar chart. With the chart selected, go to the Design tab and click Change Chart Type. Select Pie, and then choose the Donut style.

Chart 2: Stock Level for Top 10 Products (Bar Chart)

This chart shows your current stock levels for your most numerous items, helping you see what you're well-stocked on and what might be running low at a glance.

1. Follow the same initial steps to create a new PivotChart on your dashboard sheet.
2. This time, configure the fields like this:
3. This will show all your products. To see just the top 10, click the small dropdown arrow next to "Row Labels" (or "Product Name") inside the PivotTable. Go to Value Filters > Top 10. Confirm it's showing the Top 10 items by Sum of Quantity on Hand and click OK. Style this as a simple bar chart.

Report 3: The "Items to Reorder" List

While not a traditional chart, having a visible, dynamic list of exactly what to reorder is one of the most actionable parts of an inventory dashboard. We can do this with another PivotTable.

1. Again, insert a new PivotTable onto your dashboard sheet. Don't create a chart this time, just the table.
2. Drag these fields into the Rows box: Supplier, Product Name, Quantity on Hand, and Reorder Level.
3. Now for the magic. We need to filter this list to only show items where the current quantity is less than or equal to the reorder level. Add a new column to your original data table called "Reorder Needed?". Use the following formula:

=IF([@[Quantity on Hand]]<=[@[Reorder Level]],"Yes","No")

1. Go back to your PivotTable, and you'll see a new field called "Reorder Needed?" available. Drag this field into the Filters area at the top of the PivotTable Fields pane.
2. In the top-left of your dashboard, a filter will appear for "Reorder Needed?". Click it and select "Yes." Presto! You now have a live list that shows exactly what you need to order.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Make Your Dashboard Interactive with Slicers

Slicers are friendly buttons that let you filter your dashboard with just a click. Instead of fussing with dropdown menus, you can press a button for "T-Shirts" and see all charts and data update instantly.

1. Click on any of your PivotCharts.
2. From the Ribbon, go to the PivotChart Analyze tab and click Insert Slicer.
3. A box will appear showing all your data columns. Check the box for Category and Supplier. Click OK.

Two slicer boxes will appear on your dashboard. However, by default, a slicer only controls the chart it was created from. Let's fix that.

For each slicer, right-click its header and choose Report Connections. In the pop-up box, check the box for all the PivotTables/Charts in your workbook. Now, when you click a category or supplier, every single element of your dashboard will filter together seamlessly.

Step 5: Polishing Your Dashboard for a Professional Look

You've built all the functional components. The final step is to clean up the design to make it easy to read and professional.

• Arrange your elements neatly. Put your KPI cards at the top, followed by your main charts, and the "To Reorder" list.
• Remove the clutter. For each chart, right-click any gray field buttons (like "Sum of Stock Value") and choose "Hide all field buttons on chart." This cleans up the view.
• Use consistent colors. Go to the Design tab for your charts and slicers to pick a color scheme that matches your brand (or just one you find easy to read).
• Hide gridlines. On your "Dashboard" sheet, go to the View tab and uncheck the "Gridlines" box. This small change makes a huge difference, giving your dashboard a clean, custom-app feel.

Final Thoughts

Creating an inventory dashboard in Excel transforms a static spreadsheet into a dynamic tool for making better decisions. By properly structuring your data and using PivotTables, charts, and slicers, you can build a powerful, single view of your entire inventory operation that helps you avoid stockouts and understand your business health at a glance.

While an Excel dashboard is a massive leap forward, maintaining it can still involve manual work - especially if your inventory and sales data lives across different platforms like Shopify, Amazon, and your accounting software. We actually built Graphed to remove this manual busywork. You can connect all your tools in a few clicks, and instead of messing with formulas or pivot tables, just ask a question like "show me a chart of my inventory value by category from Shopify" and it builds the real-time, interactive dashboard for you automatically.