Microsoft Excel MO-200 – Level 2, Section 3 – Customise options and views for worksheets and workbooks

6. Managing macro security

Now we’ve done this macro, let’s save this workbook. So I’ll just click File Save and you can see there’s a problem. This particular file is called level two, sessionfreestart XLS x. The X indicates a standard macro free workbook. In other words, no macros. If I was to click yes, then the next time you open this spreadsheet, so you close it and reopen it, you’ll find all of the macros we’ve recorded have disappeared. And this is by design. Macros can do huge amount of things. They’re very powerful. I’ve got a different video course about this here. But you don’t need to go into huge debts of macros for this particular course. But you do need to know that you cannot save macros into a macro free workbook. So instead I’m going to say no. And I don’t want to save it again as a Xlsx. So I’m going to choose a macro enabled workbook. Now, I can also use an XLSB that will also save the macros as well. So I’m going to call this level two, session three XLS. M.

Now, also I could also use XLS. Don’t recommend that. This is for Excel 2003 and below. And macro security wasn’t as tight as it is now. And you may lose some features that you currently got. So don’t use that, use Xlsm or XLSB. So click save. I’ll close it and open it up again. And now I’ll go down and press CTRL and R and well, it’s not highlighted it, it’s done something else, but it’s not highlighted it. So I’ll just undo what it’s done lots of times. So the macro hasn’t worked. And this is by design. You’ll notice that it says macros have been enabled. So disabled macros, very powerful things. You don’t want them to run whenever they want to without your consent. So what I’m going to do, because I trust these macros, because I was the one who created them, I’m going to click Enable Content. And now when I click Control and R, then it highlights.

So let’s close it again. I’ll save the changes, open it up again. You notice that the message has not come up this time. The computer now believes it’s a trusted document because I’ve already said run macros when I open. Now this is time for a little bit more complicated security and we need to just get into it briefly. If I go into the File menu and go to Options, we have a Trust Center on the left hand side and it’s got so many options that it actually contains its own dialog box. So click Trust center settings. The first setting I want to look at are macro settings. So what we’ve got are four different types of enabling or disabling macros. Disable all macros with notification is the default. In other words, when you open up a spreadsheet with a macro, it will not run, but it will let you know that it’s not running. And allow you to run them. Now, this is the default, and I would advise you to stay on this default unless you’ve got a particular access reason, a particular reason why macros won’t run. And you annoy it when you see it.

The harshest, and I’d never recommend you do this is to click on Disable All Macros without notification. That is very harsh. You never get any macros, really only used for computers, which are really security sensitive. Disable all macros except digitally signed macros. Now, this is using a program called Self Cert, or a more formal digitally sightening system to allow you to put a certificate with your Visual Basic code and then enabling all macros. And you can see it says Not Recommended. Potentially dangerous code will run. This means any spreadsheet you get from anywhere, it could be from the Internet, you open it up, all of its macros will run. So I’m saying you can change them, but I’m also saying don’t unless you’ve got a really good reason.

So more of interest, perhaps, are trusted locations and trusted documents. With Trusted Locations, you’re saying this particular folder, I’m fine with you opening any of these documents and allowing the macros to run immediately so I can add a new location. So I’ll click on Add New Location, click on Browse, and I’ll go to the Documents folder, and I’ll go to my Excel One folder and say, okay, anything in this folder, I’m okay for it to run in macros. In fact, anything within a subfolder of this folder, I’m okay. Now, you may notice allow trusted locations on my network. Not recommended. And you may be wondering, well, why is it saying that? Well, networks are open to more people than dish yourself. So let’s say you save five spreadsheets in a particular location, a particular folder on the network, somebody else can come in and save a 6th, and you just open that one up and it automatically runs even though you weren’t expecting that one. So if you do want a particular location on a network to be trusted, then you have to check Allow Trusted Locations.

We’ve also got trusted documents. These are very specific documents that you want to be enabled. And for Trusted Documents, you won’t be prompted to run the macros next time. So you can see this particular document that we’ve got. Level two, session three is a trusted Document because there was no prompt next time you opened the document. So again, if you’re really security conscious, you really don’t want Trusted Documents and check Disable Trusted Documents. But generally the default is fine. So these are the three bits that you need to know about in the Trust Settings. Macro Settings allows you to change globally your macro settings.

