How to Create an Inventory with Excel (with Pictures)

Table of contents:

How to Create an Inventory with Excel (with Pictures)
How to Create an Inventory with Excel (with Pictures)
Anonim

This guide explains how to manage your business inventory using an Excel spreadsheet on a Windows or Mac computer. You can use a predefined template or create your own manually.

Steps

Method 1 of 2: Use a Template

Create an Inventory List in Excel Step 1
Create an Inventory List in Excel Step 1

Step 1. Launch Microsoft Excel

The app icon is dark green and has a white "X" inside.

Create an Inventory List in Excel Step 2
Create an Inventory List in Excel Step 2

Step 2. Click on the search bar

You will see it at the top of the program window.

On Mac, you must first click on File in the upper left corner, then click on New from model … in the menu that appears.

Create an Inventory List in Excel Step 3
Create an Inventory List in Excel Step 3

Step 3. Search for inventory templates

Type inventory in the search bar at the top, then press Enter. The list of inventory management templates will open.

Create an Inventory List in Excel Step 4
Create an Inventory List in Excel Step 4

Step 4. Select a template

Click on the inventory model that best suits your needs. The preview page will open.

Each model offers different features. If you don't like what you have selected, press Esc to return to the templates page

Create an Inventory List in Excel Step 5
Create an Inventory List in Excel Step 5

Step 5. Click Create

You will see this button to the right of the preview window.

Create an Inventory List in Excel Step 6
Create an Inventory List in Excel Step 6

Step 6. Wait for the template to load

This can take a few seconds. Once the model is open, you can continue.

Create an Inventory List in Excel Step 7
Create an Inventory List in Excel Step 7

Step 7. Enter your inventory information

To change a pre-filled cell, double-click on it, delete the word or number it contains, then enter the product information. Your chosen model may have slightly different options, but almost all of them will include the following items:

  • Product number is the inventory code (SKU) of the product;
  • Product name is the descriptive name of the product;
  • Unit cost is the cost of a single product;
  • Quantity in stockis the quantity of products in stock;
  • Net value is the total stock value of a product type.
Create an Inventory List in Excel Step 8
Create an Inventory List in Excel Step 8

Step 8. Save your work

To do it:

  • On Windows click on File, then on Save with name, click twice on This PC, click on a save path in the left part of the window, type the name of the document (for example "Inventory list") in the text field "File name", then click on Save.
  • On Mac click on File, then on Save with name, enter the name of the document (eg: "Inventory list) in the" Save as "field, select a saving path by clicking on the" Where "box before choosing a folder, then click on Save.

Method 2 of 2: Create an Inventory from Scratch

Create an Inventory List in Excel Step 9
Create an Inventory List in Excel Step 9

Step 1. Open Microsoft Excel

The program icon is a green square with a white "X".

Create an Inventory List in Excel Step 10
Create an Inventory List in Excel Step 10

Step 2. Click on New Workbook

You will see this box in the upper left part of the window.

Skip this step on Mac

Create an Inventory List in Excel Step 11
Create an Inventory List in Excel Step 11

Step 3. Create inventory column titles

Type the following entries in the indicated cells:

  • A1 - Product code
  • B1 - Product name
  • C1 - Unit cost
  • D1 - Quantity produced
  • E1 - Net value
Create an Inventory List in Excel Step 12
Create an Inventory List in Excel Step 12

Step 4. Adjust the column width

Click on the space between two letters of the columns (for example, TO And B.) at the top of the sheet, then drag the mouse to the right to enlarge the column.

Create an Inventory List in Excel Step 13
Create an Inventory List in Excel Step 13

Step 5. Enter the inventory code of the first product

Click on the cell A2, then type the inventory code for that product (for example, 123456) and press Enter.

Create an Inventory List in Excel Step 14
Create an Inventory List in Excel Step 14

Step 6. Add the product name

Click on the cell B2, then enter the official name (for example Steel cables.

Create an Inventory List in Excel Step 15
Create an Inventory List in Excel Step 15

Step 7. Determine the unit cost of the product

Click on the cell C2, then enter the unit cost (for example 4.99).

Create an Inventory List in Excel Step 16
Create an Inventory List in Excel Step 16

Step 8. Calculate the total number of that type of products available in stock

Click on the cell D2, then enter the quantity of units in stock (for example, if you have 80 steel cables ready for sale, enter 80).

Create an Inventory List in Excel Step 17
Create an Inventory List in Excel Step 17

Step 9. Enter the formula for the net value

Click on the cell E2, then type

= C2 * D2

inside it and press Enter. You should immediately see the net value appear in the cell.

You can repeat this general formula for all cells in the "Net Value" column. Just make sure you replace C2 And D2 with the correct cells (for example, if you want to multiply cell values C10 And D10, enter those values instead of C2 And D2).

Create an Inventory List in Excel Step 18
Create an Inventory List in Excel Step 18

Step 10. Add the rest of the products to inventory

Repeat the process described above for each item in stock. Assign each product to a new row until inventory is complete.

Create an Inventory List in Excel Step 19
Create an Inventory List in Excel Step 19

Step 11. Save your work

To do it:

  • On Windows click on File, then on Save with name, click twice on This PC, click on a save path in the left part of the window, type the name of the document (for example "Inventory list") in the text field "File name", then click on Save.
  • On Mac click on File, then on Save with name, enter the name of the document (eg: "Inventory list) in the" Save as "field, select a saving path by clicking on the" Where "box before choosing a folder, then click on Save.

Advice

You can add another sheet to your workbook by clicking on at the bottom left of the page.

Recommended: