If you are searching for the 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.
Download Practice Workbook
You can download the practice workbook from here:
6 Methods to Repeat Cell Values in Excel
In this section, I will show you 6 quick and easy methods to repeat cell values in Excel on Windows operating system. You will find detailed explanations of methods and formulas here. I have used 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.
- 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.
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.
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.
- First, insert values to repeat at the start cell.
- Then, select all of the cells of the column and go to the Home tab, and select 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.
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.
- First, call the first cell E5 in cell E6 to repeat by writing:
- Then, you will see a similar value in cells E5 and E6.
- Now, drag the Fill Handle icon to paste the similar cell reference respectively till the last cell where you want to repeat the value.
Again, you have to do the same thing for repeating another cel using cell reference.
- How to Repeat Rows at Top in Excel (3 Suitable Ways)
- [Fixed!] Excel Rows to Repeat at Top Feature Greyed Out
- How to Repeat Rows in Excel at Bottom (5 Easy Ways)
- Repeat Rows a Specified Number of Times in Excel (4 Easy Ways)
- Repeat Text in Excel Automatically (5 Easiest Ways)
4. Using AutoFill Feature
You can use the Autofill feature to repeat the cell value very easily. Follow the steps below to do this.
- 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.
5. Using IF Function
You can also use a formula to repeat cell values till a certain cell. Follow the below 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:
- Then, paste this formula into cell E6:
- Now, drag the Fill Handle icon to paste the used formula respectively 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.
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:
- 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.
In this article, you have found how to repeat cell values in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
- How to Repeat Rows in Excel When Printing (3 Effective Ways)
- How to Make a Pattern Repeat in Excel (8 Easy Ways)
- [Fixed!] Excel Rows to Repeat at Top Not Working (4 Solutions)
- How to Repeat Formula in Every nth Row in Excel (2 Easy Ways)
- How to Repeat Multiple Rows in Excel (4 Effective Ways)
- How to Find Repeated Numbers in Excel (5 Easy Methods)