Microsoft Excel is one of the easiest professional tools to use today. Most of us must have used it even though it was only limited to the use of basic functions of tables and graphs. One of the most important and most used functions of Excel is inventory management. Inventory management via Excel makes it easy to organize stock of goods and save time.
MS Excel is preferred for small and medium sized businesses where the amount of inventory is less. In small businesses, Excel is an excellent choice for managing inventory and keeping records sales and purchasing, ordering and shipping and data storage.
Excel offers a variety of different inventory formulas to help monitor daily business activities. To get the most out of it, the user must first know the usage requirements. After that the user should know the basic information about the business and the entries to be made in the worksheet stock of goods, such as what is needed and what should be included.
See also: 10+ Best Free Warehouse Stock Apps
Make your own stock items
Excel provides the latest features about all business activities and also provides a better view when viewing multiple documents. Now let’s take a look at the step-by-step process for creating stock items using MS Excel.
Run Microsoft Excel. A blank spreadsheet is ready for the creation of a stock form.
Click to the first cell in the spreadsheet which is A1, Type “Number”, “Product ID”, “Product Number”, “Catalog Number”, or any other name you prefer to use in tracking stock items. For a simple basic stock item this time, we’ll start with the Product ID.
Click on cell B1 or press the button “Tab” on the keyboard to move the next cell on the right, which is B1. Type “Received”, “Entry Date”, “Incoming Goods” or your preferred term for tracking when item is received.
In the same way, enter the header entry “Shipping Date”, “Recipient Name”, “Out Date” and “Comments” on cell C1, D1, E1 and F1. This will make the inventory header complete. Now you have header the base on which your stock of goods will be created.
place cursor on the column on the line between columns A and B and double click. This will adjust the width of column A to set the cell contents. Repeat the same for all the columns. For the Comments column, place the cursor on the row between columns F and G, click and hold to increase the column width you want.
Now it’s time to format and give the stock a professional and tidy look. First, you need a title for the stock item. Place the cursor on row 1 and click to select the entire row. Right click on “1” and select “insert“. This process will insert a blank line above the Header. Type the name of the inventory you want here. For example I will name it as “Computer Stock”
Select the title, increase the font size to 18. Place the cursor on cell A1 and drag until cell F1. Then click the button “Merge & Center” on group Alignment on tab Home. You can also give the title a touch by selecting a color using the Font group.
The header should stand out more than the item. To do so. Increase the font size to 14, in the options available in the Font group on the Home tab. Click “B” (bold) to bold the header. Then you can give the header a different color and also make it stand out more by adding color to the header cell through the options Fill Color. All these formatting options are available in the group Fonts in tab Home.
As you can see, cells and columns overlap each other. This can be easily adjusted by adjusting the column width. Repeat Step 5 to fix the column width issue.
Add a border to the stock form to make it look cleaner. Let’s say your current stock has 10 items. Select cell A2 and then drag it to A12 then drag it to F12. Now, select “All Borders” from the drop-down list borders. Then select the cell “Computer Stock” (Title) and select “Thick Outside Border” also uses the same drop-down border. Your stock list should look much more professional and tidy after doing this.
Select line 3. Go to tab “View” and then click “Freeze Panes.”. Now you’ll never lose the stock header view, no matter how much inventory you add or how far you scroll down.
Now your stock work form is ready to use. Before starting to work on it, it is highly recommended to save the file. Go to the file tab and click “Save As.” Enter a name of your choice, such as “Computer stock list“. Click the button “Save“.
MS Excel also has templates for inventory management. User can choose templates stock items from this list. The template provided is a semi-finished template that has a complete format and formula that has been inserted. These templates can be very useful. Let’s see how to use it.
Open Excel, on the main page there is a search box then type “Inventory“, this method may not be available in versions of Excel below 2016. In essence, here you are access the page for template selection and select category “Inventories“.
The template list will display a special template for stock items.
Check one by one the list of stock templates and choose the one you like/need. you can see live preview when clicking on the template or displayed in the sidebar of the window. Choose the one that is suitable for your business and goals.
Click “Create” or “Download” (for versions below 2016) to open the selected template.
The template comes with a complete, yet customizable format and you can make any changes to the stock form you want to make it more professional and attractive and relevant to your business.
Save the spreadsheet with a file name of your choice.
Excel can be a very useful tool for small businesses, especially if you know how to use it to its best advantage. One of the best benefits of Excel is that it reduces manual labor.