Excel Copy and Insert Row Multiple Times

To copy a row and insert it multiple times in Excel, you can use a simple copy-paste method, but for dozens or hundreds of repetitions, a fill-series trick or a short macro is far more efficient. This article explains the manual steps, the faster fill handle method, and when to use a VBA macro, plus common pitfalls and answers to frequent questions.

Formula

The calculator above estimates the total number of rows you will have after inserting n copies of a block that originally has r rows. The formula is simply:

Total rows = n × r

Where:

• n = number of copies you want to insert (a positive integer)
• r = number of rows in the original block (1 for a single row, more for a multi-row block)

This is useful for planning how many rows your sheet will grow by, especially when you are about to repeat a section many times.

Worked Example

Suppose you have a single row (r = 1) and you want to insert 5 copies (n = 5). Using the formula:

Total rows = 5 × 1 = 5

So after the operation, you will have 5 rows where you previously had 1. If you were copying a 3-row block (r = 3) and wanted 4 copies (n = 4), the total would be 4 × 3 = 12 rows.

Manual Copy and Insert (Small Numbers)

For a handful of copies, the classic method works fine:

1. Select the row (or rows) you want to copy by clicking the row number.
2. Press Ctrl+C to copy.
3. Right-click the row number where you want the copies to go (the row just below the original, for example).
4. Choose Insert Copied Cells from the context menu.
5. Repeat for each copy, or press Ctrl+Y to repeat the last action.

This is fine for 2–10 copies. For more, it becomes tedious and error-prone.

Fill Handle for Many Copies

When you need 20, 50, or 100 copies, use the fill handle trick:

1. Select the row(s) you want to copy.
2. Hover over the small square in the bottom-right corner of the selection (the fill handle) until the cursor becomes a thin cross.
3. Hold Ctrl (the cursor gains a small plus sign) and drag down as many rows as you need.
4. Release the mouse and then release Ctrl.

This copies the values and formatting from the original row(s) into the new rows. It works for text, numbers, and formulas (formulas adjust relative references). It does not, however, copy row height or certain conditional formatting rules perfectly.

Using a VBA Macro for Large Repetitions

If you need to insert hundreds or thousands of copies, a macro is the cleanest solution. Here is a simple macro that copies a selected row and inserts it a specified number of times below:

1. Press Alt+F11 to open the VBA editor.
2. Go to Insert > Module.
3. Paste the following code:

Sub InsertRowMultipleTimes()
    Dim r As Long, n As Long, i As Long
    r = ActiveCell.Row
    n = InputBox("How many copies?")
    For i = 1 To n
        Rows(r).Copy
        Rows(r + 1).Insert Shift:=xlDown
    Next i
    Application.CutCopyMode = False
End Sub

4. Close the editor, select any cell in the row you want to copy, and run the macro (Alt+F8, then Run).

This macro copies the row where the active cell is and inserts a copy directly below it, repeating n times. It preserves formatting and formulas. For multi-row blocks, modify the range accordingly.

Common Mistakes and Pitfalls

• Forgetting to hold Ctrl when using the fill handle – this will overwrite existing data instead of inserting.
• Inserting at the wrong location – always right-click the row number where you want the new rows to appear; Excel inserts above that row.
• Not copying row height – the fill handle method does not copy row height; use the manual insert method or set row height afterward.
• Relative formulas shifting unexpectedly – when you insert copies, formulas in other rows may adjust. Check that references are correct.
• Overwriting data – if you drag without Ctrl, Excel overwrites cells below. Always verify the destination is empty.

Alternatives: Power Query and Formulas

If you need to repeat rows as part of a data transformation, consider Power Query. You can create a query that duplicates rows by adding an index column and then expanding a list of numbers. This avoids manual copying entirely and is repeatable if your data changes.

Another alternative is to use a formula with INDEX and ROUNDUP to create a repeated list in a helper column, then copy the result as values. This works well for static data but is less intuitive for a quick one-off task.

Frequently Asked Questions

How do I insert a row multiple times without using the mouse?

Select the row, press Ctrl+C, then press Ctrl+Shift+= (the plus sign) to open the Insert dialog. Choose “Entire row� and press Enter. Then press Ctrl+Y to repeat as many times as needed. This is faster than right-clicking.

Can I copy and insert multiple rows at once?

Yes. Select several contiguous rows (e.g., rows 2–4) before copying. When you insert, Excel inserts that whole block as many times as you repeat the action. The fill handle method also works for multi-row blocks.

Why does my fill handle copy values but not formatting?

The fill handle normally copies formatting as well. If it does not, check that you are holding Ctrl (which forces copy mode) and that the source cells have the formatting you expect. Also, if you drag over a range that already has formatting, Excel may not overwrite it.

Is there a way to repeat a row 1000 times without a macro?

You can use the fill handle with Ctrl and drag down 1000 rows, but that is tedious. A faster no-macro method is to use a formula in a helper column, then copy and paste as values. For example, enter =INDEX(A:A,ROUNDUP(ROW()/n,0)) in a new column and drag down.

Does inserting rows break my formulas?

It can. If your formulas reference ranges that shift, Excel adjusts them automatically when you insert rows. However, if a formula uses a fixed range like =SUM(A1:A10), inserting a row inside that range expands the sum automatically. Inserting outside the range does not affect it. Always double-check after inserting.

Can I undo a mass insert?

Yes, press Ctrl+Z immediately after the insert. If you have done many other actions since, you may need to undo several steps. For macros, undo does not work – so save your workbook before running a macro that inserts many rows.

Related

• Best Ways to Delete Temp Files on Mac
• Recover Files or Windows from a Macrium Reflect 8 Image
• Outlook Show Two Time Zones: How to Set It Up

in n | Number of copies | - | 5 | 1
in r | Rows in source block | - | 1 | 1
out total | Total rows after insert | - | n * r | 0