In this Excel tutorial, you will learn:
– How to redo actions
– How to repeat cell values
– How to repeat patterns
– How to repeat formulas using the Fill Handle
– How to repeat the heading row at the top of every sheet while printing
While preparing this article, we used Microsoft 365 for applying all operations, but they are also applicable in all Excel versions from Microsoft Office 2016 onwards.
The Repeat feature in Excel is a handy tool that allows you to duplicate values, formulas, or formatting across multiple cells, rows, or columns. When you have a pattern, you can apply this to adjacent cells by using the repeat feature. You do not need to apply the same pattern all the time manually.
In the overview image, we are using the REPT function to repeat the value of cell B6 to cell B14.
⏷Repeat Action in Excel
⏵Apply Keyboard Shortcuts
⏵Use Repeat Command
⏵Apply Format Painter Feature
⏷Repeat Cell Values in Excel
⏵Apply Copy-Paste Method
⏵Apply REPT Function
⏷Repeat a Character in Excel
⏷Repeat Pattern in Excel
⏵Apply SEQUENCE Function
⏵Use Fill Handle Tool
⏷Repeat a Formula in Excel
⏷Repeat Heading Row at the Top of Every Sheet While Printing
⏷Frequently Asked Questions
⏷Repeat in Excel: Knowledge Hub
How to Repeat Action in Excel
We can apply keyboard shortcuts, the Repeat command button, or the Format Painter feature to repeat an action in Excel.
1. Applying Keyboard Shortcuts to Repeat Action in Excel
Here is a list of keyboard shortcuts for different operating systems that may be used to redo actions in Excel.
| Operating System | Keyboard Shortcut |
|---|---|
| Windows | F4 or CTRL+Y |
| Mac | Command+Y |
- First, insert a row manually. Here we insert a row whose row number is 7.
- Now, select the 7th row and then press F4.
- You will see another row has been created on the 8th number.
2. Using the Repeat Command to Repeat Action in Excel
You can use the Repeat command button to repeat an action in Excel. You can find this option in the title bar of your Excel window.
However, if this button is missing in the title bar, you can add it from the Quick Access Toolbar options.
- Go to the File tab.
- Click Options from the left-side pane.
- In the Excel Options dialog box, click the Quick Access Toolbar >> set the Choose commands from option to Popular Commands >> scroll down and select the Repeat command >> click the Add button >> click the OK button.
Click the image to enlarge it
- Now, you will have the Repeat command button in the title bar of your Excel window.
- To demonstrate how the Repeat command button works, we will insert a new row in our worksheet.
- Select any row number in the Row Bar >> right-click >> select the Insert option. This will insert a new row.
- Now, select any row number in the Row Bar again and click the Repeat button. This will repeat the previous task and insert another row.
3. How to Apply Format Painter Feature to Repeat Action in Excel
If you want to repeat any cell formatting action you can use the Format Painter feature to repeat that action. Consider the following dataset. We have a list of Employee IDs, Names, and Salaries.
Let’s assume, you want to highlight specific cells from this dataset using a fill color.
- Select a target cell (e.g. cell C6) >> click the Fill Color dropdown >> select a suitable color.
Now, instead of repeating the previous step for each target cell, you can apply the Format Painter feature to repeat the actions of the previous step.
- Select cell C6 >> Double-click the Format Painter button multiple times.
- Now click on the cells where you want to repeat the “applying fill color” action. You will get the same fill color in the target cells as well.
How to Repeat Cell Values in Excel
You can apply the Copy-Paste method or the REPT function to repeat cell values in Excel.
1. Applying the Copy-Paste Method to Repeat in Excel
- Select range B6:B10 then press CTRL+C to copy.
- Select another cell and then press CTRL+V to paste the copied cells.
2. Applying the REPT Function to Repeat in Excel
We can easily repeat cell values in another cell by using the REPT function. This function can repeat values several times a certain number of times mentioned in the formula.
=REPT(text, number_times)
- Select cell B14 then apply the below formula:
=REPT(B6,1)
You can see that the value of cell B6 is repeated in cell B14. Then, use the AutoFill feature to repeat the cell values to other cells.
Note:
- The syntax of the REPT function is REPT(text, number_times). The number_times field should be zero or a positive integer, otherwise, REPT will return a #VALUE error.
- If number_times is zero in the REPT function, it’ll return an empty string (“”).
- The REPT function can repeat numbers, but the result is text.
How to Repeat a Character in Excel
We can use the REPT function to repeat a character a certain number of times as well. Consider the following dataset. We have marks (out of 100) of a few students here. We will calculate the ratings for each student and the ratings will be measured using the star (★) character. A student can get a maximum of 5 stars (★★★★★).
- Apply the following formula in cell E6 >> press the Enter key >> drag down the Fill Handle icon.
=REPT(UNICHAR(9733),ROUND((D6/100)*5,0))
Here, the UNICHAR function is used for inserting the star (★) character. UNICHAR(9733) refers to the star (★) character. The ROUND function is used for rounding the number to an integer that decides how many times the character will be repeated.
How to Repeat Pattern in Excel
Here, we will discuss two methods to repeat a pattern in Excel: applying the SEQUENCE function and using the Fill Handle tool.
1. Applying the SEQUENCE Function to Repeat a Pattern in Excel
We can repeat a pattern in Excel by using the SEQUENCE function. This function returns a sequence. By using this function, we will get the Student ID sequentially in our dataset.
=SEQUENCE(rows, [columns], [start], [step])
- Select cell B14 and then apply the following formula:
=SEQUENCE(5,1,B6,1)
You can see that the value of range B6:B10 is repeated on range B14:B18.
2. Using the Fill Handle Tool to Repeat a Pattern in Excel
- We can use Excel’s Fill Handle tool to replicate the sequence in cells B14 and B15.
- Select the cells then drag down the Fill Handle to cell B18.
- You will get the Student IDs serially like the image below.
How to Repeat a Formula in Excel
- Excel formulas can be repeated easily by using Fill Handle.
- Write the formula in cell B7, then drag down the Fill Handle up to cell B10.
=B6+1
- In the image below we selected cell B10. You can see that the formula has been replicated.
How to Repeat Heading Row at the Top of Every Sheet While Printing
You can repeat the heading row at the top of every sheet while printing by freezing rows. See the below section for details.
- Go to the Page Layout tab from the ribbon.
- Then under the Page Setup category, click on the tint arrow icon to open the Page Setup dialog.
- Page Setup dialog box will appear before you.
- From the Sheet menu, you will get the Rows to repeat at top option.
- We insert $1:$5 on the corresponding box.
- Then click on Print.
- A print window will appear, and the top rows of page 1 will look like the image below.
Click the image to enlarge it
- Go to the second page and you can see that the rows are still on the top.
Click the image to enlarge it
What Things Do You Have to Remember?
- Double-check the range when you want to use the Autofill A wrong selection can lead to undesired results.
- In case you make a mistake, it’s helpful to have a backup of your data or to use the Undo feature (Ctrl+Z) to revert changes.
Frequently Asked Questions
1. What is the shortcut for repeating formulas in Excel?
Answer: There are several ways to replicate formulas in Excel. You can press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
2. How do you repeat a cell 4 times in Excel?
Answer: You can replicate cell values 4 times in Excel using the REPT function. Enter the value you want to recount in a cell (let’s assume it’s in cell A1). In another cell, enter the following formula:
=REPT(A1, 4)3. Can I repeat a cell value horizontally instead of vertically?
Answer: Yes, you can repeat a cell value horizontally instead of vertically. Instead of dragging the Fill Handle vertically, you can drag it horizontally to repeat the cell value in the desired direction.
Download Practice Workbook
Conclusion
This concludes our tutorial on repeat in Excel. We discussed repeating cell values, patterns, actions, characters, and formulas. We applied the REPT and SEQUENCE functions as well as some Excel features and keyboard shortcuts.
I hope this article was helpful to you. If you have any queries, or feedback regarding this article, please don’t forget to share them in the comment box.
Repeat in Excel: Knowledge Hub
- Repeat Text in Excel Automatically
- Repeat the Number Pattern
- Repeat Formula Pattern
- Repeat Formula for Whole Column
- Repeat Formula for Each Row
- Repeat Formula in Every nth Row
- Repeat Cell Values in Excel
- Repeat Cell Value X Times
- Repeat Multiple Rows
- Repeat Rows Based on Cell Value
- Repeat Rows at Top in Excel
- Repeat Rows in Excel at Bottom
- Repeat Rows at Top of Specific Pages
- Repeat Rows When Scrolling
- Repeat Header Row When Scrolling
- Repeat Rows When Printing
- Repeat a Pattern in Excel
- Find Repeated Cells
- Find Repeated Numbers
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
- [Fixed!] Excel Rows to Repeat at Top Not Working
- [Fixed] Repeat Last Action Not Working
