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.
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.
If you enjoyed this post check out the related posts below.
