Fix Excel Used Range Extending to Column XFD
Question details
The user needs to reset an Excel worksheet's used range so they can insert columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user tries to insert a column in a worksheet whose used range extends beyond the actual data.
- Observed behavior
- Excel treats cells through column XFD as used and may report that it cannot shift nonblank cells off the worksheet.
Press Ctrl+End to check the last cell Excel considers used, and identify the last column and row that contain data you need. Check for hidden columns, rows, formulas, and other content before deleting anything.
Delete Unused Columns and Rows to Reset the Used Range
Use this method when stray content or formatting extends the used range beyond the worksheet's actual data.
Open the affected worksheet and press Ctrl+End. Note the cell Excel selects, then locate the last column and row containing content you need.
In the Name Box to the left of the formula bar, enter the range from the first unused column through XFD, such as AF:XFD if your data ends in AE. Press Enter, then select Home > Delete > Delete Sheet Columns.
In the Name Box, enter the range from the first unused row through row 1048576, such as 101:1048576 if your data ends in row 100. Press Enter, then select Home > Delete > Delete Sheet Rows.
Save the workbook, close it, and reopen it. Press Ctrl+End again, then try inserting the column.

Move Valid Data to a New Worksheet
Use a new worksheet or workbook if deleting unused columns and rows does not resolve the insertion error.
Work With Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets. Its familiar interface and Microsoft Office format compatibility make it easy to move your files between applications. It does not repair Excel's used-range behavior.

Frequently Asked Questions
Why does Ctrl+End take me to column XFD?
Excel may consider cells in column XFD used because they contain data, formulas, or formatting. Press Ctrl+End to locate the last used cell, then inspect the area beyond your actual data.
Will clearing cell contents reset the used range?
Not always. Formatting can remain after contents are cleared. Select the unused columns or rows and use Home > Delete > Delete Sheet Columns or Delete Sheet Rows instead.
Why can't I insert a column after hiding unused columns?
Hidden columns remain part of the worksheet, and their contents can still prevent Excel from shifting cells. Inspect the hidden columns and delete unused sheet columns rather than only hiding them.
Do deleted columns disappear from the worksheet permanently?
No. Excel worksheets still have columns through XFD. Deleting unused sheet columns removes their existing contents and formatting and shifts remaining columns left.
What if the used range still reaches XFD after I delete unused cells?
Save, close, and reopen the workbook, then press Ctrl+End again. If the problem persists, copy only the needed data to a new worksheet or workbook and test the column insertion there.




