Automate SKUs & Save Hours Daily

Have you ever been trying to track your inventory when, suddenly, a formula breaks because you deleted or moved a row? It is frustrating to watch totals go haywire, see #REF! errors pop up, or even worse, end up with impossible negative numbers in your stock due to a data entry error. Managing this by manually dragging formulas is a waste of time and a constant threat to your data accuracy.

3. The Action Plan

1. Smart Centralization: The first step is to define a workspace where column names (SKU, Movement Type, and Quantity) are recognized as fixed variables. This prevents you from having to hunt through the entire spreadsheet to find where each piece of data is when something fails.
2. Movement Standardization: Instead of manually adding and subtracting, we create an invisible logic layer that automatically translates words (such as “In”, “Out”, or “Return”) into positive or negative mathematical values. This way, no matter how you type the movement, Excel will always know whether to add or subtract.
3. Execution with Automatic Reset: We implement a calculation engine that iterates through your entire list row by row, summing quantities but with one critical instruction: at the exact moment it detects that the SKU has changed, it clears the counter and starts from zero for the new product, ensuring that balances do not mix between different products.

4. Detailed Implementation

To achieve this, we are not going to use the typical sum formula that you have to drag down. We are going to use a “dynamic array formula” that is written only once in cell E2 and expands automatically through the entire column.

Prerequisites for your spreadsheet:

• Column A: Contains the product identifier (SKU).
• Column C: Contains the movement type (e.g., “In”, “Out”, “Return”, “Adjust”).
• Column D: Contains the movement quantity.

Setup steps:

1. Select cell E2 (the first cell under your “Running stock” header).
2. Copy and paste the following exact formula:

=LET(
  sku, A2:A16,
  type, C2:C16,
  qty, D2:D16,
  delta, MAP(type, qty, LAMBDA(_t, _q, SWITCH(UPPER(_t), "IN", _q, "RETURN", _q, "OUT", -_q, "ADJUST", _q, 0))),
  SCAN(0, SEQUENCE(ROWS(delta)), LAMBDA(stock, i,
    LET(
      d, INDEX(delta, i),
      currentSku, INDEX(sku, i),
      prevSku, IF(i=1, "", INDEX(sku, i-1)),
      IF(i=1, MAX(0, d), IF(currentSku=prevSku, MAX(0, stock+d), MAX(0, d)))
    )
  ))
)

What did you just install? Here is how each piece works so you can master your own automation:

• The Variable Engine (LET): This part provides your flexibility. At the beginning of the formula, you will see sku, A2:A16. If your list grows to row 500 tomorrow, you don’t have to rebuild the formula; just change that range once and you’re done. Everything else updates automatically.
• The Automatic Translator (MAP + SWITCH): This is where efficiency happens. The MAP function iterates through your “Type” column and, using a SWITCH, decides: if it says “IN” or “RETURN”, it treats it as positive; if it says “OUT”, it adds a minus sign to subtract it. This eliminates human error during data entry.
• The Accumulation Brain (SCAN): This function does the heavy lifting. It goes row by row, saving the previous result (the “stock”). However, it has an intelligent condition: it compares the SKU of the current row with the previous one. If they are the same, it continues adding; if it detects a new SKU—boom!—it resets the counter to zero to start fresh for the new product.
• The Safety Net (MAX): This is your safeguard against entry errors. By using MAX(0, ...), we are telling Excel: “If someone accidentally enters an outflow greater than the available stock, do not show me a crazy negative number; keep it at 0.” This maintains the visual integrity of your inventory.

Immediate benefits for your productivity:

• Zero Maintenance: Forget about “dragging the formula down” every time you add a movement. The formula lives in a single cell and handles the rest.
• Protection against #REF! errors: Since the logic does not depend on fixed cells above it, you can filter or even delete intermediate rows without the spreadsheet breaking.
• Clean Architecture: By using clear names (sku, type, qty), anyone viewing the formula will understand what is happening, making teamwork much easier.

5. Suggested Tool

Microsoft Excel (Microsoft 365 or Excel 2021 and later). For this solution to work like magic, you need a version of Excel that supports “Dynamic Arrays.” These versions include the LET, MAP, and SCAN functions. Do not use older versions (like Excel 2016) because the formula will not be able to expand on its own and will result in an error. This is the ideal tool because it allows you to create professional management systems without needing to program in VBA or use expensive inventory software.

6. Prompt to copy

I have an Excel spreadsheet with the following data: Column A (SKU), Column C (Movement Type), and Column D (Quantity). I need you to write a single formula using the LET function to calculate the running stock in column E. The formula must follow three rules: 1) If the type is "IN", "RETURN", or "ADJUST", the quantity is added; if it's "OUT", the quantity is subtracted. 2) The running stock calculation must automatically reset every time the SKU changes compared to the previous row. 3) The result must never be a negative number (if an outflow exceeds available stock, it should show 0). Use dynamic array functions like MAP and SCAN so that it is a single expandable formula.