How to Add Rows in Excel Using Formulas

Add rows to a worksheet using a macro

Open the project in Excel. To proceed, double-click the Excel document containing the data in the Finder (Mac) or File Explorer (Windows) interface. Or if you have Excel open, click File > Open and select the file containing the data to be processed.

Check that the Developer tab is showing in the edit ribbon. If you don't have one and are using Windows, you need to go to the File tab and click Options > Customize ribbon . Under the heading "Main tabs'", check the box next to "Developer".

On a Mac, you can enable the Developer tab by clicking the Excel menu at the top of the screen and choosing Preferences . Click the Ribbon & Toolbar , check the "Developer" box in the "Customize this Ribbon" category, and click Save .

Open the VBA editor. Click the Developer tab and select Visual Basic , or you can press Alt + F11 .

Right click on the sheet. In the vertical menu on the left side of the window is a list of worksheets for the workbook set. Right-click one of these sheets to open another drop-down menu.

Hover over the Insert option and click Module . When you hover over the Insert option , a menu pops up to the right of the current menu. Click Module to create space for the entry and execution of VBA code.

Enter this code in the VBA window:

Sub Insert_Rows_Loop () Dim CurrentSheet As Object ' Loop through all selected sheets. For Each CurrentSheet In ActiveWindow . SelectedSheets ' Insert 5 rows at top of each sheet. CurrentSheet . Range ( "a1:a5" ). EntireRow . Insert Next CurrentSheet End Sub 

With this code inserted rows will start from A1 to A5 (5 new blank rows at top of page). The addresses of these cells can be replaced with the location where you want the code to be placed, such as starting with the last row in the worksheet to any end.

Press F5. Or you can also click the green Play button icon located above the edit space of the Visual Basic editor.

Click OK to continue. The rows will be added to the sheet.

Add recipe to new row

Open the project in Excel. To proceed, double-click the Excel document containing the data in the Finder (Mac) or File Explorer (Windows) interface. Or if you have Excel open, click File > Open and select the file.

Apply this method if you want to learn how to copy an existing formula into the new row you just added.

Insert rows into the data. The keyboard shortcut you use can only add formulas from the row above, so the row you need to insert must be in the middle or bottom of the data selection.

To insert a row, right-click the row below where you are going to add the new row and select Insert .

Click the cell in the new row that you want to include the formula in. Note: the keyboard shortcut will copy the formula from the cell above the cell you selected.

The cell you selected will be highlighted.

Press Ctrl+D . This shortcut combination is used to fill the formula from the cell above into the selected cell, you can use it on both Windows and Mac.

Isabella Humphrey

Update 23 April 2022