Locked lesson.
About this lesson
The steps to take to ensure that the model looks as intended when printed.
Exercise files
Download this lesson’s related exercise files.
Printing and Viewing Tips.xlsm50.3 KB Printing and Viewing Tips - Solution.xlsm
54.4 KB
Quick reference
Printing and Viewing Tips
Tips to use when printing and viewing your financial model.
When to use
It is worth taking time to set up print margins and to include headers and footers during the preparation of a financial model so that other users don’t print out the document later to find that nothing lines up or there aren’t any headings on the pages.
Instructions
Overview
- Workbook printing
- Table of contents page numbers
- Sheet page numbers
- Page margin consistency
- Print view consistency
- Workbook viewing
- Page view consistency
- Worksheet view consistency
Example
- Prior to making any printing adjustments, ensure that all worksheets have been selected for consistency
- To add a Header or Footer to each worksheet in the workbook:
- Select the ‘Page Layout’ section as shown above in the picture
- In the ‘Sheet Options’ section, there is a small arrow in the bottom right hand corner (circled in blue in the picture above). Select this to bring up the ‘Page Setup’ dialog box
- Select ‘Header / Footer’
- Select either the ‘Customer Header’ box or ‘Custom Footer’ box to customise what you want to show at the top or the bottom of the pages i.e. you can add the date, the page number, enter your own text, file name etc.
- In the ‘Page Setup’ dialog box, you can also select the ‘Print Preview’ button to see how each page will print out – you can then go back into each page to make adjustments to ensure that the pages look presentable once printed.
- Once you have found which page needs fixing you can select the ‘Page Break Preview’ (circled in blue in the picture below) so that you can move the page breaks to ensure the data fits nicely on the page
- 00:04 This always seems to be an afterthought in model preparation, but it shouldn't be,
- 00:09 it should be something you've got your eye on the ball with the whole time,
- 00:13 that's printing and viewing.
- 00:15 How often have you received a model from somebody else,
- 00:19 decided to print it out on a printer and then, run as fast as you can over there,
- 00:23 when you realize you have to take out a Brazilian rain forest,
- 00:27 the amount of pages It's got to print out onto.
- 00:30 You really should think about this, even if you don't have access to the printer
- 00:34 the end user may likely print the model out on.
- 00:37 Workbook printing consists of getting the margins right,
- 00:41 sheet consistency, page margin consistency, putting page numbers on,
- 00:45 explaining the actual file, and making it all look neat and tidy.
- 00:51 And guess what, EXEL's not very good at getting this result,
- 00:54 this is something you still have to do.
- 00:55 A second example, I'm going to use a model very similar to the one
- 01:01 I used to show the overall check structure in the model a few sessions back.
- 01:06 It's very, very similar, we've got a navigation sheet again,
- 01:10 we've got both the style guide, we got model parameters, etc.
- 01:13 But I've got this sales assumption sheet which goes on forever.
- 01:18 Yes its got some calculations at the end, but as long as the input are separated
- 01:21 from the calculations, it's still following graph being consistency,
- 01:26 robustness, flexibility to transparency, you can still find the inputs and
- 01:31 you know where the calculations are.
- 01:34 Great, more sheets here.
- 01:37 People forget about actually setting it up for printing.
- 01:41 And one of the problems can actually be that you don't have access to the end
- 01:44 user's printer, so it can be difficult to set up the margins wonderfully.
- 01:48 So yes, it's all very well being able to set up these and
- 01:51 go for custom margins, but if you haven't got access to the printer because you're
- 01:55 on a different network or in a different country.
- 01:57 That can be a little bit awkward.
- 02:00 There are useful things on here.
- 02:01 You can change the orientation between portrait and landscape,
- 02:05 here on the page layout section of the ribbon in page setup.
- 02:09 But one of the things I can actually do in here is set the print area, if I want.
- 02:14 I can just decide I only want to print this bit, not the whole worksheet and
- 02:18 press Set Print Area, and that's the only bit that will print.
- 02:22 Things that you should consider setting up universally,
- 02:25 and the way to do that is to highlight all the sheets leads.
- 02:28 Now, they've got rid, in later versions of Excel, of the go to end sheet here.
- 02:32 So, what you do is you hold the Ctrl button down, and you go right-click.
- 02:37 We'll see all the sheets here, so it takes me to the end.
- 02:40 If I hold the Shift button down, I've now selected all the sheets in here,
- 02:45 because you can still go in and set up headers and footers.
- 02:49 Headers and footers, where is that?
- 02:51 I can't see it here.
- 02:52 Well, it's actually if you click on this button here that no one notices,
- 02:56 it's like the options.
- 02:58 Click there.
- 02:59 Here we go.
- 03:00 We're in the page setup dialog box, header and
- 03:03 footer and I've got these options I can do.
- 03:05 But it's great to set all this up consistently in one go and
- 03:10 this is the easiest way to do it.
- 03:12 Custom header, you've got three sections, the left, the middle, and the right.
- 03:16 You can put things in like your own text, page one of 20, you can insert a date,
- 03:23 a time, where the file path is, file name, etc., etc.
- 03:27 You can put all that in, brilliant.
- 03:28 I'm just going to put this in the footer.
- 03:33 For the footer I've gained notice, I can have a different first page, if I want,
- 03:36 though I don't want anything, I've got defaults unknown if you want to.
- 03:41 I'm actually going to leave it unchecked.
- 03:44 I'm going to go for footer of the last section.
- 03:47 I'm actually going to put the file name.
- 03:50 On the right one, I'm going to put the actual date and press enter and then,
- 03:55 put time and here, I'm going to have page of however many pages there are in here.
- 04:03 Steve wants an ID, he can keep a running total.
- 04:06 I click OK, that will give me how it looks and you gonna see it here.
- 04:09 Now, this is very important, this scale with document, if you uncheck that
- 04:15 that can actually be quite useful because sometimes when you print out pages,
- 04:19 sometimes you have to shrink on the pages slightly to fit it all on one page.
- 04:23 If you do that and you have scale with document.
- 04:27 The actual filter will shrink, but it's nice to have it consistent on each one.
- 04:32 So often, you want to have that unchecked, just a tip.
- 04:37 Once you're happy, you just click okay.
- 04:39 Order, brilliant and it's just waring through.
- 04:44 I've got one sheet, as I've mentioned already, the cells assumption sheet.
- 04:47 Look, I've got this whooping big line in here.
- 04:49 You'd want to do this on each sheet to make sure that it goes through, so
- 04:52 you can actually highlight all the sheets.
- 04:56 So, what I would do is highlight all the sheets and
- 05:00 you can go click on here and print preview.
- 05:03 It's one way of doing it and, depending on your printer, you can scroll through.
- 05:08 See just how good or bad it looks and you get an idea of what looks good.
- 05:14 Yeah, it looks like a good sheet doesn't it?
- 05:16 And you can see what things you need to fix.
- 05:19 So, you go to those sheets and fix them.
- 05:22 Now, given I can't go on for 20 minutes in this video,
- 05:25 I'm actually going to just look at the sales assumption sheet.
- 05:28 And you can see that this dotted line shown doesn't look light as it is.
- 05:31 Notice if I actually put this in a page break view,
- 05:35 so I've got normal here, page layout and here's page break preview.
- 05:39 You can see it's looking.
- 05:41 Now, I can drag this across, quite like that and it will widen it
- 05:48 and then I could say, this is looking yucky.
- 05:51 I've domestic and overseas and then, of course,
- 05:54 a particular liamism, underseas here and then, overall.
- 05:57 Wouldn't it be good if I surely put that there?
- 06:01 Why is that gray line down here?
- 06:02 This is one of the problems with this.
- 06:05 Try to get the blue line in the right place,
- 06:06 that grey line doesn't show in the right place.
- 06:09 It's a nice little bug in Excel that doesn't happen on every machine, but
- 06:13 can infuriate.
- 06:15 And so, if I now go and have a look at that in print preview mode.
- 06:20 Yeah, looks good.
- 06:24 Missing the targets.
- 06:27 Now, how do I get to repeat titles?
- 06:28 I think I'll go back to the print titles button on the page layout tab.
- 06:33 Here, what I can actually do is seek rows to repeat at top.
- 06:39 I want to actually highlight rows 1 to the line below 10.
- 06:44 Click.
- 06:45 Print preview.
- 06:51 Wunderbar, looks good.
- 06:53 And that's the process you have to go through.
- 06:56 Look, you can't just sort of shirk on this, you've got to do it.
- 07:00 So, do remember to put this together because then,
- 07:02 when people print it out, it will look more professional.
- 07:05 You can annoy people when you don't put time into thinking
- 07:08 about how it's going to look printed out.
- 07:11 Practice it yourself.
- 07:12 That's a guide to printing and viewing.
- 07:15 Next time, we'll be looking at linking.
Lesson notes are only available for subscribers.