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
Expand all | Collapse all

On This Page


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.


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:
  1. 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.
  2. 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.
  3. Repeat steps 1 and 2 for the rows that are under the last row that contains data.
  4. Save the file.
  5. To continue working in the file, close and then reopen the file.

Method 2: Use an Excel add-in

Note The following Excel add-in only applies to English version of Excel, not applies to non-English version of Excel. We recommend that you may change the language in Excel before you use this Excel add-in.

An 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 Microsoft Web site:
  1. On the download page, click the Download button. When you are prompted to do so, click Open or Run.
  2. When you receive the licensing agreement, click Yes.
  3. When you receive the WinZip Self-Extractor prompt, click the Browse button, and then select the location where you want the add-in to be extracted.

    Note You can put the file in the Excel add-in location, where it will automatically show up in the Addins dialog box in Excel. That location is the Library folder in the Office install location. Typically, this location is the C:\Program Files\Microsoft Office\Office\Library folder.

    After you select the download location that you want in the Browse dialog box, click UnZip in the WinZip dialog box. Click OK on the successful download prompt, and then click Close in the WinZip dialog box.
  4. Start Excel, and then follow these steps, as appropriate for the version of Excel that you are running.

    Microsoft Office Excel 2007 and 2010

    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.
  5. 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.
  6. Save, close, and then reopen the file.


Article ID: 244435 - Last Review: September 18, 2011 - Revision: 8.0
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
kbexpertisebeginner kbcode kbdtacode kbhowto kbinfo kbprogramming KB244435

Give Feedback