This is an advanced REPT function tutorial but simple to follow. To repeat multiple columns N times, we can use Google Sheets REPT function. REPT is a Text function in Google Doc Spreadsheets which you can use in many ways. Here is one such example. With a custom formula using REPT, JOIN and SPLIT, we can repeat multiple columns or rows N times. Let’s begin.
Google Sheets: Formula to Repeat Multiple Columns N Times
We can learn this tips step-by-step. Let’s begin with a single cell repeat, then single column, then two cells in two columns and finally two or more entire columns.
Repeat Value in a Single Cell N Times in Google Sheets
The value in Column A is “Info Inspired”. Let’s see how to repeat it N times. Here also, you should follow a workaround to get the repeated text value in different columns or rows.
Here the first normal REPT formula repeats the text in A2, three times. But the second formula not only repeating the text three times but also adding a semicolon between them. The third formula follows the second formula but the additional Split function here split the result to separate columns by making use of the semicolon as delimiter. If you understand this concept, the following section will be easy for you.
Repeat a Column N Times in Google Sheets
Here we can use the above third formula for our purpose. But this time a column range. So it’s necessary to use the ARRAYFORMULA function to expand the result.
Repeating Multiple Columns N Times in Google Sheets
Just compare the below formula with the third formula in the first example. Here I’ve additionally placed Curly Braces to behave the formula as an Array. Because here we have two columns to repeat.
The same formula you can use to repeat multiple column range. Just changing the range from A2: B2 to A2: B3 will repeat three columns, the number of times you want. We can use this type of column repeating to automate column labeling. Finally, let us see how to repeat two entire columns multiple times in Google Sheets.
This is the same formula as the last example. Here we are repeating two columns, Column A and B, 3 times.
There are no changes here. Just copy and paste the formula into adjoining cells in the column as above.
That’s all for now. Hope you have enjoyed the advanced us of REPT function.
Great article – thanks for the context. Can you use it within a more complicated formula? I’m trying to use a sequence to return doubles of the same date. Can’t seem to get this to work…
Hi, Julian,
I think I can do that. If possible, make an example Sheet. Let me ‘VIEW’ your sample data and the result.
Best,
I am glad that you liked it.
Regarding your question you may please consider sharing a sample sheet and also on that sheet please manually enter the output you expect.
Thanks.
Hi Prashanth! Great Article, explained in a simple and clear manner! Quick question, how can I multiply using an array. Example: I have two columns.. in the Twitter, Wikipedia example of yours, next to it I have the number of times I want to multiply each twitter, wikipedia, etc… correspondingly… the result should be in one column only… how can I use one formula for this?