Pass Microsoft Excel 77-727 Exam in First Attempt Easily

Introducing the Microsoft Excel User Interface

1. Introducing the Excel User Interface

Depending on which version of Windows you'reusing and how it's being configured, willdetermine where you'll find Microsoft Excel. However, if you go to the Start button and click on this down arrow, you get a list of available apps in alphabetical order. You can see Excel here. Now just click on the icon. If you can't find it here, click the up arrow and then the Search option. Type in Excel, and Windows will locate it for you. Once you have it running, you can pin it to the taskbar by right clicking on the icon here and selecting to pin it to the taskbar. It will always be available from this position. After that, whichever method you use to run Excel, the programme will be loaded onto your screen like so. This is the first screen that you'll see. There's a lot going on here, but for now I just want you to open up a blank Excel workbook by clicking on the blank workbook template locators here. When you do so, you'll be presented with a screen that should be identical to the one that I have. Now, let's explore the user interface for Microsoft Excel. Before we get into the nuts and bolts of the worksheet itself, we need to explore those elements of the user interface that are common to all of the new generation of Microsoft products. These are the ribbon, the file tab, the title bar, the quick access toolbar, the maximize, restore, minimize, and close buttons, the ribbon display options, and the scroll bars. The Ribbon is a collection of tabs, each of which performs a specific group of functions. In Microsoft Excel, we have the home insert page layout, formulas, data review, and view tabs. As you select each of these tabs, you can see that there are groups of functions available within the tab. For example, in the Review tab, we have the option to proof our files by spell checking. We can add comments to the file, and so on. Each of the major sets of functions within a Ribbon Tab is separated into groups. So for example, proofing, comments, and changes are all examples of Ribbon groups. Each Ribbon tab is defined in terms of such groups, and by the time you finish with this course, you'll be very familiar with all of them. All current Microsoft products have the same type of Ribbon Tab interface, so you may already be familiar with this from using Microsoft Word or PowerPoint. That's the nice thing about the current suite of Microsoft Office products. There's a consistency in the interface design between all of the available products, and this cuts down significantly on the learning time involved. The File Tab is different to the others on the Ribbon in that the commands you see on the File Tab are not grouped in the same way as the others. Here you can see that you're offered a set of commands that are mostly to do with saving your work or sharing it with others. Again, we'll look at all of these in due course. But for the moment, I want you to be aware of one in particular, and that is the Save As option. This is a critically important function because it provides you with the means to save your work. Of course, if you don't save your work, it's very likely that you're going to lose it. So right now is a good time for you to save your first file. If you've been following along and have Excel open on your desktop, then select the Save As option and you'll see the following dialogue box: If you haven't been following along, just pause the video and open Excel on your desktop. As I explained at the start of the lecture, this is the Save As dialogue box, and it's a very important feature of all Microsoft products. The first thing you have to specify is where you want to save your work—the location to which the information you're creating will be written. Now, the locations that appear here will differ depending on where you've been saving your work in the past. So my screen will look different from yours. However, you should have this PC option available to select, and when you click on it, you'll get this dialogue box, which allows you to scroll up and down and select from the different locations available on your PC. Right now, I want to save my work to the desktop on the PC. So let's select the desktop. Open this area. We can see that the location selected is this PC desktop. Next, we need to tell you this and what name we want to give the Excel file we're about to create. Let's call this one Helpless Airlines. Now, notice that we have a box here that indicates the type of file we're going to save. The default file type is an Excel workbook, and that's the one I want you to use. Just notice, however, that when I click on the pulldown menu to the side of the box, there are many different types of files that you can use. Later on in the course, we see this in detail later on in the course. For now, just make sure that the Excel workbook type is selected. Now press the Save button; we're brought back to the ExcelWorksheet screen, and notice that the title bar here now has a name that you use to save the file. This is important. The default file name convention for Excel is to use the names book one, book two, book three, and so on.

