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.
Download Practice Workbook
You can download the practice workbook from here:
5 Methods to Repeat Rows in Excel at Bottom
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.
- 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.
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.
- 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 Rows value.
- 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.
- Repeat Text in Excel Automatically (5 Easiest Ways)
- Print Excel Sheet with Header on Every Page in Excel (3 Methods)
- How to Keep Header in Excel When Printing (3 Ways)
- Set Print Titles to Repeat in Excel (2 Examples)
- How to Select Column A as Titles to Repeat on Each Page
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.
- 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.
Here, the VLOOKUP function will return the lookup value.
- lookup_value = B5: The value which 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.
- 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.
Here, the OFFSET function will return the same values from a selection.
- Firstly, B7 denotes the reference from which the Rows and Columns number 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.
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.
- 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
- 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 want to repeat.
- After that, I used a For Each Loop to repeat the values on every page as 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.
💬 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.
Now, you can practice the explained method by yourself.
I hope you found this article helpful. Here, I have explained 5 methods to Repeat Rows in Excel at Bottom. 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 Cell Values in Excel (6 Quick Methods)
- Add The Footer Page 1 To The Current Worksheet
- Find Repeated Numbers in Excel (5 Easy Methods)
- How to Insert Date in Footer in Excel (3 Ways)
- [Fixed!] Excel Rows to Repeat at Top Not Working (4 Solutions)
- How to Add Same Header to All Sheets in Excel (5 Easy Methods)
- Repeat Formula in Every nth Row in Excel (2 Easy Ways)