How to Repeat Cell Values in Excel (6 Quick Methods)

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.

📌 STEPS:

  • Rightclick on the cell to open the context menu and select the Copy.

How to Repeat Cell Values in Excel

  • Then, select the cells where you want the repeat the value and rightclick on the mouse to open the context menu.
  • Then, select the paste option to paste the value to the cells.

How to Repeat Cell Values in Excel

  • Similarly, do the same thing to other cells to repeat another value.

How to Repeat Cell Values in Excel

Read More: How to Repeat Rows in Excel Based on Cell Value (4 Easy Ways)


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.

How to Repeat Cell Values in Excel

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.

📌 STEPS:

  • 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.

How to Repeat Cell Values in Excel

  • Then, a new window named Go to will open. Click on the button named Special here.

How to Repeat Cell Values in Excel

  • As a result, a new window named Go To Special will open.
  • Select the Blanks option and press OK.

How to Repeat Cell Values in Excel

  • Now, while selecting the only blank cells, write “=E5” in cell E6.
  • And, hit Ctrl + Enter button on the keyboard.

How to Repeat Cell Values in Excel

  • As a result, all of the blank cells are filled with repeating the first cell.

How to Repeat Cell Values in Excel

Read More: How to Repeat Cell Value X Times in Excel (6 Easy Methods)


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

Using Cell Reference Only

  • 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.

Using Cell Reference Only

Again, you have to do the same thing for repeating another cel using cell reference.


Similar Readings


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.

Repeat Text Automatically Using AutoFill Feature

  • Then, if you want to repeat another cell value, drag the fill handle icon again.

Repeat Text Automatically Using AutoFill Feature


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.

Using a Formula

  • Then, use a cell reference to insert the first cell value. Write this in cell E5:
=F5

Using a Formula

  • Then, paste this formula into cell E6:
=IF(F7="",E6,F7)

Using a Formula

  • 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.

Using a Formula

Read More: How to Repeat Formula in Excel for Whole Column (5 Easy Ways)


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.

Using VBA Code

  • 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.

Using VBA Code

  • 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

Using VBA Code

  • 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.

Using VBA Code

  • In the Macros window, select the macro and press the Run button.

Using VBA Code

  • As a result, you will see the blank cells are filled with repeated cell values.

Using VBA Code


Conclusion

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.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo