Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to repeat cell value x times in Excel?

doc-repeat-values-x-times-1

For example, you have a list of values in Column A, and you want to repeat the values specific number of times based on the number times in Column B, as following screenshot shown, how could you achieve this in Excel?

Repeat cell values X times with formula and helper column

Repeat cell values X times with VBA code

Fill blank cells x time with value above by using Kutools for Excel


arrow blue right bubble Fill blank cells x time with value above by using Kutools for Excel

Fill blank cells x time with value above

With Kutools for Excel’s Fill Blank Cells feature, you can quickly fill blank cells with value above, linear value or fixed value as you need.

doc-repeat-values-x-times-1

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Repeat cell values X times with formula and helper column


To repeat the cell values X times, you can insert some helper columns and then apply some simple formula, please do with following steps:

1. Insert a column to the left of column A, and type 1 in cell A2, see screenshot:

doc-repeat-values-x-times-2

2. Then put this formula =A2 + C2 into A3, and drag the fill handle to the cell A7, see screenshot:

doc-repeat-values-x-times-3

3. And then enter 1 in cell D2, and fill the number by dragging the Auto Fill to 18 which is the total number of the times number in column C, see screenshot:

doc-repeat-values-x-times-1

4. Then enter this formula =VLOOKUP(D2,$A$1:$B$7,2) into cell E2 and copy down, you will get the following result:

doc-repeat-values-x-times-5

Notes:

1. In this formula, D2 indicates the first cell you are filled with the number sequence, and A1:B7 stands for the range of the first helper column and the original cell values that you need to repeat.

2. After getting the repeated values, you can copy and paste them as values to any other place.


arrow blue right bubble Repeat cell values X times with VBA code

If you consider the first method is difficult to understand, here, I can also introduce you VBA code to solve it.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Repeat cell values X times

Sub CopyData()
'Update 20140724
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
Next
End Sub
3. Then press F5 key to run this code, and a prompt box will appear to remind you select the range which contains the values and repeated times, see screenshot:

doc-repeat-values-x-times-6

4. And click OK, another prompt box will pop out to let you select a cell to put the result, see screenshot:

doc-repeat-values-x-times-7

5. Then click OK, and you will get the result that your selected values have been repeated specified times as your need.

doc-repeat-values-x-times-8


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    L VR · 1 months ago
    This is a VERY sloppy and cumbersome fix.
  • To post as a guest, your comment is unpublished.
    Elektra · 3 months ago
    How can I access this using a MAC?
  • To post as a guest, your comment is unpublished.
    Me · 8 months ago
    Thank You !!
  • To post as a guest, your comment is unpublished.
    hiten · 9 months ago
    great solution for multiple cells. thanks for saving me time.....
  • To post as a guest, your comment is unpublished.
    Pankaj · 9 months ago
    Great solution, saved lot of times, thank you