Excel Tip of the Week #157 - Printing redux

Hello all and welcome back to the Excel Tip of the Week!

Rather amazingly, it's our third birthday.  Starting with TOTW #1 back in November 2013, the Tip of the Week has now been going for three years - you can see the full list over in the TOTW index.  I would like to thank all of you, my readers, for your support and input over the years - and here's to the future.

With so many topics behind us, it's important not to get lost in minutia and still deliver quality, high-impact content.  While the blog has already revisited many of the most vital topics, some of these have had even the second visit a year or more ago.  As a result, I will start to occasionally go back to a hot topic for a third time.  These posts will always contain new content and examples, to make sure that long-time readers can still get something from the posts as well as neophytes.

This week we have a Basic User post which will go over the essentials of printing - most recently seen in TOTW #80.


The essential guidance on printing out spreadsheets


Seriously!  For most purposes, a printed spreadsheet is not useful - it loses the ability to alter or inspect the data, and also loses the ability to see both how a formula is made, as well as its result.  Printing also loses access to a lot of formatting, interactivity, comments, change tracking, and a host of other features.

Furthermore, because of how many knobs there are to twiddle within a spreadsheet - font size, format, row and column widths, and plenty more - it can be very tricky to get a readable result from a spreadsheet.  One trick if legibility in print is to be a key factor for a spreadsheet, is to design with that in mind from the outset - for example, switching on the 'print break preview' view from the View menu early on, so as to consider what will fall onto which page.  Pagination is another sticky wicket for Excel printing - especially large tables can be hard to set up correctly.

The key tips and tricks remain the same from the previous Tip, as follows:

Consider what needs to be printed and what doesn't

Fitting everything onto the sheet is much easier if unnecessary columns – e.g. input data or intermediary formulas - are excluded.  You could hide the data on the sheet, or set the Print Area to exclude these elements (Page Layout => Print Area)

Consider how the worksheet is laid out

The closer to a rectangular layout the worksheet is, the more efficiently and neatly it will print.   What will work well and look nice on a screen won’t always be the best use of space in a printed version.  Try to avoid creating too much whitespace (unused space).

Figure out what orientation works best

You can change between portrait and landscape orientation in Page Layout.  Many workbooks will print much better landscape than they would in portrait.

Consider paper size

Printing a large table or data-heavy workbook can be made much easier on the user by using A3 paper.

There are also some useful tips and tricks:

The Page Setup menu, accessible from the Page Layout Ribbon, can be used to apply fine control a lot of printing settings.  This includes headers and footers, but also repeated rows & columns - for example, allowing the headers of a table to be repeated on every page when printing, for ease of reading.  The gridlines, and row/column labels (A, B, C and 1, 2, 3) can also be included if desired.

Also don't forget to be careful with colours - what may be readable on a bright screen may well not translate to printed paper.

Ultimately, there is no panacea to make Excel printing easier.  But careful application of the existing tricks, as well as planning with printing in mind if it's needed, or not printing at all if it's not, can help make a difference.

Previous post - Designing for longevity
TOTW index 
Next post - Revisiting formulas that aren't calculating

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.