How to Delete Blank Rows at the End of an Excel Sheet

To delete blank rows beneath your data in Excel for Microsoft 365 on Windows, find the last row you need, select the rows below it through row 1,048,576 in the Name Box, then choose Home > Delete > Delete Sheet Rows. Save the workbook afterward. This removes leftover cell contents and formatting from those rows, but it does not reduce Excel’s fixed worksheet row count.

Before deleting, check that the rows are truly unused across the entire worksheet. A row can look blank while containing a formula, formatting, or information far to the right of the columns you normally view. The steps below help you check first, remove the right rows, and recover if the result is not what you intended.

What You’ll Need

The Excel workbook you want to clean up, preferably saved as a separate copy if it contains important work.
A clear idea of the last row that must remain. Check all relevant columns, not just the first column or the area currently on screen.
Access to the Name Box, the small reference field to the left of the formula bar. You can use it to select a large row range without scrolling to the bottom of the sheet.

Step-by-Step

Save a working copy. Use File > Save As to make a copy if the workbook is important, shared, or difficult to reconstruct. Deleting whole worksheet rows affects every column in those rows, so a spare copy gives you a safer way to compare or restore the original. If you make a mistake and have not closed the workbook, Ctrl+Z on Windows or Command+Z on Mac usually undoes the deletion.
Find the last row that contains anything you need. Scroll through the data and inspect the columns that matter, including columns beyond the visible table. You can press Ctrl+End on Windows to jump to Excel’s last used cell, but do not assume that cell marks the end of your real data: leftover formatting or an old entry can make the used area extend much farther down. Treat Ctrl+End as a clue, then check the surrounding rows and columns yourself. If the sheet has hidden rows or columns, reveal or inspect them before deciding what to remove.
Check whether the apparent blanks are actually empty. Click a few cells in the rows below your data and look at the formula bar. A cell with a formula that displays an empty string can look blank but still contain a formula. Also check for notes, values in distant columns, or other content you need to keep. If your data is in an Excel table, determine whether the rows are outside the table or are part of the table; deleting worksheet rows and removing table rows are related but not always the same task.
Note the first row you want to delete. If your last required row is 500, the first row to remove is 501. Use the row numbers at the left edge of the worksheet to confirm where that row begins. Do not select row 500 in this example, because that would delete a row containing data you intend to keep.
Select the unwanted rows through the bottom of the sheet. Click the Name Box, type 501:1048576 for the example above, and press Enter. Replace 501 with the first row you want to delete if your data ends elsewhere. The reference selects whole worksheet rows from that starting point to Excel’s final row. Make sure the Name Box shows the range you intended before continuing; typing a range in the formula bar instead will not select it.
Delete the selected sheet rows. On Windows, choose Home > Delete > Delete Sheet Rows. This removes the selected rows’ contents and row formatting and shifts any cells below the selection upward. Since the selection runs to the bottom of the sheet, there should be no needed data below it to shift. If you see a warning, read it before confirming and cancel if the selection includes anything you need.
Check the result and save. Return to the last row of your data and confirm that it is intact. Check a few columns, formulas, and totals, especially if the worksheet has information outside the main table. Save the workbook. If Excel still jumps to a faraway cell with Ctrl+End, save, close, and reopen the file, then check again; the recorded used area may not update immediately.

You can use the same Name Box method on Excel for Mac; the worksheet’s final row is also 1,048,576. The ribbon layout can vary slightly by version, but look for Delete Sheet Rows on the Home tab. In Excel for the web, select the row range using the Name Box if available, then use the Delete options on the Home tab. If the web interface does not accept the full range, open the workbook in the desktop app for this large-range cleanup.

Common Mistakes

Deleting rows based on what looks blank. A formula such as one that returns an empty display, or a value in a distant column, can be easy to miss. Check the formula bar and all relevant columns before selecting whole rows.
Starting one row too early. If the last row to keep is 500, selecting from row 500 deletes that row too. Start at 501, and confirm the range shown in the Name Box before using Delete Sheet Rows.
Using Clear Contents when you mean to remove formatting too. Clear Contents removes cell entries but leaves formatting behind. If leftover formatting is the reason the sheet seems to extend downward, delete the complete unused rows or clear their formats as a separate action.
Expecting Excel to have fewer worksheet rows afterward. Every Excel worksheet has a fixed grid. Deleting rows does not permanently remove the grid below your data; Excel still has rows to the bottom of the sheet. The purpose is to remove unwanted content and formatting from the selected area, not to change the worksheet’s maximum size.
Assuming Ctrl+End identifies the last real record. Excel can treat a formatted cell well below the data as part of the used area. Use the actual contents of the worksheet to decide where your data ends instead of deleting everything below the cell Ctrl+End lands on without checking.