But no one is surprised by this. Always give your files a name that means something relevant to the work you're going to include in the file. We're going to build a workbook for a company called Headless Airlines, so it makes sense to name it appropriately. Now, at this point, we haven't actually entered any information into the workbook, but it is a file, and it has been uniquely named and is available for use as you add information to the workbook. You don't need to use the Save As option on the File tab. This small disc icon saves your work to the location and file name you've specified every time you click on it. It's a good idea to get into the habit of doing so at reasonably frequent intervals if you're adding a lot of information to the file in order to make sure that you don't lose your work before we complete this lecture. Notice the minimize/restore button. When an Excel workbook is open and you click on this icon, Excel is minimised to the menu bar. Here you can see the icon, and if you click on it, the workbook is once again restored to the screen. This icon is useful and allows you to control the way that the ribbon is displayed. The Auto Hide option allows you to hide the Ribbon from the desktop and give yourself more space to work. So when I click on this option and click anywhere in the worksheet, you can see that the ribbon disappears. When I move the cursor back to the top of the screen, a green bar appears, and when I click on it, the ribbon is once more displayed. The "Show tabs I'm only on" option hides the underlying groups within the tabs, saving even more space. The Show tabs and commands take up the most screen real estate and are the default, but it's useful to know that you can change this display whenever you want.

2. Essential Navigation Skills

Way is to open Excel, and you should see your file listed in the recent list here. Just click on it, and it's open like so. Now let's have a look at what an Excel sheet actually is. The first thing to notice is that the worksheet is comprised of rows, which run horizontally across the screen and are numbered from one to a very large number indeed, over a million. In fact. It's unlikely you're ever going to need them all. When you click on one of the row numbers, notice that the row is highlighted so that you know it is currently selected. Now, on the top of the worksheet, we have columns that go vertically down the screen. There are over 160 columns in an Excel worksheet. Again, you're never going to need them all. But you might be wondering how Excel gives such a large number of columns names. Well, we can see that the columns are A, B, and C, et cetera. And if you use the scroll bar here and just keep clicking on it, then you see that the names become AAAB, AC, and so on. And then we get to BA, BC, etc. So that's the name convention used for the columns in Excel. That's essentially what a worksheet is. Rows and columns where a row and the column intersect are called a cell.So if I scroll back to the start of the worksheet and click on the first cell, we can see that the name I've seen in this area called the "Name box" is "A One." The intersection between row one and Columbus is named in this way. the intersection between the currently selected row and column. When I click here, you can see that the sale is highlighted, and that the cell phone number of the location currently selected is displayed in the Name box. It's fundamentally important to grasp this concept because information is placed into cells and the information in those cells is manipulated using the facilities available in Excel. Everything operates in sales, and you must understand the concept of a cell address. Just to start you off on entering some informationin Excel, click the A One sale and typemarketing plan for 2018 into the sale like thisand then press the return key on the keyboard. You notice that the cursor goes downto the next sell down A two. This is the default way that the cursor moves in Excel.

If you've made a mistake in entering the information, then you just click back into the form and you can amend the information by clicking into the formula bar here, changing the data using the backspace or delete keys, and once again pressing the Enter or Return key. Notice now that the information you just entered seems to be going across the B column as well. Click back into the A One cell and notice that the information you just typed in is also displayed in the formula bar. This bar will always display whatever is in the currently selected cell. So if you click into the B 1 cell, you see that there's nothing in it, even though it looks that way on the screen. The former bar will always tell you exactly what is at the cell address. On many occasions, what you see on the screen and what is actually there will be different. So be aware that the formula bar is very important indeed. Information can seem to span multiple columns and rows, but in many cases it is not actually doing so. Now look at this tab here, which is the worksheet name. The default name for worksheets is Sheet One, and you can add more worksheets into the workbook by clicking on this plus sign. There will be many instances where you'll have multiple worksheets in one workbook. That's the difference between a workbook, which is the file that you save, and a worksheet, which is one spreadsheet within the saved workbook. You can think of them as being like chapters in a book. So remember that the name box tells you where you are currently in the worksheet. The cell address and the formula bar tell you what information is at that address. Remember to click on this icon and save your work as you go along. To move around in the worksheet, you can use a few different methods. So you can click into a cell with the mouse.

You can use the Tab key on the keyboard to tab from Sell to Sell. You can use the directional arrows to move one step at a time. The combination of the control key together with the home key will always bring you back to the Store. Using the control key together with the end key will always bring you to the last sale in the worksheet that has data in it. Finally, a useful one to know is to use the F five-function key, which will bring you to the Go to dialogue box. Now you can just type in the cell address and go to wherever you want without having to scroll. This is a particularly useful one to be aware of. The combination of the Control key and G will have the same effect. We want to prepare the marketing plan for Helpless Airlines from 2018 to 2022. We need to put the years into our worksheets so that you can easily see which year the figures we weren't going to enter refer to. So, set a three, enter 2018, and press the Tab key. Now we're in cell B 3, and we can enter 2019. Press the Tab key for 2020, 2021, and 2022. If you had pressed the return key instead of the tab key, the cursor would have moved into the A four cell rather than the B three cell. Just put it in the correct cells and make sure that your information looks like mine. and also make sure that you hit the save icon. Before we conclude this lecture, I want to mention that Excel uses different cursor shapes depending on where you position the cursor in the cell. It's a good idea to be aware of the different cursor shapes from the outset. For example, if I selected the cell by clicking on it and moved the mouse cursor over the cell, you can see that the cursor is in the shape of a thick white cross. When I move the cursor anywhere on the edge of the selected cell, the shape changes to this four-hour cursor. If I move it to the right-hand corner, the shape changes to this dark black cross, like so. You can also change the width of columns and rows by placing the cursor at the intersection of the row and dragging. Notice the shape of the cursor changing when I move to the intersection of two rows. Now when I hold down the left mouse button, I can drag down to change the width of the row. This is the same for columns, so I can increase the width of column A. And there you can see clearly that there is no data in cell B1, even though it looked as though there were. You can also select an entire row of columns by clicking on the row number or the column letter. Like so. If I want to change information, then I can edit it. So I want to change the text in one to "marketing budget 2018 to 2022." To edit it, I just select the cell, and then now I can edit the contents. By clicking into the formula bar, you can see that the cursor changes shape and blinks at the insertion point. Now I can edit the text.

3. Entering Information

We've seen how the information being entered is seen in two places: in the cell where the data is being typed and in the formula bar. When you press the Enter key, the cursor moves automatically to the next cell down. So now we want to start entering numeric data that is relevant to the marketing plan for 20-17, 20-00, and 20-22. We do so in exactly the same manner as we entered our headings for the worksheet. So we enter 31,000 for 2017, 380 for 2018, 450 for 2020, 52,000 for 2021, and finally 65,000 for 2022. If you use the tab key and not the return or enter key when you finish typing in a number, then the cursor will go to the sales area to the right of the one you're in instead of moving down, so that's some numbered numbers entered. But we need to categorise what these numbers actually mean. And to do so, we're going to learn a couple of new techniques. The first one is the insertion and deletion of rows and cullions. We want to put a category name beside these figures because they actually represent the estimated budgets for television advertising only. The problem is, I've got no room to list the numbers. I need to insert a new column so that I can enter the category title. The way to do so is to select the column that you want to be to the right of where you need to enter the information, in this case, column A.

To select a column, simply click on its title or header, in this case column A. Once the column is selected, I can click on the right mouse button, and you can see that one of the options presented is to insert, and when I select this, everything moves to the right. Notice that the cell references also change automatically. So whereas we have the worksheet heading in cell A 1, it has now moved to B 1. Now that we have the extra space, we can define the category heading for our television advertising. So I click into "A" and type the category of advertising that these numbers represent. Now don't be alarmed when you see that the information you're typing in is making other data disappear from the screen. It's not actually doing that, as we see in a second. Press Enter when you finish entering the text. Now, the figures can be seen, but the text for television advertising is partially hidden. This is simply because the width of Column A is too narrow to display all of the information. So we need a new technique to widen the column. To do this, just position the cursor here between the A and B consumes, and you'll see that the cursor shape changes. Now hold down the left mouse button and drag to the right until you have the width you require.

