How to Repeat Text in Excel and Google Sheets

Spreadsheets are good at repeating things, but the method depends on what you need. Do you want one cell containing the same word many times? Many cells each containing the same value? Or numbered rows like Item 1, Item 2, Item 3? Each has a different answer, and none of them takes long once you know it.

Method 1: REPT, for repeating text inside one cell

Both Excel and Google Sheets have a function called REPT that does exactly what the name suggests. It takes the text and the number of times to repeat it:

=REPT("ha", 5)

That returns hahahahaha. To put a separator between copies, include it in the text you repeat:

=REPT("ha, ", 5)

This gives ha, ha, ha, ha, ha, with a stray separator at the end. Wrap it in TRIM to remove the trailing space, though the comma will remain, so you may prefer to remove the last character with LEFT and LEN:

=LEFT(REPT("ha, ", 5), LEN(REPT("ha, ", 5)) - 2)

You can also point at a cell instead of typing the text: =REPT(A1, 10) repeats whatever is in A1.

A limit to remember: a single cell can hold 32,767 characters in Excel, and REPT returns an error if the result would be longer than that. Google Sheets has its own cell limit too, so very large repeats should be split across cells.

Method 2: Fill the same value into many cells

If you want the same value in many cells rather than repeated inside one, there are three quick routes.

Drag the fill handle. Type the value in a cell, select it, and drag the small square at its corner down. In Excel, hold Ctrl while dragging if it increments numbers when you don’t want it to. This is fine for a handful of rows and awkward for hundreds.

Select a range and fill. Type the value in the first cell, select the whole range you want to fill, and press Ctrl+D to fill downward. This is the best way to fill an exact number of rows without dragging.

Paste a block. This is the quick trick. Generate the repeated lines with the line repeater, copy them, click the first cell and paste. Because each line becomes its own row, you get exactly the number of rows you asked for. It’s particularly handy when the text is long, or when you want numbering or a prefix on every row.

Method 3: Numbered or patterned rows

For labels like Item 1, Item 2, Item 3, use a formula that builds the number into the text.

In Google Sheets, one formula can create a whole column:

=ARRAYFORMULA("Item " & SEQUENCE(20))

In recent versions of Excel the same idea works because SEQUENCE spills across cells:

="Item " & SEQUENCE(20)

In older versions of Excel, put Item 1 in a cell, select it and drag the fill handle, and Excel will increment the number automatically.

If you’d rather skip formulas, paste a block from the text repeater with numbering switched on. Use a format such as Item {n} and set your starting number.

Method 4: A new line inside one cell

Sometimes you want repeated text stacked inside a single cell, one copy per line. Use CHAR(10), the line-feed character, as the separator and turn on text wrapping in the cell:

=REPT("Line" & CHAR(10), 5)

In Excel use Wrap Text from the Home tab; in Google Sheets use the wrap option in the toolbar or Format menu.

Method 5: Repeating a whole pattern of cells

To repeat a pattern of values, such as A, B, C, A, B, C, type the first pattern and drag the fill handle with the whole pattern selected. Excel and Sheets continue the cycle. For long patterns it’s often faster to generate the sequence as text elsewhere and paste it in.

Common problems

The result shows as one line. If you used CHAR(10) and see one run-on line, wrapping is off. Turn it on.

Numbers change when you drag. Excel treats a single number as a series to increment. Hold Ctrl while dragging, or use the Fill Series options after dragging to choose Copy Cells.

The pasted block lands in one cell. If you copy from a web page and your text lands in a single cell, check that each repeat is on its own line, and paste into a cell rather than into the formula bar. Pasting while editing a cell keeps everything inside that cell.

There’s a stray blank row at the bottom. The text ended with a line break. Delete the extra row, or remove the break in the source before copying.

Cleaning up after you’ve repeated

If a list ends up with repeats you didn’t intend, both programs can help. Excel has Data > Remove Duplicates, and Sheets has a Remove duplicates option in the Data cleanup menu. For plain text lists outside a spreadsheet, remove duplicate lines does the same job. We’ve written a full guide: how to remove duplicate lines from a list. To alphabetise afterwards, use sort lines.

Which method should you use?

For repeated text inside a single cell, use REPT. For the same value in many cells, fill down or paste a block. For numbered items, use SEQUENCE where it’s available, or a block with numbering. And whenever you want something specific, such as a prefix, quotes or a custom separator, generate it with a text repeater and paste it in. It’s often the shortest route.