如何让动态Excel工作表的AGE列公式自动向下填充至新增行

Got it, let's tackle this based on the two most common spreadsheet tools you're likely using—Google Sheets and Excel. The key is to set up a formula that automatically expands to new rows instead of manually dragging it every time.

For Google Sheets #

The easiest way here is to use ARRAYFORMULA to turn your single-cell formula into one that applies to the entire column automatically:

1. First, delete any existing =Today() - G2 formulas you have in the AGE column (leave the header intact).
2. In the first data cell of the AGE column (let's say that's cell H2), enter this formula:

   =ARRAYFORMULA(IF(G2:G="", "", TODAY()-G2:G))
   

   • The IF(G2:G="", "", ...) part ensures blank rows in the Created Date column won't show a random number (it'll leave the AGE cell blank instead).
   • ARRAYFORMULA tells Google Sheets to apply the calculation to every row in column G, including any new rows that get added automatically later.

For Excel #

The approach depends on which version of Excel you're using:

Excel 365/2021 (Dynamic Array Support) #

Excel's dynamic arrays make this super straightforward:

1. Clear any existing AGE formulas except the header.
2. In the first data cell of the AGE column (e.g., H2), enter:

   =TODAY()-G2:G
   

   Press Enter, and Excel will automatically "spill" the formula down to all existing rows. When new records are added to the Created Date column, the spill range will expand automatically to include them.

Older Excel Versions (No Dynamic Arrays) #

Use Excel's Table feature to ensure formulas carry over to new rows:

1. Select your entire data range (including headers: Ticket Status, Created Date, AGE).
2. Press Ctrl+T to convert it into an Excel Table (check the "My table has headers" box if prompted).
3. In the first data cell of the AGE column, enter:

   =TODAY()-[@[Created Date]]
   

   The [@[Created Date]] reference is a structured reference that refers to the Created Date cell in the same row. Now, whenever you add a new row to the table (either manually or via your automated update), the AGE formula will automatically populate for that row.

Quick Note #

Keep in mind that TODAY() is a volatile function—it recalculates every time your spreadsheet opens or makes a change. This means the AGE value will update daily, which is probably what you want since it tracks how old the ticket is.

内容的提问来源于stack exchange，提问作者Zeerak