Tip: Quickly reset the last cell and minimize the used range

Save 5 minutes a day by using ASAP Utilities to quickly reset the used range on your worksheet.

"Easily reset the last cell on your sheet"

Have you ever been frustrated when Excel acts as if the active area of your worksheet is significantly larger than the actual area where you have data?

Sometimes the last cell of an Excel sheet may be far beyond the range of your actual used data. This can cause you to be unable to easily locate the end or your data-set, have a larger file size than necessary, printing can result in extra (empty) pages, or you may even receive "Out of Memory" error messages.
Resetting the last cell can help to resolve these issues.

You can test if this is true by pressing Control+End and Excel takes you to a blank cell below or to the right of the actual data.

In such a case, you can use one of the following tools to fix it:
ASAP Utilities » Sheets » Reset Excel's last cell (Control+Alt+R)
or the following tool which is more thorough and works on all selected sheets, but can be a bit slower.
ASAP Utilities » Sheets » Delete unused empty ending rows/columns (on selected sheets)

Quickly reset the last cell on your sheet and minimize the used range

I'm a huge fan of keyboard shortcuts, and whenever Control+End doesn't take me to the actual last cell on the sheet I'm working on, I just press Control+Alt+R to reset the used range with this tool. And then Control+End works as expected again.

Do you recognize any of these situations?

  • I like to use the shortcuts Control+Home and Control+End to quickly move to the top or end of my worksheets. In one sheet, the end point is for reasons unknown several thousand rows below the actual last row. How can I change the end point so that the Control+End command will take me to where I want to go?
  • My job involves adding data to the end of a very large Excel worksheet. To get to the last cell to begin data entry, I use the shortcut Control+End. However, Excel takes me to a blank cell way below and right of the actual data. And then I have to scroll back to get to the last cell containing data, which is something I tried to avoid by using the shortcut keys. How can I fix that?
  • My Excel file size is too big. I noticed something odd. When I hit control-end, it takes me to the bottom of the sheet (row 65536), instead of row 457 which is the actual end of my data. Why is that?

Just use the following tool:
ASAP Utilities » Sheets » Reset Excel's last cell (Control+Alt+R)
or if the "Reset Excel's last cell" tool can't reset the last cell, then you can use the following tool which is more thorough and works on all selected sheets at once, but it can be a bit slower.
ASAP Utilities » Sheets » Delete unused empty ending rows/columns (on selected sheets)

This is often easier than Microsoft's advised method.
The Contextures website demonstrates how you can do it manually or with a macro.

Bonus tips, also interesting

How much time will it save?

It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.

Download

In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page

2 comments

Kanwaljit Singh Dhunna

Hi Bastien,

May the Family Live 1000 years !

Just downloaded the Latest version 5.2.1 and used "Format > Detect and Visualise Adjacent Data/Group Changes". I was not able to undo the changes. Doesn't it support "Undo" or there is something different from the previous versions ?

Regards
Kanwaljit Singh Dhunna
India

Bastien

Hello Kanwaljit,

Thank you for your feedback.

I'm sorry but that is one of the few tools where the undo is not implemented yet.

Best regards,
Bastien Mensink