Guide create table import inventory in Excel file simple best standard

In any business, production, trade or retail how to manage your inventory effectively is vital to ensure cash flow to meet order due date to avoid wasting resources. However, not every enterprise is also qualified, or deployment needs right software inventory management specialist.

Với các doanh nghiệp nhỏ hoặc mới thành lập, File Excel nhập xuất tồn đơn giản vẫn đang là công cụ phổ biến giúp tiết kiệm chi phí. Tuy nhiên, việc sử dụng bảng xuất nhập tồn kho bằng Excel cũng đòi hỏi người quản lý cần có hiểu biết cơ bản về cách lập bảng, kiểm soát dữ liệu và áp dụng công thức phù hợp để tránh sai lệch số liệu.

This article Lac Viet Computing will help businesses:

• Understand the nature and purpose of the table import inventory in Excel;
• Know how construction science worksheets, easy to use, in accordance with the practice;
• Tránh được các sai sót thường gặp khi quản lý file xuất nhập tồn Excel

1. Table import inventory in Excel, what is?

1.1 the Concept of table import inventory

Table import inventory is a data table is built to track continuously the status of goods in warehouse, through three main activities: warehousing, production, warehouse and inventory, end of period. This is important tool to help businesses answer questions such as:

• Today exists many rows?
• Row A has import/export, how many times in a month?
• Have any items are inventory is too high or shortage?

Một file xuất nhập tồn đơn giản thường bao gồm các cột thông tin sau:

• Code: help shape the goods quickly.
• Item name: the name's specific products.
• Unit: bag, carton, kg, meter...
• Amount of income: total rows entered in the states.
• Number manufacturer: total goods exported.
• Survive the end states: is calculated by the formula: the first viable states + Import – Export.

Tùy theo yêu cầu quản lý, file xuất nhập tồn có thể mở rộng thêm cột giá nhập, giá trị tồn kho, hoặc thông tin lô hàng, hạn sử dụng trong lĩnh vực thực phẩm, y tế.

1.2 Vì sao doanh nghiệp chọn file Excel để quản lý xuất nhập tồn?

Excel is selected not because the tool is report export, import, inventory most optimal, but by conformity with the actual conditions of many small and medium enterprises in Vietnam.

Advantages of Excel template in inventory management:

• Easy to use: familiar interface, does not need in-depth training.
• High flexibility: can customize the board according to the scale, type of business
• Low cost: No need to invest in software, just the computer has Microsoft Excel.
• Customizing quick reports: can create charts, tables general as you like.

However, Excel also has limitations should be noted:

• Don't automatically update with data from other parts → to false if you do not enter data right enough.
• Difficult to control historical data upon the complexity gradually over time.
• Not suitable when the number of items, large or has many branches, because the time-consuming synthesis.

For example: A business enterprise electronic devices with 30 – 50 items, can be managed on an Excel file only. But when the number of items up to a few hundred, need to track each repository (branch), the use of Excel will potential risks of the wrong number, difficult to control composite figures.

2. Bảng xuất nhập tồn kho bằng Excel gồm thành phần nào?

Một File Excel nhập xuất tồn đơn giản hiệu quả không chỉ giúp doanh nghiệp theo dõi hàng hóa chính xác mà còn hỗ trợ ra quyết định nhanh chóng trong các tình huống như đặt hàng, điều chuyển nội bộ, kiểm kê cuối kỳ… Vì vậy, việc thiết kế bảng với đầy đủ các trường thông tin cần thiết là điều rất quan trọng. Dưới đây là các thành phần không thể thiếu khi xây dựng bảng xuất nhập tồn kho bằng Excel:

2.1 general information about the goods

The goal: to Help businesses identify each item in a clear, avoid confusion, and easy to manage.

The school should have:

• Code: a unique identifier for each type of product. For example: SP001 for “shirts white male”.
• Name of goods: interpret clearly the product name. This field should be consistent with the name on the invoice input/output.
• Unit: bag, box, kg, carton... help determine how to manage inventory according to the actual use.
• Commodity groups (if available): Classified according to product group makes it easy to filter to a report by industry.

Illustrative examples:

Noted fact: Businesses should build the code goods standard, to avoid duplicate code or put the item name is too long cause difficulties in filter – search on Excel.

2.2 Number of warehouse import

The goal: to Help control the exact amount of goods imported during the period from which to calculate inventory and analysis of efficient supply.

The school should have:

• Date of entry: specifies the date goods are imported into the repository.
• Number of votes enter: encrypted votes income (may coincide with the billing code input).
• Number of entry: Record the exact number of each item to enter the warehouse.
• Unit price entry (if followed): Helps calculate the total value of import in the states.
• Source input (optional): can notes the supplier or the form of import of goods.

Illustrative examples:

Business can create a link between the export, import, inventory, and table import receipt of goods (list form) with the VLOOKUP or XLOOKUP, helping to collate accurate data, restrict entry to coincide.

2.3 Number of export warehouse

Objective: recognition of full transparency goods have out of stock help control consumption of materials, ensure the goods is not lost.

The school should have:

• Publication date: Recorded right time goods leave the warehouse.
• Coupon code export: to Help collated with slip casting internal sales orders...
• Số lượng xuất: Cần được cập nhật ngay khi phát sinh để đảm bảo dữ liệu inventory report luôn chính xác.
• Reason for casting: Sales, stock transfer, using the internal model exhibited... easy analysis later.

Illustrative examples:

Actual note: Need to update the data warehouse export right in the day or by the ca to not lead to deviations inventory when the period-end inventory.

2.4 personality inventory end of period

Goal: This is the most important – expressing the amount of goods that are currently in stock at the present time.

Basic recipe:

Survive the end of the period = the first viable states + Enter in the states – in states

