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.
Exercise files
Download this lesson’s related exercise files.
Page Setup Options - Begin.xlsx49 KB Page Setup Options - Complete.xlsx
49.1 KB
Quick reference
Page Setup Options
Setting page setup options, fitting 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 The reality is, is that no matter how paperless we go in this world,
- 00:07 there are still times where we need to print our spreadsheets.
- 00:10 So my goal with this video is actually twofold.
- 00:13 Number one is to reduce the number of trees that you have to cut down for
- 00:16 your print jobs, number two is to help you make your print jobs look good.
- 00:20 So let's get started.
- 00:22 The very first thing that I want to do is I want to take a look at this particular
- 00:26 dataset which is fairly long, but for whatever reason I need to print it for
- 00:29 an audit file or something like that.
- 00:31 So how do I actually print it so that it looks good on the least amount of pages?
- 00:36 Very first thing I'm going to do is go up to my QAT where I happen to have my
- 00:39 Print Preview and Print setup.
- 00:41 I'm going to click on that to go into the Print Preview.
- 00:44 If you don't have that button on the QAT, File Print will get you here.
- 00:48 All right, now you'll notice that when I look at this, my text is cut off at
- 00:53 the top here, and down the bottom we can see that I've got 16 pages.
- 00:57 Let me go and fast forward halfway through this dataset.
- 01:00 So I'm going to go to 8, and
- 01:02 this is going to show me the last page that I have here.
- 01:05 I'm going to go over to page 9, and now you'll see what the problem is,
- 01:08 we've got column spilling over onto the next sheet.
- 01:11 It's a little bit too wide, so I want to fix this.
- 01:15 Now, you're going to find that your Print Page layout tab has almost all of
- 01:20 the print commands you're going to want to work with.
- 01:23 Once you've done your preview,
- 01:24 notice that we have a dotted line showing where a page break is.
- 01:27 I really want to move this line just over here, but it's gotta come not just to
- 01:31 the right side of column G, but I've gotta pick up this text.
- 01:34 So what I'm going to do is I'm going to find this thing that says Width here, and
- 01:38 I'm going to change it from Automatic to One Page wide.
- 01:41 Notice that there is also an option to set your height, but that works
- 01:46 independently from width, so I could set it to one page wide, one page tall.
- 01:50 In this case one page wide by as many pages long is good by me.
- 01:55 And when I go back and
- 01:56 take a look at my print preview now, you'll notice a couple of things.
- 01:59 Number one, everything's been shrunk up so that all of my text fits here.
- 02:04 My Profit column is here and my total page count has dropped not to 8 but to 7,
- 02:08 because the scaling actually managed to get a lot of the data that was spilling on
- 02:13 a page 8 back up into an earlier page.
- 02:16 So that's pretty cool.
- 02:17 All right, now, what other options do we have?
- 02:20 Well, we could set your margins to be narrow,
- 02:23 maximizing how much we can get on the page.
- 02:25 If your data happens to be wider rather than taller, you could flip to
- 02:29 Landscape mode and will actually print out sideways on the page, which is perfect.
- 02:33 You can control your page size, depending on whether using A4, or letter, or legal,
- 02:38 depending where in the world you are, all of these are available to you.
- 02:42 Now, the next thing that I want to do here,
- 02:44 is I actually want to show a couple of other features, and for these I'm actually
- 02:49 going to jump into directly the more sort of advanced configuration.
- 02:52 So, for me personally, I rarely hit these last four buttons.
- 02:56 What I actually generally do is go and click this little page setup dialogue and
- 03:00 come right into Page Setup here.
- 03:02 Notice this gives me the ability to flip the portrait and landscape,
- 03:06 set to one pages wide by an unknown number of pages tall, and
- 03:09 my paper size is all here as well.
- 03:11 So you can get to all those through the buttons there.
- 03:14 My margins are set here as well, that margin dropdown,
- 03:17 set it to narrow margins for me already, so
- 03:19 I'd really don't have to worry about fooling around with the numbers.
- 03:23 But inside here I can get to play around with things like headers and footers.
- 03:26 And this is actually kind of cool because you have the ability to create custom
- 03:30 headers and footers.
- 03:31 I'm going to create a custom footer right now, and what I'm going to do is I'm
- 03:36 going to come over to the right section here, and I'm going to type in page space.
- 03:41 And then I'm going to click this little number symbol here which puts an end page.
- 03:45 And then I'm going to say of, and I'm going to click this one here this a total,
- 03:50 all right?
- 03:50 So page of pages.
- 03:53 We're going to say OK.
- 03:55 And you'll notice out here it says page 1 of 1, here it's page 1 of question mark.
- 03:59 I can also of course put in a custom header up here, we can say print it,
- 04:03 and we can put in the date and then space the time.
- 04:07 And if you want the file name or tabs, there's other options in here as well.
- 04:11 We say OK, and there we go.
- 04:14 You have the capability to control what's different on odd and even pages.
- 04:18 I rarely do that but it is an option that's there.
- 04:21 Finally on the last one, we're going to come over here and
- 04:24 we're going to grab our sheet.
- 04:25 The print area will allow you to zoom in and
- 04:28 only print a certain area of your worksheet.
- 04:31 For me though, what I want right now is I want this one.
- 04:34 I'm going to print a certain set of rows on top, and the row that I want,
- 04:38 I can take multiple rows, but I only want row 5.
- 04:41 I just want the header here.
- 04:42 On page 1 I'll get this, but every worksheet thereafter I'll get this as my
- 04:46 header, which is going to be really helpful for understanding my data.
- 04:50 I can also use an option to print columns at left if I want to do that.
- 04:55 If you'd like to print your grid lines, you can do that.
- 04:58 Row in column headings, you can do that as well.
- 05:00 I think that makes things look a little bit ugly but sometimes it's required.
- 05:03 So I'm going to turn them off for right now.
- 05:05 And if you want to include comments and notes, those are available to be displayed
- 05:09 either at the end of the sheet, or on sheet as needed.
- 05:12 Since this file doesn't have any, I'm going to leave it as none.
- 05:15 And now from here, I can go directly into the print preview, and
- 05:18 what you'll see is I've got my printed flag up the top.
- 05:21 My margins are very much left aligned here, and I've got page 1 of 8.
- 05:26 We're back to eight pages because with all of this extra stuff that I've added,
- 05:30 it actually takes a little bit more to fill in my data set.
- 05:33 So let's go to page 8, have a quick look.
- 05:36 Notice my header row is there, this is pretty awesome.
- 05:39 This is a nicely setup worksheet that actually gives me all of the stuff
- 05:42 that I need.
- 05:43 I'm going to be able to print, not lose track of my pages, and
- 05:46 even know when it was done.
- 05:47 So, there you go, a few different printing options to help make your reports,
- 05:51 look good, and save trees.
Lesson notes are only available for subscribers.