facebook twitter pinterest google plus Youtube
www.TestsTestsTests.com

www.TestsTestsTests.com

Printing Large Excel Worksheets Tutorial

Excel 2010 Training - Printing
Free Online Microsoft Excel Tutorial

* Scaling a Page for Printing
* Insert Page Breaks
* Move Page Breaks in Page Break Preview Window
* Set Print Area




Sometimes your whole worksheet does not print on one piece of paper. There are modifications you can make to print an Excel worksheet either on one page or at least with all of the columns on one page.

Test your Excel skills with the corresponding FREE Online Multiple Choice
Printing Large Excel Worksheets Test


* Scaling a Page for Printing

After spending hours, days or even weeks working on the same worksheet, you may be tearing your hair out when it comes to trying to print it all over a reasonable number of pages at a readable size. It may be that columns are split over several pages making the rows of information appear disconnected or in a bid to fit your data into the required space, your fonts are too small to read in printed form.  Many people create worksheets to fit in with print settings by spreading data over several separate worksheets or even by omitting columns that they judge to be less important.  Although the aforementioned are perfectly valid methods for ensuring data makes sense when printed, it may not always be practical to split up a worksheet over several different sheets. 

The good news is that there are a couple of tricks for manipulating a worksheet into fitting nicely on a page or set number of pages when printed.

Using the built-in Scaling function in Excel is a quick an easy method for forcing data to fit within the parameters of a page or a specific number of pages, as required.

To use the Scaling Options:

1.  Open the worksheet you wish to print.

2.  Click on the File button to launch the Backstage View and click on Print or alternatively, press Ctrl+P on your keyboard to open the Print area.

3.  In the second panel, look out for the Scaling button.  It is the last button in the list (circled in yellow in the screenshot below):
Scaling a Page for Printing - Excel Tutorial

4.  Click on this button to access the list of pre-set Scaling options:

  • * No Scaling – the worksheet will print as is.
  • * Fit sheet on One Page – the worksheet data will be reduced in size in order to fit all columns and rows onto a single page.
  • * Fit All Columns on One Page – the worksheet data will be reduced in size and the worksheet adjusted to fit all columns on one page.
  • * Fit All Rows on One Page – the worksheet data will be reduced in size and the worksheet adjusted to fit all rows on one page.


The most commonly used option from the above list, is possibly the Fit All Columns on One Page setting.  Before applying this setting, it is highly recommended that you change your page layout to Landscape Orientation by clicking on the Page Orientation button (highlighted yellow in the screenshot below) located in the second panel of the Print area in the Backstage View:

Scaling a Page for Printing - Excel Tutorial

Another suggestion, before applying the Fit All Columns on One Page setting, is to change the Page Margins to narrow in order to fit more columns on a single page.

The above option(s) usually resolve minor worksheet printing issues, however, should you need more precise settings than these, click on Custom Scaling Options, located at the bottom of the list of options available that appear when clicking on the Scaling button.  You can also access these Scaling options by clicking on the Page Setup link.  This will launch the Page Setup dialogue box pictured below:

Scaling a Page for Printing - Excel Tutorial


1.  In the Page Setup dialogue box,
ensure the Page tab is selected. 

2.  Under the heading:  Scaling use the Adjust box to set an exact percentage to increase or decrease the worksheet size by. 

3.  Keep in mind that when reducing the size, you may be sacrificing font size and readability for fitting the worksheet on a required number of pages.

4.  Increasing the percentage in the Adjust to box will expand a worksheet by increasing the font size to fit.  This is often a useful option if the font used, or the row and column settings applied, makes the worksheet too small to read and not quite filling a page.

At other times you may know exactly how many pages your worksheet should print over in order to be legible and user friendly.  For these instances, use the Fit to setting to specify exactly how many pages wide and how many pages tall you wish to print your worksheet over.

To fit columns to one page (or more pages), insert 1 (or whichever number required) in the Page(s) Wide box, and if you do not wish to specify over how many pages the rows should print, leave the box for Page(s) Tall blank.

Scaling is a setting worth experimenting with, try different variations of values for the Pages Wide and Pages Tall boxes in the Page Setup dialogue box.  Also experiment with changing the page layout from Portrait to Landscape and modify margin sizes in order to make your content fit over the desired number of pages.




* Insert Page Breaks

In the previous section we dealt with applying different settings to force a page to fit over a set number of pages when printed.  Page Breaks is a different method for achieving a similar result.  If a worksheet is so wide or tall that no amount of manipulation to make it fit onto a set number of pages will work, it may be time to accept this, but to still take control over where rows and columns will be split over pages when printed. 

Page Breaks in Excel function similar to those in MS Word in that it allows a new page to be manually inserted at a specific point or points in the document.  Unlike in MS Word, Page Breaks in Excel allows for separating content into pages by splitting data columns and/or rows both horizontally and vertically onto a new page.
 
To insert a Page Break between columns:

1.  Open the worksheet you wish to insert a Page Break(s) into.

2.  Select the first column you wish to appear on a new page.

3.  Click on the Page Layout tab on the Ribbon and then on Breaks in the Page Setup group.

