How to Create an Inventory List in Excel (with Pictures)

Table of contents:

How to Create an Inventory List in Excel (with Pictures)
How to Create an Inventory List in Excel (with Pictures)

Video: How to Create an Inventory List in Excel (with Pictures)

Video: How to Create an Inventory List in Excel (with Pictures)
Video: How to Repair Corrupt PowerPoint File (100% Works) 2024, November
Anonim

This wikiHow teaches you how to manage your business or company inventory using an Excel spreadsheet on a Windows or Mac computer. You can use a formatted inventory list template, or create a new spreadsheet yourself manually.

Step

Method 1 of 2: Using Templates

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

Step 1. Open Microsoft Excel

This program is marked by a dark green icon with a white “X”.

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

Step 2. Click the search bar

This bar is at the top of the Excel window.

On a Mac computer, click the “ File ” in the upper left corner of the screen first, then select “ New from Templates… ” from the drop-down menu.

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

Step 3. Locate the inventory list template

Type inventory list into the search bar at the top of the page, then press Enter. A list of inventory management templates will be displayed.

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

Step 4. Choose a template

Click the most suitable inventory list template. The preview page will open.

Each inventory list template offers a variety of features. If you don't like the selected option, press the Esc key to return to the template page

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

Step 5. Click Create

It's on the right side of the template 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 process takes a few seconds. Once the template is opened, you can move on to the next step.

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

Step 7. Enter inventory information

To change text-filled boxes, double-click the box, delete the existing number or text, and enter new information. While the selected templates have slightly different options, each inventory list typically includes the following options:

  • Item Number ” – Product/goods inventory number (SKU).
  • Item Name ” – Product/goods descriptive name or label.
  • Item Cost ” – The price of one product/item.
  • Number in Stock ” – Amount of product stock.
  • Net Value ” – The total net stock price of the product.
Create an Inventory List in Excel Step 8
Create an Inventory List in Excel Step 8

Step 8. Save the job

To save it:

  • Windows - Click menu " File ", click " Save As ", double click " This PC ”, select a storage location on the left side of the window, type a document name (e.g. "Inventory List") into the " File name " text field, and click " Save ”.
  • Mac - Click menu " File ", click " Save As… ”, enter the name of the document (eg "Inventory List") in the "Save As" field, specify a save location by clicking the "Where" box and selecting the desired folder, and clicking " Save ”.

Method 2 of 2: Creating a List from Scratch

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

Step 1. Open Microsoft Excel

This program is marked by a dark green icon with a white “X”.

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

Step 2. Click Blank workbook

It's in the upper-left corner of the window.

Skip this step on a Mac computer

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

Step 3. Create a title for the inventory list

In the following boxes, enter these titles:

  • A1 - Product Number
  • B1 - Product name
  • C1 - Price
  • D1 - Total Stock
  • E1 - Total Stock Price
Create an Inventory List in Excel Step 12
Create an Inventory List in Excel Step 12

Step 4. Adjust the column width

Click the space or delimiter between two column letters (e.g. " A" and " B ”) at the top of the worksheet, then drag the cursor to the right to widen the column.

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

Step 5. Enter the product inventory number

Click the box A2 ”, then type the inventory number (eg 123456) and press Enter.

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

Step 6. Add item name

Click the box B2 ”, then enter the official name of the item (eg fish nuggets).

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

Step 7. Determine the price per item

Click the box C2 ”, then enter the price for a single product (eg 27,500).

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

Step 8. Add up the total stock of available products

Click the box D2 ” and enter the number of products in stock (eg if you have 80 packs of fish nuggets, type 80).

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

Step 9. Enter the net value formula

Click the box E2 ”, then type in the formula

=C2*D2

in the box and press Enter. You can immediately see the total stock price on the box.

You can repeat this general formula for each box in the "Total Stock Price" column. Make sure you change the entry “ C2" and " D2 ” with the letter and number of the appropriate box (e.g. if you want to multiply the values in the box “ C10" and " D10 ”, use the letter and number of the box instead of “ C2" and " D2 ”).

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

Step 10. Add other entries to the inventory list

Repeat the above process for each inventory entry. Use one line for each product until your list is complete.

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

Step 11. Save the job

To save it:

  • Windows - Click menu " File ", click " Save As ", double click " This PC ”, select a storage location on the left side of the window, type a document name (eg "Inventory List") into the " File name " text field, and click " Save ”.
  • Mac - Click menu " File ", click " Save As… ”, enter the name of the document (eg "Inventory List") in the "Save As" field, specify a save location by clicking the "Where" box and selecting the desired folder, and clicking " Save ”.

Tips

You can also add a new sheet to the workbook by clicking the “ ” in the lower left corner of the page.

Recommended: