Level 2, Section 1 - Navigate through Excel worksheets and workbooks

1. Searching for data within a workbook

Hello and welcome to level two. And in this level, I'm going to be using a particular workbook, and I've included this as a resource at the beginning of this level. So if you click on your videos, you'll be able to see them and download them there. And this is a catalogue of my music and the reason for including it. It's not so much that I want to show everyone what sort of music I listen to, but because there are so many different types of data here. We've got text, we've got numbers, and we've got dates. There is a lot we can search for and manipulate. And we've got these 13 rows. Go up to column M and scroll down. We've got something like 865 rows. So we're going to be using this quite a lot. Now the purpose of this particular video is to find some text. And you can imagine there are lots of ways of finding text, and I'll be using some of them in later levels. But for now, I just wanted to show you the simplest way, and that is to find a dialogue box. Now you can find this dialogue box. If you go to home editing, find and select, there is a find dot, dot, dot. But you can also see when I hover over some of these commands, you can see that there are shortcuts available as well. So we find for the Find dialogue box that CTRL and F will also work. And that's the one I usually use CTRL F for Find. So when I click on this or type CTRL and F, we get this dialogue box. Now we can search the entire spreadsheet or workbook for what we're looking for. So as a default, we're just going to look at this one particular spreadsheet. So what should we look for? Well, let's look for the word "symphony." So I'll type what I want to find in the Find What section and click Find next. So here we get to our first instance in cell Bfive and I'll click Next, Find Next, Find Next and you can imagine there are an awful lot of them. So what I can also do is click find all. So this finds all 70 occurrences of the word "symphony." And I can expand this dialogue box, and you can tell any dialogue boxes that could be expanded because they have these six dots at the bottom right hand corner in a triangle. So I can see cell B five has got two symphonies, cell B ten, eight, symphony number one. So there are all these dollar signs. We'll get to all of these dollar signs just before the end of this level, but just ignore the dollar signs just for the moment. Now if I click on any of these, they are hyperlinks. They take me to that very specific cell. So not only can you see the cell itself, the results, but also its context. All the surroundings. So you can see, for instance, if they are being driven by text or a formula. Now there's a lot more in this dialogue box, and we will come back to it in later levels. But for now, if you want to find various contexts, you go to Find and select Find or you press Control and F. But what if you're not using the English language? What if you're using French or German? It's the same shortcut key as Control and F. So, for instance, if I look upon Internet keyboard shortcuts for Excel in German. So here we have a list of German shortcuts. If I put in the word Suhan, which is a German word for find and must include the word Suhon, you can see Tastan combines. I can't pronounce German correctly,but these are keyboard combinations. And further down, you can see that control and F are for find. Even though the word sucker does not have the letter F, the keyboard shortcut generally remains the same. So even if all of this was in a foreign language, you should still be able to navigate Excel quite usefully by using the shortcut. So CTRL and or for open, and K for hyperlink. The last letter of K and Control Pin Durham is the word for print. So even though there is no letter P in Durham, it is still CTRL and P to get to the print screen. So, if you find yourself suddenly confronted with an Excel document written in a different language, you should still be able to use the vast majority of the keyboard shortcuts.

2. Hyperlinks

Now in the last video, we had a look at the Find dialogue box and you could see that we could click on these various answers to go to different places. Now these are called hyperlinks, and you might want to add a hyperlink of your own to any particular document. For example, let's suppose I want a hyperlink here that says "Click here, go then there" to sell a 101. And so when I click that, I want the cursor to go to sell a 101. So I need to turn this into a clickable hyperlink. So which of these menus do you think I might need to insert a clickable hyperlink? It's insert and then I can go to link and insert link. So this inserts a hyperlink and it could be a hyperlink in an existing file page or on an existing webpage, but I don't want that. I want to go to a place in this existing document. So if I click on a place in this document, I can now say what cell reference I want to go to. So I'm going to put 10 up for sale. So this is the text that I want displayed. This is the cell reference I want to use. Or you could go to a different spreadsheet, for instance, within this workbook. So let's click OK, and now when I click the hyperlink the curse goes down to a 10 out of 10. There are additional things that you can see in this link; a dialogue box. I'll need to navigate to the cell to get there. I can't just click the cell, otherwise I'll go straight down to Ear 101, go to Insert link, so I could go to different spreadsheets, I could go to different names. We'll have a look at names a bit later on in this course. Now it could be that you've got a much shorter text. Suppose the text is click here and you want to give a bit more clarity as to what this does. Well, you could add a tool tip called a screen tip. So, clicking here goes to sell a 101. So that is the Edit Cell Screen tip. So now if I hover over that, you'll see that I've got my little dialogue, my little message. Clicking here goes to cell 10 one. Now it could be that I want to go to a completely different document. It doesn't even have to be an Excel document. So let's suppose I wanted to go to my music list. Well, I can go in, insert a link, and I want to go to an existing file, a web page, and I will go back to my music list that I had earlier. So I'll just click on that and insert a link so you can see the address at the bottom of the screen. It goes down a level, goes into level one,session two, and then clicks on the music list text. Obviously, for this to work, somebody would need to have access to this particular document. But when I click on "Music List," it opens up my music list in Notepad. Additionally, I could open up various web pages. Suppose I want to have a hyperlink to Google, so if I go to Insert Links, insert Link, then I can go to an existing webpage www.google.com/search and press the OK button. So now, when I click on that link, my internet browser opens and takes me to the Google homepage. There are numerous applications for hyperlinks. opening a Microsoft Word or Notepad file, or going to a completely different location, such as a web address So this is how to insert hyperlinks, which is Insert Link.But there is a shortcut for this, which is control, which is the last letter of the word hyperlink.

