Need help dealing with empty rows that are included at the end of your data when you use the Ctrl + End shortcut?
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.
If there was no information in column N then Excel would have taken us to Melissa’s name in cell K21.
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.
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.
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.
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.
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
Excel Ctrl End goes too far
Ever use Ctrl + End and it includes empty rows at the end of your data? Follow this step-by-step guide to help deal with those empty rows.
Instructions
How Ctrl End works
Ctrl + End is the shortcut key that takes you to the very end of your worksheet.
When Ctrl End selects blank rows
This can be because there are cells that contain hidden formatting beneath your data. Excel still sees this as an active cell. 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.
Fix for when Ctrl End goes too far
- Highlight over the rows you don’t need
- Delete them by doing a right click over the row numbers and then selecting Delete.
- SAVE your worksheet.
Notes
Extra tip: to select and delete 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.
Note: If you don't save, Ctrl + End will keep taking you too far.
Very helpful! Thank you!!
Thanks Annie. I’m so happy it helped 🙂
Saved me from a many-years-lasting frustration.
Thank you !!!
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 😊
Thanks for the tip, but in my case Excel jumps to row 1’048’547. My last used row is 5’545, so manually selecting and deleting 1’043’002 rows is not an option.
Hi Joe, check out the last tip on the page and see if this works for you “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).” Cheers, Sharyn
CTRL+S works for me
Fantastic! Such a simple fix right? Thank you for letting me know it worked for you. Take care. Sharyn
Very Helpful.
Thanks Joe! So happy our article was helpful to you. Thank you so much for letting us know.
Excellent and Thank-you. This resolved my issue after trying many other possible solutions.
That’s fantastic Don. Thanks for letting us know helped.
Very useful tip. Good explantion!
Thanks Jens! So glad it helped 🙂
This is something I had wondered about for many years. Normally the “end” is not far from where I would like it to be but in some instances if was too far. Now I have a solution. Thanks!
Thanks Lester. So glad we could help!