How to Repeat Rows in Excel at Bottom: 5 Easy Methods

Method 1 – Use of Fill Handle Icon to Repeat Rows in Excel at Bottom

Steps:

  • Select the row up to which cell you want to repeat the values. We selected the B10:D10
  • Drag the Fill Handle icon to repeat the range values respectively to the other rows.

You will see the repeated Rows.

How to Repeat Rows in Excel at Bottom

 


Method 2 – Employing Fill Feature to Repeat Rows in Excel at Bottom

Steps:

  • Select the data range, including the blank Rows for which you want to repeat the values. Here, I have selected the B10:D14
  • From the Home tab >> you need to go to the Editing Command. You can use Excel keyboard shortcuts Alt+H to go to the Home tab.
  • From the Fill feature >> you have to select the Down option.

See the following result with repeated row values.

How to Repeat Rows in Excel at Bottom

 


Method 3 – Applying Header & Footer Command for Repeating Rows at Bottom

Steps:

  • Open your worksheet.
  • From the Insert tab >> go to the Text option >> Choose the Header & Footer feature.

See the following changes.

How to Repeat Rows in Excel at Bottom

  • Click on the Add footer.
  • Write “ List of Cakes ”. Made it Bold and increased the Font size from the Home tab.

  • Click on the Right Footer box to include more information.
  • From the Header & Footer ribbon >> choose Current Date.

How to Repeat Rows in Excel at Bottom

The First Footer box, I will add a picture.

  • Select the First Header
  • From the Header & Footer ribbon >> go to Picture.

See a dialog box named Insert Pictures.

  • Choose your preferred way. We chose From a file.
  • Choose the Picture file.

How to Repeat Rows in Excel at Bottom

The Row will repeat on all pages at the bottom. We moved the data range from B2:D10 to B21:D29.


Method 4 – Applying VLOOKUP Function to Repeat Rows in Excel at Bottom

Steps:

  • Select a cell where you want to keep the result. We selected the C5 cell.
  • Use the corresponding formula in the C5 cell.
=VLOOKUP($B$5,$E$5:$F$10,2,0)

How to Repeat Rows in Excel at Bottom

Formula Breakdown

The VLOOKUP function will return the lookup value.

  • lookup_value = B5: The value that it looks for in the leftmost column of the given table. Here, the Dollar ($) sign represents that the value remains fixed.
  • table_array = $E$5:$F$10: The table in which it looks for the lookup_value in the leftmost column. Here, the Dollar ($) sign represents that the table remains fixed.
  • col_index_num = 2: The number of the column in the table from which a value is to be returned.
  • [range_lookup] = FALSE: 0 or False for an exact match, 1 or True for a partial match. Denotes the exact match for the lookup_value.

  • You need to press ENTER to get the value.

  • Drag the Fill Handle icon to repeat the lookup value respectively to the other rows.

How to Repeat Rows in Excel at Bottom

See the repeated values.


Method 5 – Use of OFFSET Function for Repeating Rows at Bottom

Steps:

  • Select a cell, where you want to keep the result. We selected the B11 cell.
  • Use the corresponding formula in the B11 cell.
=OFFSET(B7,0,0,4,3)

How to Repeat Rows in Excel at Bottom

Formula Breakdown

The OFFSET function will return the same values from a selection.

  • B7 denotes the reference from which the Rows and Columns numbers will be counted.
    • Output: Chiffon Cake.
  • 0 denotes the Rows number from which you want to offset the values.
    • Output: Row: 7.
  • 0 denotes the Column number from which you want to offset the values.
    • Output: Column: B.
  • 4 denotes the offset Height. That means up to which Rows you want to make the offset.
    • Output: Row: 7,8,9,10.
  • 3 denotes the offset Width. That means up to which Columns you want to make the offset.
    • Output: Column: B,C,D.

  • Press ENTER to get the values.

You will get the following repeated Rows.

 


Using VBA Code to Print Repeated Rows

Steps:

  • Choose the Developer tab >> select Visual Basic.

How to Repeat Rows in Excel at Bottom

  • From the Insert tab >> select Module.

  • Write down the following Code in the Module.
Sub Repeat_Row_Print()
Dim Row_Text As String
Dim setAddress As String
Dim Data_Range As Range
Dim xvalue As Range
On Error Resume Next
setAddress = ActiveWindow.RangeSelection.Address
Set Data_Range = Application.InputBox("select a row which will repeat at bottom", "Exceldemy", setAddress, 8)
 If Data_Range Is Nothing Then Exit Sub
   For Each xvalue In Data_Range
    Row_Text = Row_Text & xvalue.Value & " "
   Next
 ActiveSheet.PageSetup.LeftFooter = Row_Text
End Sub

How to Repeat Rows in Excel at Bottom

Code Breakdown

  • We created a Sub Procedure named Repeat_Row_Print.
  • Seclare some variables Row_Text and setAddress as String; Data_Range and xvalue as Range.
  • We used a massage box named Exceldemy for the selection of the particular Row/Rows which I wanted to repeat.
  • We used a For Each Loop to repeat the values on every page as a Left Footer.

  • Save the code then go back to Excel file.
  • From the Developer tab >> select Macros.

  • Select the Macro name (Repeat_Row_Print) and click Run.

How to Repeat Rows in Excel at Bottom

  • Select the preferred Row. We selected B2.
  • Press OK.

  • Use keyboard shortcuts CTRL+P keys to see the printed draft directly.

See the printed draft, which has repeated values on every single page.

How to Repeat Rows in Excel at Bottom

 


Things to Remember

  • When you want to repeat the values on every page then you should use method 3 (Header & Footer).
  • You can’t repeat any value using a formula or calculation through method 1 ( Fill Handle).
  • Using method 4 (VLOOKUP), you will get the repeated values throughout a column.
  • Suggest using either method 1 (Fill Handle) or method 2 (Fill feature), or method 5 (OFFSET) for repeating values.

Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo