When creating spreadsheets in Google Sheets, you often have to repeat the same actions and inputs over and over again. There are several ways you can automate your work in Google Sheets with formulas, macros, and add-ons.
One such form of improving your workflow is the Google Sheets repeat formula. The REPT function in Google Sheets is an easy way to automate the process of repeating a formula in cells for a set number of times.
In this article, we will explain the usage of the REPT function with examples and pictures. Read on to up your spreadsheet skills with this useful function.
What Is the Google Sheets Repeat Formula? (REPT Function)
The REPT function in Google Sheets allows users to repeat given text a specified amount of times. Using REPT alone isn’t too helpful in Google sheets. However, combining its functionality with functions like ARRAY, COUNTIF and LEN can produce excellent results.
REPT Google Sheets Syntax
The syntax of the REPT formula is:
- text-to-repeat is the cell reference or the string you wish to repeat
- number-of-repetitions is the number of times you want to repeat the text. Note that the text will be repeated in the cell in which the formula was applied in.
A simple example to get you started with learning the REPT formula is to take a word and utilize the REPT formula to repeat it multiple times in a cell. The REPT formula used below to repeat the word “Hi” five times is:
=REPT("Hi ", 5)
You may notice the space after the “Hi ” — This is to ensure there is a space between the words in the output cell.
Things to Know About theGoogle Sheets Formula to Repeat Cell Values
Here are a few things you need to keep in mind when using the REPT function inside Google Sheets.
- When using the REPT function or formula in your sheets, the output value is always a string rather than a numeric value which means you cannot input this value into many formulas.
- Setting the repetitions to zero in the formula can cause the cell to have an empty string.
- Setting the repetitions to -1 can result in a value error in Google Sheets
- If you desire to have a space between the strings being repeated, then you need to add the space at either the end of the start of your input string. For example, “Hi!” would become ” Hi! “
- The number-of-repetitions can’t be more than the character limitation of a cell. This limit is 32,000 characters. If the limit is crossed, then you will get a value error.
- The range can be repeated by enveloping the REPT function inside an ARRAY function or formula.
Repeating Values in Different Cells Using REPT
Sometimes, you don’t want a text repeated in the same cell 10 times as it can look ugly and disrupt the formatting of your spreadsheet. Instead, you may want to repeat the value or string across a different range of cells. You can do this using a modification of the REPT, SPLIT, and the TRANSPOSE formula.
Assuming that we have the text value in cell A1, and the number-of-repetitions required in cell B1, we can repeat the values in different cells using the following formula:
=TRANSPOSE( SPLIT( REPT( A1&"♕",B1 ),"♕" ))
Here, we see that the word “sample” is output 5 times into a column. If you want to achieve the same results for a row, remove the transposed part of the formula.
The symbol for Queen “♕” acts as a special identifier that can be used by SPLIT to divide the repeating strings. There are many other ways to use the delimiter part of the split formula, we’d recommend reading up on the SPLIT formula to learn more if it’s confusing.
You can achieve similar results using SEQUENCE as well. The syntax for that will look something like this:
Here, the variation utilizes SEQUENCE to display an array of 1, 2, 3, 4, 5. TEXT is used to trick all of the array numbers to become empty strings. We can then use the value from the cell A1 to replace the values in the repeating cells. The ArrayFormula is then used to ensure that the output is an array.
Repeating Images in Different Cells Using REPT
This method uses a lot of the syntax used in the previous formulas. The formula to return images in Google Sheets cells in a row will look something like this:
You can input any link to an image, and Google Sheets will grab the image from the internet and add it. This is very useful for adding logos into your spreadsheets.
Logical Formulas Using REPT
The REPT formula can sometimes be substituted for an IF formula in specific conditions.
It’s quite simple to show whether or not data meets true or false conditions using the REPT formula.
Let’s take a look at an example to explain further.
In this example, we will use a formula to identify the values over $500. The formula used in the example is as follows:
=REPT("Yes",A2 > 500)
This formula utilizes logical text checking whether the values in the Cost cells are greater than $500. This repeats the specified text of “Yes” using the REPT formula if the value in cell A2 is greater than 500. If the value is equal to or less than $500, the test is false the REPT formula outputs blank strings in the cells.
REPT Function Uses
Now that you have a basic understanding of the REPT function, we can take a look at some more fun and useful ways you can use it.
Bar Chart Using REPT Function
This essentially works by repeating a character multiple times. You can do this by putting the character code in the REPT formula to create several instances of that character. The syntax for the formula of creating picture bar charts with the REPT function is:
To make a bar chart, we used the Unicode number 10074, which is used to create a heavy vertical bar. So to make a simple bar chart, we used the following formula:
If you’re feeling creative, feel free to use any emoji to make a bar chart. Look up the unicode for the emoji or icon you wish to use. We made one using the cat emoji.
Padded Strings With REPT Function
You can also utilize the REPT function to pad out the text strings in a cell. The function will use the value added to the number-of-repetitions part of the formula to add a varying number of Underscore symbols to the ending part of the string. The syntax for the formula should look something like this:
To ensure that all text strings have equal width, you need to utilize a font that has equal letter spacing for all the letters. Although this approach does work, it’s not that efficient compared to using number formatting as it preserves the original value of the number rather than simply making it into a string.
We’d recommend just heading to the number formatting menu through Format>Number>Custom number format instead.
Frequently Asked Questions
How Do You Repeat the Same Formula in Google Sheets?
The method of how to repeat a formula in Google Sheets with the REPT formula is more complicated than you’d expect. You can fill the cells by inputting the formula as the text-to-repeat argument. But, there is a much easier way.
You can easily repeat a formula in Google Sheets:
- Select the cell with the formula you wish to repeat
- Hold Shift and press the down arrow repeatedly until all the cells are selected
- Let go of the Shift key and press Ctrl + D to apply the formulas down the column. The shortcut Ctrl + R is how to repeat in rows in Google Sheets.
What Is REPT in Google Sheets?
You can use the REPT function to repeat a string or an expression a set number of times. It can fill cells with the same names of logical formulas that can be applied to do more complex tasks.
Can You Auto-Fill in Google Sheets?
Auto-fill is possible in Google Sheets. All you have to do is:
- Open the spreadsheet and type the text you want to auto-fill in a row or a column (type in the values for at least two rows or columns and ensure both values are written beside each other.)
- Highlight the cells. You should see a blue glow around the boxes which indicates that the boxes are selected
- Use the Fill handle to drag the desired data to into other cells.
How Do You Do a Sequence in Google Sheets?
The sequence function allows you to create an array of sequential numbers found in the Mathematical functions in Google Sheets. You can access them by clicking on the Insert button on the top bar, heading over to Function, and clicking on Math.
Using the REPT Function
By now, you should have more knowledge about the usage of the Google Sheet repeat formula and how to use it in your spreadsheets. If you have any more questions, feel free to let us know in the comments.
Make sure you check out our other spreadsheet tutorials, we have plenty of offers to boost your skills to god-tier.
- Free Trucking Expenses Spreadsheet