I'll just drag it back for a moment to show you a second technique. If you once again position the cursor between the two column headings and double-click on the left-most button, the width of the column will automatically change to accommodate the largest piece of information in the column. Like so. There will be times when you want to delete rows of columns. If I select another column, say D, and right click on the mouse, I also have the option to delete a column. You can see that the data I had entered is gone now, because at some point you're going to delete something by mistake. I want to draw your attention to the undo button here. This will undo each action you've taken, one step at a time. very useful to know about when things go wrong. Just to show you, so that you're aware, it works exactly the same when you have a row selected. So I can insert and delete rows in the same way. Now let's put some more categories of expenditure into our worksheet. So we'll have radio in a five, internet in a six, newspaper advertising in a seven, new branch launch parties in a eight, aircraft livery changes in a nine, and starfire in a ten, and that's about it. Change the width of Connie Mae. If you need more space, it will be nice to make the head bigger and eventually look a little bit better. So let's see another new technique called Merge and Center. I would like the heading to be spread equally across the columns from A to G. And I'd also like the text to be bigger. To achieve this, you select the cells from A 1 to G 1. And now click on this button, the Merge and Center button. The selected cells are now all merged together and the text is centered.

Now we can change the font size by clicking here and increasing the size selected. While we're at it, we might as well make the text bold as well. Next, we want the width of Canyon B to be the same as the other year, so I'll change the width appropriately. The budget plan for each of these categories is as shown on the screen. Pause the video and complete your worksheet so that it's exactly the same as mine. Remember to save as you go along. Now, all of these numbers mean something because we know that it's a budget for five years. But what is the currency? We need to show what currency these figures refer to. Because Halpers Airlines is based in Dublin, Ireland, the currency we want to use is the Euro. How do we do that? Well, we're now going to learn another new technique, which is very useful. And once again, it's all about the right mouse button. First, we need to select all the information we want to make available. Euros, up until now we've only selected one cell at a time. Now, we want to select the group of cells starting at B 4 and finishing at G 10. These are where all our marketing data is held. To select a group of cells, first click in Selection, then hold down the left mouse button and drag the cursor to the last cell in Selection, G Ten. So you can see that selected cells are highlighted. Now click the right mouse button, and you can see that there are quite a number of options to choose from. The one I want you to choose is the Format Sales option. This brings up the Format Sales dialogue box. Like all dialogue boxes, I can move this one wherever I want on screen by clicking into the title bar, holding down the left mouse button, and dragging to where I want the box to be. There are a lot of options to choose from here, but the one we want is to use the currency option. This now allows us to choose the way we want the currency to be displayed.

So we can now click on this pull-down menu and select the currency symbol we want. In this case, the euro You can also set the number of decimal places you want the data displayed at. Because we're entering budgetary figures, we don't really need any decimals. You can also decide how you want negative figures to be displayed, so you can choose to have them appear in red if you wish. This Format Sets dialogue box will be used several times throughout the course. Press the OK button. And now the worksheet is beginning to look correct. So now your worksheet should be looking like this. It's getting quite nice, don't you think? Now ask yourself the following question: what is the content of cell B 4? It looks like €31,000, doesn't it? But remember that I told you in an earlier lecture to be careful about assuming that what you see on the screen is actually what is in the worksheet. I click on the cell, but I look at the formula bar. There are no Euros, just the number—that's the content of the cell. The Euro symbol is just that, a symbol. The data are numbers. The formatting that we did was just that—formatting, making the contents look a certain way. but it didn't change the fact that they are just numbers. So be aware that this is an important point because you will have situations as you progress where you'll get unexpected errors that will often be related to this issue. So it's just as well to be aware of it now. Next we want to total all the numbers in each year so that we know the overall annual budget we're planning for our marketing efforts. To do this, I'm going to introduce the concept of using formulas in Excel. First, I'll add in a category total in cell A 11. And then we want to put in our first formula in Excel. This is where Excel comes into its own, doing mathematical calculations and making things easy for the user. Now, there are a lot of numerical formulas in Excel, and at this level we will cover only a small fraction of them. But the approach for using formulas in Excel is pretty much the same, irrespective of how complex the formulas are. Let's just look at this for a minute so you can see that what we want to add up is the data in the sales from b 4 to b 10 so we can see the total amount of the plan budget for 2018. We want the answer to appear in this cell. So that's where we put our formula. You always put the formula into the cell where you want the answer to appear. Now, there's a long way to do this and a short way to do it.

