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.

Quick Tip

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

  1. 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).

  2. Delete all rows below this

    You can do this by selecting the first unnecessary row, then pressing Ctrl + Shift + , then pressing Ctrl + -.

  3. Locate the last column you want to use

    This is the last (usually rightmost) column of either your data or your required formatting.

  4. Delete all columns after it

    You can do this by selecting the first unused column, then pressing Ctrl + Shift + , then pressing Ctrl + -.

  5. Select the first cell

    You can select cell A1 by pressing Ctrl + Home

  6. 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).

 

Bonus Tip

Please log in to see the bonus tip.

No login?
Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

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

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.

Download files for this tip

Get Quicksheet

Quicksheet

Download example

Example file

No login?
Get one for free.