36
8
Chapter 1: Time is Money - Give your Mouse a Break
Author: Joseph Rubin, CPA www.exceltip.com
Reducing the used range in a worksheet
Delete rows that do not contain data (rows 38-40 in the figure), and then
press Ctrl+S to save the file. Press Ctrl+End. The address of the last
cell in the new used range is K37. The address of the last cell in the
used range is updated when the file is saved.
Why is it important to reduce the address of the last cell in the used
range?
ɿ
Vertical scroll bar – it gets shorter as the used
range of a worksheet grows larger and this makes it
inconvenient to use.
ɿ
Print area – the default print area is the used range
in the worksheet. If you do not set a specific area as
the print area, Excel will automatically print all of the
cells from A1 through that last cell in the used range.
ɿ
View more data in the worksheet – later in this
chapter, in the section titled View all data in the
worksheet, you will want to reduce the used range in
order to use this option.
Tip – Easily delete data from cells in the
worksheet
The shortcuts Ctrl+Shift+End and Ctrl+Shift+Home allow
you to quickly select a cell that contains data and extend the
selection to the beginning of the worksheet or the last used
cell in the worksheet.
Select a cell in the worksheet. Press Ctrl+Shift+End and
your selection will include all of the cells from the selected
cell through the last used cell in the worksheet.
Example: In the worksheet there are about 1,000 rows of data. In order
to delete the data from row 21 through the last used cell in the
worksheet, select cell A21, press Ctrl+Shift+End, and press Del.
