The Problem and Correction when using the 2024 version of Excel

1. 1. Inserting a Row from the Total Row:
      • • When you insert a new row while on the total row, Excel automatically splits the total row, creating a new blank row.
      • • The =SUM(...) formula adjusts to include the new row, which is helpful for the totals column, only when you add a new value in the new row.

1. 1. Formulas in Other Columns Not Adjusting:
      • • If there are formulas in other columns (e.g., =A1*B1), copying these formulas down into the new row does not trigger the =SUM(...) formula in that column to expand its range to include the new row. This results in incorrect totals for that column.

---

Why This Happens:

Excel treats the =SUM(...) formula in each column independently. While the formula in one column may auto-adjust for a new row, formulas in other columns copied manually (or using fill) do not prompt Excel to expand the total range in those columns.

---

Solutions:

1. Convert Your Range to a Table

• • Tables handle new rows seamlessly, regardless of where they’re inserted.

• • When you insert a row on the total row in a Table, all columns (including the totals) update dynamically.

• • Steps:
    1. 1. Select your range → Press Ctrl + T to create a Table.
    1. 1. Add a Total Row via Table Design → Total Row.
    1. 1. Insert rows directly on the Total Row—Excel will handle everything automatically.

2. Use Structured References in Formulas

• • If you use structured references (available in Tables), the SUM formulas automatically include any new rows, even when formulas in other columns are copied or modified.

• • Example: Instead of =SUM(A1:A10), the Table will use =SUM(TableName[ColumnName]).

3. Manually Adjust All Total Rows

• • After inserting a row from the total row:
    • • Update the =SUM(...) formulas in all relevant columns to include the new row.
    • • Shortcut: Double-click the formula in the Total row, or press Ctrl + Shift + Enter for recalculation.

4. Use VBA for Automation

• • You can create a VBA macro to automatically adjust all =SUM(...) formulas in the Total row whenever a new row is added.

• • Example VBA Code:vbaCopy codeSub AdjustTotalRows() Dim ws As Worksheet Dim lastRow As Long Dim lastCol As Long Dim col As Long ' Set the active worksheet Set ws = ActiveSheet ' Get the last row and column of the data range lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ' Loop through each column in the Total row and adjust the SUM formula For col = 1 To lastCol If IsNumeric(ws.Cells(lastRow, col).Value) Then ws.Cells(lastRow, col).Formula = "=SUM(" & ws.Cells(1, col).Address & ":" & ws.Cells(lastRow - 1, col).Address & ")" End If Next col End Sub
    • • This macro adjusts all Total row formulas to include the new range.

5. Add Rows Above the Total Row

• • Instead of inserting rows on the total row, always insert rows above the total row. This avoids splitting the total row and ensures proper adjustment of all formulas.

---

Summary:

The best long-term solution is to convert your range to a Table, as it eliminates these manual adjustments. If that’s not feasible, using structured references or VBA can streamline the process. Let me know if you’d like help implementing one of these solutions!

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

 

Call Ivor (08) 9275-9188 or email ivor@perthsoftware.com