Posted: May 11, 2021

Need help dealing with empty rows that are included at the end of your data when you use Ctrl + End?

It's a really simple fix.

How Ctrl End works

Ctrl + Home is the shortcut key which takes you to the top cell A1.

Ctrl + End is the shortcut key that takes you to the very end of your worksheet.

However, Ctrl + End can be a lot trickier as Excel will take you to the intersection of the last row and the last column.

You can see in the example below, that column N is the last column to have information in it and row 21 is the last row that has information in it. This means Ctrl + End will take you to cell N21.

excel ctrl shift end goes too far

If there was no information in column N then Excel would have taken us to Melissa’s name in cell K21.

in excel ctrl end goes too far

When Ctrl End selects blank rows

Sometimes there can be cells that contain hidden formatting beneath your data. To give you an example, I’ve formatted cell K25 with a yellow fill.

excel ctrl end moves too far

When I press Ctrl + End, my cell selector will move to N25, not N21 as expected. This is because, even though there is no content in cell K25, only formatting, Excel is still seeing it as an active cell.

Ctrl End goes too far

This can happen when you can’t even see the formatting. For example, if one of the cells were formatted with the Accounting Number Format. Even though the formatting isn’t obvious, Excel will still treat the cell as an active cell. It looks as though Excel is randomly jumping to an empty row. However, it’s quite possibly moving to the last formatted cell.

excel ctrl end selecting too far

Fix for when Ctrl End goes too far

So, this is what you can do.

Highlight over the rows you don’t need and delete them by doing a right click over the row numbers and then selecting Delete.

excel shift ctrl end selects blank rows

Extra tip: to select all empty rows to the bottom of your worksheet, click on to a cell in the first row you want to delete, then press SHIFT + spacebar. This will select the entire row. Now press CTRL + SHIFT + down arrow to select all of the rows below. Now delete the selected rows (as shown above).

Once you have deleted the rows, you need to SAVE your worksheet otherwise it won’t actually update and your Ctrl + End will continue taking you too far.

So, click Save and Ctrl + End should now take you to the end of your data, not the empty rows.

Was this Blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Join our free insiders Group!

Learn how to SAVE TIME and WORK SMARTER, without the 'techie' speak!

  • Yay Michael! I’m so glad it helped you. Thank you so much for letting me know 😊

  • It was frustrating couldn’t find the solution anywhere. Finally, found the answer here. Extremely uselful…!!!!!
    Thanks a lot..!!!!

  • Only one that worked for 2016 version. Others were clearing or deleting down to CTRL-END position where this seems to got to EOF.

  • Yay! So glad it helped you out. Yes, super frustrating and it’s just so easy once you know that one simple step to Save. Thanks for letting me know it helped 😊

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join our free insiders club! 

    Receive 100+ Steps to Becoming Proficient in Excel