Trusted Locations say there’s a particular folder that I’m happy for all of those workbooks to run with macros, and then we have your global settings for your Trusted Documents. So it’s down to you as to which setting of these you do. Personally, I would keep the default. And if you want to save documents with macros make sure you save them as a dot XLS, M or XLSB. And add those locations if you want to trusted locations so that they will run first time.

7. Changing workbook views

Just because we are seeing a spreadsheet in a particular way, a particular view, it doesn’t mean that this is the only view we’ve got. In fact, there are three main views and if you go to the view tab, you’ll see the main views are Normal, page Layout and page break. So at some point you might want to change the view. Now this is the normal view view. Now, it’s easier to see what the normal view is by having a look at some of the others. So this is the page break layout view. So you can see these little dotted bars. This is where the pages start and end.

So here you can see cell K 57 is the bottom right of the first page. And if I scroll all the way to the bottom, you can see that this document will be 34 pages big. Now, it could be you might want to alter where the start of a page is. Well, you can actually drag these dash lines to make them in a more fixed place. But we’ll get into this in a future video. So page break preview is where the page breaks are. And then you should also notice that the edge is gray. So in other words, it won’t be printed compared to normal, where the edge is in white because it’s not considering whether it’s going to be printed. And those are the two views that I generally use.

I usually use Null because I don’t want to I don’t want to see page one. All over the place there is another view called page layout. It interferes with something called freeze panes, which I’ll get into later. So I want to show you what the page layout looks like. So it will temporarily disable freeze panes. And now you can see the individual pages with headers and footers, which again I’ll get into in later videos. So you can literally see the edge of a page which you can contract and expand by double clicking on it. I rarely use this. I really don’t see the need for it in Excel, though you might disagree with me. And that’s great because that’s what this course is all about, finding what works for you. So these are the three main views normal, page break and Page Layout. You can also change between the three using these icons down here, although unusually the order is different. We have normal and then page layout. Whereas up here we have normal, then page break preview. Very odd. So generally I stick to Normal unless I wanted to see where the actual breaks are on a particular page.

8. Using zoom

Now, one thing that’s different between these two views is the zoom is slightly different. Now, you can adjust the zoom quite easily down here in the bottom right hand corner. So we have this bar going all the way down to the bottom. So you can see we get to 10%. Obviously you won’t be able to actually see the text of that particular view zoom, but you can have an overview of your entire high spreadsheets. Would you wish? 100% is the standard and you see this little mark in the middle denoting that and then you can go all the way up to 400% if you really want to. Typically I’ll work at about 85, 70%, but it depends on your screen setup. Now, if you wanted to go to a specific zoom point, then you can click left and click on the 100% or whatever your zoom is on the bottom right hand side. This opens up the zoom dialog box.

So if you wanted to go down to 74%, you can do that there. You can also use the zoom in the view menu. So we got zoom that gets you a diagonal box. You can go to 100% or you can do a selection. Let’s suppose I went out really wide and said I wanted to zoom on this particular selection. I can click on Zoom to selection and it will show me a zoom function, a zoom feature resolution that will allow me to see those particular cells. So zooming in and out, it doesn’t affect how it will print you’re an artist. If I go to page break, preview and zoom out, it will have the same number of pages regardless of what the zoom is like. It’s just how you view it. If you wanted to change how many pages you’re going to see, then that’s all about the page setup and we’ll have a look at that in the next session.

9. Adding values to workbook properties

Let’s say you get to the end of the spreadsheet, you’ve entered all the text and you want to say this is my spreadsheet, I was the one who created it. Well, you can if you go to file, you’ll see that you’re in the info section and you’ll see that on the right hand side we’ve got some properties such as size, title, so you can put in the title my level two session three spreadsheet you can put in tags. So I’m going to say that this says a financial spreadsheet categories. Okay. I’ll put in financial as well. Now you can’t edit last modified or created or last printed, but you’ve got people such as authors. So this is me, hello. But you can add other people as an author. So I’ll put in Douglas Adams as an additional author.

