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.xlsx32.1 KB Page Setup Options - Completed.xlsx
32.4 KB
Quick reference
Topic
Page setup options.
Description
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.
Where/when to use the technique
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 and review the Print Preview
- Notice that we have nice headers, footers and repeated rows at the top of each page
- 00:00 In this video we're going to look at how to control different printing options
- 00:05 inside Excel because once you got everything set up, that's gonna be
- 00:09 the next job, is you're always gonna wanna try and put something out to paper.
- 00:14 Now, this particular worksheet has a bit of an issue.
- 00:17 And we can see that if we go to File > Print.
- 00:21 You'll notice that everything looks fine, except that, down in the bottom here,
- 00:25 it says that we're on page 1 of 22.
- 00:27 And we think, well that's okay, it could be lots of data.
- 00:29 Well, it could be, but let's just go quickly spin through this really fast.
- 00:33 And you'll notice that when we get to page 12 it's the last column from pages 12
- 00:38 through 22.
- 00:38 So it seems like a bit of waste of paper.
- 00:41 It'd be nice if we could get all those columns fit this table so
- 00:45 that it's one column wide.
- 00:48 Now you can see that the dotted line here, this is showing a page break
- 00:53 in our actual Excel worksheet, which is definitely showing that this last column
- 00:58 is sort of falling off the page, so here's the quick trick on how to fix this.
- 01:02 What we can do is we can go to Page Layout, and we can actually go and
- 01:06 tell Excel that the width should not be automatic, but the width should be 1 page.
- 01:12 And when we do that, you'll notice that this page break moves.
- 01:16 And when we go back into File > Print now,
- 01:18 you'll notice that the print preview is not 10 pages long, not 22 anymore.
- 01:23 And that's because it scaled it down a little bit in size.
- 01:27 But there's some other issues and
- 01:28 I know this is probably a little bit small to see.
- 01:30 But you can see that we have the title, the Page Setup Options here showing up.
- 01:33 And we can see that dark blue band for the top of our table.
- 01:36 But when we move to the next page, both of those go away.
- 01:40 We also have no indicator what page it is that we're printing.
- 01:43 How many pages?
- 01:44 We got ten, but it'd be nice to know page one of ten, two of ten, three,
- 01:48 of ten, some other details like that.
- 01:49 So, let's go see what we can do about this.
- 01:53 The place that we're gonna start on the Page Layout tab is Print Titles.
- 01:57 And on the Print Titles button you'll find that we have the ability to set
- 02:00 a Print area.
- 02:01 Now that would be if we only wanted to print these cells here,
- 02:04 we would set that Print area up and it would ignore everything else.
- 02:08 The next one that we can deal with is Rows to repeat at top.
- 02:11 So what I am gonna do is I am gonna click this.
- 02:14 I'm gonna say, lets assume that these top three rows are our report headers.
- 02:19 So I've highlighted 1 through 3, left clicked and dragged, and
- 02:22 its put those in there.
- 02:23 It'll now print the first three rows on top of every page.
- 02:28 If I had categories on the left-hand side, I would also do this with categories and
- 02:32 select just column A, but in this case it's just Date, so
- 02:35 that wouldn't make a lot of sense.
- 02:38 You can also see we have the ability to modify the Header and Footer.
- 02:41 So in the Header here,
- 02:42 we have a few different options that we could could put in here.
- 02:46 I'm not gonna bother with those cuz I always like to do things kinda
- 02:49 custom anyway.
- 02:49 So, what I'm gonna do is I'm gonna put in here Printed space.
- 02:54 And then what I'm gonna do is I'm gonna click the calendar, so
- 02:57 that will give me the date.
- 02:59 I'll put a space, single click the time.
- 03:03 So this will tell me the date and time that something's been printed.
- 03:08 I'm now gonna move to the Footer and down here I'm gonna say Page.
- 03:13 And I'm gonna click this single number here, so
- 03:17 that'll give me the page number of the total number of pages.
- 03:22 That's kinda cool, and I can even print the workbook name data down here as well.
- 03:27 This one will give me the tab, if I wanted to do that.
- 03:30 So, that could give me some really good information,
- 03:33 so it'll tell me that this file is named Page Setup Options, it's page 1 of 1.
- 03:37 That's cuz it only looks at one at a time here.
- 03:39 What time and date I've actually printed this.
- 03:41 That's kind of cool.
- 03:43 I can also set my print margins here if I don't like these ones,
- 03:46 if they're a little bit too big I could go and change those down to less, and
- 03:50 I have a few options in here about adjusting the size.
- 03:52 So, you'll notice that I fit to one pages wide by as many pages tall as I need,
- 03:57 that automatically scaled the document to 89% of what it was before.
- 04:01 So let's go and say, OK here.
- 04:02 And we'll go back to File, we'll hit Print, and
- 04:06 let's what this actually looks like now.
- 04:09 Again, it's kinda hard to see, but you can see that there's something up the top,
- 04:13 so this will be the printed date and time that we configured before.
- 04:16 We can see we've got something down at the bottom left-hand corner and
- 04:19 that would be the file name.
- 04:20 And down at the bottom right-hand corner, we can see that we've got something
- 04:24 that is gonna be the page number of however many pages.
- 04:27 And when we go and spin through this,
- 04:29 you'll notice now that when I click the next page, these things stay.
- 04:33 So the the date is changing in the middle, we can see that cuz it's moving, but
- 04:37 the actual footer and headers are staying around to help us out.
- 04:41 The other thing you may want is if you want more advanced printing options,,
- 04:44 you can click your Show Details page.
- 04:46 And that will show you a few more things here where you can change the Letter or
- 04:50 Page Size and whether you'd like to print the Active Sheet or the Entire Workbook or
- 04:54 just your selected area.
- 04:56 All of these are valid options that you may want to try at some point.
- 05:01 So, this is how we set up our printing options.
- 05:03 But the biggest thing is really trying to get to that one page wide by
- 05:06 as many pages tall.
- 05:07 That's a really common occurrence for a lot of people.
Lesson notes are only available for subscribers.