MO-200: Microsoft Excel (Excel and Excel 2019) certification video training course by prepaway along with practice test questions and answers, study guide and exam dumps provides the ultimate training package to help you pass.

Level 2, Section 3 - Customise options and views for worksheets and workbooks

1. Hiding worksheets

Hello and welcome to session three. And we're going to use a workbook that I've attached as a resource to this particular video. So if you come here from Level Two, Session Two, it's exactly the same. Now, I just want to reiterate a point that I made in a previous video. When I create workbooks, sometimes I don't necessarily want the end user to be able to access the raw data. I want them to be able to see the formulas I've used and maybe type in a few values.

But basically, I want to restrict the access they've got. Now, this workbook contains three spreadsheets, but let's focus on the input spreadsheet, which I don't want the user to see. No problem; I can hide it. I can write and click the link; I can click on the spreadsheet; I can go to hide. There it is, hidden. There is no proof it ever existed. There is, after all. If you right-click on any spreadsheet that's visible, you'll see that hiding is enabled.

And if I click on "hide," I can say, "Oh, there's a sheet that's hidden. I can unhide it." There it is, everything I want to see. And now if I write and click, you'll see that Unhide is greyed out; it's not enabled. There are no hidden spreadsheets hidden. Can I hide Sheet 3 if I hide this one, these move to spreadsheets? Yes, I can hide as many sheets as I want, with one exception. Can I hide my existing spreadsheets? I'd be left with zero spreadsheets, and they won't allow me to do that. If you say that you want sheet three to be the only visible spreadsheet, then you've got to unhide the one you want visible and then hide the other spreadsheets.

So this is why, when I receive a newssheet, I check to make sure there are any hidden spreadsheets or just out of curiosity, because I want to understand the newssheets I receive better. So I generally first see if there are any spreadsheets I cannot hide and whether there's any greater depth that I can go into that the end user might not necessarily want a novice to see and make a mess with.

Now, there are ways to stop the end user from unheeding spreadsheets, and we'll have a look at those ways as part of the expert consultation. But for now, this is how you hide and unhide spreadsheets and deduce the existence of these hidden spreadsheets. You can also hide spreadsheets by going to Home Format > Hide and Unhide and hide sheet. But for me, it's a lot easier just to right-click on hide and unhide on the actual spreadsheet itself.

2. Customising the Quick Access toolbar

Now, during the course of this course, we'll be going through a lot of these menus. "Oh my goodness, this finger being hidden away, I want to use it very frequently, but I keep having to go to View and Freeze Painsand this drop-down box, and you can have things that are much more accessible for you," you may be thinking. And it's called the QAT, right up here at the top, the Quick Access Toolbar.

And we've already got some things there. We've got save, undo, and redo. The list might be different. Now, the good news is that you can customise this Quick Access Toolbar to add your own items by dropping this drop-down list or by clicking this drop-down arrow. Now, first of all, it will show some things that you might actually want, like new open emails, spelling, and sorting.

But there may be more that you want. And if you want more than just new things added or taken away, then you can click on these more commands. Now, this opens up a fairly complicated dialogue box, but you can ignore most of the complications. You can ignore the left side, for example. Here are your most popular commands, but you can choose any particular tab. So I wanted to go to the View tab, and I can put any of these icons onto the Quick Access Toolbar. So as soon as I do, maybe I'll click on the freeze panes. As you can see, it crosses over to the right side. So click OK.

And now you can see that we have the same three panes that we have over there. Now, if you want to remove an icon, you just click on the icon and click Remove, and it gets deleted from the right-hand side. It's still on the left hand side. You can still read it if you want, but it's no longer available on the Q&A. Now, the Quick Access Toolbar doesn't necessarily need to be at the top, though I actually find that to be the best. You can show it below the ribbon. But the problem with that is that it takes up more real estate. It allows another row. So keeping it at the top, I find, works much better for me. So the Quick Access Toolbar, as simple as this, has the icons you want, and if you want some more, just add them in.

3. Customising the Ribbon