3. Using Go To and Using Name Box

Now, hyperlinks are good if you have to go to a very specific location or if the end user has to. But what if you want to go to a place just a one off?Well, there are several ways. First of all, you can scroll and scroll or you could use a specialised Go To box, and it's so common that it's in the home section. Home Find and select Go, and you can see what's faster than scrolling and jumping. So just type in your reference. If I wanted to go to a 201, I would click OK in the Go To box and you're there. Another way of doing this is by going to the name box up here in the top left and typing where you want to go, a 301 Enter. Now, the keyboard shortcut for getting the Goto box is ControlG, but there's also the F5 button on your keyboard that gets you to the Go to box as well. And we also see places you have recently been. Now, if you are looking for a particular type of thing, not necessarily text,there's another Goto called Goto Special. Or you can go into Finer, select Goto, and click on the Special Daughter dot button on the bottom left. So here you can say, "I want to find any comments." We'll have to talk about comments in future levels or any precedents, what a formula is dependent on or dependent on, what is reliant on this folder. Or you can go to the very last spot that is used. So if I click that and click okay, you'll see that we go from our current location all the way down to M 865. So we've gone all the way to the right as much as we've used, all the way to the bottom as far as we've done. Now, I don't actually use this dialogue box that often because there are generally other ways of doing this and I use other ways. For instance, there are other ways of displaying precedents, other ways of finding dependence. And I'll be looking at some of these in the expert class. But for now, if you want to find a particular file type, a particular cell type, for instance, if I wanted to find all formulas, you can click on it and click OK, and it will highlight all of the formulas that are being used. So, if you wanted to, you could change the color, underline, or font to emphasise the fact that they are formula. But suppose I wanted to find all of the constants that are nonformulas, or maybe I just want to find just numbers, so that can be done as well. Now, if you've highlighted all of these ranges,that is what more than one cell is. You can do it as well. You can highlight a range yourself by starting at a particular cell, holding downshift, and using the arrow keys. This highlights a cell range. And you can see how big the range is over here in the name box. So, currently it is nine rows and five columns. So that's another way of just highlighting a group of cells, which is called a range. Well, now you've got a particular range highlighted, another way is to just click on the first cell, make sure it's not DoubleClick, make sure you're not editing it, and then drag the range. That's another way you can now use the find and go to just look for that particular section. So suppose I was looking just for the word "violin" in column B from B five to B twenty. Well, that will just find it once, even though it is contained elsewhere in the spreadsheet. And similarly, any goto specials will be restricted to your range that you have highlighted. So maybe you wanted to highlight all of the numbers that are used in a particular range. Well, you can do that. It's not something that I often do, but it is possible. So I use go from time to time, but most of the time you use go to go to a certain place. Then I have to scroll to it or type it in the namebox. But it's nice to know that all of these options are available in case you need them.

4. Hot Keys

Now, in the previous few videos, I've been talking about shortcut keys. For instance, the shortcut key for the Find dialogue box is CTRL and F. And the shortcut key for Go is CtrlG. Well, how can you find a list of these dialogue boxes? Well, the quickest way is to just google keyboard shortcuts. Excel. I generally stick with Microsoft websites. So here you can see various keyboard shortcuts and there is a huge list that you can use. And I don't use this. I probably won't even use a tent for this. Some of these are really old commands. For instance, if you click on Alt, you'll get a list of other letters that you can click on, like M for formulas, which then gets another list. Why is this? Well, this is a fallback to the Excel 2003 version. When the delete command was hidden away, you had to click on a menu, then click on another menu, and it was there and it was very confusing. But it was perhaps a bit easier if you could remember the keyboard shortcut. Now, I don't recommend that you do any of these complicated ones. Instead, I suggest you have a look at the keyboard shortcuts that could be of interest to you. Just half a dozen. Half a dozen or so. ones that could be most useful for you. Like, for instance, Control and Bapplies or removing bold formatting. It's easy to forget. For bold, use CTRL and B. And I'll be mentioning shortcut keys that I think would be useful for you. So I mentioned Control for Control, G for Go to Control, and K.K for the final letter of the hyperlink. There are some here that I use. I think that A) I wouldn't be able to remember them, and B) I would just use the mouse. So yes, I could be a bit quicker if I remembered that Control and Shift and Ampersand apply the outline border. But would I really want to memorise a hundred or so different commands? I don't need to. I'm probably happy with the ones that I've memorized. A dozen, two dozen, probably, but not much more than that. And if there was something you did want,well, just hover over a particular command and you'll see what the keyboard shortcut is. So memorise the ones you think will be useful and don't memorise the bye.

ExamSnap's Microsoft MO-200 Practice Test Questions and Exam Dumps, study guide, and video training course are complicated in premium bundle. The Exam Updated are monitored by Industry Leading IT Trainers with over 15 years of experience, Microsoft MO-200 Exam Dumps and Practice Test Questions cover all the Exam Objectives to make sure you pass your exam easily.