Select the product you need help with

How to reset the last cell in Excel

Article ID: 244435 - View products that this article applies to.

This article was previously published under Q244435

Summary

Microsoft Excel saves only the part of each worksheet that is in use, meaning the section that contains data or formatting. Sometimes the last cell of a worksheet may be beyond the range of your actual used data. This issue may cause you to have a larger file size than neccesary, you may print extra pages, you may receive "Out of Memory" error messages, or you may experience other unusual behavior. Clearing the excess rows and columns to reset the last cell can help to resolve these issues.

Note You can locate the last cell of the active worksheet by pressing CTRL+SHIFT+END.

Back to the top | Give Feedback

More information

The most common cause of the last cell being set outside the worksheet range that is currently in use is excessive formatting. When you format whole rows and columns, some types of formatting can cause the last cell to be set to a cell far below or to the right of the actual range that is in use.

This issue may also occur when you import a Lotus 1-2-3 file into Excel. When you save the Lotus file to the Excel Workbook format, Excel cannot determine the last cell in the Lotus 1-2-3 worksheet. Therefore, it makes the whole worksheet active. To reset the last cell address, you can use either of the following methods to clear the unnecessary information (data and formatting) from the unused columns and rows of the worksheet. After you remove the extraneous information, save the sheet to force Excel to rebuild the active cell table.

Note When you use either of the following methods, you may receive an "Out of Memory" error message or a similar error message. This is because Excel tries to delete the selected range. If you receive this error message, use smaller row or column ranges when you clear data.

Method 1: Manually delete excess rows and columns

To reset the last cell by manually deleting excess rows and columns, follow these steps:

Select all columns to the right of the last column that contains data by selecting the appropriate column headings.

Tip One way to do this is to press F5 and type the appropriate reference. For example, type F:IV for columns, or type 5:65536 for rows.
On the Edit menu, click Clear All.

Note If this step does not clear all the excess formatting, you may have to right-click the columns and then click Delete to fully remove the cells. If you delete cells that are referenced by formulas, the reference changes to "#REF!." Therefore, we recommend that you create a backup of the original file and then check your formulas for "#REF!" after you follow these steps.
Repeat steps 1 and 2 for the rows that are under the last row that contains data.
Save the file.
To continue working in the file, close and then reopen the file.

Method 2: Use an Excel add-in

An example Excel add-in that removes excess formatting and resets the last cell is now available for download. To download this add-in, visit the following Web site:

XSFormatCleaner.xla

(http://xsformatcleaner.codeplex.com/)

Use the Downloads section of the web page to download XSFormatCleaner.xla and save it to your PC. Once you have downloaded the xla you can use the following instructions to run the code in the AddIn to remove excess formatting in your workbook.

Start Excel, and then follow these steps, as appropriate for the version of Excel that you are running.
Microsoft Office Excel 2007, 2010 and 2013
1. Click the Microsoft Office Button, and then click Excel Options.
2. Click Add-Ins, click Excel Add-ins in the Manage box, and then click Go.
3. If you did not save the file to the Library folder, click the Browse button, locate the file, and then select the file in the Browse window.
4. Click Excess Format Cleaner 1.1, and then click OK.
Microsoft Office Excel 2003 and earlier versions of Excel
1. Click Add-Ins on the Tools menu.
2. If you did not save the file to the Library folder, click the Browse button, locate the file, and then select the file in the Browse window.
3. Click Excess Format Cleaner 1.1, and then click OK.
In Excel 2007, click the Add-Ins tab, and then click Clear Excess Formats in XSFormatCleaner.xla in the Menu Commands group.

In Excel 2003 and in earlier versions of Excel, open the workbook that you want to clean up, and then click Clear Excess Formatting in workbook on the File menu. The macro will then clear excess formatting from all worksheets in the file.

When the macro is completed, you will be informed that you must save the workbook for the changes to be permanent.
Save, close, and then reopen the file.