Forum Discussion

Guilherme_Teruaki's avatar
Guilherme_Teruaki
Copper Contributor
Aug 10, 2023

automatically fill down the equation cells as the table growths

Hi,

I have a table where I gather values from other spreadsheets.

 

Is there a way to automatically fill down the equation cells as the table growths ?

for example 

If I have cells A1:A10/B1:B10/C1:C10 where A and B are from another spreadsheet and I do A+B in C.

 

can the formula automatically update by growing downwards when line 10 is filled, so I would have the formula now in Line 11, and so on?

 

at the moment I have the formula in a large number of lines that are not needed that only makes the spreadsheet to heavy without reason.

 

 

Thank you very much

excel
Formulas and Functions

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 10, 2023

    Guilherme_Teruaki 

    If the formula is not within the same Excel Table as the input data a dynamic array solution would size (spill)  to match the data.

    = Table1[Column1] + Table1[Column2]

    Using 365, the formula above references entire columns and returns an array of results containing the correct count of totals.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 10, 2023

    Guilherme_Teruaki 

    If you convert your range to a table (Insert tab of the ribbon > click Table), formulas should automatically be propagated to all existing and new rows.

    If it doesn't:

    • Select File > Options.
    • Select Proofing.
    • Click 'AutoCorrect Options...'
    • Activate the 'AutoFormat As You Type' tab.
    • Make sure that the check box 'Fill formulas in tables to create calculated columns' is ticked.

     

Resources