GoSkills
Help Sign up Share
Back to course

Page Setup Options

Compact player layout Large player layout

Subscriber only lesson.

Sign up to this course to view this lesson.

View pricing

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

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.xlsx
32.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
Login to download
  • 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.

Custom Views
4m:38s
Security Considerations
5m:33s
Share this lesson and earn rewards Facebook Twitter LinkedIn WhatsApp Email
Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2021 GoSkills Ltd. Skills for career advancement