How to Add Rows in Excel with a Formula

Chủ nhật - 05/05/2024 23:59
Start by opening your project in Excel. Insert a row inside your data. Click to select the cell in the new row where you want to include the formula. Press Crtl + D. If you want to find the sum of two or more rows instead, see How to Sum...
Table of contents

Start by opening your project in Excel. Insert a row inside your data. Click to select the cell in the new row where you want to include the formula. Press Crtl + D. If you want to find the sum of two or more rows instead, see How to Sum Multiple Rows and Columns in Excel.

Method 1
Method 1 of 2:

Adding Rows to a Sheet with a Macro

  1. Step 1 Open your project in Excel.
    To do this, double-click the Excel document that contains your data in Finder (Macs) or File Explorer (Windows). Alternatively, if you already have Excel open, click File > Open and select the file that has your data.
  2. Step 2 Make sure the Developer tab is displayed on your editing ribbon.
    If it isn't and you're using Windows, go to the File tab and click Options > Customize ribbon. Under "Main tabs'" check the box next to "Developer."
    • If you're using a Mac, enable the Developer tab by clicking the Excel menu at the top of the screen and selecting Preferences. Click Ribbon & Toolbar, check the "Developer" box in the "Customize this Ribbon" category, and click Save.
    Advertisement
  3. Step 3 Open the VBA editor.
    Click the Developer tab and select Visual Basic to do so. Alternatively, you can press Alt + F11.
  4. Step 4 Right-click a sheet.
    In the vertical menu on the left side of the window, you'll see a list of your workbook's sheets. Right-click on any of these to get another drop-down menu.
  5. Step 5 Hover your cursor over Insert and click Module.
    When you hover over Insert, a menu will pop out to the right of the menu. Clicking on Module will create a space for you to enter a VBA code and run it.
  6. Step 6 Enter this code into your 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, you'll be inserting rows starting at A1 and ending at A5—that's 5 empty new rows at the top of the sheet.[1] You can replace these cell addresses with the location into which you want to place the code, such as beginning with the last row in the sheet to any ending point.
  7. Step 7 Press F5.
    Alternatively, you can click the green Play icon above the VB editor writing space.
  8. Step 8 Click OK to continue.
    This adds the rows to your sheet.
    9. Advertisement
Method 2
Method 2 of 2:

Adding Formulas to New Rows

  1. Step 1 Open your project in Excel.
    To do this, double-click the Excel document that contains your data in Finder (Macs) or File Explorer (Windows). Alternatively, if you already have Excel open, click File > Open and select the file.
    • Use this method if you want to learn how to copy existing formulas into new rows you add to your data.
  2. Step 2 Insert a row...
    Insert a row inside your data. The keyboard shortcut you'll use can only add a formula from the row above it, so make sure you insert the row in the middle or the bottom of your data collection.
    • To insert a row, right-click the row below where you want the new row to be added, then click Insert.
  3. Step 3 Click to select the cell in the new row where you want to include the formula.
    Remember, this keyboard shortcut will copy the formula from the cell above to the cell you have selected.
    • The cell will highlight to indicate that it is selected.
  4. Step 4 Press Ctrl+D.
    The shortcut is the same for Mac and Windows and will fill the formula down from the cell above. [2]
    5. Advertisement

Total notes of this article: 0 in 0 rating

Click on stars to rate this article

Related New

Related

Lastest News

Đá Gà ABC8 – Loại Hình Cá Cược Trực Tuyến Hấp Dẫn 2024

Đá Gà ABC8 – Loại Hình Cá Cược Trực Tuyến Hấp Dẫn 2024

24/07/2024General
How to Store Electronic Parts

How to Store Electronic Parts

06/05/2024Computers and Electronics
How to Fix Oculus Controllers That Are Not Responding

How to Fix Oculus Controllers That Are Not Responding

06/05/2024Computers and Electronics
How to Run Powershell

How to Run Powershell

06/05/2024Computers and Electronics
How to Get Data from .Nbf Files

How to Get Data from .Nbf Files

06/05/2024Computers and Electronics

Popuplar News

How to Contact Klook

How to Contact Klook

26/07/2024Travel
What Does Islam Say about Dream Interpretation?

What Does Islam Say about Dream Interpretation?

26/07/2024Philosophy and Religion
How to Contact Priceline

How to Contact Priceline

26/07/2024Travel
How to Compost in an Apartment

How to Compost in an Apartment

26/07/2024Home and Garden
How to Become an Accountant

How to Become an Accountant

26/07/2024Finance and Business