Tips

Use the Name Box for very large selections. Entering a row range such as 501:1048576 is faster and more precise than dragging the scrollbar for hundreds of thousands of rows. Adjust the first number to match your actual last data row.
Make the selection visible before deleting. After pressing Enter in the Name Box, look at the highlighted row headers and verify the starting row. If the selection is not what you expected, click the Name Box again and correct the reference before using the ribbon.
Keep a clean boundary between data and spare rows. If you regularly add records, leave a small number of blank rows only when you have a reason to. Excel tables can expand as you enter new data, so a table is often a better fit than maintaining a large preformatted block of empty rows.
Save, close, and reopen when checking the used range. Excel may not immediately update where Ctrl+End lands after a large deletion. Reopening can refresh that reference, but it does not guarantee a smaller file or remove the worksheet’s built-in rows.
Use the right command for rows inside a table. If the blank rows are part of the table itself, click within the table and remove the table rows using the table’s row controls or resize the table with Table Design > Resize Table. Check that you are not deleting sheet rows that contain unrelated information elsewhere.

Troubleshooting

The Name Box does not select the range. Click the field to the left of the formula bar, not the formula bar itself. Enter the row references with a colon and no extra text, such as 501:1048576, then press Enter. If Excel reports that the reference is invalid, check for a mistyped number or punctuation and try again.

The sheet still seems to contain endless blank rows. That is normal: Excel always displays a worksheet grid up to row 1,048,576. Deleting trailing rows does not make the grid end at your data. If your concern is that scrolling or file size is excessive, save and reopen the workbook and check whether unwanted formatting or content remains; do not expect the visible row count to shrink.

Ctrl+End still jumps far below your data. A stray value or formatting can keep the used area extended. Inspect the cell Excel selects and the rows between it and your data. If the distant cells are genuinely unnecessary, select the relevant rows or cells and remove the unwanted content or formatting, then save, close, and reopen. Avoid deleting rows based only on Ctrl+End if other columns may contain useful information.

You deleted a row you needed. Press Ctrl+Z on Windows or Command+Z on Mac immediately, before making other changes that could complicate recovery. If you already saved or closed the file, use your backup or a version-history option available through your storage service. This is why saving a copy before a large deletion is a useful precaution.

The blank rows are inside an Excel table. Table rows can carry table formatting or formulas even when they appear empty. Click inside the table and check its boundaries on the Table Design tab. Remove only the unneeded table rows or adjust the table range with Table Design > Resize Table; do not remove worksheet rows if they contain other material outside the table.

Frequently Asked Questions

Does deleting blank rows at the end reduce the number of rows in Excel?
No. A worksheet always has 1,048,576 rows. Deleting trailing rows removes their existing contents and formatting, but Excel retains its full worksheet grid.

Will this delete formulas that look blank?
Yes, if those formulas are in the rows you select. A formula can display nothing and still be present. Check the formula bar and decide whether you want to keep those formulas before deleting whole rows.

Can I use Ctrl+Shift+Down instead of the Name Box?
It can extend a selection downward, but the result depends on the active cell and the contents below it. For a whole-row selection through the final worksheet row, entering the explicit row range in the Name Box is easier to verify.

Is clearing the rows the same as deleting them?
No. Clear Contents removes cell values and formulas but leaves the cells and usually their formatting in place. Delete Sheet Rows removes the selected worksheet rows’ contents and formatting and shifts cells below the selection upward.

Can I delete trailing rows on a Mac?
Yes. Use the Name Box to select the row range and the Delete Sheet Rows command on the Home tab. The exact ribbon appearance may differ from Windows, but the worksheet’s last row is the same.

Should I delete every blank row in the worksheet?
Not necessarily. This guide is for unused rows below the data. Blank rows within a list may separate sections or affect sorting, formulas, and table behavior, so inspect their purpose before removing them.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.