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

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel Functions that we can use to create formulas. Many institutions use Excel files to store important data. Sometimes, they need to repeat cell values and it’s easier to do that in Excel. This article will show you 7 easy methods to repeat cell value X times in Excel.

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

Whenever we are working with datasets in Excel worksheets, it becomes essential to repeat cell values as many times as we need. This is for various reasons. We can do that manually. But that’s tiresome and can take a considerable amount of time. So, we can create simple formulas and insert built-in functions to perform the task. In this article, we’ll show you all the methods to repeat cell value X times. To illustrate, we’ll use the following dataset as an example. For instance, the dataset contains 3 Products. Here, we’ll repeat the products according to the Repeat Time.

1. Repeat Cell Value X Times with Helper Column and VLOOKUP Function in Excel

In our first method, we will take the help of helper columns and the VLOOKUP function. This function searches for a value in a range. Then, it returns a cell value from the specified column. Therefore, follow the steps below to perform the task.

STEPS:

• First, we’ll input 1 in cell B5 of the helper column.

• Then, select cell B6.
• Type the formula:

• Subsequently, press Enter.
• Use AutoFill to complete the rest.

• Again, take another helper column.
• Next, 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.

• Finally, choose cell F5.
• Insert the formula:

• Use AutoFill to return other products.
• In this way, you can easily repeat cell values.

Read More: How to Repeat Cell Values in Excel

2. Copy and Paste Cell Value to Repeat Multiple Times

We can manually repeat cell values by using copy and paste features. So learn the steps to carry out the operation.

STEPS:

• Firstly, right-click on row header 6.
• As a result, you’ll get the Context Menu.
• Press Insert.

• It’ll insert blank rows.
• Repeat the steps as many as needed.
• See the below figure to understand better.

• Now, click cell B5 and press the Ctrl and C keys simultaneously.
• This will copy the cell.
• Next, select cell B6 and press the Ctrl and V keys together to paste it.
• Repeat the steps for returning the cell values to your desired amount of time.

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

3. Use Excel Fill Feature for Repeating Cell Value

Moreover, we can apply the Fill feature for repeating cell values. Hence, follow the below process.

STEPS:

• First of all, select cell B7 and 2 other cells below it.
• Then, go to Home ➤ Editing ➤ Fill ➤ Down.

• Accordingly, it’ll return the TV in cells B8 and B9.

Read More: How to Repeat Multiple Rows in Excel

4. Return Cell Value X Times Using AutoFill Tool in Excel

However, we can also use the AutoFill tool to return cell values. Therefore, learn the process.

STEPS:

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

• Subsequently, click and drag the cell up to the desired cells below it to repeat.
• Look at the following figure where we repeat it 2 times.

5. Apply Excel VBA to Get Cell Value X Times

Code lovers will love this method. You can apply the VBA code to get cell values as many times as you want. So, go through the steps.

STEPS:

• In the beginning, go to Developer ➤ Visual Basic.

• Consequently, the VBA window will pop out.
• Now, click Insert ➤ 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

• Next, save the file and press F5 to run the code.
• As a result, a dialog box will pop out.
• Select the desired range and press OK.

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

• Thus, you’ll see the cell values like it’s displayed below.

6. Return Cell Value Multiple Times with Power Query Editor

In this method, we’ll apply the Excel Power Query Editor. We’ll show how to repeat the products as many times as the total number of months (3). Hence, follow the steps.

STEPS:

• In the beginning, select cell range B4:B7.
• Then, press the Ctrl and T keys to form a table.
• A dialog box will pop out.
• Press OK.

• Click any cell in the table.
• Next, go to Table Design ➤ Properties.
• Type the Product in the Table Name.

• Repeat the same steps to form another table of Months.
• Now, we’ll take the data to the power query editor.
• For that purpose, select the month’s table.
• Next, select Data ➤ From Table/Range.

• Thus, you’ll get a power query.
• Press the Close & Load To option from the drop-down.

• In the dialog box, check the circle for Only Create Connection.
• Then, press OK.

• Similarly, bring the Product table into the power query editor by following the same steps.
• After that, go to the Add Column.
• Select Custom Column.

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

• Afterward, select the icon as shown in the below figure.

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

• As a result, it’ll return the repeated product lists.
• Subsequently, press Close & Load.

• At last, you’ll see each of the products for as many as months.

7. Combining CHOOSE and SEQUENCE Functions for Repeating Cell Value X Times

In this section, we’ll combine the CHOOSE and SEQUENCE functions to repeat cell value X times in Excel.

The formula we’ll use is:

Here, you can place your desired value or cell reference in the places of C5 and B5 in the formula. It’ll spill the output accordingly.

Download Practice Workbook

Download the following workbook to practice by yourself.

Conclusion

Henceforth, you will be able to repeat cell value X times in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task.

Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

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

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