Function Problems

How to Repeat Values at a Variable Cell Frequency Using Excel Formulas

Maira Mehtab Sep 20, 2026 870 views

Question details

Use a dynamic array formula to repeat specific values across a range based on a repetition frequency count provided in another cell.

Product: Spreadsheets
Device & OS: not provided
Scenario: Expanding or duplicating a list of items where each item has a specified repetition count stored in an adjacent cell, without manually copying and pasting.
Observed behavior: A dynamic array formula is required to automate the repetition pattern and spill the correct number of repeated values continuously down a column.

Before you start

Ensure your spreadsheet software supports dynamic array functions. If you are using an older version, you may need to update to the latest release to utilize functions like REDUCE and VSTACK.

Solution 1Recommended

Use Dynamic Array Functions (REDUCE, VSTACK, EXPAND)

Utilize a combination of dynamic array formulas to automatically repeat each value based on the frequency specified in an adjacent column.

This method leverages the LAMBDA helper function REDUCE to iterate through the list of values. It uses EXPAND to duplicate them based on the adjacent frequency cell (located via OFFSET), and stacks them together using VSTACK.

Identify your source data range

Assume your target values to repeat are in column A (e.g., A2:A3) and the corresponding repetition frequencies are in column B immediately next to them.

Select the target cell

Click on the destination cell where you want the repeated values to start appearing, such as cell D2.

Enter the dynamic formula

Input the formula: =DROP(REDUCE("",A2:A3,LAMBDA(a,b,VSTACK(a,EXPAND(b,OFFSET(b,0,1),,b)))),1) and press Enter. The formula will calculate and automatically spill the repeated values down the column.

Understanding the DROP function: The DROP function is used at the beginning of the formula to remove the initial empty row created by the REDUCE function's starting value, leaving only your generated data.

Efficient Spreadsheet Management

Easily Manage Dynamic Arrays with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, making it simple to manipulate complex data sets, repeat values based on cell frequencies, and automate your workflow without needing VBA.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your original values and their frequency counts.
2. Apply the dynamic formula: Select your destination cell and enter the combined =DROP(REDUCE(...)) formula as described in the solution.
3. Analyze the spilled data: Press Enter to see the data instantly expand. Update the frequency counts in your source cells, and the dynamic array will automatically adjust its output.

Fully compatible with Microsoft Excel formulas and dynamic array functions.Supports modern functions like VSTACK, REDUCE, and LAMBDA for automated data expansion.Lightweight software with a fast, intuitive interface for data analysis.Free to use for everyday data management and complex reporting tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my dynamic array formula returning a #NAME? error?

A #NAME? error usually means your version of the spreadsheet software does not support one or more of the newer functions used in the formula, such as REDUCE, VSTACK, or EXPAND. Ensure you are running the most recent software version.

Can I repeat rows instead of just single cell values?

Yes, but you will need to adjust the EXPAND and OFFSET parameters to reference an entire row array instead of a single column cell. Functions like CHOOSEROWS combined with sequences can also replicate full rows.

How do I do this if my frequencies are not directly adjacent to the values?

If the frequency column is not immediately to the right of your values, you must change the OFFSET(b,0,1) part of the formula to point to the correct column offset. For example, use OFFSET(b,0,2) if the frequency is two columns to the right.

What does the REDUCE function do in this scenario?

The REDUCE function iterates over your selected range of values (A2:A3). For each value, it applies a custom LAMBDA function that duplicates the value and stacks it onto an ongoing list, processing the entire array dynamically.

Excel & Data Problems