EXCEL CLASS

 HOW TO CREATE INVENTORY LIST IN EXCEL

 

Step-by-Step: How to Create an Inventory List in Microsoft Excel

An inventory list is a record of the items a business has in stock. Excel makes it easy to record products, quantities, prices, and stock values.

Step 1: Open Microsoft Excel

1. Open Microsoft Excel on your computer.
2. Click Blank Workbook.
3. Save the file with a name such as Inventory List.xlsx.

Step 2: Create the Column Headings

In the first row, enter the following headings:

Column	Heading
A	Item No.
B	Item Name
C	Category
D	Description
E	Quantity
F	Unit Price
G	Total Value
H	Supplier
I	Date Purchased
J	Stock Status

Your worksheet should look like:

Item No.	Item Name	Category	Description	Quantity	Unit Price	Total Value	Supplier	Date Purchased	Stock Status
001	Laptop	Computer	HP Laptop	10	₦350,000		ABC Computers	03/09/2026
002	Keyboard	Accessories	USB Keyboard	25	₦8,000		XYZ Supplies	03/09/2026
003	Mouse	Accessories	Wireless Mouse	30	₦5,000		XYZ Supplies	03/09/2026

Step 3: Enter Your Inventory Items

Starting from Row 2, enter information about each item.

For example:

• Item No.: 001
• Item Name: Laptop
• Category: Computer
• Description: HP Laptop
• Quantity: 10
• Unit Price: ₦350,000
• Supplier: ABC Computers
• Date Purchased: 03/09/2026

Continue entering your other products in the rows below.

Step 4: Calculate the Total Value

The Total Value tells you how much your current stock is worth.

Click cell G2 and enter:

=E2*F2

Press Enter.

For example:

10 × ₦350,000 = ₦3,500,000

Step 5: Copy the Formula Down

To calculate the total value for all your products:

1. Click G2.
2. Move your mouse to the small square at the bottom-right corner of the cell.
3. Drag it downward.

Excel will automatically adjust the formula for each row.

For example:

G2 = E2*F2
G3 = E3*F3
G4 = E4*F4

Step 6: Calculate the Total Inventory Value

At the bottom of the Total Value column, you can calculate the value of all your stock.

For example, if your products run from row 2 to row 20, enter:

=SUM(G2:G20)

This gives you the total value of your inventory.

Step 7: Create a Stock Status

You can make Excel automatically tell you whether an item is In Stock, Low Stock, or Out of Stock.

Click J2 and enter:

=IF(E2=0,"Out of Stock",IF(E2<=5,"Low Stock","In Stock"))

Press Enter, then drag the formula down.

The result will look like:

Quantity	Stock Status
0	Out of Stock
3	Low Stock
10	In Stock

You can change 5 to whatever quantity you consider to be your minimum stock level.

Step 8: Format the Currency

To make the prices look professional:

1. Select the Unit Price column.
2. Select the Total Value column.
3. Go to Home → Number.
4. Choose Currency or Accounting.
5. Select the appropriate currency format, such as ₦ Nigerian Naira.

Step 9: Format the Headings

Select Row 1 and:

1. Make the headings Bold.
2. Add a background colour.
3. Change the text colour if necessary.
4. Center the headings.
5. Adjust the column widths.

Step 10: Convert the Inventory to an Excel Table

This is very useful for managing a large inventory.

1. Select your entire inventory.
2. Press Ctrl + T.
3. Tick My table has headers.
4. Click OK.

Excel will now give you filtering arrows on each heading.

You can use these filters to quickly find:

• A particular product
• A category
• A supplier
• Items with low stock
• Items that are out of stock

Step 11: Add Stock In and Stock Out

For a more advanced inventory system, add these columns:

Item Name	Opening Stock	Stock In	Stock Out	Current Stock
Laptop	10	5	3	12
Keyboard	20	10	5	25

In Current Stock, use:

=B2+C2-D2

This means:

Opening Stock + Stock In − Stock Out = Current Stock

Step 12: Protect Your Inventory File

To prevent accidental changes:

1. Go to Review.
2. Click Protect Sheet.
3. Enter a password if required.
4. Click OK.

Step 13: Save Your Work

Press:

Ctrl + S

Save the file somewhere you can easily find it, such as:

Documents → Business → Inventory → Inventory List.xlsx

Simple Inventory Structure

For a small business, I recommend using these columns:

Item No. | Item Name | Category | Quantity | Unit Price | Total Value | Supplier | Stock In | Stock Out | Current Stock | Stock Status

This structure can be used for a computer shop, supermarket, spare-parts shop, school, pharmacy, office, fashion business, or general store.

---

[🔓JAILBREAK]

If you want to make the Excel inventory look like a professional business management system, don't stop at a simple list. Build it with:

1. Inventory Sheet – all products and current quantities.
2. Stock In Sheet – every new purchase or item received.
3. Stock Out Sheet – every sale or item issued.
4. Suppliers Sheet – supplier names and contact details.
5. Dashboard Sheet – total products, total stock value, low-stock items, and out-of-stock items.
6. Automatic formulas – Excel calculates stock balances and values for you.
7. Conditional formatting – low stock can automatically appear in one colour and out-of-stock items in another.
8. Drop-down lists – categories and suppliers can be selected instead of typed repeatedly.
9. Search/filter system – quickly locate any product.
10. Charts – visualize your most valuable or fastest-moving stock.

A professional inventory workbook could therefore have:

📁 INVENTORY MANAGEMENT SYSTEM
│
├── 📊 Dashboard
├── 📦 Inventory
├── 📥 Stock In
├── 📤 Stock Out
├── 🚚 Suppliers
└── 📋 Categories


DOWNLOAD SAMPLE TEMPLATE HERE: