Used range

In Excel, there is a concept known as the "used range"; the region on each individual worksheet which Excel thinks is being used.  The larger the used range, the larger your file size and the slower your workbook calculation speed.

You can identify this region on a worksheet yourself by pressing the Ctrl+End keys.  This takes you to the cell in the bottom right-hand corner of the used range, referred to as the last cell.  Thus the used range on your worksheet is defined as the rectangular region from cell A1 to the last cell.

The last cell can be empty because it is determined by finding the intersection of the maximum used column and the maximum used row.

Usually the last cell is at, or only a few rows or columns beyond, the final cell which actually contains formulas or constants.  This means that the used range is no larger than, or only marginally larger than, the minimum size it could be.

However, there are times when the last cell, as defined by Excel, is far beyond the final cell which contains information, making the used range much larger than it should be.

One of the items which is listed on the OAK Workbook Summary report is the last cell location.  This tells you which worksheets in your workbook have used ranges which are much larger than the region on the worksheet which actually contains non-blank cells.

Why this is useful

If the used range is unnecessarily large, the size of your XLS file will be larger than it should be and your workbook calculates more slowly than it should.  Even navigating around the worksheets can be sluggish.

To reduce the used range of a worksheet to the correct size once it has grown very large:

If you import a Lotus 1-2-3 spreadsheet into Excel, you may find that the used range is much larger than it should be.  Following the steps outlined above will often resolve the problem.