Locked lesson.
About this lesson
Learn about page setup options, fitting print jobs on a page, headers and footers, and other ways to customize print jobs.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Page Setup Options.xlsx32.5 KB Page Setup Options - Completed.xlsx
32.8 KB
Quick reference
Topic
Setting page setup options, fit print jobs on a page, creating headers and footers, and otherwise customizing a print job to look as attractive as possible.
When to use
There is nothing more frustrating than spending a bunch of time setting up your worksheet to look great, and then find out it won’t print correctly. These techniques will help you deal with this issue.
Instructions
Forcing a print job to fit on one page
- Check print preview first (notice that this job fits on 6 pages!)
- Go to the Page Layout tab and choose “1 Page” from the Width dropdown
- The job will now print to two pages only, as it has been scaled (1 page wide by x pages tall)
Setting headers and footers
- Go to Page Layout > Print Titles
- On Rows to Repeat at Top click the ref-edit box and select rows 1:3
- Select the Header/Footer tab
- Click Custom Header and click in the right box
- Type “Printed “, click the calendar icon, type a space, click the clock icon, click OK
- Click Customer Footer and click in the right box
- Type “Page “, click the # icon, type “ of “, click the multi-page icon, click OK
- Click Print Preview
- Notice that we have nice headers, footers and repeated rows at the top of each page
Adding row and column headers and gridlines
- Go to Page Layout > Print Titles
- Check the box nest to Gridlines
- Check the box next to Row and column headings
- Click Print Preview
- Notice that we now have a print job that looks like a spreadsheet (very useful if you’ve selected to print comments at the end of the sheet, as it gives context to reference.)
- 00:04 We're now gonna look at scaling print jobs inside Excel using the Page Setup Options.
- 00:10 The issue that we have here, if we look at our example page, is that when we go into
- 00:15 Print Preview, this tells us that it's gona print to 16 pages.
- 00:20 And if we actually scroll through these for a bit, let's go through to page 9.
- 00:25 You can see that one of our problems here is that from pages 9 through 16,
- 00:29 we've only got a single column of data that's showing up here
- 00:33 because the print job was slightly too wide for the actual page.
- 00:39 So to fix that problem, we can go to the Page Layout tab and up here,
- 00:43 we have something that says Width Automatic.
- 00:46 If we change this to one page, this print job will now be forced to fit
- 00:51 one page wide by as many pages tall as it needs.
- 00:55 And in this case we get down to a print job that's now eight pages long instead,
- 00:59 and it's not gonna have that extra column hanging off the right-hand side..
- 01:03 That's a very, very useful thing to know right off the bat.
- 01:07 In addition, when you're printing your data out,
- 01:08 one of the things that can become challenging when you print a multiple page
- 01:12 document, is you can see that we have our headers here.
- 01:15 But when we get to the next page, they're all completely gone, and
- 01:17 that's not really all that helpful at all.
- 01:20 So it would be helpful to actually have our repeating headers
- 01:23 showing up at the top.
- 01:25 Maybe not the title, although we'll grab that too,
- 01:27 but certainly the columns on our table.
- 01:30 So to control that again on the Page Layout tab, we go to Print Titles.
- 01:35 And what we do is, we select the number of rows to repeat at top.
- 01:38 So we'll click here and we'll highlight the first three rows.
- 01:43 So that's rows 1 through 3, and that'll work nicely.
- 01:45 You'll also notice that if we had data that spans across many pages width-wise,
- 01:50 we could also repeat the number of columns.
- 01:54 While we're here, let's go to the Header and Footer tab as well.
- 01:58 And let's go and provide ourselves a custom header by clicking here.
- 02:05 And in the right-hand side, we'll actually select and
- 02:08 tell ourselves when this was actually printed.
- 02:10 So, we'll go with Printed Date and Space and click the Time button here.
- 02:15 And this will put in the field codes that are required for this.
- 02:20 In addition, we'll say OK here now.
- 02:23 Let's go to the custom footer and on the bottom right hand side,
- 02:27 we'll say Page and then we'll click the Number button.
- 02:30 And then a space, of and a space, the number of pages.
- 02:37 So, the single page will give us a single page.
- 02:39 This will give us the total count of pages.
- 02:42 And what you'll see is, when we click OK here, it'll give us Page 1 of 1 in our
- 02:46 Preview, and it tells us what date it was actually printed.
- 02:48 Let's click Print Preview and see what's happened now.
- 02:53 So we can see at the top that there is the little header marking for
- 02:57 when it was printed.
- 02:57 Down on the bottom it actually says Page 1 of 8.
- 03:00 And as we click to the right, we can see that the page numbers keep indexing up.
- 03:05 The date, time isn't gonna change because it was only printed at the same time.
- 03:08 But we've manged to preserve the header for the information that we have as well.
- 03:12 So that's really quite helpful.
- 03:15 Something else we may want to do in certain cases, particularly if we're
- 03:19 actually looking at printing comments, is we may want to actually print
- 03:23 the grid lines on the worksheet as well as the row and column headings.
- 03:27 Now if you've got a table here, this doesn't make a lot of sense, but
- 03:30 if you're printing something that doesn't have a table,
- 03:32 cuz you can see we add the row and column headings across the top.
- 03:36 If you're printing something that doesn't have a proper table assigned to it and
- 03:40 you're trying to get your index for
- 03:41 where comments are actually shown, this can be really, really useful.
- 03:45 So these are a lot of the different print options that you have in Excel for
- 03:49 playing around.
- 03:49 Of course you can also change the orientation of your pages,
- 03:52 flip them to landscape, change the size of your pages here as well.
- 03:57 So right now, it's set to a letter size here.
- 03:59 You can change your margins and do all kinds of other wonderful things as well.
- 04:03 But these are the basics of what you really need to know in order to be able to
- 04:07 print an effective worksheet.
Lesson notes are only available for subscribers.