May 11

0 comments

Excel Ctrl End goes too far

By Sharyn Baines

May 11, 2021

Save, Shortcut, Shortcuts

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.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

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

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>