How to Repeat Cell Value X Times in Excel (6 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 6 easy methods to Repeat Cell Value X Times in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


6 Easy Methods to Repeat Cell Value X Times in Excel

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 inserting 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.

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

  • Then, select cell B6.
  • Type the formula:
=B5+D5
  • Subsequently, press Enter.
  • Use AutoFill to complete the rest.

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

  • 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.

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

  • Finally, choose cell F5.
  • Insert the formula:
=VLOOKUP(E5,$B$5:$C$8,2)
  • Use AutoFill to return other products.
  • In this way, you can easily repeat cell values.

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

Read More: How to Repeat Cell Values in Excel (6 Quick Methods)


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 (4 Easy Ways)


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.


Similar Readings


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 step. 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 our last 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.

Return Cell Value Multiple Times with Power Query Editor

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

repeat cell value x times in excel

  • 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.

repeat cell value x times in excel

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

repeat cell value x times in excel

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

repeat cell value x times in excel

  • 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.

repeat cell value x times in excel

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

Return Cell Value Multiple Times with Power Query Editor

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

repeat cell value x times in excel

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

repeat cell value x times in excel

  • 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.

Read More: Repeat Rows a Specified Number of Times in Excel (4 Easy Ways)


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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo