Printing and exporting issues and solutions
Blank pages at the end of your document? Page alignment look wrong? Strange print or export results?
Here’s how to solve some of the common spreadsheet printing and exporting issues.
Reset the used area of a worksheet to solve common printing and exporting issues
There are many situations where it can help to reset the used area of a worksheet:
- Unnecessary blank pages appearing when printing or exporting
- Page numbering indicating more pages than what the content uses
- Scroll bars look funny (e.g. scroll box very small)
- Scrolling down jumps many rows at once when dragging the scroll box
- Excel responds slowly when using your workbook
- Your spreadsheet uses more disk space than expected
Here’s how to solve it:
How to reset the used area
You can reset the used range in a worksheet by following these steps:
Time needed: 1 minute.
Reset the used area of a worksheet
- Locate the last row you want to use
This is the last row of either your data or your required formatting (for example, you might want to use some blank rows or a border to assist your layout for presentation).
- Delete all rows below this
You can do this by selecting the first unnecessary row, then pressing Ctrl + Shift + ↓, then pressing Ctrl + -.
- Locate the last column you want to use
This is the last (usually rightmost) column of either your data or your required formatting.
- Delete all columns after it
You can do this by selecting the first unused column, then pressing Ctrl + Shift + →, then pressing Ctrl + -.
- Select the first cell
You can select cell A1 by pressing Ctrl + Home
- Save the workbook
Press Ctrl + S.
Ensure you perform each step in order, and with no other actions or editing in between steps.
If you followed the steps but the used area did not change:
- Be sure that you actually have no cells containing formatting or content beyond the used area.
- Check for worksheet objects such as shapes, pictures, controls or comments/notes. (Beware these may have been resized with a height of zero, so may not be visible). You need to delete or move them, as the used range will extend to the cells under any object on the worksheet.
- Reset the row height and column width of the whole spreadsheet to exactly the default height and width that is used in new workbooks. You need to select all cells before resetting the row height.
- Check if AutoSave is enabled. If it is, disable it; you can enable it again later.
- Then, follow the steps again to clear the used area.
Check the used area
From anywhere in the worksheet, press Ctrl + End to go to the last used cell.
This will take you to the cell that Excel thinks is the lowermost and rightmost used cell.
If this is significantly below or to the right of what you actually need, follow the steps to reset the used area.
Sometimes, if you edit the contents (or formatting) of a cell or range of cells that extends below what you actually need, Excel thinks you are using those cells too. This happens even if they contain no contents or if you subsequently delete the contents.
Effect of a large used area
If your used area is unnecessarily large, you could experience various printing and exporting issues such as:
- Blank pages appearing within or at the end of a print or export
- Page numbering not working (particularly the total pages count)
You may also experience slow application responsiveness when using the workbook, and may find the file taking up more disk space than expected. (The extent of this depends on the type and nature of any objects or formatting in the unused area, as well as how many extra cells it contains).
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
Download files for this tip
Get one for free.