Microsoft Excel MO-200 – Level 2, Section 4 – Configure Excel worksheets and workbooks to print Part 3

7. Inserting watermarks

Now, the header and footer tab doesn’t quite seem to be the right place to talk about inserting watermarks. But believe it or not, this is where you can do it. You can insert a picture and blow it up to be a full size watermark. So I’ll click in the custom header. We’ve got a spare section here. Now only one picture per section is allowed, so you can have up to six pictures for the header and footer.

So we’ve got these two icons, insert Picture and format Picture. So I’m going to insert a picture and what I’m going to do is I’m going to search Bing. So that’s an alternative to Google. For a picture relating to the word Draft, I want a watermark that says Draft. And here you can see such a watermark, a nice one, going diagonally down. So click Insert and we have the word picture inserted.

And now we can format this picture so we can say exactly how big we want it to be. So if you want it to be an entire paid size, that’s absolutely fine. So we’ll make it 13 inches, that’s about 37 CM by ten inches, that’s 30 CM. We can have it in and washed out or black and white type of color. We can adjust the brightness, we can adjust the contrast. So let’s go for wash out, click OK. Click OK and click OK. And we’ll go to the print preview and see what this looks like. And I must admit, I can’t actually see it. It’s so in the background I can’t see it.

So we’ll go back into page setup, go into header to the footers and just reformat this picture. Get into the right section first, so that instead of wash out, it is automatic. So now let’s have a look.

And there we can see the word Draft, a bit too big perhaps. So we’ll reformat it again and just shrink it back into size. So we’ll make it back to seven inches. There, much better. So you can see how the headers and footers allow you to insert a watermark. And it could also be a logo for your particular company. So let’s say your company was WordPress. So I’m going to insert instead of this, created by Philip Burton or no, as well as, as well as created by Philip Burton, I’m going to insert a picture. So I’m going to click on picture. Remember, I’m allowed one per section.

I’m going to look for WordPress. Let’s say this is our company. Click insert click OK. Click OK. And here you can see the WordPress logo. I would probably shrink it down in size because that’s a bit too big for pictures, little icons to be, it should be a lot smaller. So I will rotate, I will get it down to a quarter of the size 25%. That might look a lot better.

Now, what happens if you try to insert a second picture in the same section, it will say no, you can’t you can either replace the existing picture or you can keep the picture. So let’s say I wanted to replace the picture so I could do my own searches. If you’ve got a file that you’ve previously downloaded, then you can click Browse and find that particular picture. So this is how to insert your company logo on the bottom left, bottom middle, bottom right or on the top and also watermarks using your headers and footers.

8. Page setup, Part 3 (Headings etc.)

The fourth tab of the Page layout dialog box has some really cool miscellaneous items. First of all, we have got the print area, so we had to look at the print area earlier. But whereas you had to press this button here and highlight what you want, you can actually alter it here. So I wanted it to go to n 21. Alternatively, you can press this little button which will allow you to select the area and then press Enter. We get back to the dialog box so I can now alter it again if I wanted to. You can show which roles are to be repeated at the top and which columns are to be repeated on the left. This is like freeze panes, except freeze panes only affects your view, it doesn’t affect the printing, whereas this affects the printing and not the view.

So if you wanted your headers to be frozen both on your view and your pane, then you would have to do it twice. Once in the paid setup box and then another time in the view menu. If you don’t want rows to be repeated, then you can just highlight them and press Delete. If you want to update them, then press this little button and you can highlight the rows or columns that you want. Selecting grid Lines do you want grid lines to be printed? Normally you don’t see grid lines in the print area, you see them in the normal view, but you can see them here.

But generally when you print, you don’t actually see the grid lines. Sometimes you might actually want to see the grid lines in addition to seeing the rows and column headings. So you might actually want to be able to see column A, column B, column C and row six, especially if you’re doing a draft document. So if I take those off, you’ll see the grid lines here, for instance, disappear black and white if you wanted to be black and white for some reason, and draft quality, I don’t think I’ve ever used those, but they’re there comments now.

We’ll be looking at comments later in the expert course, but they can be displayed on the sheet or at the end of a sheet. And if you got sell errors, it’s handy to modify what could be displayed. So you might want to or hide all cell errors. But personally I generally use Raw and column headers.

When I’ve got a new spreadsheet and I want to have a look at it on the page, I find that quite useful to help me understand a spreadsheet. And then finally the page order. If you’re not just having one page wide, then you will have to decide how you’re going to print out. Are you going to for instance, if you’ve got four pages? So let me start a new notepad. If you got one page there, one page there, one page there. Do you want it to be displayed going across or do you want it to be displayed going down and then going across? So whichever way you want, you can adjust that. Now, you might have noticed that a lot of these options can be set in the menu.

For instance, we’ve got margins, orientation, paper size, print area, and you might be looking at this going background. That’s how I can do a watermark well, unfortunately, it’s not quite so if I was to put in, say, a final image. So this is the final draft, so the end maybe with the final four click insert, it shows it in the background of the spreadsheet. So here you can see this final four, but when you print it out, it doesn’t get printed. So this is only to show what you can see in the background while you’re working, as opposed to what gets printed out. I don’t think I’ve ever used the background. I think it’s a bit messy if you’re something unobtrusive like pebbles or a sunrise sunset, yeah, that could be a nice environment.

