Material Stock Register Format In Excel Work (2027)

An effective Material Stock Register in Excel serves as a live ledger to track inventory levels, preventing both stockouts and over-ordering. Whether you are managing raw materials for manufacturing or office supplies, a well-structured sheet should automate the calculation of your Closing Stock based on daily movements. Recommended Excel Format

To build a professional register, set up your columns with the following headers: Item Description Unit (e.g., Kg, Pcs) Opening Stock Stock In (Received) Stock Out (Issued) Closing Stock Reorder Level Key Data Points to Include: Item Code/SKU:

A unique identifier to avoid confusion between similar items. Opening Stock: The quantity available at the start of the period or day. Stock In/Out:

Columns to record new deliveries and items consumed or sold. Closing Stock: Use the formula: = (Opening Stock + Stock In) - Stock Out Reorder Level:

A threshold value that triggers a restock alert when the closing stock falls below it. Pro Tips for Automation Use Tables: Highlight your data and press

. This automatically expands your formulas and formatting as you add new rows. Data Validation: Data Validation Material Stock Register Format In Excel

tool (under the Data tab) to create dropdown menus for "Item Names" or "Units." This prevents typos that break your formulas. Conditional Formatting: Set a rule to highlight cells in Closing Stock is less than or equal to the Reorder Level Freeze Panes: View > Freeze Panes > Freeze Top Row

so your headers remain visible as you scroll down long lists of entries. If you prefer not to build one from scratch, Microsoft Support offers various pre-made inventory templates accessible via File > New within Excel. Microsoft Excel Are you tracking raw materials for production or finished goods for

How to Maintain Daily Stock in Excel — Download Free Template

Step 5: Calculating Values (Financial Column)

To track money, you need Value columns.

• Column G (Value IN): =E2 * F2
• Column J (Value OUT): =H2 * I2
• Column L (Balance Value): Use a similar SUMIFS logic, or simply: =K2 * (Average Cost). For simplicity, use Weighted Average:

= (Opening Value + SUM(Value IN)) - SUM(Value OUT) An effective Material Stock Register in Excel serves

Example columns with sample Excel formulas

• Opening Value (col G): =E2*F2
• Receipt Value (col L): =J2*K2
• Issue Value (col Q): =O2*P2
• Balance Qty (col R) for row n: =IF(A2=A1, R1 + J2 - O2, E2 + J2 - O2) — adjust item grouping logic accordingly
• Balance Value (col T) for row n: =IF(A2=A1, T1 + L2 - Q2, G2 + L2 - Q2)
• Balance Rate (col S): =IF(R2>0, T2/R2, 0)

(Replace column references to match your sheet layout; use INDEX/MATCH or SUMIFS for more robust per-item running totals.)

3. "ABC Analysis" Dropdown (Hidden column or print note)

• A items (high value, low volume) → Weekly review
• B items (medium) → Monthly review
• C items (low value, high volume) → Quarterly review

Section B: Transaction Data (One row per movement)

This section records every single transaction (Received or Issued).

| Column | Field Name | Description | | :--- | :--- | :--- | | H | Date | Day of transaction | | I | Voucher/Ref No. | GRN (Goods Receipt Note) or Material Issue Slip number | | J | Receipt (In) | Quantity added to stock | | K | Receipt Rate ($) | Purchase cost per unit | | L | Receipt Value ($) | (Receipt Qty * Rate) | | M | Issue (Out) | Quantity removed from stock | | N | Issue Rate ($) | Cost method (FIFO/LIFO/Weighted Avg) | | O | Issue Value ($) | (Issue Qty * Rate) | | P | Balance Qty | (Opening + Total Receipts - Total Issues) | | Q | Balance Value ($) | (Opening Value + Receipt Value - Issue Value) | | R | Remarks | Damage, Return to Vendor, Transfer, etc. |

Option 2: Automated Format (With Formulas)

This method calculates the Balance Stock and Total Value automatically so you don't have to do the math manually.

Step 1: Prepare the Headers Keep the headers as described in Option 1. Step 5: Calculating Values (Financial Column) To track

Step 2: Apply the Formulas Assume your data starts in Row 2.

1. Balance Stock Formula (Column I):

   • For the very first entry (Row 2), type the formula: =G2-H2 (Receipts minus Issues).
   • For the second entry onwards (Row 3), the balance must add the previous balance. Type this formula in cell I3 and drag it down: =I2+G3-H3 (Logic: Previous Balance + Received Today - Issued Today)

2. Total Value Formula (Column K):

   • In cell K2, type: =I2*J2
   • (Logic: Balance Stock × Rate Per Unit)

Step 3: Formatting for Professionalism

• Date Column: Select Column A > Right Click > Format Cells > Date.
• Currency Columns: Select Columns J and K > Right Click > Format Cells > Currency/Accounting.
• Borders: Select your entire table area > Go to the 'Home' tab > Click the Borders icon > Select 'All Borders'.