Fix an Excel used range that remembers phantom rows
Excel’s last cell is wrong when the sheet’s stored dimension reaches far past the last value. Ctrl+End jumps into empty space, saves get slower, and the file keeps a rectangle it does not need. Delete entire unused rows and columns, clear leftover formatting if that is what anchors the range, save, and confirm Ctrl+End now lands on the real table.
What you are seeing
The status area or Ctrl+End lands on row 1048576, or on a column out near XFD, while your table ends at row 80. Scroll and the cells look blank. They are blank of values. They are not blank of Excel’s memory of the sheet. The worksheet XML contains a dimension ref that still names the old rectangle. Sometimes it also contains row elements far below the data, left behind by a format or a deleted value that did not delete the row. The used-range checker compares that declaration with the last cell that holds a value or a formula.
Confirm you have the right sheet
Run the checker and read the per-sheet table. Only the sheets with a large wasted-row or wasted-column count need this repair. A workbook can have one import sheet that is phantom and twenty sheets that are fine. Note the last real cell. You will not delete anything above that row. If the actual range says empty and the declared range is huge, the sheet has a dimension and no values. Be careful: a sheet of only formatting, or a sheet whose values sit in a chart, can look empty here and still matter. Open it before you delete it.
Delete rows and columns, do not merely clear them
Go to the first unused row. Press Ctrl+Shift+Down, or select down to the last row if that shortcut is swallowed by a table. Right-click and choose Delete, then Entire row. Do the same from the first unused column with Delete, Entire column. Clearing contents leaves the cells in the used range. Deleting the rows removes them. If the sheet is a table, drag the table handle up to the last data row instead. Tables keep a range of their own, and deleting worksheet rows inside a table is a different operation from resizing it.
If Ctrl+End does not move
Save, close the file, and reopen it. Excel sometimes rewrites the dimension on save. If Ctrl+End is still wrong, look for a cell with a space, a color, or a border in the empty zone. Go To Special, Blanks, is the wrong tool here. Go To Special, Last cell, is the symptom. Look at objects: a comment, a shape, or a chart anchored far away keeps the range. Select it and delete it or move it. Look at the print area and at freeze panes. A defined print area is a name, not a phantom row, but a print area that refers to a million rows will keep that reference. Reset it to the real table.
When you want the numbers from your own workbook, check the phantom rows first.
Styles that refuse to die
Sometimes every cell in the column has a style even though the checker’s last data row is correct. The wasted count may be moderate rather than a full-grid phantom. Select the unused rows and clear formats, then delete them. Unused styles in the workbook gallery do not by themselves set the last cell, but they do swell xl/styles.xml. You can remove unused cell styles after the range is honest. Do not remove styles you do not recognize if the workbook is a corporate template. Save a copy first.
After the repair
Ctrl+End should land at the bottom-right of the real data, or one row or column past it at worst. Drop the file on the used-range checker again. Wasted rows should collapse. The file size often drops on the next save, especially if many sheets were affected. If a formula elsewhere used a whole-column reference, it still works, because whole-column references do not depend on the dimension. If a formula used a range that extended into the rows you deleted, it now shows #REF!. That is the checker’s error list telling you the formula was pointing at the empty zone on purpose. Restore those rows from the copy you kept, and shorten the formula instead.
A sheet that became phantom in the first place
Imports do this quietly. A query or a paste writes 50,000 rows, a later run writes 2,000, and the dimension stays at 50,000 because the old rows were cleared or overwritten with blanks. Someone applies a border to the current region and the current region is already wrong, so the border paints the mistake into the file. Another person inserts a chart while the selection is that huge region, and the chart’s anchor remembers it. The repair is still the same, but the prevention is to resize the destination before the next import, or to write the import into a table that is resized by the query. If this sheet is produced every week, fix the step that writes it. Otherwise you will delete phantom rows every Monday and call that maintenance. After the import step is fixed, run the used-range check on a fresh output once. Wasted rows near zero means the job is done. If several sheets share the problem, repair one, confirm Ctrl+End, and only then repeat. A macro that deletes “empty” rows across every sheet will also delete a sheet that looks empty and holds a named input cell someone relies on. The per-sheet table is there so you can choose.
When several people share the sheet
A phantom range comes back if the next person pastes a full-column format or if a scheduled job writes cleared rows instead of resizing the output. Agree on one sign that the sheet is in order: Ctrl+End lands on the last real row. Anyone who imports into the sheet checks that sign before they hand the file on. If the import is a query, the query’s destination should be a table that shrinks, not a static range that only ever grows. Document the sheet name from the checker so the agreement is about a specific tab, not about “the workbook” in the abstract. Shared files fail this repair when everyone deletes rows and nobody changes the step that recreates them.
Related reading
The closest repair notes are Why the file got large and Causes of slow calculation .
Walk the same checks in order with the Workbook audit checklist.
The terms behind this guide are Used range and Open XML package .
The paired check for that note is the Excel file size checker.
Questions people ask before trusting the result
Is the last row of the grid always a phantom?
A dimension that ends on the last row while data ends much earlier is the usual phantom. A sheet that truly has a value on the last row is rare and is not a phantom. The wasted-row number is the test.
Will this break my pivots?
A pivot whose source was the phantom rectangle should be pointed at the real table before you delete rows. A pivot whose source was already the real table is unaffected.
Can I do this with a macro?
Yes, and a bad macro is how some sheets became phantom in the first place. If you automate it, limit the delete to rows below the last value and skip sheets you have not inspected.
Why does one sheet say “not recorded”?
The worksheet XML had no dimension element. The checker then treats the last populated cell as the range. That is not a phantom. It is a sheet Excel will rewrite the next time it saves.
Does .xlsb have a used range?
Yes, inside the binary sheet. This site does not read it yet. Use Ctrl+End in Excel for .xlsb, or save a copy as .xlsx to use the checker.