How to Repeat Cell Value X Times in Excel (7 Easy Methods)

The dataset contains 3 Products. We’ll repeat the products according to the Repeat Time.

Method 1 – Repeat a Cell Value X Times with a Helper Column and the VLOOKUP Function in Excel

Steps:

• Input 1 in cell B5 of the helper column.

• Select cell B6.
• Use the formula:

• Press Enter.
• Use AutoFill to complete the rest.

• Take another helper column.
• Type 1 in cell E5 and apply AutoFill to cell E9.
• We’ll get 5 in cell E9 which is the sum of repeat time.

• Choose cell F5.
• Insert the formula:

• Use AutoFill to return other products.

Read More: How to Repeat Cell Values in Excel

Method 2 – Copy and Paste Cell Value to Repeat Multiple Times

Steps:

• Right-click on row header 6.
• You’ll get the Context Menu.
• Press Insert.

• It’ll insert blank rows.
• Repeat the steps as many times as needed so you can fit copies.

• Click cell B5 and press Ctrl + C. This will copy the cell.
• Select cell B6 and press Ctrl + V keys to paste.
• Paste to any other rows you need, then repeat the process for other values you need to copy.

Read More: How to Repeat Rows in Excel Based on Cell Value

Method 3 – Use the Fill Feature for Repeating Cell Values

Steps:

• Select cell B7 and 2 other cells below it.
• Go to Home then to Editing and select Fill, then choose Down.

• This’ll return TV in cells B8 and B9.

Read More: How to Repeat Multiple Rows in Excel

Method 4 – Return a Cell Value X Times Using the AutoFill Tool in Excel

Steps:

• Select cell B7.
• At the bottom-right corner of the selected cell, the cursor will turn into a + icon as shown below.

• Click and drag the cell down to the desired cells below it to repeat.
• Look at the following figure where we repeat it 2 times.

Method 5 – Apply Excel VBA to Get a Cell Value X Times

Steps:

• Go to Developer and select Visual Basic.

• The VBA window will pop out.
• Click Insert and select Module.
• The Module window will appear.
• Copy the following code and paste it there.

Sub RepeatValue()
Dim rg As Range
Dim ir As Range, org As Range
xTitleId = "ExcelDemy"
Set ir = Application.Selection
Set ir = Application.InputBox("Range :", xTitleId, ir.Address, Type:=8)
Set org = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set org = org.Range("A1")
For Each rg In ir.Rows
    xValue = rg.Range("A1").Value
    xNum = rg.Range("B1").Value
    org.Resize(xNum, 1).Value = xValue
    Set org = org.Offset(xNum, 0)
Next
End Sub

• Save the file and press F5 to run the code.
• A dialog box will pop out.
• Select the desired range and press OK.

• Another dialog box will appear.
• Select the cell where you’ll spill the data.
• Press OK.

• Here’s the result for the sample.

Method 6 – Return a Cell Value Multiple Times with the Power Query Editor

We’ll show how to repeat the products as many times as the total number of months (3).

Steps:

• Select the cell range B4:B7.
• Press Ctrl + T.
• A dialog box will pop out.
• Press OK.

• Click any cell in the table.
• Go to Table Design and select Properties.
• Type the Product in the Table Name.

• Repeat the same steps to form another table of Months.
• Select the months table.
• Select Data and choose From Table/Range.

• You’ll get a Power Query editor.
• Press the Close & Load To option from the drop-down.

• In the dialog box, check Only Create Connection.
• Press OK.

• Bring the Product table into the power query editor by following the same steps.
• Go to the Add Column.
• Select Custom Column.

• Name the column as Month.
• In the formula section, type Months.
• Press OK.

• Select the icon as shown in the below figure.

• Uncheck the box marked in the following picture.
• Press OK.

• This’ll return the repeated product lists.
• Press Close & Load.

• You’ll see each of the products for as many as months.

Method 7 – Combining CHOOSE and SEQUENCE Functions for Repeating a Cell Value X Times=

• The formula we’ll use in the result cell is:

Download the Practice Workbook

Related Articles

• How to Repeat Formula for Each Row in Excel
• How to Repeat Formula in Every nth Row in Excel
• [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out

<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel