Function Problems

How to Automatically Generate Excel Labels from a Bin Quantity

Khadija Khan Oct 7, 2026 869 views

Question details

The user needs a method to automatically generate a list of formatted inventory labels based on a specified aisle, shelf, and bin quantity.

How to Automatically Generate Excel Labels from a Bin Quantity

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up an automated inventory labeling system where aisle and shelf names repeat for every label, but the bin names increment sequentially up to a user-defined total count.
Observed behavior: The goal is to output a dynamic list containing the exact number of labels required (e.g., Bin 1, Bin 2) alongside the repeating aisle and shelf labels without manual copying and pasting.

Before you start

Ensure you are using a modern version of Excel or WPS Office that supports dynamic array functions. Set up an input table with clear column headers such as Aisle Label, Shelf Label, and Number of Bins.

Solution 1Recommended

Use Dynamic Array Formulas to Generate Labels

Utilize modern dynamic array functions like SEQUENCE and HSTACK to automatically spill the correct number of label rows based on your bin count.

This approach requires an Office version that supports dynamic array formulas (such as Microsoft 365, Excel 2021, or the latest WPS Office). Dynamic arrays allow a single formula to return multiple values that automatically 'spill' into adjacent cells.

Set up the input data

Enter your reference data in a row. For example, put your Aisle Label in cell A2 (e.g., 'A-1'), your Shelf Label in cell B2 (e.g., 'Shelf 1'), and the total Number of Bins in cell C2 (e.g., 20).

Use SEQUENCE to generate bin numbers

The SEQUENCE function can generate a list of numbers. To create the bin labels, you can use a formula like ="Bin "&SEQUENCE(C2). This will generate 'Bin 1', 'Bin 2', up to 'Bin 20'.

Combine data with HSTACK

To repeat the Aisle and Shelf labels alongside the sequence, select your target output cell and enter the formula: =HSTACK(IF(SEQUENCE(C2), A2), IF(SEQUENCE(C2), B2), "Bin "&SEQUENCE(C2)).

Apply and review the spilled array

Press Enter. The formula will automatically spill down the columns, generating the exact quantity of labels specified in cell C2. The aisle and shelf names will repeat on every row.

Use Dynamic Array Formulas to Generate Labels

Dynamic Spilling: If you change the number of bins in cell C2, the output list will automatically expand or shrink without needing to drag the formula.

Solution 2

Use a VBA Macro for Older Excel Versions

If your spreadsheet software does not support dynamic arrays, you can use a simple VBA macro to loop through the input and output the labels.

Automate Label Generation with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like SEQUENCE and HSTACK, making it incredibly easy to automate warehouse or inventory labels without complex coding.

1. Open your data in WPS: Launch WPS Spreadsheet and open the file containing your inventory data.
2. Enter the label inputs: Input your Aisle, Shelf, and total Bin Count in dedicated cells (e.g., A2, B2, C2).
3. Apply the dynamic formula: Type the HSTACK and SEQUENCE combination formula to instantly spill your complete list of labels.
4. Format and Print: Use WPS Spreadsheet's Page Layout tools to format the spilled array and print the generated list directly to your label printer.

Fully compatible with Microsoft Excel formulas and dynamic arrays.Effortlessly generate sequential data with built-in array functions.Lightweight and runs smoothly on Windows, Mac, and Linux.Free to use for everyday office tasks and data management.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SEQUENCE formula returning a #NAME? error?

The #NAME? error occurs if your spreadsheet software does not support dynamic array functions. Ensure you are using Microsoft 365, Excel 2021, or the latest updated version of WPS Office.

Can I generate labels for multiple aisles and shelves at once?

Yes, but you will need a more advanced formula using functions like REDUCE, LAMBDA, or TOCOL to loop through multiple rows of data and stack the results. Alternatively, a VBA macro is highly effective for processing multiple rows of input data simultaneously.

How do I format the output to print nicely on sticker sheets?

Once your dynamic list is generated, highlight the spilled range, go to the Page Layout tab, and select 'Set Print Area'. You can then adjust the row height and column width to match the physical dimensions of your label sheet.

Do dynamic arrays update automatically when I change the bin count?

Yes. One of the main advantages of using functions like SEQUENCE is that the resulting array automatically resizes (spills or retracts) to match the new bin quantity entered in your input cell, instantly updating your label list.

Excel & Data Problems