How to make an inventory to keep track of items in Microsoft Excel? step by step guide

This software of microsoft-office stands out as an essential tool for most companies and businesses today, this is because it offers wide variety of business functions that allow to carry out some of the necessary tasks in these areas easily and quicklyas it is creating warehouse inventories.

And is that one of the easiest ways to being able to control all the incoming and outgoing items in any business is through inventoriesespecially when handled thousands of products. In addition, this tool can also be very useful in householdsat the moment of wanting control some of the products that are used there.

Fortunately, this program has formulas and tables that help you have a very good control over all the products you have available and you will not need to make a investment to obtain the dream catalog. According to this, here we are going to explain how you can do an item control inventory in Microsoft Excel. To do this, follow in detail everything that we will explain in the post.

What to keep in mind when doing an inventory using Microsoft Excel?

Thanks to all the tools offered by this program, you will have the opportunity to make a fairly easy stock managementwithout having to resort to expensive programs for it. You can also support yourself either in one of program templates or simply do it from zero. Considering that there are different methods for create inventories.

You must bear in mind that the type of inventory carried out will also depend on the stock you havethat is, if you have little variety and quantity of products, East item control what to do manual.

but if you have one relatively large number of products then it is best that you make use of a computer program such as Excel. At the time of create these inventories It is important that you take into account several aspects.

For this you must previously have some details of the products which will be necessary for the same.

Some of them include the following:

• You should count with the name of the product.
• make one description of each item that you have stored.
• set a code for each of them.
• write down the date of receipt thereof.
• write down equipment sale datewill allow you to know the departure time from each of them.
• The price.
• And finally the available quantity of each of themwill let you know when it is running out of existence.

The latter named point it is very important to take into account all the items that are registered and soldsince this will be what allows you know how many products remain in your warehouse. This way you will know if you should buy one. new amount of them or not.

You should also keep in mind that for greater ease when creating these inventories, each of your items must contain the following details:

• order everybody items that are the same in the same place.
• Creates a label for each of the products, containers or boxThis will allow you to find them more easily.
• Everytime that receive merchandise from your suppliers it is important that you verify that everything you received matches what you requested.
• And finally, you must make a periodically review in your warehousethis will allow you to verify that the physical existence coincides with the existence that is marked in the inventory.

Steps to make an inventory in Microsoft Excel and keep track of your products

Knowing which are the most important aspects to take into account inventories, Now it only remains to explain how you can start creating them from Microsoft Excel. As already mentioned above There are two ways to create them. either through a template or manually.

To do this, follow in detail each of the methods that we will teach you below:

with a template

Microsoft Excel It stands out mainly for offering a large number of free templates what can you use for create your item stocks. To do this, the first thing you should do is download the template what do you want to use in your computer. Keep in mind that this procedure is much simpler and faster to perform than create them manually.

Therefore, to create your inventories through a template you must perform the following steps:

• One time downloaded to your computer, the next thing will be look for it in the folder where it is stored. There you must double click on it to open it. As you can see, a window opens asking you to select the program you want to open it within this case “Microsoft Excel” and click on “To accept”.

• Then the template will appear in a software spreadsheet. As you can see the template has all the columns and details necessary for the preparation of the stock.

• In this case all you have to do is start replace each of the boxes with the information you want to add there. This process may seem somewhat tedious but in a few minutes your inventory It will be completely created in a very simple and easy way. As you can see, working with the help of templates will save you a lot of work.

manually

the other form of power creating the stocks is manuallyPlease note that this is a quite long process so you must pay close attention to each of the steps, as this will prevent make a mistake during the procedure.

Also, you don’t need be an expert to create an inventory of products, because it is a prsimple but very extensive procedure.

In this way, follow each of the steps that we are going to indicate below:

Create your inventory in a spreadsheet with inputs and outputs

To start this procedure you need to do you think a document of Excel, this must have three sheets available to avoid confusion later. It is also advised that each leaf has its name. The first leaf can be called “Inventory”, on this sheet you will find all the products you have in warehouse.

As for the second sheet you can call it “Tickets”, here will be registered amount of products that shopping. And the third and last leaf will be called “Departures”, in it must be registered all the products that bandages.

Once this is done, in the first line of each column you must write the following:

• Code or item key.
• Name of the product.
• Description.
• Initial stockthe number it starts with, it can be zero.
• Tickets.
• Departures.
• total stock what do you have after purchases and sales.

Depending on your needs you can implement other columns like “Category” or “Brand”.

In the input column you can set the following details:

• Invoice, name or number of the invoice.
• Date, you must include the day, month and year in which the merchandise was bought.
• Code.
• Product.
• Number of items to be they bought.

In the output column you can include the following details:

• Invoice, name or number bill of sale.
• Date, you must include the day, month and year in which the merchandise was bought.
• Code.
• Product.
• Quantity of products sold.

Once you have all this in your three tables, you must continue with the other steps:

Check that your tables have the correct format

• In order to ensure this, you need to select all your table completely and go to the tab “Insert”.
• There you must select the icon “Boards”.
• Now in the dropdown Choose the option of “Table” again.

• Here a new window will appear “Create table”, In this case you must select the option “The table has headers” and then click on “To accept”.

• After this you will see how the excel table it’s going to take on a totally different look. if you have done everything correctly will appear as follows:

• The next thing will be change the name of the table.

• Now repeat these same steps with the remaining two sheets.
• Note that the table “Inventory” should be called inventory, the table of “Entry” should be called input and the table “Departure” should be called output.

Add the products

• when you already have all three spreadsheets formatted correctlythen you must locate yourself in the sheet of “Inventory”, here you must fill the column of “Code” with all the products you have in stock. Note that here you must put a code to each of themnamely, if you have 50 items then you must create 50 codes.

