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.xlsx13.6 KB Page Setup Options - Completed.xlsx
13.8 KB Page Setup Options - Extra Practice.xlsx
32.1 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 So it's inevitable that once you've worked with your Excel file long enough
- 00:07 you're going to have it to that perfect point where everything is formatted right and now you need to print it out
- 00:11 and give it to someone. And that can cause its own whole set of issues.
- 00:15 Let's take a look at this particular file here. We'll go into print preview
- 00:19 and you can see that we have six pages is what it's showing us here is what this printout is.
- 00:25 And when we go and click on the next page we can see that we're missing our headers.
- 00:31 It also has some extra columns that are hanging out down the side. So this isn't ideal.
- 00:36 So what can we do to fix this? Well there's a few different things we can do for it.
- 00:41 We're going to run over to the Page Layout tab right now and we're going to see if we can make some changes here.
- 00:46 The first thing we want to do is we want to try and change this width it's not really
- 00:50 all that great that that last column is spilling over to another page.
- 00:54 So we can make that change right here by saying the width we'd like to print it so the width is always one page.
- 01:02 By leaving the height alone this will make it one page wide by as many pages tall as it needs. So let's go take a quick look at that
- 01:09 and we can now see that we're down to two pages we've got the first page and it shrunk it down. That's not bad.
- 01:16 So it's a a big change right off the bat.
- 01:19 Now what if we want to go and add our header rows to this as well? Well at this point we're
- 01:24 going to go again on the page layout tab we'll click print titles
- 01:28 and first thing we can see here is we have the ability to repeat a certain amount of rows at the top of the printout.
- 01:34 So if we click the RefEdit box we can actually go and highlight rows1 to 3
- 01:41 and when we click RefEdit again and go back in here
- 01:44 say OK.
- 01:46 You can now go to the print preview and what do we get? We've still got two pages but when we flip to the second page
- 01:53 we can see that the headers are still there. That's much better and gives us a little more context to work with.
- 01:59 Now what other things could we do? Well we could add headers and footers. That
- 02:03 might be something we would want to look at. So again we'll go back into print titles. There's
- 02:07 no short direct cut into this so we go to Headers/Footers and
- 02:12 we have the ability to set both a custom header and a custom footer. So we're going to click custom header
- 02:18 Over on the right hand side here we're going to go and we're going to put in the date and time that this is printed. So we can type in Printed
- 02:26 and these little icons that we have up here, this is the date, so if we click on that it puts in a formatting code for us. You can hit space bar
- 02:35 and then we can put in the time so that will give us both the date and time that it gets printed.
- 02:39 So as you can see right now from the preview this is June 28 at approximate 11:00 PM.
- 02:45 We can also go and set up custom footers so if we go into a custom footer down the bottom right, let's see here,
- 02:51 how about page and this is the page number, number of.
- 02:58 And this is the total number of pages in the document.
- 03:02 We say OK to that it will come back and tell me the preview was page 1 of 1.
- 03:09 We can jump directly into print preview to see what's happening here.
- 03:13 And in very small type we can see that it does say that its printed and has the
- 03:17 current date and time at the top and down the bottom right hand corner we
- 03:20 can also see, it's really hard to read but it does say page1 of 2.
- 03:25 When I click over to the next one is now says page 2 of 2
- 03:30 and the header is still here with a printed date and time.
- 03:35 The last thing that we might want to do is we may want to go and also add
- 03:41 printing of row and column headers and the grid lines and those are both here as well. So right back into the sheet tab.
- 03:48 Go Row and column headings and we go with the grid lines
- 03:52 and when we go back to print preview
- 03:54 that can help your spreadsheet look more like a spreadsheet. Oftentimes we don't want to do that but there are certainly times where maybe
- 04:01 we're printing comments or if we've got a nice big sheet that we're actually trying to
- 04:05 work on manually because we just can't see it on the screen
- 04:09 we may actually want to have those kind of row and column headings showing with the grid lines
- 04:12 so that we can actually trace and figure out where things are. So
- 04:16 those are the different ways we can go through and configure our print jobs to make sure that everything works correctly for us.
Lesson notes are only available for subscribers.