Sometimes you may need to repeat rows in Excel at bottom. Excel has so many features to repeat data. In this article, I will explain how to repeat rows in Excel at bottom.
How to Repeat Rows in Excel at Bottom: 5 Easy Ways
Here, I will describe 5 suitable methods to repeat Rows in Excel at bottom. In addition, for your better understanding, I’m going to use the following dataset. Which contains 3 columns. Those are Product, Cost, and Sales.
1. Use of Fill Handle Icon to Repeat Rows in Excel at Bottom
You can use the Fill Handle Icon to repeat rows at bottom in Excel. This is a very common and simple feature for repeating the same values in Excel. The steps are given below.
Steps:
- Firstly, you have to select the row up to which cell you want to repeat the values. Here, I have selected the B10:D10
- Secondly, drag the Fill Handle icon to repeat the range values respectively to the other rows.
Finally, you will see the repeated Rows.
Read More: How to Make a Pattern Repeat in Excel
2. Employing Fill Feature to Repeat Rows in Excel at Bottom
Furthermore, you can employ the Fill Feature to repeat rows at bottom in Excel. This is a very easy feature for repeating the same values in Excel. Moreover, with the help of this feature, you can repeat the values in any direction. The steps are given below.
Steps:
- Firstly, you have to select the data range including the blank Rows for which you want to repeat the values. Here, I have selected the B10:D14
- Secondly, from the Home tab >> you need to go to the Editing Command. Here, you can use Excel keyboard shortcuts Alt+H to go to the Home tab.
- Thirdly, from the Fill feature >> you have to select the Down option.
Finally, you will see the following result with repeated row values.
Read More: How to Repeat Rows for a Specified Number of Times in Excel
3. Applying Header & Footer Command for Repeating Rows at Bottom
You can use the Header & Footer command to repeat rows in Excel at the bottom. The steps are given below.
Steps:
- Firstly, you have to open your worksheet.
- Secondly, from the Insert tab >> go to the Text option >> Then, you need to choose the Header & Footer feature.
At this time, you will see the following changes.
- Now, you need to Click on the Add footer.
- Then, you can write down your preferred words. Furthermore, you can do formatting to this. Here, I have written “ List of Cakes ”. Also, I have made it Bold and increased the Font size from the Home tab.
- Again, you can click on the Right Footer box to include more information.
- Then, from the Header & Footer ribbon >> you may choose Current Date.
Now, in the First Footer box, I will add a picture.
- Firstly, select the First Header
- Secondly, from the Header & Footer ribbon >> go to the Picture option.
At this time, you will see a dialog box named Insert Pictures.
- From this box, choose your preferred way. Here, I have chosen From a file.
- Then, I have chosen the Picture file.
Finally, you will see the Row which will repeat on all pages at the bottom. Here, for your better understanding, I have moved the data range from B2:D10 to B21:D29.
4. Applying VLOOKUP Function to Repeat Rows in Excel at Bottom
You can use the VLOOKUP function to repeat rows at bottom in Excel. Follow the steps below.
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the C5 cell.
- Secondly, you need to use the corresponding formula in the C5 cell.
=VLOOKUP($B$5,$E$5:$F$10,2,0)
Formula Breakdown
Here, 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. So, it denotes the exact match for the lookup_value.
- Subsequently, you need to press ENTER to get the value.
- Now, drag the Fill Handle icon to repeat the lookup value respectively to the other rows.
Finally, you can see the repeated values.
5. Use of OFFSET Function for Repeating Rows at Bottom
Moreover, you can use the OFFSET function to repeat rows at bottom in Excel. Follow the steps below.
Steps:
- Firstly, you have to select a cell, where you want to keep the result. I have selected the B11 cell.
- Secondly, you need to use the corresponding formula in the B11 cell.
=OFFSET(B7,0,0,4,3)
Formula Breakdown
Here, the OFFSET function will return the same values from a selection.
- Firstly, B7 denotes the reference from which the Rows and Columns numbers will be counted.
- Output: Chiffon Cake.
- Secondly, 0 denotes the Rows number from which you want to offset the values.
- Output: Row: 7.
- Thirdly, another 0 denotes the Column number from which you want to offset the values.
- Output: Column: B.
- Fourthly, 4 denotes the offset Height. That means up to which Rows you want to make the offset.
- Output: Row: 7,8,9,10.
- Finally, 3 denotes the offset Width. That means up to which Columns you want to make the offset.
- Output: Column: B,C,D.
- Subsequently, you need to press ENTER to get the values.
Finally, you will get the following repeated Rows.
Read More: Repeat Text in Excel Automatically
Using VBA Code to Print Repeated Rows
You can employ a VBA code to repeat rows at the bottom in Excel. The steps are given below.
Steps:
- Firstly, you have to choose the Developer tab >> then select Visual Basic.
- Now, 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
- Here, I have created a Sub Procedure named Repeat_Row_Print.
- Next, declare some variables Row_Text and setAddress as String; Data_Range and xvalue as Range.
- Then, I used a massage box named Exceldemy for the selection of the particular Row/Rows which I wanted to repeat.
- After that, I used a For Each Loop to repeat the values on every page as a Left Footer.
- Now, Save the code then go back to Excel file.
- After that, from the Developer tab >> select Macros.
- Then, select the Macro name (Repeat_Row_Print) and click on Run.
- After that, you must select the preferred Row. Here, I have selected B2.
- Finally, press OK.
- At this time, you may use keyboard shortcuts CTRL+P keys to see the printed draft directly.
Finally, you will see the printed draft which has repeated values on every single page.
Read More: How to Repeat Rows in Excel When Printing
💬 Things to Remember
- When you want to repeat the values on every page then you should use method 3 (Header & Footer).
- Furthermore, you can’t repeat any value using a formula or calculation through method 1 ( Fill Handle).
- Moreover, while using method 4 (VLOOKUP), you will get the repeated values throughout a column.
- Thus, I would suggest using either method 1 (Fill Handle) or method 2 (Fill feature), or method 5 (OFFSET) for repeating values.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
I hope you found this article helpful. Here, I have explained 5 methods to repeat rows in Excel at bottom.
Please, drop comments, suggestions, or queries if you have any in the comment section below.
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!
