If you can’t insert a row in an Excel table, first click a cell inside the table, right-click the row, and choose Insert > Table Rows Above. If that command is unavailable or Excel shows an error, check whether the sheet is protected, the worksheet is at its row limit, merged cells or other content block the shift, or you are trying to change a query-generated table.
The key distinction is that an Excel table is a structured range inside a worksheet. Adding a table row changes that range; inserting an entire worksheet row shifts cells across the sheet. Choosing the table-specific command is usually the simplest fix, but the right solution depends on what Excel is preventing.
First, identify what you are trying to insert
Microsoft Excel for Microsoft 365 on Windows uses tables to organize related data, apply consistent formatting, and support features such as filter buttons and structured references. A table has a defined boundary, so inserting a table row is not always the same operation as inserting a blank worksheet row.
To add a row within the table, select a cell in the table row that should move down. Right-click and choose Insert > Table Rows Above. Excel adds a row to the table and moves its existing table rows down. The table’s formatting, filter controls, and calculated-column formulas generally extend into the new row.
To insert a whole worksheet row, select a row number along the left edge of the grid, then use Home > Insert > Insert Sheet Rows. That operation shifts cells across the worksheet, including content outside the table, and can fail if the shift would push occupied cells beyond the worksheet boundary.
Use a table-specific method to add the row
Try these options before changing worksheet protection or clearing cells:
- Insert above a chosen row: Right-click a cell in the table row below the intended position and select Insert > Table Rows Above.
- Add a row at the bottom: Click the last cell in the table’s last data row and press Tab. Excel normally creates a new table row when you press Tab from that final cell. If the table has a totals row, use the last data row rather than a cell in the totals row.
- Extend the table range: Select a cell in the table and open Table Design > Resize Table. Adjust the range to include the additional row, making sure it does not overlap another table or unrelated data.
- Try typing immediately below the table: In many workbooks, entering data in the row directly below a table prompts Excel to expand the table. This depends on the workbook and Excel settings, so confirm that the new row receives the table’s formatting and filter behavior.
On Mac, the same table concepts apply, although the ribbon layout and context-menu wording can differ slightly by Excel release. Excel for the web also supports adding table rows, but its available commands and menus can differ from desktop Excel. If a menu option is missing, use the table’s last-cell Tab method or open the workbook in desktop Excel.
Check whether the worksheet is protected
A protected sheet can allow users to select or edit certain cells while preventing them from inserting rows. This can make the command appear unavailable or cause Excel to reject the action. In Windows desktop Excel, open the Review tab and look for Unprotect Sheet. If the sheet is protected, select that command and provide the password if Excel requests one.
If the sheet belongs to someone else or is designed as a controlled form, do not remove protection without permission. Ask the workbook owner to unlock the sheet or grant the permission needed to insert rows. Workbook structure protection is different from sheet protection: it restricts changes to workbook organization, such as adding or deleting sheets, rather than ordinary table-row entry. Look under Review for workbook protection controls if the issue involves the workbook’s structure.
Investigate worksheet limits and the “can’t push cells off” error
Excel worksheets have a fixed capacity of 1,048,576 rows and 16,384 columns. Inserting a full worksheet row shifts existing cells downward. If cells, formulas, or formatting occupy the bottom edge of the sheet, Excel may report that it cannot insert new cells because doing so would push non-empty cells off the worksheet.
This can happen even when the bottom of the sheet looks blank. A cell may contain a value, a formula, or formatting left over from earlier work. To investigate, press Ctrl+End on Windows. Excel moves to the last cell it considers used. If that location is far beyond your real data, inspect the intervening area and the bottom or far-right edge for stray content or formatting.
- Save a copy of the workbook before cleaning up anything important.
- Check whether the apparently unused cells contain data or formatting you need to retain.
- If the distant cells are genuinely unnecessary, select the unused rows or columns and use Home > Clear > Clear All, or delete the unneeded rows or columns where appropriate.
- Save the workbook, close it, and reopen it before trying the insertion again. Excel may need the workbook to be reopened to update its used range.
Be cautious with Clear All: it removes cell contents and formatting in the selected range. Do not clear whole rows or columns until you have confirmed they contain nothing you need. If you only want to remove formatting, use the appropriate clear-formatting option instead.
Look for merged cells, nearby data, and table overlaps
Merged cells cannot be part of an Excel table. Merged cells elsewhere on the worksheet can also complicate inserting or shifting a whole row, especially when the selected insertion range crosses a merged area. If Excel reports that the operation would affect merged cells, inspect the nearby cells and consider unmerging them before inserting. In Windows Excel, the merge controls are on Home; the exact button display can depend on the ribbon layout.
Also check what sits immediately beside or below the table. A table cannot be expanded into cells that contain unrelated data if the expansion would overwrite them, and one table cannot overlap another. If you are using Table Design > Resize Table, verify the proposed range carefully. Move conflicting content elsewhere or choose a different table boundary rather than deleting data just to make the command work.
Another common source of confusion is selecting a row number instead of a cell in the table. A row-number selection targets the worksheet, not just the table. If you only want a new table record, click within the table and use Table Rows Above or the last-cell Tab method. This avoids shifting unrelated content elsewhere on the sheet.
Check filters, totals rows, and query-generated tables
Filtering a table hides rows; it does not remove them. Inserting a table row while a filter is active may not place the new record where you expect visually, because some existing rows are hidden. Clear or adjust the filter using the table’s filter buttons, then inspect the table before deciding that a row is missing. A new record may also fail to meet the current filter criteria and therefore remain hidden after insertion.
A totals row is a special table row, not an ordinary data row. If it is enabled, add a record above it rather than trying to type into the totals row. The totals row may display calculations, such as a sum or count, and can update as the table changes.
If the table is the output of a Power Query connection, treat it differently from a manually maintained list. The query’s source and transformation steps determine the output, and a refresh can replace or rebuild that output. Adding a manual row directly to the result may not be a durable way to add a record. Add the record to the source data or adjust the query workflow, then refresh and confirm the result.
Separate an insertion failure from an expansion setting
Sometimes Excel is not refusing to insert a row; the table simply is not automatically expanding when you type below it. That is a different issue. Use Table Design > Resize Table to include the new row, or use the Tab method from the last cell in the last data row. Check that the new row is inside the table by looking for consistent table formatting and the table’s filter behavior, rather than assuming that a colored row is part of the table.
Similarly, pasting a block of cells is not always equivalent to inserting a table row. A paste can overwrite adjacent data or paste only values into a range without changing the table boundary. If your goal is to add several records, insert the required table rows first or resize the table to include an appropriately sized blank range, then paste the data into the table. Verify that existing records and formulas have not been overwritten.
A quick troubleshooting order
If the cause is still unclear, work through the checks in this order. It minimizes the chance of changing the workbook unnecessarily:
- Click inside the table and use Insert > Table Rows Above.
- For an added record at the bottom, try pressing Tab from the last cell in the final data row.
- Check Review for sheet protection and ask for the password or permission if needed.
- Read the exact error message. If it mentions cells being pushed off the sheet, inspect the distant used range with Ctrl+End.
- Look for merged cells, occupied cells beside the table, another table in the proposed range, or a totals row.
- If the table comes from Power Query, add the record to its source or query process rather than relying on a manual edit to the output.
Undo is available for many ordinary insertions: use Ctrl+Z on Windows or Command+Z on Mac. However, save a copy before cleaning up large ranges or changing a query workflow. A mistaken clear or delete can affect data outside the table.
Frequently Asked Questions
How do I add a row to the bottom of an Excel table?
Click the last cell in the last data row and press Tab. Excel normally adds another table row. You can also resize the table with Table Design > Resize Table or enter data directly below the table and check whether it expands.
Why is Insert Table Rows Above greyed out?
Check that the active cell is inside a table, not merely in a nearby worksheet row. If it is inside the table, sheet protection is a likely reason the command is unavailable. Also confirm that you are not working in a restricted, query-managed output or a context where the selected object is not an Excel table.
Can I insert a row in a filtered Excel table?
Usually, yes. A filter hides records but does not delete them. The new row may be hidden if it does not match the active filter, so clear the filter or check the filter criteria before concluding that the insertion failed.
Why does Excel say it cannot push cells off the worksheet?
Excel is trying to shift cells beyond its fixed row or column limit. Content or formatting near the bottom or right edge may be responsible even if it is not visible in the current view. Use Ctrl+End to inspect the last used cell, carefully remove only unnecessary content or formatting, then save, close, and reopen the workbook.
Is adding a table row the same as inserting a worksheet row?
No. A table-row insertion changes the table’s data range, while a sheet-row insertion shifts cells across the worksheet. Use the table-specific command when you want to add a record without unnecessarily changing unrelated worksheet content.
Related
- Group Rows in Excel Based on Criteria Step by Step
- Group Rows in Excel by Values: A Practical How-To
- How to Hyperlink in Excel SharePoint
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Find Tool Not Working in Excel? Fix Find and ReplaceIf Excel’s Find tool is not working, press Ctrl+F, open Options in the Find and Replace dialog, and…
- Excel UPPER Not Working? Fix the Common CausesIf UPPER is not working in Excel, enter =UPPER(A2) in a cell beside the text in A2, then…
- Excel #REF! Error: Causes, Fixes, and PreventionA #REF! error in Excel means a formula contains an invalid cell reference: Excel can no longer find…
- SUMIFS Not Working? Fix Common Excel ErrorsSUMIFS usually fails because its sum range and criteria ranges do not line up, a criterion does not…
- VLOOKUP Not Working with General Format? Fixes That WorkYes—VLOOKUP can work with cells set to General; that format alone does not prevent a lookup. If the…
- Power Query Keep Errors: Find and Inspect Error RowsIn Power Query for Excel on Windows, select the column or columns you want to check, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.