How to Make an Inventory Database in Excel

Tired of guessing what’s in your stockroom or garage? For many growing businesses, an Excel spreadsheet is the perfect first step to wrangling inventory chaos into a clean, organized system. This guide will walk you through building a simple but powerful inventory database from scratch, complete with automated calculations and basic reporting.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Start with a Plan: What to Actually Track

Before you open Excel, take a minute to think about the essential pieces of information you need to manage your inventory. A well-structured database is built on having the right columns (or fields) from the very beginning. Scrambling to add them later is a headache you can easily avoid.

Every inventory sheet should start with these core columns:

• Item Name: A clear, descriptive name for the product (e.g., "Men's Classic T-Shirt - Blue").
• SKU (Stock Keeping Unit): A unique code for each specific item variant. If you have the same shirt in three sizes, each size needs its own SKU. This is the most important field for accurate tracking.
• Description: A bit more detail about the item, like size, color, or material.
• Supplier: Who you buy the item from. This is useful for reordering.
• Unit Cost: How much you pay for one unit of the item.
• Sale Price: How much you sell one unit for.
• Quantity on Hand: The number of units you currently have in stock. This will be your most frequently updated number.
• Inventory Value: A calculated field that shows the total value of your stock for that item (Unit Cost x Quantity on Hand).
• Reorder Level: The minimum quantity you want to have on hand. When your stock dips to this level, it's time to order more.

Step-by-Step: Building Your Excel Inventory Table

Once you've decided on your columns, it’s time to build the database. We're not just going to type into cells, we're going to use one of Excel's best features for data management: Tables.

Step 1: Set Up Your Columns and Format as a Table

Open a new Excel sheet. In the first row (Row 1), type out the column headers you planned above: Item Name, SKU, Description, and so on.

Now, highlight the row of headers and a few empty rows below it. Go to the Insert tab on the Excel ribbon and click Table. A small dialog box will pop up, make sure the box for "My table has headers" is checked, and click OK.

Your range will transform into a formatted table with filter dropdowns in the headers. Why is this so important? Tables in Excel automatically expand when you add new rows, your formulas will auto-fill down the columns, and referencing data becomes much cleaner. It turns a static grid of cells into a structured database object.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Add Your Product Data

Start filling in the table with your product information. For each product, enter its name, unique SKU, supplier, cost, sale price, starting quantity, and reorder level. Don’t worry about the "Inventory Value" column yet, we’ll automate that next.

Your spreadsheet should now look like a clean grid of your current inventory.

Making It Smart: Essential Formulas for Automation

A static list is good, but a dynamic database is better. Formulas are what separate a simple list from a management tool. Because we formatted our data as a Table, writing formulas is incredibly easy and intuitive.

Calculating Total Inventory Value

Let's automatically calculate the value of the stock we have for each item.

Go to the first cell in your "Inventory Value" column (cell I2 in our example). Type the equals sign = to start a formula. Now, instead of typing a cell reference like E2, simply click on the "Unit Cost" cell in the same row. Excel will automatically insert the structured reference [@Unit Cost].

Then, type the multiplication symbol * and click the "Quantity on Hand" cell in that row. Your final formula will look like this:

=[@[Unit Cost]]*[@[Quantity on Hand]]

Press Enter. The best part? Excel automatically fills this formula down the entire column for all your existing and future products. You'll instantly see the total stock value for every item in your inventory.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Creating Automated Reorder Alerts

Next, let's create a visual cue to tell us when it's time to reorder a product. We can do this with a simple IF function. Create a new column called "Reorder Status."

In the first cell of that new column, we’ll write a formula that checks if the "Quantity on Hand" is less than or equal to the "Reorder Level." If it is, we want Excel to display "REORDER", otherwise, it should show "OK."

The formula looks like this:

=IF([@[Quantity on Hand]]<=[@[Reorder Level]],"REORDER","OK")

Now, you have an at-a-glance status for every item. You can even use Excel's Conditional Formatting (on the Home tab) to turn the "REORDER" cells red to make them stand out even more.

Step 3: Tracking Sales to Update Stock Levels Automatically

This is where your inventory database becomes truly powerful. To update your stock levels without manual data entry every time you sell something, we can create a second tab to act as a "Sales Log." Manually adjusting inventory is a recipe for errors, this method keeps your core database clean.

1. Create a New Sheet: Create a new tab and name it "Sales Log."
2. Set Up Columns: In this sheet, create a simple table with headers for: Date, SKU, and Quantity Sold.
3. Log Your Sales: Each time you make a sale, simply add a new row to this table with the date, the SKU of the item sold, and the number of units sold. You can sell the same SKU multiple times, just add a new row for each transaction.

Now, let's connect this back to our main inventory sheet. We will use the SUMIF formula to calculate the total units sold for each SKU.

Go back to your main inventory sheet (let’s call it "Inventory"). Add a new column called "Total Units Sold." In the first cell of this column, enter this formula:

=SUMIF('Sales Log'!B:B,[@SKU],'Sales Log'!C:C)

Let's break that down:

• 'Sales Log'!B:B: Look at the entire SKU column on our Sales Log sheet.
• [@SKU]: Find all the rows that match the SKU in this specific row of our Inventory sheet.
• 'Sales Log'!C:C: For all those matching rows, add up the values in the "Quantity Sold" column.

Finally, we need to adjust our "Quantity on Hand." We must first rename our current "Quantity on Hand" to "Starting Inventory." Insert one last column called "Current Stock."

The formula for "Current Stock" is simple subtraction:

=[@[Starting Inventory]]-[@[Total Units Sold]]

Now your "Current Stock" level will decrease automatically every time you add a new entry to your sales log! You have a dynamic inventory system living inside your Excel file.

Graphed

Still Building Reports Manually?

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

Watch the Demo

From Database to Dashboard: Visualizing Your Inventory

Numbers in a table are useful, but charts and summaries are better for quick decisions. PivotTables are Excel's way of creating these summaries without writing complicated formulas.

Using PivotTables to Summarize Data

Let's say you want to see the total inventory value you hold from each supplier.

1. Click anywhere inside your main inventory table.
2. Go to the Insert tab and click PivotTable.
3. Excel will ask where to place it. A new worksheet is usually best. Click OK.
4. You'll see a blank PivotTable and a field list on the right. Simply drag the "Supplier" field into the "Rows" area and drag the "Inventory Value" field into the "Values" area.

Instantly, you have a clean summary report showing your total stock value broken down by supplier. You can build reports for sales, stock levels, or anything else in seconds.

Final Thoughts

By following these steps, you've turned a blank spreadsheet into a dynamic inventory management tool. You've structured your data in a table, automated key calculations with formulas, built a separate log to track sales, and learned how to summarize it all with a PivotTable. This foundation is a massive step up from manually updating a static list.

Managing this Excel file is fantastic, but the process can become tedious when you also have a dozen other platforms to pull sales or marketing data. Many businesses graduate to a point where the weekly ritual of downloading CSVs from Shopify or HubSpot, cleaning the data, and pasting it into a master spreadsheet takes hours. We built Graphed to eliminate that friction. We connect directly to your data sources and create live, real-time dashboards so you’re never working with outdated numbers. Instead of crafting SUMIF formulas, you can simply ask, "create a report showing my top-selling products vs. ad spend this month," and get an answer in seconds.