Or if you are recorded by date, can apply:

Survival current = SUMIF(Enter) – SUMIF(Cumshot)

Hint Excel formula illustration: suppose the line 2 is the first data cell G2 is inventory: =E2 – F2 (E2 is the total income, F2 is the total production)

Tip control data:

• Dùng hàm IF để cảnh báo khi tồn kho âm. Ví dụ: =IF(G2<0, “Cảnh báo: Âm kho”, G2)

• Using Conditional Formatting, red car car inventory

3. How to set the table import inventory in Excel standard, easy to control

Việc lập bảng xuất nhập tồn kho bằng Excel không chỉ dừng lại ở việc nhập dữ liệu đơn thuần, mà cần đảm bảo tính chính xác, dễ thao tác, tự động hóa ở mức cơ bản để hỗ trợ quản lý kho hiệu quả. Dưới đây là hướng dẫn từng bước giúp doanh nghiệp tự xây dựng một file xuất nhập tồn chuẩn, dễ dùng.

3.1 Create a table structure and format cell data

Step 1: Define the data columns needed

Depending on the characteristics of goods and regulatory requirements, businesses can build Excel sheet with the columns the following:

Step 2: data format suitable

• Date column (date of entry, date): format type Date.
• Column quantity: format type Number, avoid error to enter text.
• Column item code, item name: format type Text to avoid lose the first 0 line (if any).

Step 3: Apply conditional formatting (Conditional Formatting)

Use conditional formatting to help visual alerts when inventory is low or negative.

• Select columns “to be present”
• Go To Home > Conditional Formatting > Highlight Cell Rules > Less Than
• Enter the warning value (e.g.

3.2 Use Excel formula to calculate inventory automatically

To Excel table is really useful, you need integration formulas for auto calculation. Below is the simple recipe but very effective:

How to calculate the amount of import or export according to each items

Suppose you have a list of transactions in Sheet “GiaoDich”, with the columns include:

• Code (B),
• Type of transaction (C): “Import” or “Export”,
• Number (D).

In Sheet “TồnKho”, you want to calculate the total amount of income of each code:

=SUMIFS(GiaoDich!D:D, GiaoDich!B:B [@Mã_hàng], GiaoDich!C:C, “Enter”)

Similar with:

=SUMIFS(GiaoDich!D:D, GiaoDich!B:B [@Mã_hàng], GiaoDich!C:C, “Export”)

Calculate current inventory:

=Total income – Total production

Tips for beginners:

• Use IFERROR to avoid error display #N/A:

=IFERROR(SUMIFS(...), 0)

• Use VLOOKUP or XLOOKUP if the table data is divided according to multiple sheets.

3.3 Separating the board from time to time (by day/month/quarter)

Keeping track of inventory over time to help businesses understand trends, consumption, thereby optimizing the order entry, inventory reduction die.

Way 1: Use advanced filters

• Select the table data > the Data > Filter
• Filter by month and year, type of goods, item code for detailed analysis

Cách 2: Dùng Pivot Table để tổng hợp theo thời gian

• To Insert > Pivot Table
• Choose the column “Date of transaction” as the time axis (Rows)
• Select “Code” or “item Name” classification
• Select “Number” as the value of the total
• Group by month or quarter to see the trend import – export

Way 3: Create a chart inventory intuitive

• From Pivot Table or tables were aggregated into Insert > Column Chart/Line Chart
• A chart to help leaders hold fast items which are sharp fluctuations in inventory

4. Lưu ý khi sử dụng file Excel nhập xuất tồn đơn giản hiệu quả

Excel là công cụ linh hoạt, dễ tiếp cận nhưng nếu không kiểm soát tốt, doanh nghiệp có thể đối mặt với những rủi ro đáng kể như sai lệch dữ liệu, mất file, hoặc thiếu đồng bộ giữa các bộ phận. Dưới đây là những lưu ý quan trọng giúp bạn sử dụng Excel một cách hiệu quả và an toàn hơn trong quản lý kho.

4.1 control right to edit to avoid edit wrong data

One of the most common mistakes when using Excel to manage inventory is accidentally edit the wrong original data, leading to inventory report is false. Especially in the work environment group, the multiple users to access an Excel file as to develop error.

How to fix:

• Lock the important data: Use the function to Protect Sheet in Excel to lock cells with formulas or the original data, only allowed to enter in the region are pre-designed.
• Set a password for the file or sheet: limited editing rights for the responsible person, especially with the column contains the formula to calculate inventory.
• Decentralization in Google Sheets (if used online): can grant read-only for some parts (such as sales), only the warehouse management new updates.

4.2 automatic backup and version management

Lost file due to computer errors, override the unwanted or forget to save the old version is that the risks are not rare when using Excel craft.

Proposed solution:

• Hosted on cloud platform: Google Drive, OneDrive, or Dropbox automatically sync, don't worry about losing files if your computer crashes.
• Set schedule recurring backups: Create a copy at the end of each day/work week and save in the format “day-month-year”.
• The use of the “Version history” (available on Google Sheets or OneDrive): When needed, can easily restore the previous version.

Table import inventory in Excel is the solution warehouse management simple, efficient cost savings for the small and medium enterprises. When the construction is excellent, fully integrated recipe, updates properly, this tool can support the business to control inventory, reduce the loss and make a decision on input – output logical.

Tuy nhiên, nếu hoạt động của doanh nghiệp ngày càng mở rộng, số lượng hàng hóa tăng lên hoặc cần tính đồng bộ dữ liệu giữa nhiều bộ phận, thì file xuất nhập tồn Excel sẽ dần bộc lộ hạn chế. Lúc này, việc chuyển sang phần mềm quản lý kho chuyên nghiệp là bước đi cần thiết để nâng cao hiệu suất vận hành.