Fix Excel Table Formulas That Skip a Row When Using Tab
Question details
The user needs to repair an Excel Table calculated-column formula that skips the newly inserted row after using Tab.
- Product
- Microsoft Excel Table calculated columns
- Device & OS
- not provided
- Scenario
- A new table row is inserted with Tab, and the formula must calculate the difference between the correct current and previous meter readings.
- Observed behavior
- The formula changes its relative reference to the cell below the table, causing the new row to be skipped.
Identify the table name, the calculated-column cell, and the exact cells or sheet references used by the formula. Compare the formulas in the rows above and below the affected row to confirm whether the calculated column is consistent.
Overwrite the Excel calculated column with a consistent formula
Use this first when Excel has shifted one row or the calculated column contains mixed formulas.
Excel may extend a relative reference to the cell below the table when a row is inserted with Tab. Reapplying one correct formula to the entire calculated column restores a consistent pattern.
In the affected Excel table, click a cell in the calculated column that contains the intended formula, then press F2 and press Enter.
Click the AutoCorrect Options icon that appears near the edited cell, if available.
Choose Overwrite all cells in this column with this formula. Excel will apply the selected formula pattern to the entire calculated column.
Insert or select the new row and verify that its formula references the correct current and previous readings rather than the row below the table.

Use a direct current-row and previous-row reference
Use this approach when the table formula needs a predictable reference to two specific meter-reading rows.
Preserve the next-row reference with a relative defined name
Use a relative defined name when the formula must always refer to the cell immediately below the current row.
Repair Skipped-Row Table Formulas in WPS Spreadsheet
Use WPS Spreadsheet to inspect the calculated column, correct the current-row and previous-row references, and fill the repaired formula through the table column. WPS supports familiar spreadsheet formulas and Microsoft Excel workbook formats for an easy migration.
- 1. Open the workbook: Launch WPS Spreadsheet, select Open from the Home screen, and open the affected .xls or .xlsx workbook.
- 2. Inspect the formula: Click the affected table cell and review the formula in the formula bar, comparing its current-row and previous-row references with a neighboring correct cell.
- 3. Enter the corrected formula: Select the formula cell, click the formula bar, and enter a formula such as =MAX('Water Usage'!D29-'Water Usage'!$D28,0), updating the sheet and row references for your data.
- 4. Fill the calculated column: Press Enter, copy the corrected cell with Ctrl+C, select the remaining cells in the calculated column, and use Home > Paste.
- 5. Save the repaired file: Choose File > Save As, select Excel Workbook (*.xlsx) when required, and reopen or inspect the new row to confirm the formula no longer skips it.

Frequently Asked Questions
Why does pressing Tab make my Excel table formula skip the new row?
Excel may extend a relative reference to the cell below the table when Tab creates a new row. Check the formula bar and restore a reference to the actual current and previous rows.
How can I apply one corrected formula to an entire Excel calculated column?
Edit a correct cell, press F2, press Enter, and choose AutoCorrect Options > Overwrite all cells in this column with this formula. If that option is unavailable, copy the corrected cell and use Home > Paste on the selected column cells.
What formula can calculate the difference between two meter readings without returning a negative value?
Use a formula such as =MAX('Water Usage'!D29-'Water Usage'!$D28,0), replacing the sheet name and row numbers with the references in your workbook.
Can I stop Excel from automatically extending formulas into new table rows?
Yes. Go to File > Options > Advanced and clear Extend data range formats and formulas. This changes table behavior globally, so use it only if automatic formula extension is causing repeated problems.
Can WPS Spreadsheet open and repair this Excel workbook?
Yes. Open the XLS or XLSX file in WPS Spreadsheet, inspect the formula bar, enter the corrected formula, copy it through the calculated column, and save the repaired workbook in Excel format.




