Share via

Is there a way to make the formulas in Excel continue down the next rows, without having to drag them there as more rows of data are added?

Anonymous
2015-08-30T09:04:03+00:00

I have a very large spreadsheet that I will be using over time to enter many rows of data.  

I have made my formulas and drug the corner of each one down to say row 100.

But when I enter data for row 101, the formulas are not there.  I have to go back and drag the little lower right corner of the cell to copy the formulas to that cell.  

Then I enter data for row 102 and I have to drag all the formulas again.

Is there someway to just have the formulas automatically copy to the next row as you enter data?

I have colored say Row 100 bright yellow and then drug all the formulas down to that level.  Not so great, because then all the zeros show in  various columns.   But now I'm at Row 100 and will have to drag all the formulas again.  

In this spreadsheet I have no totals at the bottom of the columns.

Is there someway to just have the formulas automatically copy to the next row as you enter data and add additional rows?

Thank You

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-08-30T09:33:37+00:00

    Hi,

    Excel can make the formula fill automatically.

    File | Options | Advanced | In the 'Editing options group' Ensure that 'Extend data range formats and formulas' is checked.

    10+ people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2015-08-30T09:28:16+00:00

    One option would be to modify the formulas. Let's say that you will always enter data in column A, and that you now have a formula

    =formula

    in cell D2. Change this to

    =IF(A2="","",formula)

    and fill down a good way, say to D300. The formula will return a blank for rows in which column A has not yet been filled in.

    Another option would be to use VBA code to create the formulas as you enter data. This is quite possible, but it will have the disadvantage of disabling the Undo feature.

    6 people found this answer helpful.
    0 comments
  3. Anonymous
    2015-08-30T09:41:53+00:00

    Hi,

    another solution is to convert the range to a table

    in the sample below

    in row 1 are headers in row 2 data and cell D2 has formula

    =IF(C2="","",A2+B2+C2)

    here....

    xxxxxxxxxxxx

    select the range A1:D2

    from the ribbon

    Insert > Table

    here..

    xxxxxxxxxxxxxxxxx

    now,

    select cell D2 press Tab key and you have a new row,

    cell D3 has formula

    =IF(C3="","",A3+B3+C3)

    here...

    https://www.youtube.com/watch?v=fZ4jBvmkyD8

    4 people found this answer helpful.
    0 comments