Now, just like you can access and customize the Quick Access Toolbar, you can also customize these menus. And the easiest way to do that is exactly how we access the Quick Access Toolbar. So click on the drop-down box and go to More Commands. And on the left side, we have Customize Ribbon. So all of these menus are part of the Ribbon. That is the quickest way. There is a slower way. You can customize the ribbon by going to File and Options and then Customize Ribbon. However, I find that going through the QAT is much faster for me. So you can see on the right-hand side that we've got all of these tabs, and you can try to hide individual tabs. As you can see, I simply deleted the Home tab, and it has vanished. probably not the wisest move. So I'll just put it back in.

So let's say I've got a good idea. I want to go home. I've got this clipboard, and I want to add an additional icon there. So can we do this? So, I will expand the clipboard, and you can see that we have got these five icons representing the five icons that are currently there. One of them is hidden. But what if I wanted to go into all commands and scroll down? I wanted to add "paste" as a hyperlink. So, I need it in the clipboard. So I'll click Add, and you can see I can't, and you'll also notice that these are greyed out as well. And this is something that Excel has done since 2007. You can't alter the Home or Insert and Draw tabs. Unfortunately, it used to be that if you wanted to play a prank on someone, you could get rid of their font and other icons. You can't do that now.

So they need to be added to a custom group. So what I'm going to do is add a new group to the home icon, to the Home Ribbon tab. So here is my new group. So I'm going to click Rename, and I'm going to rename this paste—let's call it. So I clicked on a new group and then renamed it, and now I'm going to add a pasted hyperlink over to this new group. So now when I click OK, you can see we have a new group. The home set of menus is in the "Home" ribbon, which is fantastic. And here it is. So I could, if there was a hyperlink, use that to quickly paste. This is a hyperlink. So this is an alternative way to get individual commands that you are going to use. Often, when you have to insert, you have to go to a new group to add a new group, and then you can insert it. I'm just going to get rid of this new group. Now you can also create new tabs. So I want a completely new tab. So I'm going to rename this and call this my new tab. And I'll have a group.

All icons must go into a group. So here's my first group, and I'm going to put some really random things in there. So now, when I click OK and go to my new tab here, you can see all of the keyboard menu items that I find important. I don't have to remember the keyboard shortcut. I can just click on any of these and say, "Well, actually, I want this to be a different color, or I want a different sort, or I want to insert pictures." It allows me to have a completely new set of menu items. Now, you may notice that there is one set of tabs that is not checked by default, and that is the Developer tab. There's also the Draw tab, but this is more useful if you have a touch-sensitive laptop where you can actually draw on it.

The Developer tab is not enabled by default. And that's a bit of a shame because there are some nice things in there when you start getting a bit more advanced with Excel. So to add it in, just check the checkbox (the tick box here), click OK, and you'll notice that the Developer tab has now been added. Now, there's nothing here that we need for this particular course, though we will be looking at some of these in this session. But it's nice to have all of the tabs available. So to add new icons, you either go through the QAT or you go to file options, and that allows you to go to the customised ribbon where you can add new menus, new tabs, new groups, and new icons. But for now, I'm going to delete my new tab and get back to the standard set of menus.

4. Assigning shortcut keys

Now, we spoke earlier about how to get a complete list of the shortcut keys that you can use in Excel. But what if you had a favorite command that you wanted to shortcut, but the shortcut key was too long? Well, this is where the QAT, the Quick Access Toolbar, comes in, because each of these icons has its own shortcut key. And to show you, all you have to do is press the Alt key. Now, you can see that each of the menus has been assigned a particular letter, but each of the icons on the Quick Access Toolbar has been assigned a particular number. So suppose you wanted to add an icon. So let's add a new icon. and I'll put it in the center icon.

That would be Alt and five. So if you couldn't work out or you didn't have a keyboard shortcut for center, well, now you can use it. So I can go to here and press Alt-5, and it centers. So let's just add a few additional items. So there you have it. There we go. Only a few items chosen at random. So you notice I've added more than ten items, so I press Alt.

So we have 123-45-6789. And notice what the next one is. Zero nine. Then eight zeros. They become two digits. So you can have the first nine items being convenient shortcut keys and the next few not being that convenient. But this is a way to have easy shortcuts. And just in case you're wondering where the Alt key is on my keyboard, it is here, just to the left of the space. So here we have Control, for instance. And here we have the Windows key. And here we have the Alt key. So this is one method for assigning keyboard shortcuts to your favorite menu items quickly.

Prepaway's MO-200: Microsoft Excel (Excel and Excel 2019) video training course for passing certification exams is the only solution which you need.