How to Add Rows in Excel Using Formulas

If you're looking for a quick way to add blank rows to an Excel spreadsheet, you'll be surprised to learn that there's no Excel function or formula that can do this. However, you can write a macro (a command that executes a certain job script) to instantly add a certain number of rows to the specified location. Today's TipsMake will show you how to write a simple macro to add new rows to an Excel spreadsheet. Besides, the article also shows how to add existing formulas to new rows in the spreadsheet.

Add rows to a worksheet using a macro

How to Add Rows in Excel Using Formulas Picture 1

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.

How to Add Rows in Excel Using Formulas Picture 2

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 .

How to Add Rows in Excel Using Formulas Picture 3

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

How to Add Rows in Excel Using Formulas Picture 4

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.

How to Add Rows in Excel Using Formulas Picture 5

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.

How to Add Rows in Excel Using Formulas Picture 6

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.

How to Add Rows in Excel Using Formulas Picture 7

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

How to Add Rows in Excel Using Formulas Picture 8

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

Add recipe to new row

How to Add Rows in Excel Using Formulas Picture 9

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

How to Add Rows in Excel Using Formulas Picture 10

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 .

How to Add Rows in Excel Using Formulas Picture 11

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.

How to Add Rows in Excel Using Formulas Picture 12

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.