But if it’s got really bold colors, then you got to make sure you can actually see your data on top. So I think I’ve used background once and after doing that, I think there was a particular reason never to use it again. And then over here we’ve got the scale to fit, so we can say I want it to be one page wide or want it to be one page tall, and it works out the most appropriate scale for that. But all of these things are also in the dialog box. And this dialog box allows you to fine tune things. And there are some things here like the custom headers, custom footers that you simply can’t find in the ribbon. So this is my goto place the page setup dialog box.

9. Configuring workbooks to print

The final bit in this session is configuring not just worksheets, but workbooks to print. So far, we’ve been concentrating on just the one worksheet, where you can highlight all of the worksheets and then go to page setup and do a lot of adjusting. And you can adjust them all to a portrait or landscape, to have the same header and footer, to have the same margin. However, this fourth dialog box, you can’t change the print area for them to all to be the same.

You can’t change the print titles or the rows, you can’t change the print title columns, you have to adjust each individually. So you have to go into your first spreadsheet and adjust it, and then the second spreadsheet, and so forth. So if you’re going to start to create a workbook and you have some standard repetition, some standard printery, standard number of rows and columns, then my advice would be to start off with a completely blank spreadsheet and configure this however you want it to be configured. And then that’s your template, and then copy that and copy it. Another way of copying is to drag it, like moving, and hold down the control key and copy it, and copy it and copy it.

And now you’ve got six spreadsheets, which all have roughly which all have exactly the same setup, which you can then alter according to what that particular spreadsheet needs. So this way we’ve avoided the problem of highlighting all of the spreadsheets going into the page setup dialog box, and not being able to adjust the print titles or the print areas. However, if it’s anything else, the heads of the footer, the margins, the page, feel free to edit all of those en masse. So here they’re all under letter size. I’ll change that to the British, a four size, and that’s adjusted it for all of these spreadsheets. So now you should be well versed in being able to print and set up individual spreadsheets and workbooks for printing.

10. Inspecting your workbook prior to distribution

Now, before sending a workbook out, it might be a good idea to check if there’s anything hidden that you don’t want anyone to see. It could be like who last offered it to what computer and so forth. There were three basic categories of this sort of information checking hidden properties or personal information, accessibility issues and compatibility issues. And the good news is, all three of these are available in the same place. If we go to file and info. So we can check for issues, we can check the workbook for hidden properties, we can check the workbook for content that people with disabilities might find difficult to read, and we can check for compatibility features not supported by Excel 2003 and below.

So let’s inspect the document, right, we need to save the changes. So yeah, I’ll just save them. So I’ll call it level two, session four. I will now inspect the document and you can see it looks for some really advanced stuff. We’ve had a quick look at macros, we’re not touching ActiveX controls. In the expert course, we’ll be looking at pivot tables and Pivot charts and that sort of thing. So a lot of really technical things. So it can inspect and it will have a look for this.

And here we have some information and we have the option occasionally to automatically remove. So if I want to remove the offer, fine, click remove, all of that’s removed. Want to remove the headers and footers? Fine. There may be some information there that you don’t want people to see. So once you’re finished, click clause. If you are sending this to somebody with disabilities, then you might want to check to see if there’s anything that’s a bit difficult. For example, there’s a warning that sheet three, it’s a default sheet name, which might make it more difficult. And that’s actually a fairly good point. We haven’t given that a real title, so we might want to fix that. And some hard to read content. The color is too similar to the color behind it, so that might be useful to know and it shows you how to fix it. And finally we’ll have a look for issues of items that would not allow this spreadsheet to be run as well in Excel 2003 and before.

And you can see there are an awful lot of issues. There are six different issues that potentially could happen to stop smooth running of this spreadsheet in Excel 2003, but that’s way over a decade ago, checking compatibility was a huge issue in 2007. It’s not so much now this year, but it’s still there if you need it. And we can copy all of these items to a new sheet, all of these particular things. And so you can see where all 1721 occurrences of this particular formula are.

And you can see these are all hyperlinked, so you can click on them and it will show where it’s talking about. So this is how we can check a spreadsheet or workbook before sending it out. We got a file info, so there’s stuff you might want to remove. There’s stuff that people with disabilities might find difficult to read, and there’s stuff that a person using Excel 2003 or earlier might find difficult to deal with when opening the spreadsheet. And if you are going to send this spreadsheet to someone with Excel 2003, I suggest you go to file Save as and then save type excel 97 Hyphen 2003 workbook.

11. Practice Activity Number 5 – The Solution

Hi, how did you do with this practice? So first of all, we opened up the practice file 0204. We want to change the print area so that it sells B nine to e ten while the quickest way of doing that is to highlight b nine to e ten. Got your page layout and set print area. Plenty of for ways of doing that because change the print scale so that it will print at 80%. Well, at the moment I can’t adjust the scale because it is printing to one page width.

I have to change that to automatic and now I can change the scale to 80% again. I could use the dialog box to do that as well. Change the margins so that the printout will be in the middle of the page both horizontally and vertically. So you have to use the dialog box for this and it’s in the second tab. And you can see currently it is centered horizontally only. And you can see that by the little grid here. So that’s neva that’s horizontally and that’s horizontally and vertically. Change the footer so that the date and time are included in the bottom right of the page.

So go to custom footer and we put in in the right hand section date, space, time. Change the print title so that rule eight will be repeated at the top of every single page. So we’re going to sheet and we select this little box and click on rule eight. There we go. And look at the print preview of this page. So there we can see the print preview of this page. All right, I hope this is starting to make sense or this does make sense to you and I will see you on the next session. If you’re it doesn’t, then please go back to the earlier videos and have a look at them. And you may want to just send me a question using the Q, the nays and hopefully I’ll see you on the next video before long.