For instance. Now these are some of the properties. You can see small properties by going to this Properties dropdown and click on Advanced Properties. So this gives you a dialog box which allows you to put in things like Company and Manager and comments and that sort of thing. And you can see some more general noneeditable properties, some statistics. Again, not editable the contents of the worksheets for instance, and any named Rangers, we’ll talk about that later. And any custom contents that you want to put in. So for instance, suppose I want to do put in language, so I want to language English as a custom. Or maybe you’ve got Content type, maybe that is important. And I’m going to say content type music for click add. So now I have a new category called Content type. So if I click OK, I can save this and there we go. But what use is all of that? Well, if I go into Windows Explorer for level two, session three, and I right, and click on this file and go to Properties, you will see in the details the authors Philip Burton and Douglas Adams, the tags, the categories, and you might even be able to search for certain things.

Like, for instance, if I wanted to search for financial. You can see even though my spreadsheet has nothing to do with financial, because I’ve given it a category and a tag of financial, it comes up in search results. So this allows you to find your spreadsheets much more simply if you give them a tag. For instance. So this is the huge advantage about using properties. It allows you firstly to tell others about the actual spreadsheet and secondly allows you to find it again.

10. Displaying formulas

Let’s say you have a workbook with lots of formulas into it and you want to investigate the workbook because you want to see if there are any consistencies. Or maybe you just want to actually look at all the formulas en masse. Maybe oh, this one isn’t the same as all the others. Maybe there’s a change that needs to be done. Well, it’s a very simple toggle to view the formulas instead of the values. And it’s in formulas, show formulas. And if I click on it now, you can see if I expand column C, we don’t just have the results, in fact we don’t have the results at all. We now have the formulas being shown. And that can be quite powerful for error checking. If you want to investigate somebody else’s formulas. Now, it does do other things as well. If I go over to the dates, these were the dates last modified, they are now numbers and we’ll go later to show why these dates have changed from being a date to a number, what these numbers actually represent.

But the most important thing is that you are able to now see the formulas and if you wanted to get rid of that then go to formulas and click show formulas and that will then hide the formulas. There is a more complicated way of doing this and that is file go down to options and there’s this big formulas tab and you’d think you’d been there, but no it isn’t. It’s in the advanced tab. And if I go down a couple of pages you will see that there is a show formulas in cells instead of their calculated results. That is another way of doing it, but far easier to go to formulas and click on show formulas. Now it doesn’t mean that the values don’t get calculated and they’re formulas relying on those formulas aren’t calculated. It’s just a difference in what you actually see. So show formulas allows you to see the formulas instead of the values.

11. Freezing panes

Now in this spreadsheet that I’ve got at the moment, we have got the top four rows being header rows. In other words, they show things, they’re not really data. It’s like the beginning of a report and it’s things that you want to be repeated. For instance, if I want onto a second page, I’ll probably want to see all of this or maybe just this particular row. But look what happens when I scroll down. It gets pushed off the top and I really want always to be able to see these headers. How to do this is called freezing the panes. Now freezing the panes freezes a point of the sheet to keep it visible while you scroll through the rest. So it’s most likely to do with headers and labels. So the way to do this is to go to view freeze panes and we’ve got three options. So the second option just freezes the very top row. So if all you want is the top row to be frozen, then that’s what will happen. The third option just freezes the first column. So if I scroll across then the first column will always be visible. So I’m going to unfreeze those panes. What I want to do however, is to freeze the first four rows. And what I need to do is to go to the point that’s just down and just to the right of where I want to be frozen. Because this final option, freeze panes, freeze panes, freezes everything that’s above the cursor and to the left of the cursor. So at the moment I’m in a five.

So it’s going to freeze the first four rows and zero columns because there are no columns to the left of column A. So if I was to do that now you can see that these first four rows are always going to be shown. So here’s a quick test for you. Currently the cursor is in cell B seven. If I go to view freeze Pains of Panes, how many rows and how many columns will be visible? You might just want to pause this video just to have a think. Well, it’s going to freeze everything above and everything to the left. So it’s going to freeze the first six rows and one column and that’s what it does.

