We have to repeat particular rows, columns, or cells often while working on a big data set on an Excel sheet. It is very time-consuming if we want to insert every piece of data manually. Rather, we can make it easier and less time-consuming at the same time by repeating the desired rows, columns, or cells in the Excel sheet. In this tutorial, we are going to learn how to perform Excel repeat text automatically. Here, we have an Excel Sheet having the monthly purchases of 4 people in January, where cell B4 represents Name, cell C4 represents Month and cell D4 represents Purchase.
Now, we want to insert their purchases for the very next month of February. Manually typing all the names will be more time-consuming. That’s why we are going to use some Excel magic to make this insert task easier.
Download Practice Workbook
1. Using REPT Function
In this tutorial, we are going to demonstrate how to repeat text in Excel automatically using the REPT function.
- First, you have to select a cell where you want to place the resultant value. Then simply type the formula given below.
- The REPT function in Excel also takes 2 arguments. The first one is a character or string that we want the function to repeat. We have inserted cell B5 as the first argument. This means we want the function to repeat the value in cell B5. The second argument is a numeric value that tells the REPT function how many times it should repeat the character or string. In this case, it is 1. So, the function will repeat the value of cell B5 just 1 time.
Suppose, I want to insert the name Adam in cell 9. That’s why, first, I selected cell C9. Then I selected cell B5 as text, I typed 1 as I wanted the name Adam to be repeated for once. Now, click the ENTER button and wait for the magic.
This is one of the easiest methods to repeat the text in any cell.
2. Using a Simple COPY-PASTE Method
We can also repeat a text or multiple texts in any cell by using the traditional Copy-Paste method.
2.1. Using Keyboard Shortcut CTRL C + CTRL V
By using this shortcut, we can repeat both single text or multiple texts at a time. Here, we are going to discuss repeating the multiple texts in an Excel Sheet.
- Let’s select the cells first we want to repeat. Here, we selected the cells from B5 to B8 and used the keyboard shortcut CTRL + C to Copy those texts.
- Now select any cell where you want to place repeated texts. Here, we selected cell B9.
- Then we are going to use the keyboard shortcut CTRL + V to Paste those texts.
2.2. Manual Copy-Paste
In this method, we are going to copy and paste the information manually instead of using keyboard shortcuts.
- First of all, we have to select the cells we want to repeat. Here, we selected the cells from B5 to B8 and clicked on the mouse’s right button. Then, select ‘Copy’ from there.
- Now select any cell where you want to insert repeated texts. Here, we selected cell 9 and again clicked on the mouse’s right button. From there, select the Paste option and wait for the magic.
Here, we can see that after clicking on the paste option all the texts selected before have been repeated from cell B9 to cell B12.
- How to Find Repeated Numbers in Excel (5 Easy Methods)
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
- How to Repeat Formula in Excel for Whole Column (5 Easy Ways)
3. Repeat Text Automatically Using Keyboard Shortcut CTRL + ENTER
We usually use this method to repeat the same text multiple times in any particular rows or columns. Like here, we have a report on the Purchase Number of customers for January. It is very time-consuming to fill the word January in the Month Section manually. Even the Copy-Paste method will take a huge time to have this work done. That’s why we will use a keyboard shortcut here.
- First, we have to select our desired cells at a time when we want to insert the data. Here, we select the cells from C5 to C8 at a time.
- Then type January in the C5 cell.
- Finally, click CTRL + ENTER and wait for the magic.
Here, we can see that immediately after clicking CTRL + ENTER all the cells from C5 to C8 are filled with January.
4. Repeat Text Automatically from One Sheet to Another
We can also repeat a text or multiple texts from one working sheet to another. Like here we want to repeat the Name section from Introduction Sheet to Repeat in Another Sheet. For this, in the new sheet (Repeat in Another Sheet), first, we have to select a cell where we want to repeat the texts. Here B3 is our desired cell to repeat the text. And here we have to apply a very simple but convenient function.
- First, select cell B3. Then click Equal (=) Sign.
- Next, go to the working sheet from where you wanted to repeat the text. Here, I am going to repeat the text (Adam) from the Introduction Sheet. We will see that Excel has automatically created the following formula in cell B5 (Repeat in Another Sheet) for us to populate the cell with the value of cell B5 of the Introduction sheet.
- Then I have to click on the B5 cell to select Adam. Finally, click ENTER and wait for the magic.
Immediately after clicking on the ENTER button, we get our desired text Adam at our new working sheet in Excel.
Now dragging it down, we can easily repeat all other texts in the Name section from the Introduction Sheet.
5. Repeat Text Automatically Using AutoFill
This is one of the easiest methods to repeat the text in our desired cells. If we consider our Excel sheet here and want to repeat January in the Month section.
- First, we have to select cell C5.
- Now drag it to cell C9.
- Now type CTRL + D and wait for the magic.
Here, immediately after clicking that keyboard shortcut, we can see that all the cells are filled with the same text.
Things to Remember
In case you want to repeat a text multiple times in a particular cell, in the Repeat function, enter the number (you want to repeat actually) after the text.
I’ve given a practice sheet in the workbook to practice these explained ways to repeat text automatically in an Excel Sheet. You can download it from the link given above.
In this article, I have tried to explain 4 different ways to repeat a text in Excel. Note that, you can also repeat multiple texts in Excel, even on any different sheet by just following the same methods. Last but not least, I will be extremely grateful if you comment down below any of your suggestions, ideas, or feedback.