• The next thing will be to go to the sheet of “Tickets”, there you must select all the cells of the first column named “Code”, when it is selected you must go to the menu of “Data”.
• Now look for the option “Data validation” and select it.

• Here a new window will appear, in the menu of “Allow” You must select the option “Ready”.

• After this, a new section will appear called “Source”, in it you must do click in the icon to select “Cells”.

• A new window will appear. Here you should go to the sheet of “Inventory” and select the code column and press the key “Enter”.

• Now go back to the previous window and in the “Source” you will see all cells that you have selected, click on “To accept”.

• Once this is done, every time you hover over one of the the cells of that column, you will be able to click on the drop down menu in which you can choose your product code. This will prevent you from making mistakes write the product code.

• Then you must fill your table with all product codesthis will be easier since you only have to choose them from the drop down menu That has been created.

• The next thing will be to repeat these steps in the output sheet to get the same results.

Complete all other information in the other columns

• In this case you must return to the window “Inventory”, when you have all codesyou must finish filling all the cells of the columns of “Product”, “Description”, the “Initial Stock”, among other. In the event that still do not have any productthen the initial stock must be zero.

• Once this is done, you should go to the spreadsheet “Entry” in her the code column must also be filled
• in the column of “Product” you must write the following formula: =SEARCH([@Código]INVENTORY, INVENTORY[Producto]). This way the column will be filled with all the names of your products automatically. This will prevent you from having to do it Handbook again.

• The next thing will be to fill in the rest columnsin this case it would be missing “Invoice”, “Date” and “Amount”.

• Once all this is done, the following will be repeat the same steps for the window “Departure”.

Use some formulas so that your tables finish filling automatically

When all of the above has been completed, the following will be apply the necessary functions so that each of the windows auto fillthis will prevent you from having to do everything manuallywhich will consume more time.

To do this, perform these steps:

• Once located on the sheet “Inventory” you must select the input column and copies the following formula: =SUMIF(INPUTS[Código],[@Código],TICKETS[Cantidad])
• After this the column will be filled with automatically with all the input information.

• Now for the column “Departure” you should use this formula: =SUMIF(OUTPUTS[Código],[@Código],DEPARTURES[Cantidad])

• In this way, all the information will be completed. “Departure”.

• in the column of “Total” you must write the following formula: =[@[Stock inicial]]+[@Entradas]-[@Salidas] this will allow it to be completed as well.

everything is ready

Once all of the above is done, you can see how they will all appear the numbers needed for your inventorywhere you will have all the values ​​of the combined sales and purchases.

At this point it is not important whether the leaves “Input and output” they have several columns with the same product, So keep in mind that purchases and sales are made daily so the inventory will be updated every day.

In this way, your product stock will have been successfully created. This way you can start preparing each of your inventories from Microsoft Excel. As you can see, it is a fairly simple procedure but a bit extensive, so it can take several hours to do it.

All this information that is presented in this table is intended to help you know what are your best-selling products, the least sold, how much merchandise you have left in the warehouse, among many other details that will help you get the most out of your business.

List of the best inventory templates that you can use in Microsoft Excel

One of the advantages that can be obtained when work with Excel, is that it offers a lots of inventory templatesso your work in the program will be much simpler and faster, since you will only have to dedicate yourself to modifying each one of default data for those you want to add.

no doubt some power use templates for this type of projects it is very important, first of all it will ensure that your work will turn out correctly, of course you must Enter all values ​​and formulas correctly. Furthermore, and perhaps most important of all, is that It will save you a lot of work time.

According to the importance of these templates, here we are going to leave you a list with the best of them so that you can start downloading them for free:

Daily sales report

With this type of template you will have the opportunity to carry out a gwide variety of daily sales reports either for your company or personal business. A way to keep the accounting of sales in your company day by day.

Download Daily Sales Report Template Word

Home contents inventory

Many people want to have a ccontrol over everything around him, and what better way to do it than carrying aControl or inventory of all the content that is in the house. That is why here we leave you a word template so you can keep track of all those items you have in your home on a daily basis.

Download Housing Content Inventory Word Template

Registering fixed assets with a declining balance depreciation

This is another type of template that you can start using in your word processorwith it you will have the opportunity to have a record of all fixed assets in an easy and fast way.

Download Template Registry of Fixed Assets with Declining Balance Depreciation Word

Home inventory template

Being able to control the movement of all the items in your home is very important, for this you can carry out a inventory in your home. To make this much easier for you, you simply have to use the template offered by Microsoft Word For this, here we leave you one that you can start downloading easily.

Download Home Inventory Word Template

Basic inventory control template

Have an inventory either in your home, business, company, among others it is always very important, since this will help you to carry a mbetter control and organization over all those products or materials with which you are working. Luckily, Word offers this type of template that we present below:

Download Basic Inventory Control Template Word

Stock Inventory Control Template

have a cStock inventory control is essential in any business, since this will be what allows them to know if a product is being Running out or still in stock. It will also allow you to know which products come out more easily and which ones do not.

Download Stock Word Inventory Control Template

employee team

When working with a large dognumber of employees, it is very important to have the necessary tools to keep track of each one of them. In this case Microsoft Word It offers the possibility of keeping an employee team template where you can keep track of each one of them.

Download Employee Team Word Template

Food Inventory Template

feed inventory templates They are another important factor to be able to manage all the stock of food that you have, either in your business or home. For this, Word offers you the following template.

Download Food Inventory Word Template

Personal Inventory Template

And finally, you can find what is the personal Inventory Template, this will help you have better control over all your stuff, letting you know How long do your products last, how often do you consume them, among many other alternatives that you can get with them.

Download Personal Inventory Word Template