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?

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 left screenshot shown, how could you achieve this in Excel?

 

Copy and insert rows X times based on specific number easily

Normally, there is not a good method for copying and inserting rows multiple times except dealing with manually copy and insert. But, with Kutools for Excel's Duplicate Rows / columns based on cell value feature, you can solve this problem with ease. Click to download Kutools for Excel!

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


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:

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

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

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

  • Notes:
  • 1. In this formula, D2 indicates the first cell you are filled with the number sequence, and A1:B6 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.

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()
'Updateby Extendoffice
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 to select the range which contains the values and repeated times, see screenshot:

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

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


Copy and insert cell values X times with an awesome feature

Sometimes, you may need to copy and insert the cell values x times based on the specific number, Kutools for Excel's Duplicate Rows / Columns based on cell value can help you quickly copy and insert the rows based on the number you specified.

Tips:To apply this Duplicate Rows / Columns based on cell value feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Insert > Duplicate Rows / Columns based on cell value, see screenshot:

2. In the Copy and insert rows & columns dialog box, select Copy and insert rows option in the Type section,  then select the data range you want to duplicate, and then specify the repeat time to copy and insert the rows, see screenshot:

4. Then, click Ok or Apply button, you will get the following result as you need:

Click to Download Kutools for Excel and free trial Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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.
    mati · 4 months ago
    thank u! the vlookup function worked!
  • To post as a guest, your comment is unpublished.
    Joe · 4 months ago
    The VLOOKUP table option was the best for my use case. I wanted to take appointment times (i.e. 8am, 9am, 10am, and all in between) and repeat those several times but with different frequencies. It saved having to type a time, copy/paste it multiple times, and then repeat the process until I was done. The VLOOKUP table also gives me the option to change it up some at a later date if I wanted more or less.
  • To post as a guest, your comment is unpublished.
    Yzack Alva · 4 months ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    Gary · 5 months ago
    That save me like a million hours of time.
    Thank you a million
  • To post as a guest, your comment is unpublished.
    Cristina · 5 months ago
    Me poupou muito trabalho essa procv!
    Obrigada!
  • To post as a guest, your comment is unpublished.
    L VR · 8 months ago
    This is a VERY sloppy and cumbersome fix.
  • To post as a guest, your comment is unpublished.
    Elektra · 9 months ago
    How can I access this using a MAC?
  • To post as a guest, your comment is unpublished.
    Me · 1 years ago
    Thank You !!
  • To post as a guest, your comment is unpublished.
    hiten · 1 years ago
    great solution for multiple cells. thanks for saving me time.....
  • To post as a guest, your comment is unpublished.
    Pankaj · 1 years ago
    Great solution, saved lot of times, thank you
  • To post as a guest, your comment is unpublished.
    Christina · 1 years ago
    The VLOOKUP option was perfect! Thank you for saving me hours of my life
  • To post as a guest, your comment is unpublished.
    Bill · 1 years ago
    Great solution! Thanks much
  • To post as a guest, your comment is unpublished.
    Kanwaljit · 1 years ago
    The Simplest and The Best !
    Thanks a Lot !!!!!
  • To post as a guest, your comment is unpublished.
    Courtney · 1 years ago
    Thank you so much! The VLOOKUP workaround works perfectly!
  • To post as a guest, your comment is unpublished.
    Master Org · 2 years ago
    Useless page and codes
  • To post as a guest, your comment is unpublished.
    Steve · 2 years ago
    Thank you for making the vlookup formula instructions so clear!!! This was immensely helpful!
  • To post as a guest, your comment is unpublished.
    Lanc3lot · 2 years ago
    Can you alter the vba code to mix also the cells upon result? So the lines won't be in order, but mixed
  • To post as a guest, your comment is unpublished.
    Vassi · 2 years ago
    The VBA code does not work if you don't have Kutools. It gives me error "variable not defined"
  • To post as a guest, your comment is unpublished.
    Prashant · 2 years ago
    The VBA or the formula. Neither of them is working for me. It gives "application defined or object defined error" Error 1004. Can anyone assist?
  • To post as a guest, your comment is unpublished.
    Vipul Pandey · 2 years ago
    मैं बहुत दिनों से यह ऑप्शन ढूढ रहा था
    धन्यवाद sir
  • To post as a guest, your comment is unpublished.
    Max · 2 years ago
    This was very helpful, came in handy to create a spreadsheet for work
  • To post as a guest, your comment is unpublished.
    m · 2 years ago
    Is there a way to alter the code so there are 2 columns with data and 1 column with the qty of repititions. i.e.
    A1 = x
    B2 = y
    C3 = 2

    the macro would then repeat A1 twice and b2 twice in the cels to the right

    Any help appriceated. Thanks.
  • To post as a guest, your comment is unpublished.
    raju · 2 years ago
    Thanks a Lot dude, U done a great job.
  • To post as a guest, your comment is unpublished.
    Odel · 2 years ago
    Still saving projects in 2017! Thanks so much for this! :-)
  • To post as a guest, your comment is unpublished.
    Kamna · 3 years ago
    Awesome! Many Thanks!!!!!!!!
  • To post as a guest, your comment is unpublished.
    Eshan Gupta · 3 years ago
    Hi,

    I have an query:
    Base data:
    Client
    Country
    Overall

    Output:
    Client1
    Client2
    Client3 (if any)
    Country1
    Country2 & so on...(if any)
    Overall1
    Overall2 & so on...(if any)

    Kindly any one can solve this while using formula in excel ASAP
  • To post as a guest, your comment is unpublished.
    Hammad Bin Idrees · 3 years ago
    Thank you so much. :)
  • To post as a guest, your comment is unpublished.
    Cayla · 3 years ago
    Hi,

    Will the suggested VBA code in the article work if the cells with the number of times is not next to the cells with the value? If not, is there a way to modify the VBA to get it to work?
  • To post as a guest, your comment is unpublished.
    SivaM · 3 years ago
    Thanks alott !!!!

    VB Macro worked for me and saved my 1 week time....

    Thanks Again..
  • To post as a guest, your comment is unpublished.
    JohnnyRocket · 4 years ago
    Like #John Said, is there a way to have a set range so you do not need to select. I am also trying to paste each individual Value into its own column. I have changed the line
    Set OutRng=OutRng.offset(xNum,0) to Set OutRng=OutRng.offset(xNum,1) in attempt to make it into columns
    I seem to be getting an error on the line OutRng.Resize(xNum,1).value=xValue even if I copy and paste the code from this site. Any help would be greatly appreciated.
  • To post as a guest, your comment is unpublished.
    Johnny · 4 years ago
    Thanks!! Saved me tons of time. Clever use of vlookup!
  • To post as a guest, your comment is unpublished.
    Ranjeet Ligade · 4 years ago
    Thanks Man ..!! This is working and saved my ample amount of time.
  • To post as a guest, your comment is unpublished.
    Ehsan · 4 years ago
    I used the first approach, it worked flawlessly, Thank you.
  • To post as a guest, your comment is unpublished.
    Narayan · 4 years ago
    Thanks Man!! Saved a lot of time. :-)
  • To post as a guest, your comment is unpublished.
    joe average · 4 years ago
    The VBA code breaks when there is a 0 value in the "number of times" column. Adjust the For Each loop as follows:

    For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    If xNum > 0 Then
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
    End If
    Next
  • To post as a guest, your comment is unpublished.
    John · 4 years ago
    Is there a way to have the macro remember the input and output destination so next time the macro is run you do not need to reselect?
  • To post as a guest, your comment is unpublished.
    Adam · 4 years ago
    Thank you for this, very easy to do and understand. I used to do this manually with 2600 cells.