🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Page Setup Options

Compact player layout Large player layout

Locked lesson.

Upgrade

  • 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
04m:38s
Security Considerations
05m: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

© 2023 GoSkills Ltd. Skills for career advancement