Formulas not auto-filling in inserted rows in an Excel table

Question

I have a table sorted in descending order by date. I add new rows at the top for the current data. I created a hidden dummy row 2 to maintain formatting. I also created the two formulas there and replicated them down the two columns. When I insert a new row 3, the formulas do not auto-fill in the inserted row. How do I get these formulas to auto-fill so I do not have to do it manually with each new row insertion?

Before I inverted the sort order (and adjusted the formulas accordingly) the formulas auto-filled properly with each new row added at the bottom of the table.

Answer 1

Hi Wcstarks,

I see, thank you for clarifying. To make the formula compatible with Excel's auto-fill feature and to correctly reference the cells in the same row, you can indeed use the "@" symbol for structured referencing within Excel tables. Here's how you can adjust your formula:

=([@'Current Value'] + 1) + [@'Amt Added'] + [@'Amt Withdrawn'] + [Return] In this formula:

[@'Current Value'] refers to the "Current Value" column in the current row. [@'Amt Added'] refers to the "Amt Added" column in the current row. [@'Amt Withdrawn'] refers to the "Amt Withdrawn" column in the current row. [Return] refers to the "Return" column in the current row (as it's not part of the Excel table, it doesn't require the "@" symbol). Make sure to use single quotes (' ') around column names with spaces in their names, like "Current Value" and "Amt Added".

This formula should allow Excel to auto-fill correctly when inserting rows at or near the top of the table. If you continue to experience issues with auto-fill, ensure that your Excel table is formatted correctly and that there are no other errors in your worksheet.

I would appreciate your feedback.

Kindly note that this is a user-to-user forum, we are users helping other users, we aren't Microsoft employees and neither are we Microsoft agents.

Best regards, Amadeusz

Answer 2

I gave the names incorrectly before. Using your syntax, it would be:

=([Current Value] + 1) + [Amt Added] + [Amt Withdrawn] + Return

Excel complains saying this has many references and suggests putting "@" into each element. When I let it do that, it still doesn't work.

Of course the whole purpose of doing this is that it might then allow Excel to auto-fill the formulas when inserting rows at or near the top--the real problem.

Answer 3

Hi Wcstarks,

To convert the formula to use cell names instead of table column references (@ symbols), you would replace the [@Amt Added], [Amt Withdrawn], and [Return] references with the corresponding cell names.

Assuming that the cell names for Amt Added, Amt Withdrawn, and Return are "AmtAdded", "AmtWithdrawn", and "Return" respectively, the formula would be:

=(AmtAdded + 1) + AmtWithdrawn + Return

This formula will sum the values from the cells named "AmtAdded", "AmtWithdrawn", and "Return" in the current row, and then add 1 to the result.

If you prefer to use the SUM function instead, you can rewrite the formula as:

=SUM(AmtAdded, 1, AmtWithdrawn, Return)

Both formulas should work to achieve the desired result, but you can choose the one that you find more intuitive or easier to understand.

I would appreciate your feedback.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Kindly note that this is a user-to-user forum, we are users helping other users, we aren't Microsoft employees and neither are we Microsoft agents.

Best regards, Amadeusz

Answer 4

I have another Excel document which contains a table I also sort in descending order by date and insert at or near the top to add current information. It now contains thousands of rows with several rows per day. Of course, I am more interested in the more current data. However, I do need the historical data. The formulas use cell names. It has no problem with auto-filling formulas. However, most of its formulas work with data from the same row. A couple of formulas work with @date minus a number (7 or 90) days depending on the formula. Someone in the community helped me convert to using cell names several years ago.

This new document will also contain thousands of rows over time. So, I need to keep it sorted in descending order by date. It, however, refers to the next row as in: =E4+B3+C3+D3. The names of E, B, C, and D are: Current Value, Amt Added, Amt Withdrawn, and Return. Perhaps you could show me how to change this formula to using cell names and see it that helps. I don't understand the syntax very well, but I think the "@" means to reference the current row. Is it then, something like this? Or should I use the Sum function?

=([@Amt Added]+1) + [Amt Withdrawn] + [Return]

Answer 5

Hi Wcstarks,

I am an independent advisor, trying to help other users in the Community with my experience in Microsoft products. Please note that I am a user like you and don't work directly for Microsoft.

The behaviour you're encountering might be influenced by the specific setup of your table and how Excel interprets the insertion of new rows within a table structure, especially when you're working against the default flow (i.e., adding new data to the top rather than the bottom of the table). There are a few options that may resolve the issue.

1. Ensure Table Structure Integrity First, make sure your range is indeed formatted as a table (Home tab > Format as Table). Excel tables are designed to automatically extend formulas when new rows are added, but this feature works most reliably when rows are appended to the bottom of the table.
2. Adjust Your Data Entry Flow Adding Rows at the Bottom: If possible, consider adding new rows at the bottom of your table instead of the top. This aligns with Excel's default behaviour for extending formulas and formatting in tables. Re-sorting: After adding data at the bottom, you can re-sort your table to have the most recent dates at the top. This might be an extra step but it could streamline your process without needing manual formula replication.
3. Use Structured References in Formulas Ensure that your formulas use structured references (i.e., referring to table columns by name). This can sometimes help with ensuring that formulas are correctly auto-filled when new rows are added, as Excel recognizes the column-based reference as part of the table structure.

I would appreciate your feedback.

Kindly note that this is a user-to-user forum, we are users helping other users, we aren't Microsoft employees and neither are we Microsoft agents.

Best regards, Amadeusz