Let's start with the long way so that you will appreciate the difference. All formulas require an equals-two sign first. That's how Excel knows that you're entering a formula. So we have two equals, and now we can do the addition. So we type in B four plus b five, plus b six, plusb seven, plus b eight, plus b nine and plus b ten. And now press the return key. And that's our first formula. And it works if you type in b four and so on. There is a slightly better way, and one which you should get used to doing in Excel, and that is to click on the cell you want to refer to rather than typing Sell reference," which is prone to error. So just remove the contents of beleven by clicking Intimate and pressing Delete, which will remove the contents. Now type the equals sign again, and instead of typing in the cell addresses, click on each one, type the plus sign, and then click on the next one until you have all of these references in the formula. Then press Enter. It's the same formula with the same answer. But it's a better way to put cell references into a formula because it makes it less likely that you'll make an error in typing. But there is still a better way to do this, which reduces all of that typing. and that's used as some function in the formula. Excel comes with hundreds of functions that do preset operations to make life easier for hard-pressed users. But the one we want is the sum function, and we can type this straight into the cell where we want the answer to appear. We still need the Equals to sign. Now we put in the function we want to use, sum. And as you type, notice that a list of all the functions in Excel, starting with the word cell, is displayed. We want the first one.

Now, as with most functions, we must include an opening bracket, such as cell. Then we put in the names of the cells that hold the data we want to add up. Now we could type in the name of the cells we want to include in the edition the way we did before. But that's a slow way. The better way is to select the cells that you want to include in the addition by using their mouths. So start at the first cell and, holding down the mouse button, move to the last cell in the selection. Notice the formula bar here. Excel has put a colon between the first and the last cell in the range we selected. This represents a range of cells, in this case B 4 to B 10. So we'll include all of the cell references in between. Also, now we have to close the brackets, like so, press return, and we have the answer. So that's our first formula. Now we also need tokens for the other years. In our plan, we could repeat the exercise and type the same formula into each of the cells yet again. Excel gives us a quick way to do this and save some time. If you select a cell with the formula and now place the cursor at the right-hand corner of the cell, you can see that the cursor changes shape to this dark black cross. When you're in this position, you can now hold down the left mouse button and drag across the other cells where you want the toe to appear. Now let go. And you can see that the formulas have automatically been entered into the appropriate cells. This feature is called autofill. Excel assumes that you want to use the same formula, and it automatically changes the cell references as you drag to the right. Notice that we aren't changing the row references, only the column references. And as we move to the right, Excel automatically adjusts the column references to take account of this. This is called "relative referencing" because the relative column address is automatically changed. If any of the columns are not wide enough to accommodate the total figures, you will see that these symbols are shown instead of the result. Just widen the column and you'll see the answer like so. For the moment, ignore these small green symbols in the left-hand corner of the cells. Later on in the course, we'll come back to this.

Hide

1.1 Create Worksheets and Workbooks

1. 1.1.1 Create a Workbook

A skill tested in this part of the domain is how to create a new workbook based on a template. We've seen this in a previous lecture, which explored the Excel interface. But now let's ensure that you're fully aware of the requirements for the test. So when you run Excel, you see this screen and icon on the right-hand side, which represent some of the templates available for use. Creating a new workbook from a template is straightforward. You just click on the required template and then click the "Create" button. A new workbook is created. Of course, you'll want to save it by using the Save As button on the File tab. So go to the File tab, select Save As, specify the location to save the file, give it a name here, and optionally specify the file type by selecting from the pull-down menu. This generally is not required, as the default is an Excel workbook. It's as straightforward as that. However, in the test, you will be asked to create the workbook based on a specified template, which may not be visible on the screen. So you need to be able to navigate to where that template is available. You do this by entering the name of the template into the search box.

Be careful to type in the name specified in the question. So, for example, if you are asked to create a new workbook based on the Expense Report template, then that is what you type into the search box. Now press the return key, and Excel will search for the available templates. You can see that it's found quite a few because there are a number of them that have the words in the title or the words in the description for the template. Notice here that we have one called "Expenses Report," the plural version, and one called Expense Report, singular." These are different templates, and you have to be careful to select the correct one as specified exactly in the question posed in the test. So the Expense Report Singular is the one I showed you based on the question. After that, it's always just the process of creating the workbook. By clicking the Create button in the exam, you most likely will not be asked to save the workbook you've created. You're being tested on whether you know how to create the workbook, but of course you should by now be comfortable with the Save As dialogue box and how to save your workbooks. You.

