Osman Goni Ridwan
If you are searching for a solution or some special tricks to repeat cell values in Excel then you have landed in the right place. There are 6 or more ways to repeat cell values in Excel. This article will show you each and every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the main part of the article.
How to Repeat Cell Values in Excel: 6 Quick Methods
In this section, I will show you 6 quick and easy methods to repeat cell values in Excel on the Windows operating system. You will find detailed explanations of methods and formulas here. I have used the Microsoft 365 version here. But you can use any other versions as of your availability. If any methods don’t work in your version then leave us a comment.
1. Copy Cell Values and Paste Directly
We can easily repeat a text or numbers in any cell by using the traditional Copy-Paste method.
📌 STEPS:
- Right–click on the cell to open the context menu and select the Copy.
- Then, select the cells where you want the repeat the value and right–click on the mouse to open the context menu.
- Then, select the paste option to paste the value to the cells.
- Similarly, do the same thing to other cells to repeat another value.
Read More: How to Repeat Rows in Excel Based on Cell Value
2. Using Keyboard Shortcut to Repeat Cell Values
There are some keyboard shortcuts in Excel that will allow you to repeat cell values. I will show you two ways here.
2.1 Press Ctrl+C and Ctrl+V
You can use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste. Select the cell first and press Ctrl+C to copy. Then, select the other cells where to paste the value and press Ctrl + P.
By this method, you have to select manually to copy and paste repeating values in Excel. It’s an easy method but you may find it clumsy for large datasets.
Read More: [Fixed] Repeat Last Action Not Working in Excel
2.2 Combine Go To Special Feature and Ctrl+Enter Shortcut
You can use Ctrl + Enter shortcut with a cell reference to repeat values automatically till a new value is reached. But, when a new value comes, it will repeat that value automatically by this method. Follow the below steps to implement this method.
📌 STEPS:
- First, insert values to repeat at the start cell.
- Then, select all of the cells of the column, go to the Home tab, and select the Go To option.
- Then, a new window named Go to will open. Click on the button named Special here.
- As a result, a new window named Go To Special will open.
- Select the Blanks option and press OK.
- Now, while selecting the only blank cells, write “=E5” in cell E6.
- And, Hit Ctrl + Enter button on the keyboard.
- As a result, all of the blank cells are filled with repeating the first cell.
Read More: How to Repeat Cell Value X Times in Excel
3. Using Cell Reference
You can use only the cell reference method to repeat the cell values. But it will not become an automatic method. You will have to drag the fill handle icon to the cell where you want to repeat it. Follow the steps below.
📌 STEPS:
- First, call the first cell E5 in cell E6 to repeat by writing:
=E5
- Then, you will see a similar value in cells E5 and E6.
- Now, drag the Fill Handle icon to paste the similar cell references to the last cell where you want to repeat the value.
Again, you have to do the same thing for repeating another cell using cell reference.
Similar Readings
- How to Repeat Rows at Top in Excel
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
- How to Repeat Rows in Excel at Bottom
- How to Repeat Rows at Top of Specific Pages in Excel
4. Using AutoFill Feature
You can use the Autofill feature to repeat the cell value very easily. Follow the steps below to do this.
📌 STEPS:
- First, click on the cell that you want to repeat.
- Then place the cursor on the right-bottom corner of the cell and the cursor will become a Plus icon which is called the Fill Handle.
- Drag the fill handle icon to the last cell of repetition.
- As a result, the cell value is repeated to the other cells.
- Then, if you want to repeat another cell value, drag the fill handle icon again.
Read More: How to Make a Pattern Repeat in Excel
5. Using IF Function
You can also use a formula to repeat cell values till a certain cell. Follow the below steps.
📌 STEPS:
- First, insert the repeating values in the adjacent cells of starting the repetition.
- Then, use a cell reference to insert the first cell value. Write this in cell E5:
=F5
- Then, paste this formula into cell E6:
=IF(F7="",E6,F7)
- Now, drag the Fill Handle icon to paste the used formula to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
- So, you will get the repeated cell values in the column as your command.
Read More: How to Repeat Formula in Excel for Whole Column
6. Repeat Cell Values Using a VBA Code
Alternatively, you can use a VBA macro code to repeat values in Excel. Follow the steps below to do this:
📌 STEPS:
- For this, first, go to the top ribbon and press on the Developer then press on the Visual Basic option from the menu.
You can use ALT + F11 to open the ‘Microsoft Visual Basic for Applications” window if you don’t have the Developer tab added.
- Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.
- Now, a new “Module” window will appear. And Paste this VBA code into the box.
Sub repeatvalues()
Dim Ran As Range
Dim Rows As Long, Cols As Long
Dim Row As Integer, Col As Integer
Set Ran = Selection
Cols = Ran.Columns.CountLarge
Rows = Ran.Rows.CountLarge
For Col = 1 To Cols
For Row = 1 To Rows - 1
If Ran.Cells(Row, Col) <> "" Then
Ran.Cells(Row, Col) = Ran.Cells(Row, Col).Value
If Ran.Cells(Row + 1, Col) = "" Then
Ran.Cells(Row + 1, Col) = Ran.Cells(Row, Col).Value
End If
End If
Next Row
Next Col
End Sub
- After inserting the code, go back to the worksheet and select the cells where you want to repeat.
- Then, go to the top ribbon and select Developer >> Macros.
- In the Macros window, select the macro and press the Run button.
- As a result, you will see the blank cells are filled with repeated cell values.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, you have found how to repeat cell values in Excel. I hope you found this article helpful.
Please, drop comments, suggestions, or queries if you have any in the comment section below.
