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.
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.
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.
- 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.
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.
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)
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.
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)
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.
- 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
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.
- 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.
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
- How to Repeat Rows at Top in Excel
- How to Repeat Rows at Top of Specific Pages in Excel
- [Fixed] Repeat Last Action Not Working in Excel
<< Go Back to Repeat in Excel | Excel Cell Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