So wherever we scroll, we have got those columns visible. Now just one word of warning, this is based on the current view. So let’s say I scroll down so that cell A four is the first row, first cell that we can see. I click in a five and I go freeze panes, freeze panes. So it freezes everything above and everything to the left. But I can’t see rows one to three. And wherever I go I can no longer see rows one to three. They’re not hidden, they’re frozen out of the view. So if you’re having problems with a spreadsheet that’s somebody gives you and there are rows at the top or columns on the left hand side that aren’t visible and you can’t see them, then try unhiding them. And if you can’t unhide them, then try unfreezing them. And then have a look around.

12. Splitting the window

Now, there is another way of allowing you to see more than one part of the spreadsheet at once. This one I’m not particularly keen on and is called Splitting the Window. How it works. Let’s say I wanted a dividing line above Raw Ten. So I highlight Raw Ten and I go to view Split. This one window has now been divided into two with two independent scroll bars.

So I can scroll on the top half of the window and I can scroll on the bottom half of the window. Independently, it doesn’t allow you to see a different spreadsheet. These are two views of the one spreadsheet, so I can select split again to get rid of the split. Now, here’s where it gets a bit more difficult. If I was to click on Sell e five, for instance, and now split, our window has now been split into four.

And there are two vertical scroll bars and there are two horizontal scroll bars, each of which act independently. Personally, I don’t use a split. I don’t find it that clean. I find it quite messy, in fact. So there are, I suppose, some occasions when you might possibly want to be able to do that, but the vast majority of the time I don’t. And if you freeze pains and then try to split, you’ll notice that it’s now split at the freeze panes.

So, personally, I don’t use a split. I use freeze panes just to be able to have a look at different parts of a spreadsheet. Now, you could, if you wanted to add a new window and that duplicates the window that you are seeing. So now we’ve got two independent spreadsheets, which you could put on two different monitors, if you’ve got two monitors and have a look around there. But again, this gets more complicated. I personally just usually use the freeze panes and leave it at that.

13. Practice Activity Number 4 – The Solution

So how did you do with this practice test? So open up the practice file 0203. Hide the spreadsheet called invoice Two. So lots of ways of doing this, right? And click on invoice two and go to hide. Alternatively, I can go to the insert, I can go to Format Hide and Unhide and hide the sheet. Either one of those will work. Record a macro that changes the highlighting in cell C four from blue to yellow. So I go view macros, record macro. I call this my highlight.

Click OK, going to cell C four. Change it to yellow. Press stop. There we go. Change the zoom of the spreadsheet to 150%. So I can drag this to the right. If you’re having problems getting it to 150%, then you can click on this to get the dialog box and just type in 150%. Add the spelling button to the Quick Access toolbar. So I’ll click on this drop down and is spelling one of these? Yes, it is. So I’ll just check spelling and there it is. Assign the keyboard shortcut Alt plus Five to this new button. Well, at the moment it has a keyboard shortcut of Alt seven. So how are we going to do that? Well, what I need to do is move this up so that it becomes the fifth item. So now it’s the fifth item and you can see that it is Alt five. Show the formulas.

So you can see the formula in cell D nine, I mean D five. So show the formulas. We’re going to formulas and we click on show formulas. So here we can see the formula. Freeze the panes so that the first seven rows are hidden and row number eight is always displayed. So I’m going to scroll down so that the first seven rows are off the top of the screen. Click on Sell a nine which is immediately below row eight, and go to view freeze Panes. Freeze Panes. Go to the Workbook Properties and add the word invoice to the categories properties. That’s how I do that. Go back to the Invoice one spreadsheet and change the view to Page Break Preview. So, two ways of doing that.

You can go to View Page Break Preview, or you can go to the bottom right and click on the Page Break Preview icon. And then split the window into two. So view split. So it doesn’t matter where you split it. There we go. Because you’ve already fraughtened the pains so it will do the default at the freeze pains. So how did you find that? Were you able to remember how to do most of these items? If so, good. Let’s see you on the next video. If not, you might want to revisit one or two of the videos in this session and then I’ll join you in session number four.