2. 1.1.2 Importing Data from a Delimited Text File

Domains one and two require that you be able to import data into an Excel workbook. If you're new to Excel, you may not understand the point of actually doing this in the first place. The main reason is that there will be many situations where companies buy data from outside organisations who specialise in collecting information, such as customer profiles, email addresses, contact details, and so on. Then they sell this information to other companies who may wish to market their products or services to those individuals. So these companies purchase the information. The data that is purchased may not be in a format that is immediately recognisable by Excel.

And so you need to be able to import the information and alter its format to be compatible with that used by Excel. On the Data tab, we have a group called Get External Data. There are quite a few options to choose from here, but the one I want to concentrate on is the From Text option, which is examined very regularly in the exam. The From Text option will provide you with a wizard that guides you through the import process. Just to make sure that you understand what the text file actually is, I'm going to create one using Microsoft Word, so I can just type in some names, first name, and surname, and I'm going to separate the elements of the names using commas. At the end of the surname, I'll use the return key to move to the next line in the file. So here we have the information typed in, and now we want to save the file as a text file. So I'll give the file a name and location, and now I select the plain text option from the Save As pull-down menu. When I press okay, we'll have a text file saved, which is delimited by comments. In other words, each of the pieces of information we want to recognise is separated or delimited by a comma. Each row would be recognised by the character that represents the return key. Now we can go back to Excel and start the import process. So I go to the data tab and the GetExternalDataGroup, from which I select the text option. Now I can navigate to where I created the text file. And when I select this, I'm brought into the Text Import Wizard dialogue box. You can see that the first thing you have to tell Excel is how the data you want to import is delimited.

Well, we've seen that we use commas to deliver the data, so I'll select that. You can see here that you could also use the topkey rather than a comma to deliver each field of information. Field is just a term used to describe each end of the data we're importing. In our example, the first name is Afield and the surname is a field.If the data is fixed width, it means that each field in the original text file was given a set number of characters to fit into, which is not very effective in terms of using memory on the computer because you'd be leaving a lot of blank space in many cases. So it's far more usual to use delimited textfiles such as the one I created earlier. So we specify commas as the delimiter. Sometimes a text file will have a header that describes the information, such as first name, comma, and surname, in which case you need to check this box.

In most instances, there won't be one. Now we press the next button and proceed to step two of the process. Here you can specify which cylinders are used in the text file. You can select more than one type of delimiter. We use the comma, so I'll select that. The data preview section shows how the data will appear on the worksheet using the delimiter you've selected. So we can see that there will be two kinds of data, one for the first names and one for the surnames, which is exactly what we want. Next we get to the third and final step in the process. Here we can select each column in turn and specify the type of data that can be expected in each. The general option will normally survive since it converts a number into a number, anything which looks like a date into a date, and treats all of the data as text. You also have the choice to be specific about each element if you wish to do so, and that's the way that you import a text file. In the exam, you will be told how the file is delivered, and all you have to do is follow the steps in the cases in the Wizard.

3. 1.1.3 Add a Worksheet to an Existing Workbook

Create a new workbook with one worksheet and name it the same as the one associated with the template. But you can add and remove worksheets from your workbooks. The number is only limited by the availability of memory you have on your computer in this part of the domain. You're asked to create a new worksheet in an existing workbook. You'll also be asked to position the new worksheet in a specific position. So let's add some worksheets into our new expense report workbook. All you have to do is click on the plus symbol, and a new blank worksheet will be created. Note the name and convention. The original one was named after the template name. Each of the new ones is named Sheet One, Sheet Two, and so on. Worksheets can also be repositioned within the workbook by selecting the one you want to move and dragging it to the new position required.So if I want Sheet Three to be the first worksheet in the work book, I just click on it. Selected: hold down the left mouse button and drag to where I want it to be positioned. You can see that as I drag it, a small black indicator shows you where it will be positioned when I let go of the mouse button. You can also insert a new worksheet by selecting Insert from the right-mouse button. Options: In this case, you'll be presented with a number of different options. The only one you need to be awareof at this point is the worksheet option. A new worksheet is inserted and is named with the next available default sheet name. in this case, sheet four. Because we already have used Sheet Twoand Sheet Three and it's simple ass.

Hide