How to Delete Unused Rows at the Bottom of an Excel Sheet (and Reset the Last Cell)

Does your sheet scroll far past your data, does Ctrl + End jump to a cell way below the last row, or does the file feel bigger than it should? Then Excel thinks the empty rows under your data are in use, usually because they hold formatting, like borders or a fill color 😕

Here’s the quick fix:

1. Click the row number of the first empty row under your data.
2. Press Ctrl + Shift + Down Arrow to select every row down to the bottom of the sheet.
3. Right-click the selection and click Delete.
4. Do the same for the unused columns to the right of your data (click the first empty column letter and press Ctrl + Shift + Right Arrow).
5. Save the workbook, then press Ctrl + End to check that Excel now stops at the last cell of your data.

Below, I’ll show you three ways to delete the rows, why the last cell gets stuck, how to clean every sheet at once with Check Performance, and how to hide the unused rows instead 👇

Looking to remove blank rows between your data instead? That’s a different job. Here’s my guide on how to delete blank rows in Excel.

You can also download the sample workbook here to practice along 📥

Delete all the rows below your data

Deleting the unused rows at the bottom of the sheet takes a few clicks. Let’s see it with an example.

Say we have the marks of the students in a class in different subjects.

We accidentally added a border to the rows beneath the last row of our data. Now Excel treats those rows as used. Since there are many rows, we don’t want to delete them one by one.

Here are three easy ways to remove all of them in one go 😀

Using the right-click menu

1. Select all the blank rows under the data. Click the row number of the first empty row, then press Ctrl + Shift + Down Arrow. If every row below is empty, this selects all the way down to the last row of the sheet (row 1,048,576).

2. Right-click the selected area.
3. A menu appears.

4. Click Delete. If you selected cells (like A11:D17 in the screenshot) instead of whole rows, Excel asks what to delete. Choose Entire row and click OK.
5. All the selected rows are removed, including the stray borders.

Pretty easy, no? 😎

Don’t worry when you scroll down and still see rows. A worksheet always has 1,048,576 rows, so Excel adds fresh, unformatted rows at the bottom when you delete some. What matters is that the rows below your data are now truly empty.

Using a keyboard shortcut

This method is for you if you prefer the keyboard.

1. Select the entire empty rows (click the first row number under your data and press Ctrl + Shift + Down Arrow).

2. Press Ctrl + Minus sign (-) to delete the rows.
3. All the selected rows are deleted.

If you selected cells instead of whole rows, Ctrl + Minus sign opens the Delete dialog box. Choose Entire row and click OK.

And we’ve done it 🥇

Try it yourself using the sample workbook given above.

Using the Delete button on the ribbon

You can also use the Delete button on the Home tab. The process is the same.

1. Select the rows to delete.

2. On the Home tab, in the Cells group, click the arrow under Delete and choose Delete Sheet Rows. (With whole rows selected, clicking Delete itself does the same.)

3. Excel deletes the entire rows.

You can use any of these methods to delete the rows at the bottom of your sheet 🧐

Got unused columns to the right of your data too? Delete them the same way: click the first empty column letter, press Ctrl + Shift + Right Arrow, right-click and choose Delete.

Why Ctrl + End jumps past your data

Press Ctrl + End in any sheet. Excel jumps to what it thinks is the last cell: the lowest used row of the rightmost used column.

Excel only saves the part of a sheet that contains data or formatting. So if empty cells below or to the right of your data have formatting (a border, a fill color, a number format), Excel counts them as used. That’s why:

• the scroll bar gets tiny and lets you scroll far past your data,
• Ctrl + End lands hundreds of rows below the last row you can see,
• the file gets bigger than it needs to be,
• and Excel may print extra (blank) pages.

To reset the last cell:

1. Select the rows below your data (first empty row number, then Ctrl + Shift + Down Arrow) and delete them, or go to Home > Clear > Clear All.
2. Do the same for the columns to the right of your data (Ctrl + Shift + Right Arrow).
3. Save the workbook. If Ctrl + End still goes too far, close the workbook and open it again.

Now Ctrl + End should stop at the last cell of your data 🎯

Extra blank pages when you print? Here’s more on how to stop printing blank pages in Excel.

Clean up all sheets at once with Check Performance

If a workbook has lots of sheets with formatted empty cells, Excel for Microsoft 365 can clean them up for you.

1. Go to Review > Check Performance.
2. The Workbook Performance pane shows how many cells are used and how many can be optimized.
3. Click Optimize all to clean the whole workbook, or click a sheet and then Optimize sheet to clean just that one.

It removes cells that hold no data but still take up space because of formatting, blanks or non-printing characters. So empty cells with a deliberate fill color (like a colored layout) get cleaned too.

Use a version of Excel with the Inquire add-in (Microsoft 365 Apps for enterprise)? Then you also have Inquire > Clean Excess Cell Formatting. Save a backup copy first, because Microsoft notes that it can’t be undone.

Want the file even smaller? Here are more ways to compress an Excel file.

Hide the unused rows instead

Deleting rows doesn’t make the sheet shorter. Excel always shows 1,048,576 rows. If you want the sheet to look like it ends right below your data, hide the unused rows:

1. Click the row number of the first empty row under your data.
2. Press Ctrl + Shift + Down Arrow.
3. Right-click the selection and click Hide (or press Ctrl + 9).

Do the same with the columns to the right of your data (Right-click > Hide). To bring them back, click Select All (the triangle in the top-left corner where the row and column headers meet), then right-click a column header and choose Unhide. For the rows, right-click a row header and choose Unhide. More in my guide to hiding and unhiding rows and columns.

That’s it! Now what?

Now you know how to delete the unused rows at the bottom of a sheet, reset the last cell so Ctrl + End behaves again, and clean up a whole workbook with Check Performance 💪

Apart from this, Excel has many other cool features. The best of them are the functions. Excel has over 400 functions, and the most popular include VLOOKUP, IF and SUMIF.

If you want to master Excel, then these functions are a must-know. Learn them for free in my 30-minute free email course that teaches these and more 📧

You can enroll only at the cost of your email address. So sign up now!

Other resources

Did you enjoy reading this article? If yes, then we’re sure you’d love to read more.

Try similar topics: Hide Multiple Rows, Delete Blank Rows in Excel, Insert Rows, and more.

Need to remove lots of rows inside your data? See how to delete multiple rows in Excel.

Frequently asked questions

How do I delete all rows below a certain row in Excel?

Click the row number of the first row you want to delete, press Ctrl + Shift + Down Arrow to extend the selection to the bottom, then right-click and click Delete. If there’s data further down, the shortcut stops at it. Press Ctrl + Shift + Down Arrow again until you reach the last row.

Why are there still empty rows at the bottom after I delete them?

Because a worksheet always has 1,048,576 rows. Deleting rows removes their content and formatting, and Excel adds new empty rows at the bottom. To make them disappear from view, hide the unused rows.

Why does Ctrl + End go to a cell below my data?

Some empty cells down there still contain formatting, so Excel counts them as used. Delete or clear the rows and columns outside your data and save the workbook. See how to reset the last cell.

Will deleting the unused rows make my file smaller?

It can. Formatting in unused cells makes a workbook larger than necessary. Clear it, save, and the file usually shrinks. For a whole workbook, use Review > Check Performance.

How do I delete blank rows in the middle of my data?

That needs a different method (Go To Special or a filter), because the blank rows are mixed in with your data. Follow my step-by-step guide to delete blank rows in Excel.

Does this work in Excel for Mac and Excel for the web?

Yes. Select the row numbers, right-click and choose Delete. On a MacBook keyboard without an End key, the “go to the last cell” shortcut is Fn + Control + Right Arrow.