4.  Select Insert Page Break to insert a page break to the left of the selected column. 

The Page Break will appear as a vertical dashed line to indicate where the columns in your worksheet will be split onto a new page.

To insert a Page Break between rows:

1.  Open the worksheet you wish to insert a Page Break(s) into.

2.  Select the first row you wish to appear on a new page.

3.  Click on the Page Layout tab on the Ribbon and then on Breaks in the Page Setup group.

4.  Select Insert Page Break to insert a Page Break above the selected row. 

The Page Break will appear as a horizontal dashed line to indicate where the rows in your worksheet will be split onto a new page.

To split a worksheet into pages between both rows and columns:

1.  Open the worksheet you wish to insert a Page Break(s) into.

2.  Position your cursor in the worksheet:

  • * in the first column you wish to appear on a new page; and, if applicable
  • * in the first row you wish to appear on a new page

 

3.  Click on the Page Layout tab on the Ribbon and then on Breaks in the Page Setup group.

4.  Select Insert Page Break to insert a page break both to the left of the column and above the row in which your cursor is positioned.

You can insert as many Page Breaks into a worksheet as you need. 

To remove specific page breaks:

1.  Position your cursor to the right of or below the dashed Page Break line.

2. Click on the Page Layout tab on the Ribbon and then on Breaks in the Page Setup group.

3.  Select Remove Page Break from the list by clicking on it.  The dashed horizontal and/or vertical lines to the left of the column or above the row in which your cursor is positioned, will disappear.

To quickly remove all Page Breaks in a worksheet:

1.  Position your cursor anywhere in the worksheet.

2.  Click on the Page Layout tab on the Ribbon and then on Breaks in the Page Setup group.

3.  Select Reset All Page Breaks from the list.  All of the dashed horizontal and/or vertical lines in the worksheet will disappear.

Using Page Breaks you can literally divide your worksheet up to print over as many pages as you wish.  This is a great option for when the Scaling function simply will not work.  Experiment with inserting different Page Breaks and then go to the Print Preview Pane to review what the document will look like when printed and over how many pages it will print.



* Move Page Breaks in the Page Break Preview Window

Page Breaks, as discussed in the previous section, allow you to manually specify where a worksheet should be divided in order to print it over the desired number of pages.  Using the method(s) described in the previous section to insert Page Breaks will allow you to insert and remove page breaks, but what happens if you just wished to move breaks or get an overview of all the page breaks in a worksheet?

This is where the very useful Page Break Preview comes in.  To use this view:

1.  Open the worksheet you wish to Print.

2.  On the Ribbon click on the View tab. 

3.  In the Worksheet Views group click on Page Break Preview.

This will change the view of your worksheet to include a visual representation of page breaks.  Each page break is represented by a dark blue vertical and horizontal line and the page number:

Move Page Breaks in the Page Break Preview Window - Excel Tutorial

These Page Break lines can be moved in order that they are positioned where you wish the page(s) to break off. 


To move a Page Break:

1.  Click on a vertical or horizontal blue Page Break line and drag it to a new position. 

2.  Dragging a vertical blue line will include fewer or more columns.  You can drag it to the right-hand edge of your data to include all columns on one page.

3.  Dragging a horizontal blue line upwards or downwards will include fewer or more rows on each page.

Insert more page breaks by following the instructions in the Insert Page Breaks section.   Always take note of the page numbers that appear in this view as it gives you an indication of how many pages your worksheet is separated over.  Once you have moved and inserted Page Breaks as needed, always view the entire document in the Print Preview Pane in the Backstage View, by clicking Ctrl+P on your keyboard.



* Set Print Area

If a worksheet is exceptionally large and you only need specific parts of the data to be printed, for example, just the updated totals in columns containing a formula, you can use the Set Print Area option to indicate exactly which part of the worksheet needs to be printed. 

To set a Print Area:

1.  Open the worksheet you wish to specify a Print Area for.

2.  Select the cells, rows or columns you wish to print.  You can select non-contiguous cells, rows or columns by holding down the control key on your keyboard whilst making the selection.

3.  Once you have selected all the areas to include in the pre-set Print Area, click on the Page Layout tab on the Ribbon.

4.  In the Page Setup group, click on Print Area (circled in yellow in the screenshot below):

Set Print Area - Excel Tutorial

5.  Click on Set Print Area to add the selected cells, rows and columns to the Print Area.

6.  After adding cells, rows and columns to a Print Area, you can add more sections to the Print Area by selecting additional sections of the worksheet, going back to the Print Area button and selecting Add to Print Area from the list.

Go to the Print Preview Pane by using the shortcut key combination: Ctrl+P, to view the different sections that will print and to ensure that they are correct.

To remove all Print Areas:

1.  Click on the Page Layout tab on the Ribbon.

2.  In the Page Setup group click on the Print Area button and select Clear Print Area.

This will remove any Print Areas that have been created in the worksheet.

Now you have done the tutorial...
Test your Excel skills with the corresponding FREE Online Multiple Choice
Printing Large Excel Worksheets Test