VBA & Macro Problems

How to Fix Excel Formulas Skipping New Rows Inserted by Macros

Maira Mehtab Sep 22, 2026 869 views

Question details

Formulas on a Profit and Loss sheet fail to include new rows inserted by a macro in an Expenses Output sheet, causing the formula ranges to shift down instead of expanding.

Product: Excel
Device & OS: not provided
Scenario: Running a VBA macro that inserts new rows at the top of a dataset which is referenced by SUMIFS formulas on another sheet.
Observed behavior: Formulas continue referencing the old rows or shift down, ignoring the newly inserted data rows.

Before you start

Before modifying your formula ranges, ensure that your macro is correctly inserting the rows and that you have identified all the formulas currently referencing the affected dataset.

Solution 1Recommended

Use Full-Column References in Formulas

The most straightforward fix is to update your SUMIFS formulas to reference entire columns instead of a specific row range.

When a macro inserts a row exactly at the upper boundary of a formula's range (e.g., row 6), Excel assumes the new row should push the existing range down rather than expand it. By changing your formula to reference the entire column, it will automatically include any newly inserted rows anywhere in that column.

Locate the affected formula

Navigate to your Profit and Loss sheet and select the cell containing the SUMIFS formula that is missing the new data.

Modify the range to include entire columns

In the formula bar, replace the specific row references (such as 'Expenses Output'!$H$6:H9996) with full-column references (like 'Expenses Output'!$H:$H).

Apply and copy the formula

Press Enter to save the changes, then drag the fill handle to apply the updated formula to any other necessary cells. Test the macro to ensure new rows are now captured.

Success Confirmed: Using complete-column references like $H:$H prevents ranges from breaking when macros insert rows above your data set.

Solution 2

Convert the Data Range to a Structured Excel Table

Convert your source data into an Excel Table, which automatically expands formula references when new rows are added by a macro.

Powerful Spreadsheet Tool

Handle Dynamic Data and Macros Seamlessly with WPS Office

WPS Spreadsheet offers comprehensive support for advanced formulas like SUMIFS, dynamic ranges, and VBA macros, ensuring your data calculations remain accurate even when new rows are inserted automatically.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your macro and SUMIFS formulas.
2. Update your formulas: Select the cells with the shifting formulas and change their ranges to full-column references (e.g., A:A).
3. Run your macro: Execute your macro to insert new rows. The full-column references in WPS Spreadsheet will instantly capture the new data without skipping a beat.

Fully compatible with Microsoft Excel formulas and VBA macros.Supports full-column references and dynamic arrays for flexible data management.Lightweight and runs smoothly even with large datasets and complex calculations.

microsoft office alternative - wps office

Frequently Asked Questions

Why do formulas shift down when a macro inserts a new row?

By default, when a row is inserted above or exactly at the first cell of a defined range boundary, the spreadsheet application shifts the defined range down to preserve the original set of data. It does not automatically expand the range upward to include the newly inserted row.

Do full-column references slow down spreadsheet performance?

While modern spreadsheet applications are highly optimized to handle full-column references efficiently, using them with volatile formulas (like INDIRECT) or massive array calculations can sometimes cause a slight performance dip. For most standard SUMIFS, it is perfectly fine.

Can I fix this issue by changing the macro code instead of the formulas?

Yes. If you adjust your VBA macro to insert the new row inside the existing formula range (for example, inserting at row 3 instead of row 2, assuming row 2 is the top boundary), the spreadsheet will naturally expand the range to accommodate the new row.

Excel & Data Problems