You need to know how to do an inventory in Excel, because with this tool it is very easy and essential for your business. In fact, it is easy to use for inventory control and to do it through certain formulas, but for this you must know them.
Step by step solve how to make an inventory in Excel
Sort out how to do an inventory in Excel It is the easiest and cheapest way to control the inventory of your business, but for this you must use the correct formulas and make your spreadsheet and you can properly carry your inventory according to the inputs and outputs and update it.
Step 1: Start by creating a table of your products, because you must create the catalog, because from this you can track the stock of each product and you will do it through formulas.
- So create a blank workbook and give that spreadsheet a name and add the title such as Product List in row 1 Column A.
- Then add columns according to the need of your catalog.
- In row 2, Column B write product description and in the same fine but column C write current stock.
- Go to the Insert menu, choose Table and set a price range. You must choose the table has headings so that Excel understands that the headings are in the first row of the table and then click OK.
- Then you will change the name of the table by choosing a cell in the table.
- Go to Table Tools and choose Layout.
- Write ProductTbl as the name of the table.
- If you want to change the format that was applied when you created the table, you can do so in the Design and Table Style menu, as you will simply choose the design you want.
You have already created the product table and if you want the title to be better visualized, you can center it by choosing the range of cells where you want to center it and click on Combine and center and take the opportunity to increase the font size if you want.
As you already created the table of your products, now you will create the sheet for the movements of your business.
Step 2: In the movement sheet you will record the entries and exits of the inventory products, because according to the entries and exits the existence of the products is automatically updated, then:
1.Create a new sheet with the title Inventory movements and create the columns for Product key, Product description, Movement, Incoming quantity, Outgoing quantity, price of each one, Total and adjust the width of each column as you like.
- Go to Insert, choose Table and give it the price range always choosing the table has headings for Excel to understand.
- If you want to modify the format you can do it in Design and then in Table Tools.
- Now choose a cell in the table and rename it.
- Choose Design from Table Tools.
- Write TblMovement in Table name.
- You can center the title if you want by selecting the cell range and pressing Merge and center and also increase the font size and you will have the inventory movements table ready.
Step 3: Now you will apply the formats and formulas to the product tables.
- Go to the product table and choose cell C3.
- In the Number section choose number format.
- Go to cell C3 and put this formula:
= ADDIF (TblMovement[Clave de Producto], ”=” &; A3, Tbl Movement[Cantidad Entrada]); ADDIF (TblMovement[Clave de Producto], ”=” &: A3, TblMovement[Cantidad Salida])
Step 4:
- Apply this format in the movement table:
- You will apply the Number format to the input and output quantity columns by selecting cells D3 and E4.
- In Number of the Menu choose the number format and add or remove decimals.
- To have the product description automatically filled in, link the Description and Product Key column with product tables.
- In the movement sheet choose cell A3; A300 from the Product Key column. 300 is the registration amount.
- In Data click Data validation.
- In Allow choose List and then in Origin place value = product key and OK and enter this formula in cell B3: = VLOOKUP ([Clave de Producto], TblProduct[[Clave de Producto]:[Descripción del Producto]]2, FALSE)
Download: